A relation is in 4NF if it satisfies the following conditions:
- It is in BCNF (Boyce Codd Normal Form).
- It does not have any multi-valued dependency.
What is multi-valued dependency?
Before we learn how to decompose a relation into 4NF, let’s learn what is a multi-valued dependency.
A relation is said to be in multi-valued dependency, if it satisfies the following conditions:
- For a multi-valued dependency to occur there must be at least 3 columns in the relation, for example R(X, Y, Z).
- X -> Y (Y is dependant on X), for a single value of X, there are multiple values of Y.
- If X -> Y exists in a relation R(X, Y, Z) then Y and Z should be independent of each other.
Consider a Table Student that contains the following records:
A student can join more than one courses, let’s say Ram joins another course “Java”.
The issue: For a new course, instead of one row, two rows need to be added.
This relation has multi-valued dependency as
Stu_Id -> Stu_Course, Student course is dependent on Student Id and for each student id, there are multiple courses.
Stu_Hobbyare independent of each other.
- And relation has at least three columns.
This table has unnecessary records as for each new course, more than one records needs to be added to the table. Also, we have concluded that the table has multi-valued dependency.
Let’s decompose this table in 4NF:
This table is already in BCNF. We just need to remove the multi-valued dependency. Let’s decompose the original table into two tables.
Table 1: Course
Table 2: Hobby
These tables are in 4NF as the multi-valued dependency is removed. Now, let’s say you want to add another course “Python” to student id
101, you just need to add one record in the
Course table. Similarly if you want to add a new hobby “Travelling”, you can just add a single record to the