Multivalued Dependency & 4NF (Optional)

What is a Multivalued Dependency?

In relational databases, a Multivalued Dependency (MVD) is a type of dependency where one attribute in a table uniquely determines a set of values for another attribute, independent of all other attributes.

For example, consider a student named Rita who is working on two projects, Python and Java and has two hobbies, namely Football and Reading. This information can be expressed in a tabular format as follows:

In this case, Project and Hobby are multivalued attributes since they have multiple values for a single entity, i.e., Rita.

Understanding 4NF (Fourth Normal Form)

The Fourth Normal Form (4NF) is a stricter version of the Boyce-Codd Normal Form (BCNF). A table is in 4NF if:

1. It is already in BCNF.
2. It has no non-trivial multivalued dependencies between two or more sets of attributes.

Why do we need 4NF?
Even if a table is in BCNF, it can still suffer from redundancy if it has multivalued dependencies. 4NF ensures that such dependencies are removed by splitting the table into smaller relations.

How to Achieve 4NF

To normalize a table to 4NF, we identify the multivalued dependencies and decompose the table into two or more tables that isolate these dependencies.

Here we have a Students table which has information about the subjects and the activity the students have enrolled in. Subjects_Enrolled and Activty_Enrolled are the two attributes that are independent of each other. The given table is in BCNF which satisfies the first condition of 4NF.

Let’s check further the Multi-valued Dependency.

The dependencies in this relation are:

Student_Roll_No —> Subject_Enrolled
Student_Roll_No —> Activity Enrolled

Based on the conditions for Multi-Valued dependency, checking the existing relation.

There should be at least 3 columns in a table. – – Satisfied

For every dependency A-> B, for every value of A multiple values of B exists then the dependency is referred to as multi-valued dependency. – – Roll no 45 has enrolled in Economics and History in terms of academics and Painting and Hockey as activities. Thus for a value of Student_Roll_No different values of Activity_Enrolled exist.

In the relation of 3 columns R(XYZ), if there exists a multi-valued dependency between X and Y then Y and Z should be independent of each other. – – Subject_Enrolled and Activity Enrolled are independent of each other.

As we checked the above conditions it is clear that the relation consists of multi-valued dependency. In order to normalize the table into 4NF, we need to decompose it into smaller tables.

Student relation is now been decomposed into smaller tables S1 and S2 which are in 4NF.

S1:

S2:

Summary

Multivalued Dependency occurs when one attribute determines a set of values for another attribute, independently of other attributes.

4NF ensures that tables do not have multivalued dependencies, thus reducing redundancy and improving data structure.

Achieving 4NF often involves decomposition—splitting one table into two or more that separately handle each multivalued dependency.