Fourth Normal Form - Multi valued dependency

A relation (table) is said to in 4NF:

1. If it is in BCNF.
2. There must be no multi valued dependency.

Multi valued dependency :

Suppose a relation R (A B C), If multi valued dependency exists between A and B, then it is denoted by A->-> B, It means for a single value of attribute A, there are multiple values of attribute B and there must be B and C independent to each other.

Note: There must be at least three attributes to occur multi valued dependency

Consider the following table:

One Student can have multiple mobiles and One student can enroll on multiple courses.

The Candidate key in above table {stu_id, stu_mobile, stu_course}

The table stratifies the rule of BCNF [The whole attribute in the table are the part of the
candidate key]

But the table still have data redundancy due to multi valued dependencies:

stu_id stu_mobile [ for stu_id 101 and 103, there are more than one mobile]

stu_id stu_course [ for stu_id 101 and 102, there are more than one course ]

stu_mobile and stu_course are also independent to each other.

Therefore, to satisfy 4NF, it needs to be decompose into

R1 (stu_id, stu_mobile)

R2(stu_id, stu_course)

See how data redundancy is removed by decomposing it into 4NF:

Check here for more details on Multi valued dependency