5-NF

About 5-NF Form in DBMS

In this article, we will learn about 5-NF Form in DBMS.
Normalization is a process of breaking a table into you still optimized ones so that they are free from all problems faced due to insertion updation and deletion of data in the table

 

5-NF form in DBMS

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.

5th Normal form in DBMS – 1

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.

Prime Course Trailer

Related Banners

Get PrepInsta Prime & get Access to all 200+ courses offered by PrepInsta in One Subscription

Get over 200+ course One Subscription

Courses like AI/ML, Cloud Computing, Ethical Hacking, C, C++, Java, Python, DSA (All Languages), Competitive Coding (All Languages), TCS, Infosys, Wipro, Amazon, DBMS, SQL and others

Checkout list of all the video courses in PrepInsta Prime Subscription

Checkout list of all the video courses in PrepInsta Prime Subscription