Home »
DBMS
What is PJNF (Project-Join Normal Form) in DBMS?
DBMS | PJNF (Project-Join Normal Form): In this tutorial, we will learn about the PJNF (Project-Join Normal Form), Properties of 5NF with the help of examples.
By IncludeHelp Last updated : May 29, 2023
Overview
Database normalization is the method of restructuring a relational database in accordance with a set of so-called normal forms to minimize data complexity and increase data integrity.
Ronald Fagin first defined the fifth normal form in 1979 during a conference in which he presented his research paper on normal forms and relational database operators. Fifth normal form (5NF), also known as project-join normal form (PJNF) is a level of database normalization designed to minimize duplication in relational databases documenting multi-valued facts by isolating semantically linked multiple relationships.
What is PJNF (Project-Join Normal Form)?
Fifth normal form (5NF), is also known as project-join normal form (PJNF). The fifth normal form (5NF) is a degree of information base standardization intended to decrease repetition in relational information bases. A relation is supposed to be in 5NF if and just on the off chance that it fulfills 4NF and no join reliance exists. A relation is said to have join reliance on the off chance that it tends to be reproduced by joining different sub relations and every one of these sub relations has a subset of the characteristics of the first relation.
A table is said to be in the 5NF if and only if the candidate keys indicate any non-trivial joint dependence in that table.
- A database table that decomposes into two database tables must have the property of lossless-join, which ensures that when database tables are reunited via a natural joint operation, no spurious tuples are created.
- However, if a database table is to be decomposed into more than two database tables, there are criteria. Although uncommon, these instances are treated by joint dependence and the usual fifth type.
- If a table has a composite key made up of at least 3 attributes, 5NF occurs.
Condition for join dependency
If the R1 and R2 over Q join is equal to the R database table, then we can assume that there is a joint dependence where R1 and R2 are the R1 (P, Q) and R2 (Q, S) decomposition of a given R database table (P, Q, S). A lossless decomposition of R is R1 and R2.
Properties of PJNF (Project-Join Normal Form) / 5NF
- A database table R is present in 5NF if and only if the following conditions are met:
- In 4NFF, R should be (no multi-valued dependency exists).
- Unable to undergo lossless decomposition (join dependency)
PJNF (Project-Join Normal Form) Example
Consider the database table R below having the schema R (supplier, product, consumer). The primary key is a combination of all three attributes of the database table.
Table 1:
| SUPPLIER |
PRODUCT |
CONSUMER |
| SK SALES |
JUICE PACK |
LILA |
| SK SALES |
WASHING SYSTEM |
LILA |
| VASUDHA ENTERPRISES |
JUICE PACK |
LILA |
| MANISH SALES |
FANS |
REX |
Table 2:
| SUPPLIER |
PRODUCT |
| SK SALES |
JUICE PACK |
| SK SALES |
WASHING SYSTEM |
| VASUDHA ENTERPRISES |
JUICE PACK |
| MANISH SALES |
FANS |
Table 3:
| CONSUMER |
PRODUCT |
| LILA |
JUICE PACK |
| LILA |
WASHING SYSTEM |
| REX |
FANS |
Table 4:
| SUPPLIER |
CONSUMER |
| SK SALES |
LILA |
| VASUDHA ENTERPRISES |
LILA |
| MANISH SALES |
REX |
When joined, Table 2, Table 3, and Table 4 yield the original table 1. Thus in Table 1 there is join dependency; therefore in 5NF or PJNF, Table 1 is not. Table 2, Table 3, and Table 4, however, satisfy 5NF as it has no multivalued dependence and can not be further decomposed (join dependency does not exists). But this may not be valid in all cases i.e. the resulting table may not be equal to the original table when we combine the decomposed tables, in which case the original table is said to be in 5NF such that it is already in 4NF.