Take home Course Work for BIT2-2024.

Attempt all questions and we shall be presenting our solutions on 8th April 2024 in office 4
science block at 5:00pm.
Question One: Area Examined: Analyzing and solving real-world problems with database
solutions.

Scenario: Cervical cancer is a dominant cause of cancer-related mortality among women

globally but stands out as one of the most preventable types. Its early detection through regular
screening, and the identification of precancerous lesions for timely treatment, can drastically
mitigate its impact. However, the inherent challenges in managing cervical cancer screening data,
encompassing test results and subsequent appointments, are significant. Current systems are
often expensive, overly dependent on experts, and inefficient. A streamlined database system can
offer healthcare professionals a comprehensive solution, facilitating efficient management of this
pivotal data and ensuring patients receive timely and appropriate care. Enhancing data
management can significantly elevate the quality of care, potentially reducing cervical cancer
fatalities. Imagining yourself as a pivotal member of the team addressing this issue:

a. In light of the gaps in existing systems, which database system would be the most
appropriate to address these challenges, and provide your reasoning? [2 marks]

b. Please describe two functional and two non-functional requirements of this proposed
system. [4 marks]

c. Suggest three data compilation methodologies appropriate for gathering system

requirements and justify each of your choices. [3 marks]

d. Identify and elaborate on three primary stakeholders associated with this system. [3
marks]
e. Considering traditional flowcharts offer a rudimentary depiction of processes, design a
flowchart to elucidate the system's workflow to a nurse? [4 marks]

f. Draw a meticulously normalized Enhanced Entity Relationship Diagram, incorporating at

least four tables, to outline your conceptualization of logical data storage in this system. [4
marks]

1|Page
Question Two: Area Examined: Utilizing Data Definition Language (DDL) and Data
Manipulation Language (DML) Commands in Database Management.
a. Utilize Data Definition Language Commands to Address the Following Tasks
i. Create a table named Patient with the following fields: PatientID (as the primary
key), FirstName, LastName, DOB, and EmailAddress. Ensure EmailAddress is
unique. [ 1mark] ii. Add a column named PhoneNumber to the Patient table.
[1mark] iii. Rename the Patient table to HospitalPatient. [1mark] iv. Drop the
column DOB from the HospitalPatient table. [1mark]

v. Create an index on LastName in the HospitalPatient table for faster

queries.[1mark]

b. Utilize Data Manipulation Language Commands to Address the Following Tasks:

i. Insert records into the HospitalPatients table for three patients of your choice.
[1mark] ii. Update the EmailAddress of a patient whose PatientID is 2. [1mark]
iii. Delete the record of a patient whose PatientID is 3. [1mark] iv. Retrieve the
FirstName and LastName of all patients who have an EmailAddress ending with
"@gmail.com". [1mark]

v. Count the number of patients in the HospitalPatients table. [1mark]

c. Utilize Advanced SQL Operations to Address the Following Tasks:
i. Create a table named Appointments with fields: AppointmentID, PatientID (as a
foreign key to HospitalPatients), DoctorName, and AppointmentDate. [2 marks]
ii. Using DML commands, insert three records into the Appointments table. [2
marks]

iii. Retrieve the names of patients who have an appointment on a date of your choice.
[2 marks] iv. Update the AppointmentDate for a specific
AppointmentID. [2 marks]

v. Write a query to join the HospitalPatients and Appointments tables on PatientID,

retrieving patient names along with their respective appointment dates. [2 marks]

Question Three: Area Examined: Utilizing Data Definition Language (DDL) and Data

2|Page
Manipulation Language (DML) Commands in Database Management

You are a database developer for Mbarara University’s Academic registration department. The
university wants to create a new system to manage courses, students, and enrollments.
Entities to consider:
1. Student: Attributes include StudentID (unique identifier), FirstName, LastName,
DateOfBirth, and Email.

2. Course: Attributes include CourseID (unique identifier),

CourseName, CourseDescription, and Credits.

3. Enrollment: This represents which student has enrolled in which course. Attributes
include EnrollmentID (unique identifier), StudentID (foreign key), CourseID (foreign
key), and EnrollmentDate.

Tasks:
a. Use Data Definition Language commands and create a schema for these tables including
primary and foreign keys. [3 Marks]

b. Write SQL queries to:

i. Add a new student to the Student table. [ 1 Mark] ii. Enroll a student in a
course, updating the Enrollment table .[2 Marks] iii. List all students enrolled in a
specific course .[2 Marks] iv. List all courses a particular student is enrolled in.[2
Marks] v. Update a course description in the Course table.[2 Marks]

vi. Remove a student's enrollment from a course.[2 Marks]

c. The university has a policy that a single course can't have more than 100 students. Write a
trigger to enforce this policy, ensuring that when an enrollment attempt exceeds 100
students for a course, it is denied .[4 Marks]

