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.
Definition of 5-NF:
A table is said to be in Fifth Normal Form if it satisfies the following properties
- If it is in
fourth normal formand
- It should
not have join dependency
- If it is in
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
- 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
- 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.