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:

stud_id	stud_name	stud_zip	stud_state	stud_city	stud_district
1001	Komal	202009	UP	Agra	Dayal Bagh
1002	Atul	202001	TN	Chennai	M-City
1006	Adarsh	202008	TN	Chennai	Urrapakkam
1101	Mansi	202009	UK	Pauri	Bhagwan
1201	Sachin	202999	MP	Gwalior	Ratan

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:

stud_id	stud_name	stud_zip
1001	Komal	208009
1002	Atul	202001
1006	Adarsh	202008
1101	Mansi	202009
1201	Sachin	202999

stuudent_zip table:

stud_zip	stud_state	stud_city	stud_district
208009	UP	Agra	Dayal Bagh
202001	TN	Chennai	M-City
202008	TN	Chennai	Urrapakkam
202009	UK	Pauri	Bhagwan
202999	MP	Gwalior	Ratan