Open In App

Third Normal Form (3NF)

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

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 will be in an inconsistent state. This update anomaly is caused by a transitive dependency. We need to remove such dependencies by progressing to the Third Normal Form (3NF).

Third Normal Form (3NF)

A relation is in the third normal form, if there is no transitive dependency for non-prime attributes as well as it is in the second normal form. A relation is in 3NF if at least one of the following conditions 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,

Example

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.

Conclusion

In conclusion, a crucial stage in database normalization is Third Normal Form (3NF). It deals with transitive dependencies and improves data integrity through effective information organization. 3NF ensures that non-key properties only depend on the primary key, removing redundancy and helping to create a well-organized and normalized relational database model.


Last Updated : 04 Dec, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads