*72*

# Fifth normal form (5NF)

- A relation is in 5NF if it is in 4NF and not contains any join dependency and joining should be lossless.
- 5NF is satisfied when all the tables are broken into as many tables as possible in order to avoid redundancy.
- 5NF is also known as Project-join normal form (PJ/NF).

### Example

SUBJECT | LECTURER | SEMESTER |
---|---|---|

Computer | Anshika | Semester 1 |

Computer | John | Semester 1 |

Math | John | Semester 1 |

Math | Akash | Semester 2 |

Chemistry | Praveen | Semester 1 |

In the above table, John takes both Computer and Math class for Semester 1 but he doesnâ€™t take Math class for Semester 2. In this case, combination of all these fields required to identify a valid data.

Suppose we add a new Semester as Semester 3 but do not know about the subject and who will be taking that subject so we leave Lecturer and Subject as NULL. But all three columns together acts as a primary key, so we canâ€™t leave other two columns blank.

So to make the above table into 5NF, we can decompose it into three relations P1, P2 & P3:

**P1**

SEMESTER | SUBJECT |
---|---|

Semester 1 | Computer |

Semester 1 | Math |

Semester 1 | Chemistry |

Semester 2 | Math |

**P2**

SUBJECT | LECTURER |
---|---|

Computer | Anshika |

Computer | John |

Math | John |

Math | Akash |

Chemistry | Praveen |

**P3**

SEMSTER | LECTURER |
---|---|

Semester 1 | Anshika |

Semester 1 | John |

Semester 1 | John |

Semester 2 | Akash |

Semester 1 | Praveen |

Next TopicDBMS Relational Decomposition