Q1
The ER model, provided below, contains e
ors and is incomplete. You need to identify the e
ors and omissions, rectify them, and then redraw the diagram with co
ect notations so that the redrawn ER model illustrates the entities, attributes, and relationships for the CovidTrack data model with the following requirements: Note: Please use Disjoint
· A patient possibly has contacted many people, who are called closely
contacted persons. The entity name is CloseContactPerson in the diagram.
· Each closely contacted person may have contacted more than one patient.
The description of the contact process and date/time must be recorded.
· The full name, address, phone, date of birth, email (possibly multiple emails)
are required to be recorded.
· A patient should undergo at least one test of the virus, possibly many tests.
· The details of the virus test including test method, result, date and time, as well as the technician names are needed to be recorded.
· The patient can be categorized into two types – home patient and resident
patient. The former stays home, while the latter refers to a patient who
resides in a hospital. Each category has its specific data item such as
isolation description or admitted date to be added.
· The patient, the closely contacted person, and the test are identified by their
respective IDs.
Note: If you have any additional assumptions, mention them along with your redrawn diagram.
XXXXXXXXXXThe relationships should include the co
ect cardinality notations.
CLOSECONTACTPERSON
XXXXXXXXXXPersonID
XXXXXXXXXXFullName
……………………
ER model(incomplete):
XXXXXXXXXXPatient
XXXXXXXXXXPatientID
………………
Test
TestID
……………
ResidentPatient
HomePatient
Converting ER models
After your co
ections of ER model given in Question 1 of Part A, convert your co
ect ER model into a set of relations that satisfy Third Normal Form (3NF). You do not need to show your workings. You do not need to justify that they are in 3NF at this stage. You do not need to show sample data. Just show your relations. You should write your relations in either format shown below:
Student (StudentID, StudentName, DateOfBirth)
Enrolment (EnrolmentID, StudentID, DateOfEnrolment)
or:
Student (StudentID, StudentName, DateOfBirth)
Enrolment (EnrolmentID, StudentID, DateOfEnrolment)
Foreign key (StudentID) references Student
Question 3 XXXXXXXXXX5 Marks
Relational model and Normalisation
The following table is designed to include patient, drug, and prescription information for a database to be used in a pharmacy. But this relation has been inco
ectly designed. It has been assumed that the ID attribute uniquely identifies each tuple in this relation.
Prescription
ID
Date
Drug
Dose
Vial
PatID
FirstName
LastName
Phone
1
1/2/22
Atropine
1mg
1ml
1
Kim
Gordon
XXXXXXXXXX
2
1/2/22
Digoxin
0.5mg
1ml
2
Mary
Smith
XXXXXXXXXX
3
1/2/22
Dobutamine
5mg
10 ml
3
David
Ho
XXXXXXXXXX
4
7/3/22
Digoxin
0.5mg
1ml
2
Mary
Smith
XXXXXXXXXX
(a) In the above table, if deleting the information of the patient – David Ho, justify what anomaly will happen. XXXXXXXXXX1 mark)
(b) Identify functional dependencies in this relation. XXXXXXXXXX1 mark)
(c) What is the highest normal form that this relation satisfies and why?
XXXXXXXXXX1 mark)
(d) Normalise the relation into a set of relations that satisfy 3NF using the format as follows:
Customer (CustomerID, CustomerName)
Order (OrderID, Amount, Date, CustomerID)
XXXXXXXXXXForeign key (CustomerID) references Customer