Third Normal Form (3NF)

In database design, normalization is an important process to organize data, reduce duplication, and improve accuracy. The Third Normal Form (3NF) builds on the rules of the First (1NF) and Second (2NF) Normal Forms. Reaching 3NF ensures that the database is well-structured, efficient, and free from data issues or inconsistencies.

Even though tables in 2NF have less duplication than 1NF, they can still face problems like update errors. For example, if only one row is updated while another is not, the data becomes inconsistent. This happens due to transitive dependencies. To solve this, we move the table to 3NF, which removes such dependencies and makes the database more reliable.

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 : Consider the below Relation,

In the relation CANDIDATE given above:

• Functional dependency Set: {CAND_NO -> CAND_NAME, CAND_NO ->CAND_STATE, CAND_STATE -> CAND_COUNTRY, CAND_NO -> CAND_AGE}
• So, Candidate key here would be: {CAND_NO}
• For the relation given here in the table, CAND_NO -> CAND_STATE and CAND_STATE -> CAND_COUNTRY are actually true. Thus, CAND_COUNTRY depends transitively on CAND_NO. This transitive relation violates the rules of being in the 3NF. So, if we want to convert it into the third normal form, then we have to decompose the relation CANDIDATE (CAND_NO, CAND_NAME, CAND_STATE, CAND_COUNTRY, CAND_AGE) as:
  CANDIDATE (CAND_NO, CAND_NAME, CAND_STATE, CAND_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. Therefore, the above

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 .

What is Transitive Dependency?

A transitive dependency occurs when a non-key attribute depends on the another non-key attribute rather than directly on the primary key. For instance, consider a table with the attributes (A, B, C) where A is the primary key and B and C are non-key attributes. If B determines C then C is transitively dependent on the A through B. This can lead to data anomalies and redundancy which 3NF aims to eliminate by the ensuring that all non-key attributes depend only on the primary key.

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 .

GeeksforGeeks

Improve

Article Tags :

• DBMS
• GATE CS
• DBMS-Normalization