1 What is the key of the original ORTHODONISVISIT table 2 Te

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

1.

Key of the original ORTHODONIS_VISIT table -----{doc#,office#,Treatment#,patient#} composite key

2.

The relation is not in 1st normal form as there exists non atomic values in Tcost which consists of LaborCosts and SuppliesCosts

so to make values atomic make separate columns for LaborCosts and SuppliesCosts instead of TCosts

3

The relation is not not 2nd normal form as non key attributes are not functionally dependent to whole composite primary key attributes. . So make separate table according to functional dependencies given

4

The relation is not in 3NF as there is transitive dependency in OFFICE table. DocName and DocDeg are functionally dependent on Doc# and Doc# is functionally dependent on Office#. So make two separate tables

Primary keys are underlined