 Open in App
Not now

# Difference between 2NF and 3NF in DBMS

• Difficulty Level : Hard
• Last Updated : 02 Aug, 2021

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 attribute 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 :

My Personal Notes arrow_drop_up