Difference between 2NF and 3NF in DBMS

1. Second Normal Form (2NF) :
A relation is said to be in second normal form when it is already in first normal form and there is no partial functional dependency that is no non-prime attribute should be functionally dependent on prime attribute. It was given by E.F Codd in 1971. If the candidate key comprises of only single attribute and relation is in 1NF then it is already in 2NF. The concept of checking relation for 2NF applies when there is composite candidate key that is candidate key comprises of more than one attribute.

Example:
Consider a relation R(A, B, C, D) with functional dependencies: {AB–>CD, BC–>D}

Closure of (AB)={A, B, C, D} 

So AB is a candidate key.
The relation R is in 1NF as relational DBMS does not allow multi-valued or composite attribute.
In AB–>CD (AB is candidate key and C, D is non-prime)
In BC–>D (BC is non-prime and D is non-prime which is allowed in 2NF)
The relation R is in 2NF as no prime attribute is deriving non prime attibute that is there is no partial functional dependency.
The relation R is not in 3NF as non-prime attribute is deriving non-prime attribute.

2. Third Normal Form (3NF) :
A relation is said to be in third normal form when it is already in first normal and second normal forms and every non prime attribute is non-transitively dependent on superkey of relation or in simple language there is no transitive functional dependency. It was also given by E.F Codd in 1971. In this form duplication of data is reduced and referential integrity is ensured. A relation R having functional dependency A–>B is in 3NF if either of the conditions given below are true .

  1. A is a superkey.
  2. B is prime attribute, that is B is the part of candidate key.

Example:-
Consider a relation R(A, B, C) having functional dependency {AB–>C, C–>A}



Closure of (AB)={A, B, C}
Closure of (BC)={A, B, C}
Candidate keys are-{AB, BC} 

The relation R is in 1NF as relational DBMS does not allow multi-valued or composite attribute.

AB-->C(prime deriving prime)
C-->A(prime deriving prime) 

So the relation R is in 2NF and in 3NF also because there is no prime deriving non-prime and no non-prime deriving non-prime that is there is no partial functional dependency and no transitive functional dependency.

Difference between 2NF and 3NF :

S.NO. 2NF(Second Normal Form) 3NF(Third Normal Form)
1. It is already in 1NF. It is already in 1NF as well as in 2NF also.
2. In 2NF non-prime attributes are allowed to be functionally dependent on non-prime attributes. In 3NF non-prime attributes are only allowed to be functionally dependent on Super key of relation.
3. No partial functional dependency of non-prime attributes are on any proper subset of candidate key is allowed. No transitive functional dependency of non-prime attributes on any super key is allowed. .
4. Stronger normal form than 1NF but lesser than 3NF Stronger normal form than 1NF and 2NF.
5. It eliminates repeating groups in relation. It virtually eliminates all the redundancies.
6. The goal of the second normal form is to eliminate redundant data. The goal of the third normal form is to ensure referential integrity.

Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


1


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.