A complete collection of Database management system notes,tools and project..

Menu

Friday, 27 June 2014

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.