4NF in DBMS

Introduction to 4NF in DBMS

Before learning about 4NF in DBMS, let's have a brief on Normalization and Normal Forms.

Normalization is the process of reducing redundancy in a relation. Redundancy in relation may result in insertion, deletion, and update errors. As a result, normalization helps in reducing relational redundancy and the normal forms are used to remove these redundancies from a database table.
4NF in DBMS stands for Fourth Normal Form, a relation is said to be in 4NF if the relation is in Boyce Codd Normal Form (BCNF) and has no multi-valued dependency. Now let's have a brief of BCNF, then we will move to Multi-Valued Dependency.

A relation is said to be in BCNF if the relation's attributes contain only an atomic/single value, all the non-key attributes must be fully functionally dependent on the primary key, there is no transitive dependency for non-key attributes, and for every functional dependency X$->$Y, X is the super key of the table. We will understand this better once we go through the examples.

What is Multi-valued Dependency ?

If the following requirements are met, a table is said to have a multi-valued dependency,

• For a single value of A in the dependency A -> B, multiple values of B exist.
• A table should have at least 3 columns.
• For the relation $R(A, B, C)$, if A and B have a multi-valued dependency, then B and C should be independent of each other.

Let's have an example to understand multi-valued dependency :
The below table shows the details of an office department exchange event having the columns, EMPLOYEE_ID, DEPARTMENT, and HOBBY.

As you can see in the above table, Employee E901 is interested in two departments HR and Sales, and, has two hobbies Badminton and Reading. This will result in multiple records for E901 as,

In the above table, you can see that for the Employee E901 multiple records exist in the DEPARTMENT and the HOBBY attribute. Hence the multi-valued dependencies are,

EMPLOYEE_ID $->$ DEPARTMENT and
EMPLOYEE_ID $->$ HOBBY
Also, the DEPARTMENT and HOBBY attributes are independent of each other thus leading to a multi-valued dependency in the above table.

Join Dependency

Join Dependency is similar to Multi-Valued Dependency as Join Dependency is also a constraint.

Let R be a relation schema and the decompositions of R are $R1, R2, R3,..., Rn$. R is said to be in Join Dependency if and only if every instance of R, r is equal to the join of its projections on $R1, R2, R3,..., Rn$.

You can read more about Join Dependency here at Join Dependency in DBMS.

Examples of 4NF

Let's have a look over some tables and check whether these are in 4NF or not.

Candidate keys are (TEACHER, STUDENT) and (SUBJECT, STUDENT).

The above table is not in 4NF as the table doesn't satisfy the condition for BCNF. If we try to delete the student Raman from the table we will not be able to determine that Anurag teaches OS. Hence TEACHER attribute must also be a key to satisfy the condition for BCNF.

Let's have another example where a table is used to store the Roll Numbers and Names of the students enrolled in a university.

Let's check for BCNF first :

The Candidate key is ROLL_NO, and the prime attribute is also ROLL_NO
The above table has a single value for each attribute, the non-key attribute STUDENT is fully dependent on the primary key, there is no transitive dependency for the non-key attribute STUDENT, and for ROLL_NO $->$ STUDENT, ROLL_NO is the super key of the table. Therefore the above table is in BCNF.

Now let's check for Multi-Valued Dependency :

Since there are only two columns there is not any multi-valued dependency in the above table hence the above table is in 4NF.

Rules for 4NF

A table is said to be in 4NF if the following conditions are met,

• The table is in Boyce-Codd Normal Form (BCNF).
• The table is not any having multi-valued dependency.

How to Satisfy 4NF ?

Let's see how we can satisfy the conditions of 4NF in DBMS by looking at the example we used in Multi-Valued Dependency

The multi-valued dependencies in the above table are,

EMPLOYEE_ID $->$ DEPARTMENT and
EMPLOYEE_ID $->$ HOBBY

To satisfy the fourth normal form, we can decompose the relation into two tables,

and,

Now the above relation is in 4NF.

So if $R(A, B, C, D)$ has A $->$ B and A $->$ C then, R is decomposed to $R1(A, B)$ and $R2(A, C, D)$.

In addition to multi-valued dependency, a table can have functional dependency too. In that case, the functionally dependent columns are moved to a different table, while the multi-valued dependent columns are moved to other tables.

Read More:

• Super Key in DBMS.
• DBMS Denormalization.
• Redundanc in DBMS.