Reference no: EM131282278

Consider the following table which 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.

Staff_No	Dentist_Name	Pat_No	Pat_Name	Appointment Data               Time	Surgent_No
S1011 S1011 S1024 S1024 S1032 S1032	Tony Smith Tony Smith Helen Pearson Helen Pearson Robin Plevin Robin Plevin	P100 P105 P108 P108 P105 P110	Gillian White Jill Bell Ian Mackay Ian Mackay Jill Bell John Walker	12-Sep-08      10.00 12-Sep-08      12.00 12-Sep-08      10.00 14-Sep-08      14.00 14-Sep-08      16.30 15-Sep-08      18.00	S15 S15 S10 S10 S15 S13

Describe and illustrate the process of normalizing the above table to 3NF. State any assumptions you make about the data shown in this table.

Identify the functional dependencies represented by the attributes show in the 3NF tables.

Suppose that a data warehouse consists of the three dimensions time, doctor, and patient, and the two measures count and charge, where charge is the fee that a doctor charges a patient for a visit.

Draw a star schema diagram for the above data warehouse.