Difference between 4NF and 5NF

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:

• It must be in Boyce Codd Normal Form (BCNF).
• It should have no multi-valued dependency.

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 :

S.NO	4NF	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.