Friday 27 June 2014

Second Normal Form(2NF) | normalization and its type


SECOND NORMAL FORM(2NF)

A relation is in 2NF if it holds these two conditions:-
1. The relation is in 1NF.
2. Every non -key attribute should be fully functional dependent on its primary key.If the primary key consists of a single attribute then the relation is in 2NF.

EXAMPLE- Consider a relation. "order books" where primary key is ('ORDER NO','BOOK NAME')

ORDER NO
BOOK NAME
QUANTITY
PRICE
1
C
15
175
1
DB
20
220
2
.NET
30
200
3
C
40
175
4
DB
15
220

Let's find fully functional dependency,functional dependency and also which are not  fully functional dependent in this relation.

In this order no, book name and quantity are fully functional  dependent,order no, book name and price  are not fully functional  dependent because book name is functional  dependent  on price. Therefore the relation is not in 2NF.

HOW TO MAKE IT IN 2NF-
Partially key dependency should be remove by splitting the 1NF relation into appropriate relations such that every non-key attribute is fully functional dependent on primary key of respective relations.

1. Create a new relation from source relation that contains the attributes that are fully functional dependent on the concatenated primary key.

ORDER NO
BOOK NAME
QUANTITY
1
C
15
1
DB
20
2
.NET
30
3
C
40
4
DB
15

2. Create any relation from the source relation with the attributes that are not fully functionally dependent on the concatenated primary key and the member of the key on which they are dependent.  

BOOK NAME
PRICE
C
175
DB
220
.NET
200
C
175
DB
220

Now the relation is in 2NF.
Anomalies-Insertion,deletion and updation anomalies are still there.

0 comments:

Post a Comment