Normalisation (Total Marks 45)
The following relation lists doctor/patient appointment data in a relational database for the Bruce Medical Centre (BMC).
| DoctorId | DoctorName | PatNo | PatName | ApptDate | ApptTime | RoomNo |
| S111 | Sonia Gandhi | P103 | Mark Grade | 12/08/2015 | 10:00 AM | R15 |
| S111 | Sofia Gandhi | P108 | Jill Kant | 12/08/2015 | 12.30 PM | R15 |
| S124 | Helen Smith | P111 | Mark Dunn | 12/08/2015 | 10.00 AM | R10 |
| S124 | Helen Smith | P111 | Mark Dunn | 14/08/2015 | 2.00 PM | R11 |
| S132 | Robin Duong | P108 | Jill Kant | 14/08/2015 | 4.30PM | R15 |
| S133 | Robin Redbrest | P113 | Paul O’Smoke | 15/08/2015 | 6.00 PM | R13 |
It is assumed that BMC has several doctors and several rooms for them to consult with patients. On each day of patient appointments, a doctor is allocated to a specific room for that day. However on a given day a room may be allocated to more than one doctor but at no time would two doctors sit together in the same room.
Each appointment is of 30 minutes duration. A patient can have an appointment with any of the doctors in BMC. A patient cannot have two appointments in BMC on any given day.
Requirements
- The above table is subject to update anomalies. Using the data in the table, provide examples of insertion, modification and deletion anomalies. (15 marks)
- Identify the candidate keys of the above relation, and select one of them as the primary key. (7 marks)
- Identify the functional dependencies on the alternate keys as well as the primary key. (5 marks)
- Using the functional dependencies identified in part (c), normalise the above relation to 3NF showing any Foreign Keys. (No need to include data in the normalised tables). (18 marks)
For a custom-written paper, place your order now
What We Offer
• On-time delivery guarantee
• PhD-level professionals
• Automatic plagiarism check
• 100% money-back guarantee
• 100% Privacy and Confidentiality
• High Quality custom-written papers