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.