Third Normal form (3NF)

A realtion(table) is said to be in Third Normal form (3NF), if it satifies the following condition:

• Table must be in 2NF
• Transitive functional dependency of non-prime attribute on any super key should be removed.

An attribute that is not part of any candidate key is known as non-prime attribute.

In other words 3NF can be explained like this: A table is in 3NF if it is in 2NF and for each functional dependency X-> Y at least one of the following conditions hold:

• X is a super key of table
• Y is a prime attribute of table

An attribute that is a part of one of the candidate keys is known as prime attribute.

Example: Suppose a college wants to store the complete address of each student, they create a table named student_details that looks like this:

Super keys: {stud_id}, {stud_id, stud_name}, {stud_id, stud_name, stud_zip}…so on
Candidate Keys: {stud_id}
Non-prime attributes: all attributes except stud_id are non-prime as they are not part of any candidate keys.

Here, stud_state, stud_city & stud_district dependent on stud_zip. And, stud_zip is dependent on stud_id that makes non-prime attributes (stud_state, stud_city & stud_district) transitively dependent on super key (stud_id). This violates the rule of 3NF.

To make this table complies with 3NF we have to break the table into two tables to remove the transitive dependency:

student table:

stuudent_zip table: