Normalization is a process that involves removing or decreasing the redundancy present in the database. Normalization mainly focuses on removing duplicate data from the database and making it more consistent.
There are various types of normalization such as 1NF,2NF, 3NF, BCNF, 4NF, and 5NF. 5NF is one of the highest levels of normalization form present in database normalization.
Fifth Normal Form (5NF)
- A Relation is said to be in 5NF if both conditions are satisfied.
1) Relation should be already in 4NF 2) It cannot be further non-loss decomposed (JoindDependency should not be present)
- The Fifth Normal Form (5NF) is also known as the Project-Join Normal Form (PJNF).
- 5NF gets satisfied when the table is broken down into as many parts as possible to avoid data redundancy.
Let’s have a look at the above 2 conditions for 5NF.
Relation Should be Already in 4NF
- It should satisfy all the conditions of 4NF i.e
- It should be in BCNF.
- No multi-valued dependency should exist.
Non-Loss Decomposition
- When the table does not contain any join dependency then it is called a lossless /non-loss decomposition.
- In other words, we can say that
- A database is in 5NF when there is no join dependency present in the table / database.
- When we decompose the given table to remove redundancy in the data and then compose it again to create the original table , we should not lose any data, and the original table should be obtained as a loss should happen after the decomposition of the table.
- Join dependency for relation R can be stated as
- R=(R1 ⨝ R2 ⨝ R3 ⨝ ………Rn) where R1,R2,R3…..Rn are sub-relation of R and ⨝ is Natural Join Operator.
- Here R1, R2, R3, ….Rn are the sub-relation of relation R.
Example
- let’s, take of Table R which has 3 columns i.e. subject, class, and teacher where each subject can be taught by many teachers in many classes, and a teacher can teach more than 1 subject.
|
Subject |
Class |
Teacher |
|---|---|---|
|
math |
class 10 |
kartik |
|
math |
class 9 |
yash |
|
math |
class 10 |
yash |
|
science |
class 10 |
yash |
- Here the subject of math is taught by both teachers kartik and yash. Also yash can teach math and science. Yash teaches math to both class 9 and class 10.
- As there is redundancy in data we will decompose it into two tables R1 and R2 such that R1 will have attribute Subject and Class and R2 will have attribute class and teacher.
Table R1
|
Subject |
Class |
|---|---|
|
math |
class 9 |
|
math |
class 10 |
|
science |
class 10 |
- Here we removed the redundancy in the table by removing the extra tuple with the same values i.e. subject math taught in class 10. This tuple is repeated 2 times in the main table but in table R1 this redundancy is removed.
Table R2
|
Class |
Teacher |
|---|---|
|
class 10 |
kartik |
|
class 9 |
yash |
|
class 10 |
yash |
- Here we removed the redundancy in the table by removing the extra tuple with the same values i.e. yash is teaching for class 10. This tuple is repeated 2 times in the main table but in table R2 this redundancy is removed.
- After combining both tables R1 and R2 we will get as mentioned below:
Table (R1 ⨝ R2)
|
Subject |
Class |
Teacher |
|---|---|---|
|
math |
class 9 |
yash |
|
math |
class 10 |
kartik |
|
math |
class 10 |
yash |
|
science |
class 10 |
kartik |
|
science |
class 10 |
yash |
- Here if we notice the newly composed table from R1 and R2 and the original table, an extra tuple is added that did not exist in the original data, This breaks the second rule of 5NF i.e. non-loss decomposition.
- This type of unwanted tuple is known as Spurious tuple.
- Here we will decompose the given table in another relation R3 where it will have 2 columns i.e. subject and teacher.
Table R3
|
Subject |
Teacher |
|---|---|
|
math |
yash |
|
math |
kartik |
|
science |
yash |
- Here the newly decomposed table R3 will have 3 tuples only as the repeated tuple (redundancy ) is not added to the table. yash teaching the subject math is repeated 2 times in main table R but here it will be added only one time resulting in removing the redundancy in the table.
- Now if we compose or rejoin the tables R1, R2, and R3 we will get
Table (R1 ⨝ R2⨝ R3)
|
Subject |
Class |
Teacher |
|---|---|---|
|
math |
class 9 |
yash |
|
math |
class 10 |
yash |
|
math |
class 10 |
kartik |
|
science |
class 10 |
yash |
- Now if we see the re-composed table and the original table, there is no loss of data.
- Here all the tables, R1, R2 and R3 had a natural join which resulted in the table R. After the natural join, the original table is retained as it is. There is no loss of the data.
- So it is atables
- Given Table R1, R2 and R3 are in the Fifth Normal Form(5NF).
Uses of Fifth Normal Form(5NF)
- 5NF ensures that there will be no redundancy present in the database. Removing the redundancy in the database helps the data to remain more optimized and easy to perform database actions.
- It also ensures that there will be non-lossy decomposition only which will result in data consistency and data integrity.
- As data redundancy and anomalies are removed, the database performance gets enhanced.
Limitation of Fifth Normal Form(5NF)
- One of the biggest limitationsis of 5Nf is the complexity of the database. Due to 5Nf large number of tables and relation gets created which eventually increases the complexity of the database.
- Slow exhibition due to large number of tables.
- The cost of implementation of 5NF is also high as it increases the complexity of the database.
Frequently Asked Questions on Fifth Normal Form – FAQs
Which dependency is related to 5NF?
Join dependecy is checked in the 5NF. Relation should satisfy the condition of join dependency then only it will be in the fifth normal form.
What is 5NF also called?
5NF is also known as Project Join Normal Form (PJNF).
Is 5NF is used?
As 5NF increases the complexity of the databse, it is less frequently used in the indusrty. Complexity is the reason for less use of 5NF.