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
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.
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
| Supplier | Product | Customer |
|---|---|---|
| S1 | P1 | C1 |
| S1 | P2 | C2 |
| S1 | P1 | C2 |
| S2 | P2 | C1 |
| S1 | P2 | C1 |
- 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
| Supplier | Product |
|---|---|
| S1 | P1 |
| S1 | P2 |
| S2 | P2 |
Supp_Cust table
| Supplier | Customer |
|---|---|
| S1 | C1 |
| S1 | C2 |
| S2 | C1 |
Cust_Pro table
| Product | Customer |
|---|---|
| P1 | C1 |
| P2 | C2 |
| P1 | C2 |
| P2 | C1 |
- 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
Login/Signup to comment