Friday 27 June 2014

Fifth normal form in Database management system | Normalization and its type


FIFTH NORMAL FORM-

DEFINITION- A table is in fifth normal form (5NF) or Project-Join Normal Form (PJNF) if
1. It is in 4NF 
2. It cannot have a lossless decomposition into any number of smaller tables. 

EXAMPLE- Consider a relation as an example of a buying table.This is used to track buyers, what they buy, and from whom they buy.



Buyer

Vendor

Item
Sally
Liz Claiborne
Blouses
Mary
Liz Claiborne
Blouses
Sally
Jordach
Jeans
Mary
Jordach
Jeans
Sally
Jordach
Sneakers


Problem:- The problem with the above table structure is that if Claiborne starts to sell Jeans then how many records must you create to record this fact? The problem is there are pair wise cyclical dependencies in the primary key. That is, in order to determine the item you must know the buyer and vendor, and to determine the vendor you must know the buyer and the item, and finally to know the buyer you must know the vendor and the item.

Solution:- The solution is to break this one table into three tables; Buyer-Vendor, Buyer-Item, and Vendor-Item. So following tables are in the 5NF.

Relation Buyer-Vendor-


Buyer
vendor

Sally

Liz Claiborne

Mary

Liz Claiborne

Sally

Jordach

Mary

Jordach

Relation Buyer-Item-


Buyer

Item

Sally

Blouses

Mary

Blouses

Sally

Jeans

Mary

Jeans

Sally

Sneakers

Relation Vendor-Item-


Vendor

Item

Liz Claiborne

Blouses

Jordach

Jeans

Jordach

Sneakers

These relation are in 5NF.

0 comments:

Post a Comment