Sp24_Assignment4_Solution

MSIS 5663 – Data Warehousing – Spring 2024 Assignment 4 - Solution Problem 1 ( 3 points ) 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 appointments, a dentist is allocated to a specific surgery for that day. a. The table is susceptible to update anomalies. Provide examples of insertion, deletion and update anomalies. b. State why the above table is in 1NF. c. Identify all candidate key(s) and your choice of primary key. d. Identify the functional dependencies in the 1NF table above based on the chosen primary key, including any partial and transitive dependencies. (If necessary, clearly state your assumptions). In answering this, you may use the format: fd# : attribute(s) on the left-hand side)  (attribute(s) on the right-hand side (state whether full , partial or transitive dependency) example: fd1 : (A1, A2)  A3, A4, A5, A6 (full dependency) e. After removing partial dependencies, show all tables, with data , that are in 2NF . For each table, give the name and the primary key attribute, foreign key(s) if any, and which primary key of which table the foreign key(s) reference. f. After removing transitive dependencies, show all tables, with data , that are in 3NF . For each table, give the name, the primary key attribute, foreign key(s) if any, and which primary key of which table the foreign key(s) reference. a) ( 0.3 points ) Insertion Anomaly : Every time you enter staffNo, you have to enter staffName Deletion Anomaly : If you delete staffNo S1032, you will lose information on patient John Walker Update Anomaly : If you change patient name (say last name of Jill Bell) you have to change update multiple records, which may lead to inconsistent values if we miss changing in one record. ( Note: Other examples are possible ) b) ( 0.2 points) The original table is in 1NF because there are no repeating groups For c) through f) I am showing two possible solutions ( Solution 1 and Solution 2 ) based on whether you split up aDate and aTime, or don’t. ( GRADING NOTE : 0.3 Points will be deducted overall, if you have duplicate entries for the primary key in any table ), even if overall design is correct.

Solution 1 c) ( 0.5 points) Candidate keys: If you split up appointment date (aDate) and appointment time (aTime), candidate key is: (staffNo, aDate, aTime) Given that on each day of patient appointments, a dentist is allocated to a specific surgery for that day , another candidate key is: (patNo, aDate, aTime) d) ( 0.5 points ): (staffNo, aDate, aTime) as primary key: Functional Dependencies in 1NF table StaffPatientSurgery fd1: (staffNo, aDate, aTime)  dentistName, patNo, patName, surgeryNo fd2: staffNo  dentistName (partial dependency) fd3: (staffNo, aDate)  surgeryNo (partial dependency) fd4: patNo  patName (transitive dependency) fd5: (patNo, aDate, aTime)  staffNo, dentistName, patName, surgeryNo (alternate key) StaffPatientSurgery Primary Key – (staffNo , aDate , aTime ) staffNo aDate aTime dentistName patNo patName surgeryNo S1011 12-Sep-08 10:00 Tony Smith P100 Gillian White S15 S1011 12-Sep-08 12:00 Tony Smith P105 Jill Bell S15 S1024 12-Sep-08 10:00 Helen Peterson P108 Ian MacKay S10 S1024 14-Sep-08 14:00 Helen Peterson P108 Ian MacKay S10 S1032 14-Sep-08 16:30 Robin Plevin P105 Jill Bell S15 S1032 14-Sep-08 18:00 Robin Plevin P110 John Walker S15 e) ( 0.5 points ) 2NF Tables (removing partial dependencies fd2 and fd3) Based on the primary key in the 1NF table, we identified two partial dependencies in the 1NF StaffPatientSurgery table (fd2 and fd3 When we remove these partial dependencies, we have the following tables in 2NF: StaffPatient : Primary Key – (staffNo , aDate , aTime ) Foreign Key (staffNo, aDate) references primary key (staffNo, aDate) of StaffSurgery table staffNo aDate aTime patNo patName S1011 12-Sep-08 10:00 P100 Gillian White S1011 12-Sep-08 12:00 P105 Jill Bell S1024 12-Sep-08 10:00 P108 Ian MacKay S1024 14-Sep-08 14:00 P108 Ian MacKay S1032 14-Sep-08 16:30 P105 Jill Bell S1032 14-Sep-08 18:00 P110 John Walker Fd4: patNo  patName (transitive dependency) remains StaffSurgery Primary Key – (staffNo , aDate) Foreign Key staffNo references primary key staffNo of Staff table staffNo aDate surgeryNo S1011 12-Sep-08 S15 S1024 12-Sep-08 S10 S1024 14-Sep-08 S10 S1032 14-Sep-08 S15 Staff Primary Key – staffNo staffNo dentistName S1011 Tony Smith S1024 Helen Peterson S1032 Robin Plevin ------------------------------------------------------------------------------------------------------------------- f) ( 1 point ) 3NF Tables (removing trasnitive dependency fd3 in StaffPatient Table) Based on the primary key in the 2NF tables, we one transitive dependency in StaffPatient table (see dependency diagram). When we remove the transitive dependency, we have the following additional table, Patient . The StaffPatient Table also gets modified.

