Third Normal Form (3NF)
Third Normal Form:
For a relation to be in Third Normal Form, it must be in Second Normal form and the following must satisfy:
- No non-prime attribute is transitively dependent on prime key attribute
- For any non-trivial functional dependency, X → A, then either
- X is a superkey or,
- A is prime attribute.
We find that in above depicted Student_detail relation, Stu_ID is key and only prime key attribute. We find that City can be identified by Stu_ID as well as Zip itself. Neither Zip is a superkey nor City is a prime attribute. Additionally, Stu_ID → Zip → City, so there exists transitive dependency.
We broke the relation as above depicted two relations to bring it into 3NF.
Boyce-Codd Normal Form:
BCNF is an extension of Third Normal Form in strict way. BCNF states that
- For any non-trivial functional dependency, X → A, then X must be a super-key.
In the above depicted picture, Stu_ID is super-key in Student_Detail relation and Zip is super-key in ZipCodes relation. So,
Stu_ID → Stu_Name, Zip
Zip → City
Confirms, that both relations are in BCNF.