Data Science and Big Data Assignment 2: Normalization and Star Schema

This assignment solution covers key concepts in data science, specifically focusing on database normalization and star schema design. The solution details the process of normalizing a given table to 3NF (Third Normal Form), including the identification of functional dependencies and the assumptions made during the process. The assignment also addresses the creation of a star schema diagram for a data warehouse, illustrating the relationships between dimensions such as time, doctor, and patient, and measures like count and charge. This comprehensive solution provides a practical understanding of database design principles and data warehousing techniques. Desklib provides this and other solutions to help students with their assignments.

Fall_2016 CSC342
Assignment 2
1- 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
a) Describe and illustrate the process of normalizing the above table to 3NF. State any assumptions you
make about the data shown in this table.
b) Identify the functional dependencies represented by the attributes show in the 3NF tables.
2- 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.
a) Draw a star schema diagram for the above data warehouse.
1