Question

Explain the difference between 2NF and 3 NF relations

Answer 1

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.