5. The table shown below lists sample dentist/patient appointment data. A patien

Question

5. The table shown below lists sample dentist/patient appointment data. A patient is given an appointment at a specific time and date with a dentist located at a particular office. On each day of appointments, a dentist is allocated to a specific office for that day. (20 pts.)



Staff No Dentist Name Pat. No. Pat. Name Appointment Office

Date Time



S101 T. Smith P100 G. White 9/12 10:00 L15

S101 T. Smith P105 J. Bell 9/12 12:30 L15

A264 M. Sagaria P108 I. McKay 9/12 10:00 L10

A264 M. Sagaria P108 I. McKay 9/14 14:15 L10

V555 R. Plevin P105 J. Bell 9/14 16:30 L15

V555 R. Plevin P110 P. Wax 9/15 18:00 L13





A. Give examples of insertion, deletion and update anomalies with the above table.

B. Convert the table to 3NF. Represent your answer in DBDL. (i.e. Give table name and fields. Underline the primary key. Draw an entity-relationship diagram showing all relationships.)



Identify any assumptions you made that will assist in my understanding of your solution.

Explanation / Answer

(a) - Cannot Insert Patient details unless a dentist exists - If you delete dentist M. Sagaria then you delete appointment details for I. McKay - If T. Smith is replaced by a new dentist -two records have to be changed. (b) 1NF StaffNo, Date, Time, DentistName, PatientNo, PatientName, SurgeryNo FD1 StaffNo, Date, Time ? PatientNo, PatientName FD2 StaffNo ? DentistName FD3 PatientNo ? PatientName, SurgeryNo FD4 StaffNo, Date ? SurgeryNo FD5 Date, Time, PatientNo ? StaffNo, DentistName 2NF (Note. FD2 and FD4 violates 2NF) StaffNo, Date, Time, PatientNo, PatientName StaffNo, DentistName StaffNo, Date, SurgeryNo 3NF (Note FD3 Violates 3NF) StaffNo, Date, Time, PatientNo, StaffNo, DentistName StaffNo, Date, SurgeryNo PatientNo, PatientName, SurgeryNo

Related Questions

Navigate

• Browse (All)
• Browse 5
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.