5-NF

5-NF

 

In this article, we will learn about 5-NF in DBMS.

Normalization is a process of making a table optimized by eliminating problems caused due to insertion updation and deletion.

 

Learn more about Normalization here on this page.

5-NF

Definition of 5-NF:

A table is said to be in Fifth Normal Form if it satisfies the following properties

    1. If it is in fourth normal form and
    2. It should not have join dependency

It is also called as Project Join Normal Form(PJNF)

If the table is having join dependency then it should be broken into smaller tables.

Problem table : SPC table

SupplierProductCustomer
S1P1C1
S1P2C2
S1P1C2
S2P2C1
S1P2C1

 

  • The above Supplier-Product-Customer table is in 4th normal form.
  • But still there exists anomalies
  • The above table is defined by ternary relationship among supplier,product and customers
  • A single supplier can supply a product which will be used by more than one customers
  • A customer can buy two or more products from a single supplier
  • A product used by a customer can be supplied by more then one supplier.

 

How to eliminate join dependency ?

  • Join dependency is converted to separate tables
  • The simplest solution to eliminate join dependency from the above table is to break the table into 3 binary one-one relationships
  • The resultant tables which are in 5th normal form are

Supp_Pro table

SupplierProduct
S1P1
S1P2
S2P2

Supp_Cust table

SupplierCustomer
S1C1
S1C2
S2C1

Cust_Pro table

ProductCustomer
P1C1
P2C2
P1C2
P2C1

 

  • When we combine the above three tables, the resultant table will be the original SPC table
  • But this might not be true in all cases i.e., when we combine the separated tables, the resultant will not be equivalent  to the original table, in that case we should not break down the original table.