Please answer all of the questions for full credit. Thank you! Given the followi
ID: 3555402 • Letter: P
Question
Please answer all of the questions for full credit. Thank you!
Given the following Dentist-patient database schema: Dentist-patient (staffNo. dentistNanie. patNo, patName. aDate. aTiine. surgeryNo) A dentist has a unique number (staffNo) and a name (dentistNanie ); aDate stands for appointment date and aTime stands for appointment time: patNo is a unique number for patient and patName stands for patient name. Figure 1 shows some sample data: Normalize the above relation to 3NF Show appropriate dependency diagrams to justify decomposition. State any assumptions you make about the data. Identify the primary, foreign keys in each step of the decomposition.Explanation / Answer
The functional dependencies that we can see in this table are:
1. staffNo->dentistName
2. patNo->patname
Starting from the first dependency. This dependency is non-trivial functional dependency. Hence, the table is not in BCNF. Thus we break the original table into two tables.
(staffNo, dentistName) : Primary Key: staffNo
(staffNo, patNo, patName, aDate, aTime, surgeryNo): foreignKey: staffNo
In the second table we again have the non-trivial functional dependency patNo->patName. So, this table is also not in BCNF and hence we once again break down this table as follows.
(patNo, patName) : primary key patNo
(staffNo, patNo, aDate, aTime, surgeryNo): Foreign keys are staffNo and patNo
Thus, the overall decomposition is
(staffNo, dentistName) : Primary Key: staffNo
(patNo, patName) : primary key patNo
(staffNo, patNo, aDate, aTime, surgeryNo): Foreign keys are staffNo and patNo
This decomposition brings all the table in BCNF.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.