Normalisation (Total Marks 45)
The following relation lists doctor/patient appointment data in a relational database for the Bruce Medical Centre (BMC).
|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.
- 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