CSC 443 Data Base Management Systems

Dr. R. M. Siegfried

Assignment #5 - p. 390/14.14, 14.15, 14.16

Due Wednesday, October 9, 2013

14.14.

Examine the Patient Medication Form for the Wellmeadows Hospital below. (see Section B.3 in Appendix B of the textbook.)

  1. Identify the funciton dependencies represented by the attributes shown in the form below. State any assumptions that you make about the data and the attributes shown in the form.
  2. Describe and illustrate the process of normalizing the attributes shown below to produce a set of well-designed 3NF relations.
  3. Identify the primary, alternate and foreign keys in your 3NF relations.
Wellmeadows Hospital
Patient Medication Form
Patient Number P10034

Full Name Robert MacDonald             Ward Number Ward 11
Bed Number 34            Ward Name Orthopedic
Drug
Number		 Name Description Dosage Method
of Admin 		Units per
Day		 StartDate Finish Date
10223 Morphine Pain Killer 10 mg/ml Oral 50 24/03/08 24/03/08
10334 Tetracycline Antibiotic 0.5 mg/ml IV 10 24/03/08 17/04/08
10223 Morphine Pain Killer 10 mg/ml Oral 10 25/04/09 02/05/10

14.15

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 surgery. On each day of patient appointment, a dentist is allocated to a specific surgery for that day.

  1. The table below is susceptible to update anomalies. Provide examples of insertion, deletion and update anomalies.
  2. Identify the func tional dependencies represented by the attributes shown in the table below. State any assumptions you make about the data and the attributes shown in the table.
  3. Describe and illustrate the process of normalizing the table shown below. Identify the primary, alternate and foreign keys in your 3NF relations.
staffNo dentistName patNo patName Appointment
date time 		surgeryNo
s1011 Tony Smith P100 Gillian White 12-Sep-08 10:00 S15
s1011 Tony Smith P105 Jill Bell 12-Sep-08 12:00 S15
s1024 Helen Pearson P108 Ian MacKay 12-Sep-08 10:00 S10
s1024 Helen Pearson P108 Ian MacKay 14-Sep-08 14:00 S10
s1032 Robin Plevin P105 Jill Bell 14-Sep-08 16:30 S15
s1032 Robin Plevin P110 John Walker 15-Sep-08 18:00 S13

14.16

An agency called Instant Cover supplies part-time/temporary staff to hotels within Scotland. The table below displays sample data, which lists the time spent by agency staff working at various hotels. The Natoinal Insurance Number (NIN) is unique for every member of staff.

  1. The table shown below is susceptible to update anomalies. Provide examples of insertion, deletion and update anomalies.
  2. Identify the functional dependencies represented by the attributes shown in the table below. State any assumptions that yo make about the data and the attributes shown in the table.
  3. Describe and illustate the process of normalizing the table shown below to 3NF. Identify primary, alternate and foreign keys in your relations.
NIN contractNo hours eName hNo hLoc
1135 c1024 16 Smith J H25 East Killbride
1057 c1024 24 Hocine D H25 East Killbride
1068 c1025 28 White T H4 Glascow
1135 c1025 15 Smith J H4 Glascow

[Back to the Assignment Page]