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.

  • 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

Bruce Medical Centre

Leave a Reply