Insertion, Deletion, and Modification Anomalies Assignment | Homework For You
February 17th, 2020
(b) The table 1 below lists dentist patient appointment data. A patient is given an appointment at a specific time and date with a dentist located at a particular surgery. On each day of patient appointments, a dentist is allocated to a specific surgery for that day.
Table 1: staffNo dentistName patopatName appointment datetime Surgery No S15 S1011 S1011 S1024 S1024 S1032 S1032 Tony Simth P100 Tony Smith P105 Helen Pearson P108 Helen Pearson P108 Rolin Plevin PIOS Robin Plevin P110 Gillian White 12-Sep-01 10.00 SIS Jill Beli 12-Sep-01 12.00 Ian MacKay 12-Sep-01 10.00 SIO lan Mackay 14-Sep-01 14.00 SIO 14-Sep-01 16.30 John Walker 15-Sep-01 18.00 Bell EXAMINER: GEORGE KODJO ANNI Page 2 of 4
(1) The table shown above is susceptible to update anomalies. Provide examples of how insertion, deletion, and modification anomalies could occur on this table.
(11) Identify the functional dependencies that exist between the columns of the table and primary key (PK) and any alternative key(s) (if present for the table)
(iii) Using the functional dependencies identified in
3.b.(1), convert the table to 3NF. In each of the derived tables in (3NF) indicate the primary keys and the foreign keys. (18 marks] (Note: Show tables with values. Get Finance homework help today
