DBMS 4NF

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.

4NF Example

Consider a Table Student that contains the following records:

Stu_Id	Stu_Course	Stu_Hobby
101	C++	Reading
101	C++	Writing

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.

Stu_Id	Stu_Course	Stu_Hobby
101	C++	Reading
101	C++	Writing
101	Java	Reading
101	Java	Writing

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.
• Also, Stu_Course and Stu_Hobby are 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

Stu_Id	Stu_Course
101	C++
101	Java

Table 2: Hobby

Stu_Id	Su_Hobby
101	Reading
101	Writing

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