Open In App

Difference between 4NF and 5NF

Last Updated : 20 May, 2020
Like Article

Prerequisite – 4th and 5th Normal form

1. Fourth Normal Form (4NF) :
Any relation is said to be in the fourth normal form when it satisfies the following conditions:

A multi-valued dependency is said to occur when there are two attributes in a table which depend on a third attribute but are independent of each other.
For a functional dependency X->Y there will be a multi-valued dependency if there exists multiple values of Y for a single value of X.
Thus if a relation is in BCNF and also it does not have any kind of multi-valued dependency then that relation will be in 4NF.
In order to denote a multi-valued dependency, “->->” this sign is used.

Example –
Consider the following relation:

Student-ID Course Hobby
100 Science Dancing
100 Maths Singing
101 C# Dancing
101 PHP Singing

In this relation Student-ID 1 has thus opted for two courses and has two hobbies.Similarly Student-ID 2 has opted for two courses and has two hobbies.Thus it contains multi-valued dependencies. It is not in 4NF and in order to convert it into 4NF it can be decomposed into two relations:

Table – R1

Student-ID Course
100 Science
100 Maths
101 C#
101 PHP

Table – R2

Student-ID Hobby
100 Dancing
100 Singing
101 Dancing
101 Singing

Now this relation is thus in 4NF. A relation can contain a functional dependency along with a multi-valued dependency also.So when such a case arises the columns which are functionally dependent are moved to a separate table and the columns which are multi-valued dependent are moved to a separate table.This converts the relation into 4NF.

2. Fifth Normal Form (5NF) :
Any relation in order to be in the fifth normal form must satisfy the following conditions:

  • It must be in Fourth Normal Form (4NF).
  • It should have no join dependency and also the joining must be lossless.

In the fifth normal form the relation must be decomposed in as many sub-relations as possible so as to avoid any kind of redundancy and there must be no extra tuples generated when the sub-relations are combined together by using natural join.

A relation in 5NF cannot be decomposed further without any kind of modification in the meaning or facts. 5NF is also known as Project Join Normal Form (PJNF).

Example –

Student-ID Mobile Number Hobby
123 9999900000 Dancing
124 9999900000 Singing
124 9999900000 Dancing
123 8975622122 Singing
123 9999900000 Singing

This can be further decomposed into three relations:

Table – R1

Student-ID Mobile Number
123 9999900000
123 8975622122
124 9999900000

Table – R2

Student-ID Hobby
123 Dancing
123 Singing
124 Singing
124 Dancing

Table – R3

Mobile Number Hobby
9999900000 Dancing
9999900000 Singing
8975622122 Singing

Thus if natural join is performed on all the three relations then there will be no extra tuples.Hence R1, R2 and R3 are in fifth normal form (5NF).

Difference between 4NF and 5NF :

1. A relation in 4NF must also be in BCNF(Boyce Codd Normal Form). A relation in 5NF must also be in 4NF(Fourth Normal Form).
2. A relation in 4NF must not have any multi-valued dependency. A relation in 5NF must not have any join dependency.
3. A relation in 4NF may or may not be in 5NF. A relation in 5NF is always in 4NF
4. Fourth Normal Form is less stronger in comparison to Fifth Normal form. Fifth Normal form is more stronger than Fourth Normal Form.
5. If a relation is in Fourth Normal Form then it will have more redundancy. If a relation is in Fifth Normal Form then it will less redundancy.
6. If a relation is in Fourth Normal Form then it may be decomposed further into sub-relations. If a relation is in Fifth Normal Form then it cannot be decomposed further into sub-relations without any modification in meaning or facts.

Like Article
Suggest improvement
Share your thoughts in the comments

Similar Reads