Home » DBMS

Fifth Normal Form (5NF) | DBMS

DBMS Fifth Normal Form (5NF): In this tutorial, we will learn about the fifth normal form (5NF), its properties with the help of example in the database management system. By Anushree Goswami Last updated : May 29, 2023

What is Fifth Normal Form (5NF) in DBMS?

Fifth normal form (5NF) is also known as project-join normal form (PJ/NF). It is designed to minimize redundancy in relational databases by separating semantically connected relationships in multiple formats to store multi-valued facts.

A relation R is in 5NF if and only if every non-trivial join dependency in R is implied by the candidate keys of R. A relation break up into two relations must contain lossless join Property, which makes certain that no invalid or extra tuples of attributes are created when relations are again joined together through a natural join.

Fifth Normal Form (5NF) Properties

A relation R with attributes, its values and tuples is in 5NF if and only if the following conditions are satisfied,

1. The relation R should be already in 4NF.
2. The relation R cannot be additionally non loss decomposed (join dependency).

If the relation or table can further decompose to remove redundancy and anomaly, and when the process of rejoining followed the decomposed tables through the means of candidate keys, we should not be losing the original data or any new record set should not arise. Understandably, joining two or more decomposed table should not lose records or create new records.

What is Join dependency?

A table or relation can be recreated or re-designed by joining multiple tables and each table of this contain a subset of the attributes and values of the table, then the table is in Join Dependency. It is a generalization of Multivalued Dependency.

If the join of R1 and R2 over S is equal to relation R then we can say that a join dependency exists, where R1 and R2 are the decomposition R1 (P, Q, S) and R2 (Q, T) of a given relation R (P, Q, S, T). As a possibility, R1 and R2 are a lossless decomposition of R.

Over a relation R a Join Dependency ⋈ {R1, R2, ..., Rn} is said to hold if R1, R2, ..., Rn is lossless-join decomposition. The *(P, Q, S, T), (S, T) will be a Join Dependency of R if the join of join's attribute is equal to the relation R. Here, *(R1, R2, R3) is used to specify that relation R1, R2, R3 and so on is a Join Dependency of R.

Join Dependency can be associated to 5NF, wherein a relation is in 5NF, only if it is already in 4NF and it cannot be decomposed further.

Contrarily, in the case of functional dependencies, there is no sound and complete axiomatization for join dependencies, however, axiomatization exists for more expressive dependency languages such as full typed dependencies.

On the other hand, the implication of join dependencies is decidable.

Fifth Normal Form (5NF) Example

Consider a table which contains a record of Subject, Professor and Semester in three columns.

The primary key is the combination of all three columns. Also take an account that the table is in 4NF, as long as there are no multivalued dependencies (2-part join dependencies) in the table: no column (which by itself is not a candidate key or a super key) is a determinant for the other two columns.

If the presence of any properly structured rule is not there to organize the three attributes of the table, it is important to structure them correctly by handling constraint.

In the table, Data structure is taught by Madhurima and Sravan in semester 3, Automata by Abhijeet and Sushmita. In this case, the combination of all these fields required to identify valid data.

So to make the table into 5NF, we can decompose it into three relations,

Subject	Professor	Semester
Maths 2	Saurav	2
Data structure	Madhurima	3
Automata	Abhijeet	5
Data structure	Sravan	3
Automata	Sushmita	5
Computer network	Karan	2
Digital logical design	Abhijeet	5

Semester	Subject
2	Maths 2
3	Data structure
5	Automata
2	Computer network
5	Digital logical design

Subject	Professor
Maths 2	Saurav
Data structure	Madhurima
Automata	Abhijeet
Data structure	Sravan
Automata	Sushmita
Computer network	Karan
Digital logical design	Abhijeet

Semester	Professor
2	Saurav
3	Madhurima
5	Abhijeet
3	Sravan
5	Sushmita
2	Karan
5	Abhijeet