d. Considering the university's needs, suggest one index you might add to improve query
performance and explain your choice .[2 Marks]
Question Four: Area Examined: Utilization of SQL Views and Joins
Mr Beinomugisha Micheal, the esteemed proprietor of A+ Fashions Statement, a renowned
boutique situated in Mbarara City, has approached you for assistance. He has presented

3|Page
 particular tables (Sales and Customers) from the A+ Fashions Statement database. These tables
are crucial for a task that Ms Igga Judith, his co-proprietor, assigned him. He requires your
expertise to navigate and complete this task adeptly.

Here is the assignment,

i. Write an SQL query to introduce a new column, designated as 'product name', to the 'sales'
table.[2 Marks] ii. Design a unique Index titled 'customer_index' for the 'sales' table,
specifically on the

'SalesId' field. This index should optimize the speed of data retrieval.[4 Marks] iii. Mr.
Beinomugisha Micheal needs an SQL query that fetches the First Names, Last Names, and Sales

Amount for all customers with at least one purchase to their name.[4 Marks] iv. Ms. Igga
Judith wishes to have a daily glance at customers making purchases. For this, create a view
named 'daily_Customers' that will simplify this process for her. The view should display
customers' First Names, Last Names, and Sales Amount, all sorted according to their Date of
Birth. [6
Marks]

v. Following the presentation to Ms Igga Judith, in the presence of the Director, Mr.
KIPROTICH PHILIP suggested a modification. He desires the 'daily_Customers' view

4|Page
 to be updated and also display the customers' e-mail addresses and the DOB. Write a
query that enacts this modification.[4 Marks]
Question Five: Area Examined: Utilization of SQL Triggers
The Cyber College of Technology (CCT) in Uganda, established in 2000, is known for its
research and education in various disciplines. Adopting the European polytechnic model, it
emphasizes hands-on laboratory instruction. Although it's traditionally known for physical
sciences and engineering, it now excels in biology, economics, and management. Recognized
among the world's top universities, CCT requires prospective students to undergo aptitude exams,
admitting only those who score 80 or above. For the academic year 2023-2024, Byonanebye
Alosius, a prospective student, failed to meet the required score. However, upon receiving his
results, he hacked the database to alter his marks, as illustrated in the subsequent tables.

Table “studentsmarks2018” Before the hack on the database

Table “studentsmarks2018” After the hack on the database

As a matter of fact, the database administrator at Cyber College of Technology (CCT) had
already implemented measures to fight such unauthorized activities and they were able to recover
from such unauthorized activities as the seen figure below;

5|Page
Upon learning about the remarkable achievements of the solution at "Cyber College of
Technology (CCT)," the Kigali College of Developers approached Mr Ambrose Izaara, the
Research and

Innovations Coordinator at FCI-MUST. They sought his recommendations for skilled students
who could train their database manager, Mr. KISAMBIRA ABEL JORUM, in specific concepts
as listed below.

a. What are SQL Triggers? [1 mark]

b. Using examples, elucidate the different scenarios where SQL Triggers might be employed.
[4 marks]
c. Differentiate between BEFORE and AFTER triggers and Explain Under what
circumstances might one be favored over the other? [4 marks]

d. Given the context of the Cyber College of Technology (CCT), create the structure for a
table named 2018markschanged where data is inserted post-trigger execution. [3 marks]

e. Create a trigger for the Cyber College of Technology (CCT) named 2018markschange.
Ensure that this trigger is invoked BEFORE an UPDATE operation is performed on the
relevant table. [5 marks]

f. Given the situation at Cyber College of Technology (CCT), create the SQL query that
would have been used to update the studentsmarks2018 table. [3 marks]

Question Six. Areas Examined: Stored Procedures as applied in SQL

A stored procedure is a set of SQL structured Query Language (SQL) statements with an
assigned name, which is stored in a relational database management system as a group and
executed as a block. As Mr Joel Natubu was interviewed on the position of being a Database
administrator at Mbarara University of science and technology, he was assigned by the Human

6|Page
resource manager to develop a database to store information about staff members. Using the table
below, help him to complete his assignment and be able to get the job, below is the staffInfo
table

a. Explain why you will recommend Mr Joel Natubu to use stored procedures in his
assignment
(4 reasons). [4 marks]

b. Explain to him the difference between a stored procedure and a stored function. [2
marks]
c. Using an example, explain to him the syntax of a stored procedure and how it can be
executed to perform the set function. [4 marks]

d. Write a stored procedure that will be used to create accounts for each staff mmber. It
should perform the following;

i. Input the First name, Last name, Gender, Address, Year of birth, address, salary
[2 marks]
ii. Compute the age based on the year of birth and the current year. [3 marks] iii.
Compute the final salary after being taxed 5% [3 marks] iv. Insert the result
in a table called staffInfo. [2 marks]

End

7|Page