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.
Consider the following relation:
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:
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).
This can be further decomposed into three relations:
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.|
Attention reader! Don’t stop learning now. Learn all GATE CS concepts with Free Live Classes on our youtube channel.