# Third Normal Form (3NF)

Although Second Normal Form (2NF) relations have less redundancy than those in 1NF, they may still suffer from update anomalies. If we update only one tuple and not the other, the database would be in an inconsistent state. This update anomaly is caused by a transitive dependency. We need to remove such dependencies by progressing to Third Normal Form (3NF).

**Third Normal Form (3NF):**

A relation is in third normal form, if there is no transitive dependency for non-prime attributes as well as it is in second normal form.

A relation is in 3NF if at least one of the following condition holds in every non-trivial function dependency X –> Y:

- X is a super key.
- Y is a prime attribute (each element of Y is part of some candidate key).

In other words,

A relation that is in First and Second Normal Form and in which no non-primary-key attribute is transitively dependent on the primary key, then it is in Third Normal Form (3NF).

**Note –** If A->B and B->C are two FDs then A->C is called transitive dependency.

The normalization of 2NF relations to 3NF involves the removal of transitive dependencies. If a transitive dependency exists, we remove the transitively dependent attribute(s) from the relation by placing the attribute(s) in a new relation along with a copy of the determinant.

Consider the examples given below.

**Example-1:**

In relation STUDENT given in Table 4,

FD set:

{STUD_NO -> STUD_NAME, STUD_NO -> STUD_STATE, STUD_STATE -> STUD_COUNTRY, STUD_NO -> STUD_AGE}

Candidate Key:

{STUD_NO}

For this relation in table 4, STUD_NO -> STUD_STATE and STUD_STATE -> STUD_COUNTRY are true. So STUD_COUNTRY is transitively dependent on STUD_NO. It violates the third normal form. To convert it in third normal form, we will decompose the relation STUDENT (STUD_NO, STUD_NAME, STUD_PHONE, STUD_STATE, STUD_COUNTRY_STUD_AGE) as:

STUDENT (STUD_NO, STUD_NAME, STUD_PHONE, STUD_STATE, STUD_AGE) STATE_COUNTRY (STATE, COUNTRY)

**Example-2:**

Consider relation R(A, B, C, D, E)

A -> BC, CD -> E, B -> D, E -> A

All possible candidate keys in above relation are {A, E, CD, BC} All attribute are on right sides of all functional dependencies are prime.

**Note –**

Third Normal Form (3NF) is considered *adequate* for normal relational database design because most of the 3NF tables are free of insertion, update, and deletion anomalies. Moreover, 3NF *always ensures functional dependency preserving and lossless*.

## Recommended Posts:

- First Normal Form (1NF)
- Second Normal Form (2NF)
- Introduction of 4th and 5th Normal form in DBMS
- Domain Key Normal Form in DBMS
- Minimum relations satisfying First Normal Form (1NF)
- Boyce-Codd Normal Form (BCNF)
- How to find the highest normal form of a relation
- Converting Context Free Grammar to Greibach Normal Form
- Converting Context Free Grammar to Chomsky Normal Form
- Normal Forms in DBMS
- Mathematics | Probability Distributions Set 3 (Normal Distribution)
- Digital logic | Canonical and Standard Form
- Advanced SQL Interview Questions
- Architecture of Apache Cassandra

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.