Database Help 1 What is the key of the original ORTHODONISVI

Database Help

1. What is the key of the original ORTHODONIS_VISIT table?
2. Tell if the relation is 1NF. If it is, tell how you know, if not put it in 1NF form.
3.Then tell if the relation is 2NF. If it is, tell how you know, if not put it in 2NF form.
4.Then tell if the relation is 3NF. If it is, tell how you know, if not put it in 3NF form.

Solution

office# is the key of the original ORTHODONTIST_VISIT table Only when the data is present in the table we can say that it is 1NF or not. Here, no data in the table. We predict that the values are atomic. So, it is not in 1NF. It is not in 2NF. In 2NF, non-prime key attribute should be fully functionally dependent on prime key attribute. Here, DocName and DocReg depending on Doc#, PName and PAddress are depending on Patient#, TCost and TreatmentGiven are depending on Treatment#, OAddress and Doc# are depending on Office#, ApptDate and ApptTime are depending on Office#, Treatment# and Patient#. So, these tables are seperated to form new tables. Every partial dependencies are to be taken away because partial dependencies are not allowed in 2NF. So, now we put them in 2NF. Below are the tables. APPOINTMENTS Office# Treatment# Patient# ApptDate ApptTime OFFICE Office# OAddress Doc# TREATMENT Treatment# TCost TreatmentGiven PATIENT Patient# Pname PAddress DOCTORS Doc# Dname DocReg Now the database is in 3NF because there are no transitive dependencies.