# 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 .

- A is a superkey.
- 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. |

Don’t stop now and take your learning to the next level. Learn all the important concepts of Data Structures and Algorithms with the help of the most trusted course: DSA Self Paced. Become industry ready at a student-friendly price.

## Recommended Posts:

- Difference between DDL and DML in DBMS
- Difference between 1NF and 2NF in DBMS
- Difference between Database and DBMS
- Difference between RDBMS and DBMS
- Difference between 3NF and BCNF in DBMS
- Difference between Trigger and Procedure in DBMS
- Difference between Selection and Projection in DBMS
- Difference between Cursor and Trigger in DBMS
- Difference between File System and DBMS
- Difference between OLAP and OLTP in DBMS
- Difference between Classification and Clustering in DBMS
- Difference between Schema and Instance in DBMS
- Difference between Generalization and Specialization in DBMS
- Main difference between Timestamp protocol and Thomos write rule in DBMS
- Difference between Row oriented and Column oriented data stores in DBMS
- Difference between E-R Model and Relational Model in DBMS
- Need for DBMS
- Starvation in DBMS
- Deadlock in DBMS
- Interfaces in DBMS

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.