Third Normal Form (3NF)

.

Third Normal Form (3NF)

A table (relation is said to be in 3NF if and only if.

1. It is in 2NF

2. No non-prime attribute is transitively dependent on primary key.

                                         OR

Transitively functional dependency of non-prime attribute on any supper key should be removed.

In other words 3NF can be explain as a table is in 3NF if it is in 2NF  and for each functional dependency X - Y at list one of the following condition holds.

• X is a supper key of table.
• Y is a prime attribute of table.

An attribute that is a path of the candidate key is known as prime attributes.

Student Detail

Supper key : {S_id}, {S_id,S_Name},{S_id,S_Name,Age}

Candidate key : {S_id}

Non-Prime attribute : All attributes except S_id.

Functional dependency of above table are:

S_id : S_name

S_id : Age

S_id : Gender

S_id : Hostel-Name

Gender : Hostel-Name

    Thus in above relation contains transitive dependency i.e. S_id ->Gender, Geder->Hostel_Name & S_id->Hostel_Name. This violeds the second rule of 3NF. 

i.e. "No non-prime attribute is transitively dependent on primary key.

We can convert it into 3NF by decomposing the relation as bellow.