1. 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.
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.