Fifth Normal Form

Let's consider the PropertyItemSupplier relation shown below:

PropertyItemSupplier
 propertyNo   itemDescription   supplierNo 
PG4 Bed S1
PG4 Chair S2

This relation describes properties (propertyNo) that require certain items (itemDescription) which are supplied by suppliers (supplierNo). Furthermore, whenever a prperty (p) requires a certan item (i) and supplier (s) supplies that item (i) and the supplier (s) supplies at least one item to that property (p), then the supplier (s) will also supply the required item (i) to the property (p). In this example, assume that a description of an item (itemDescription) uniquely identifies each type of item.

Now, let's add one more row to the relation:

PropertyItemSupplier (illegal state)
 propertyNo   itemDescription   supplierNo 
PG4 Bed S1
PG4 Chair S2
PG16 Bed S2

To identify the type of constraint on the PropertyItemSupplier relation consider the following statements:
If Property PG4 requires Bed  (from data in tuple 1)
Supplier S2 supplies propertyPG4  (from data in tuple 2)
Supplier S2 provides Bed  (from data in tuple 3)
Then  Supplier S2 provides Bed for property PG4  

This example illustrates the cyclic nature of the constraint on the PropertyItemSupplier relation. If this constraint holds then the tuple (PG4, Bed, S2) must exist in any legal state of the PropertyItemSupplier relation as shown below:

PropertyItemSupplier (legal state)
 propertyNo   itemDescription   supplierNo 
PG4 Bed S1
PG4 Chair S2
PG16 Bed S2
PG4 Bed S2

This is an example of update anomaly and we say that this relation contains a join dependency. As the PropertyItemSupplier relation contains a join dependency, it is not in 5NF. To remove the join dependency, we decompose this relation into three mew relations:

  • PropertyItem
  • ItemSupplier
  • PropertySupplier

    PropertyItem
     propertyNo   itemDescription 
    PG4 Bed
    PG4 Chair
    PG16 Bed
    ItemSupplier
     itemDescription   supplierNo 
    Bed S1
    Chair S2
    Bed S2
    PropertySupplier
     propertyNo   supplierNo 
    PG4 S1
    PG4 S2
    PG16 S2

    It is important to note that performing a natural join on any two relations will produce spurious tuples; however, performing the join on all three will recreate the original relation.

    For a detailed discussion on 5NF we refer to

  • Date C.J. An Introduction to Database Systems 7th ed. Reading MA: Addisson-Wesley, 2000.
  • Elmarsi R and Navathe S. Foundamentsls of Database Systems 3rd ed. Addisson-Wesley, 2000.
  • Hawryszkiewyecz I.T. Database Analysis and Design 2nd ed. New York, NY: Macmillan Publishing Company, 1991.