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. |

Attention reader! Don’t stop learning now. Learn all **GATE CS concepts with Free Live Classes** on our youtube channel.