Solution 2 c) If you don’t split up aDate and aTime, we use appointment (aDate and aTime as a single attribute) then candidate key is: (staffNo, appointment) Given that on each day of patient appointments, a dentist is allocated to a specific surgery for that day , another candidate key is: (patNo, appointment) d) Solution 2 : (staffNo, appointment) is primary key: Functional Dependencies in 1NF table StaffPatientSurgery: fd1: (StaffNo, Appointment)  (dentistName, patNo, patName, surgeryNo) fd2: StaffNo  dentistName (partial dependency) fd3: patNo  patName (transitive dependency) StaffPatientSurgery Primary Key – (staffNo , aDate , aTime ) staffNo appointment dentistName patNo patName surgeryNo S1011 12-Sep-08 10:00 Tony Smith P100 Gillian White S15 S1011 12-Sep-08 12:00 Tony Smith P105 Jill Bell S15 S1024 12-Sep-08 10:00 Helen Peterson P108 Ian MacKay S10 S1024 14-Sep-08 14:00 Helen Peterson P108 Ian MacKay S10 S1032 14-Sep-08 16:30 Robin Plevin P105 Jill Bell S15 S1032 14-Sep-08 18:00 Robin Plevin P110 John Walker S15 e) ( 0.5 points ) 2NF Tables (removing partial dependency fd2) StaffPatient : Primary Key – (staffNo , appointment ) Foreign Key (staffNo) references primary key (staffNo) of Staff table staffNo appointment patNo patName surgeryNo S1011 12-Sep-08 10:00 P100 Gillian White S15 S1011 12-Sep-08 12:00 P105 Jill Bell S15 S1024 12-Sep-08 10:00 P108 Ian MacKay S10 S1024 14-Sep-08 14:00 P108 Ian MacKay S10 S1032 14-Sep-08 16:30 P105 Jill Bell S15 S1032 14-Sep-08 18:00 P110 John Walker S15 fd3: patNo  patName (transitive dependency) remains Staff Primary Key – staffNo staffNo dentistName S1011 Tony Smith S1024 Helen Peterson S1032 Robin Plevin f) 3NF tables (removing transitive dependency f3) StaffPatient: Primary Key – (staffNo , appointment ) Foreign Key staffNo references primary key staffNo of Staff table Foreign Key patNo references primary key patNo of Patient table staffNo appointment patNo surgeryNo S1011 12-Sep-08 10:00 P100 S15 S1011 12-Sep-08 12:00 P105 S15 S1024 12-Sep-08 10:00 P108 S10 S1024 14-Sep-08 14:00 P108 S10 S1032 14-Sep-08 16:30 P105 S15 S1032 14-Sep-08 18:00 P110 S15 This table is in 3NF because it has no repeating groups (so 1NF), no partial dependencies (so 2NF) and no transitive depencies (so 3NF)

Staff Primary Key – staffNo staffNo dentistName S1011 Tony Smith S1024 Helen Peterson S1032 Robin Plevin This table is in 3NF because it has no repeating groups (so 1NF), no partial dependencies (so 2NF) and no transitive dependencies (so 3NF) Patient Primary Key – patNo patNo patName P100 Gillian White P105 Jill Bell P108 Ian MacKay P110 John Walker This table is in 3NF because it has no repeating groups (so 1NF), no partial dependencies (so 2NF) and no transitive depencies (so 3NF)

Course { CourseID , CourseName} Primary Key: CourseID Student { StudentID , LastName, First Name} Primary Key: StudentID FacultyCourse ( Semester , CourseID , FacultyID, Professor_LastName, Professor_FirstName, Bldg, Office #) Primary Key Semester , CourseID Fd5 : Faculty_ID  Professor_LastName, Professor_First Name, Bldg, Office_# (transitive dependency) ------------------------------------------------------------------------------------------------------------------------------------------------- d) (0.5 points) We remove transitive dependency to get 3NF relations. Student { StudentID , LastName, First Name} Primary Key: StudentID Course { CourseID , CourseName} Primary Key: CourseID Faculty { FacultyID , Professor_LastName, Professor_First Name, Bldg, Office_# } Primary Key: FacultyID SemesterGrades { StudentID, Semester , CourseID, CourseSection, Grade} (Optionally renamed to make more sense) Primary Key: (Semester, CourseID,) Foreign Key: CourseID references CourseID in Course Foreign Key: StudentID references StudentID in Student Foreign Key: FacultyID references FacultyID in Faculty Foreign Key: (Semester, CourseID) references (Semester, CourseID) in FacultyCourse FacultyCourse ( Semester , CourseID , FacultyID) Primary Key Semester , CourseID (assumption 2 earlier- all sections of a course in a semester are taught by same faculty) ----------------------------------------------------------------------------------------------------------------