What is 4NF in DBMS?

DBMSDatabaseBig Data Analytics

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

RegnoPhonenoQualification
1P1Diploma
1P1B.Tech
1P1M.Tech
1P2Diploma
1P2B.Tech
1P2M.Tech

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

RegnoPhoneno
1P1
1P2

R2

RegnoQualification
1Diploma
1B.Tech
1M.Tech

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.

raja
Published on 06-Jul-2021 12:26:24
Advertisements