What is 4NF in DBMS?

A relation R is in 4NF if it is in BCNF and there is no non-trivial multivalued dependency.

For a dependency A->B, if for a single value of A, multiple values of B exist, then the relation will be a multi-valued dependency.

Example

Consider the following table

Here,

regno->-> phoneno

regno->-> qualification.

Both are non trivial MVD

The given relation is in BCNF [since no functional dependency exists]. But the above table is not in 4NF [since there is a non trivial MVD].

Anomalies

It also suffers with anomalies which are as follows −

• Insertion anomaly: If we want to insert a new phoneno for regno3 then we have to insert 3 rows, because for each phoneno we have stored all three combinations of qualification.

• Deletion anomaly: If we want to delete the qualification diploma, then we have to delete it in more than one place.

• Updation anomaly: If we want to update the qualification diploma to IT, then we have to update in more than one place.

4NF decomposition

If R(XYZP) has X->->Y and X->->Z then, R is decomposed to R1(XY) and R2(XZP).

=> R(regno, phoneno, qualification) is decomposed to R1(regno, phoneno) and R2(regno, qualification).

R1

R2

All the anomalies discussed above are removed. The above two relations are in 4NF.

Now, regno->->phoneno is trivial MVD (since {regno} U {phoneno}=R1)

=>R1 is in 4NF.

Regno->-> qualification is trivial MVD (since {regno} U {qualification} =R2)

=> R2 is in 4NF.