What is Third Normal Form (3NF)?
A relation is said to be in Third Normal Form (3NF), if it is in 2NF and when no non-key attribute is transitively dependent on the primary key i.e., there is no transitive dependency. Also, it should satisfy one of the below-given conditions. For the function dependency C->D:
- C should be a super key and,
- D should be a prime attribute i.e., D should be a part of the candidate key.
3NF is used to reduce data duplication and to attain data integrity.
Example of 3NF
For the relation R(L, M, N, O, P) with functional dependencies as {L->M, MN->P, PO->L}:
The candidate keys will be : {LNO, MNO, NOP}
as the closure of LNO = {L, M, N, O, P}
closure of MNO = {L, M, N, O, P}
closure of NOP = {L, M, N, O, P}
This relation is in 3NF as it is already in 2NF and has no transitive dependency. Also there is no non prime attribute that is deriving a non prime attribute.
Difference between 3NF and BCNF in DBMS
A relation is in the third normal form, if there is no transitive dependency for non-prime attributes as well as it is in the second normal form. The Boyce-Codd Normal Form (BCNF) is based on functional dependencies, BCNF has additional constraints when compared to the basic definition of 3NF. In this article, we will see the differences between 3NF and BCNF.