Third Normal Form (3NF) |Normalization and its type

THIRD NORMAL  FORM(3NF)

INTRODUCTION--

A relation is said to be in third normal form(3NF) if both conditions hold simultaneously.

1.) The relation is in Second Normal Form(2NF)

2.) Non-key attributes of the relation should not be transitively dependent on the primary key.

The main purpose of the 3NF is to remove the transitive dependency which is the main cause of anomalies in the 2NF.

EXAMPLE- To explain the 3NF, let us consider the example of STUDENT relation.Here the primary key is Stu_Id.

Stu_Id	Stu_Name	Teach_Id	Teach_Name	Teach_Qual
2523	Anurag	T001	Navathe	Ph.D
3712	Pankaj	T004	Date	M.Tech
4096	Gagan	T001	Navathe	Ph.D
2716	Anshu	T004	Date	M.Tech
1768	Harman	T009	Desai	M.Tech

The STUDENT relation is in 2NF but it suffers from insertion,deletion,updation anomalies.The main reason of these anomalies is because some attributes are transitively functionally dependent on the primary key.This results in redundancy in the relation.The dependencies that exist in STUDENT relation are:-

Here, the Stu_Name is functionally dependent on the attribute Stu_Id. Also Teach_Id is functionally dependent on the attribute Stu_Id. Also, Teach_Name is functionally dependent on Teach_Id  so we can say that Teach_Name is transitively functionally  dependent on Stu_Id.Similarly, Teach_Qual is transitively functional dependent on Stu_Id attribute.

                   So to make the relation in 3NF, We have to eliminate this transitive dependence on the primary key.To achieve this

1.) Create a new relation from the source relation that contains all the original attributes but without those attributes that are transitively dependent on the primary key.So in case of STUDENT relation,the resulting relation will contains Stu_Id ,Stu_Name and Teach_Id attributes.The primary key in this relation is same as in the original relation i.e. (Stu_Id).

Stu_Id	Stu_Name	Teach_Id
2523	Anurag	T001
3712	Pankaj	T004
4096	Gagan	T001
2716	Anshu	T004
1768	Harman	T009

2.) The other relation created from the source relation will contain attributes that are transitively functionally dependent on the primary key of the original relation plus the non key attribute from the original source relation on which the transitive functional dependent  attributes are functionally dependent. The primary key of the resulting relation will be a non key attribute on which the non key attributes are functionally dependent.

Teach_Id	Teach_Name	Teach_Qual
T001	Navathe	Ph.D
T004	Date	M.Tech
T009	Desai	M.Tech

Now the relation is in 3NF.