Second normalization (2 NF)

=> Second normalization (2 NF) : 

                 A relation is in 2NF if it is in 1NF and every non-primary key attribute is fully functionally dependent on primary key of the relation.

Conditions :

                a) A table is in 1NF and
                b) All non-key (not primary or other keys) fields must depend on all components of the primary key.

1st Example :
           Consider a table below…

           Teacher (matNr, classNr, name, hours, salary)

           N.B Here matNr and classNr are candidate keys.

Full function dependency : 

           {matNr, classNr} => {hours}
           {matNr, classNr} => {salary}

if we convert it in 2NF then the answer will be : {matNr, classNrhours, salary}

Partial function dependency :

           {matNr} => {hours}
           {matNr} => {name}

if we convert it in 2NF then the answer will be : {matNr, hours, name}

2nd Example :

This table is not in 2NF because : price and tax depend on item but not on color.

Now after convert above table into 2NF the result will be like below :

Table 01 :

Table 02 :

Now these tables are in 2NF.

For more detail click here.

3rd Example : 

In above table : 

Composite primary keys : CustomerID & StoreID 

Non-key attribute : Puchase_Location

Here Puchase_Location only depends on StoreID, but StoreID is part of the primary key.

Thats why this table dose not satisfy 2NF.

If we want to convert it into 2NF form then the output will be like below :

Now these tables are in 2NF.
Here we remove partial function dependency. Now, in the table [TABLE_STORE], the column [Purchase Location] is fully dependent on the primary key of that table, which is [Store ID].

For more detail click here

Leave a Reply