Fifth Normal Form (Projection-Join Normal Form)

text zoom

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

Properties of 5NF:-



  • Anomalies can occur in relations in 4NF if the primary key has three or more fields.

  • 5NF is based on the concept of join dependence - if a relation cannot be decomposed any further then it is in 5NF.

  • Pair wise cyclical dependency means that:


    • You always need to know two values (pair wise).

    • For any one you must know the other two (cyclical).




Example to understand 5NF


Take the following table structure as an example of a buying table.This is used to track buyers, what they buy, and from whom they buy. Take the following sample data:



































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.



  •  Buyer-Vendor


























buyer


vendor

Sally



Liz Claiborne



Mary



Liz Claiborne



Sally



Jordach



Mary



Jordach




  •  Buyer-Item






























buyer



item



Sally



Blouses



Mary



Blouses



Sally



Jeans



Mary



Jeans



Sally



Sneakers




  •  Vendor-Item






















vendor


item



Liz Claiborne



Blouses



Jordach



Jeans



Jordach



Sneakers




Note:- There is also one more normal form i.e. 6 NF. A table is in sixth normal form (6NF) or Domain-Key normal form (DKNF) if it is in 5NF and if all constraints and dependencies that should hold on the relation can be enforced simply by enforcing the domain constraints and the key constraints specified on the relation. The 6NF is more of a practical scenario and is out of scope of this tutorial.
                    

Database Discussion

...more

Database Source Code

...more

Database Related Tutorials

...more

New Database Resources

...more

Copyright © 2014 VisualBuilder. All rights reserved