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.
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