段江涛《数据库原理》第8章作业.docVIP

Chapter 8 Normalization - Exercises 1． The table shown in Figure 8.9 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. staffNo dentistName patientNo patientName appointment date time surgeryNo S1011 Tony Smith P100 Gillian White 12-Aug-03 10.00 S10 S1011 Tony Smith P105 Jill Bell 13-Aug-03 12.00 S15 S1024 Helen Pearson P108 Ian MacKay 12-Sept-03 10.00 S10 S1024 Helen Pearson P108 Ian MacKay 14-Sept-03 10.00 S10 S1032 Robin Plevin P105 Jill Bell 14-Oct-03 16.30 S15 S1032 Robin Plevin P110 John Walker 15-Oct-03 18.00 S13 Figure 8.9 Details of patient dental appointments. The table shown in Figure 8.9 is susceptible to update anomalies. Provide examples of insertion, deletion, and modification anomalies. Describe and illustrate the process of normalizing the table shown in Figure 8.9 to 3NF. State any assumptions you make about the data shown in this table. 2． An agency called InstantCover supplies part-time/temporary staff to hotels throughout Scotland. The table shown in Figure 8.10 lists the time spent by agency staff working at two hotels. The National Insurance Number (NIN) is unique for employee. NIN contractNo hoursPerWeek eName hotelNo hotelLocation 113567WD C1024 16 John Smith H25 Edinburgh 234111XA C1024 24 Diane Hocine H25 Edinburgh 712670YD C1025 28 Sarah White H4 Glasgow 113567WD C1025 16 John Smith H4 Glasgow Figure 8.10 Employees of InstantCover and their contracts to work at hotels. The table shown in Figure 8.10 is susceptible to update anomalies. Provide examples of insertion, deletion, and modification anomalies. Describe and illustrate the process of normalizing the table shown in Figure 8.10 to 3NF. State any assumptions you make about the data shown in this table. Database Solutions (2nd Edition) 2