DBMS Menu

Fifth Normal Form (5NF or PJNF) in DBMS



The Fifth Normal Form (5NF), also known as Project-Join Normal Form (PJNF), is a further step in the normalization process. It aims to address redundancy arising from certain types of join dependencies that aren't covered by earlier normal forms.

A relation is in 5NF or PJNF if:

1. It is already in BCNF.

2. Every non-trivial join dependency in the relation is implied by the candidate keys.

A join dependency occurs in a relation R when it is always possible to reconstruct R by joining multiple projections of R. A join dependency is represented as {R1, R2, ..., Rn} ⟶ R, which means that when R is decomposed into R1, R2, ..., Rn, the natural join of these projections results in the original relation R.

The join dependency is non-trivial if none of the projections Ri is equal to R.

Example for Fifth Normal Form (5NF)

Consider a relation involving suppliers, parts, and projects:

Initial Table (SupplierPartsProjects) 

| Supplier | Part  | Project |
|----------|-------|---------|
| S1       | P1    | J1      |
| S1       | P2    | J1      |
| S1       | P1    | J2      |
| S2       | P2    | J2      |

Assume the following constraints for our example:

  1. Every part supplied for a project is supplied by all suppliers supplying any part for that project.
  2. Every part supplied by a supplier is supplied by that supplier for all projects to which that supplier supplies any part.

Given the above constraints, the following join dependencies exist on the table:

  • {Supplier, Part} ⟶ SupplierPartsProjects
  • {Supplier, Project} ⟶ SupplierPartsProjects
  • {Part, Project} ⟶ SupplierPartsProjects

To decompose the relation into 5NF:

SupplierParts: 

| Supplier | Part  |
|----------|-------|
| S1       | P1    |
| S1       | P2    |
| S2       | P2    |
SupplierProjects: 

| Supplier | Project |
|----------|---------|
| S1       | J1      |
| S1       | J2      |
| S2       | J2      |
PartsProjects: 

| Part  | Project |
|-------|---------|
| P1    | J1      |
| P2    | J1      |
| P1    | J2      |
| P2    | J2      |

Now, these decomposed tables eliminate the redundancy caused by the specific constraints and join dependencies of the original relation. When you take the natural join of these tables, you will get back the original table.

It's worth noting that reaching 5NF can lead to an increased number of tables, which can complicate queries and database operations. Thus, achieving 5NF should be a conscious decision made based on the specific requirements and constraints of a given application.

Next Topic :Sixth Normal Form (6NF) in DBMS