@Questions Answers needed(Photo Screenshot after writing in a paper also acceptable)
Q1 The ER model, provided below, contains errors and is incomplete. You need to identify the errors and omissions, rectify them, and then redraw the diagram with correct 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. The relationships should include the correct cardinality notations. CLOSECONTACTPERSON PersonID FullName …………………… ER model(incomplete): Patient PatientID ……………… Test TestID …………… ResidentPatient HomePatient Converting ER models After your corrections of ER model given in Question 1 of Part A, convert your correct 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 5 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 incorrectly 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 0411098123 2 1/2/22 Digoxin 0.5mg 1ml 2 Mary Smith 0422098555 3 1/2/22 Dobutamine 5mg 10 ml 3 David Ho 0456133009 4 7/3/22 Digoxin 0.5mg 1ml 2 Mary Smith 0422098555 (a) In the above table, if deleting the information of the patient – David Ho, justify what anomaly will happen. (1 mark) (b) Identify functional dependencies in this relation. (1 mark) (c) What is the highest normal form that this relation satisfies and why? (1 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) Foreign key (CustomerID) references Customer