4NF Normal Form

4NF Normal Form A relation R is in Fourth Normal Form(4NF) if :

• It is in BCNF
• It has no Multivalued Dependencies

Definition of Multivalued Dependency 

Let R be the relational schema, X,Y be the attribute sets over R. A MVD (X→→Y) exists on a relation R : If two tuples t₁ and t₂ exists in R, such that t₁[X] = t₂[Y] then two tuples t₃ and t₄ should also exist in R with the following properties where Z = R – {X ∪ Y}:

• t₃[X] = t₄[X] = t₁[X] = t₂[X]
• t₃[Y] = t₁[Y] and t₄[Y] = t₂[Y]
• t₃[Z] = t₂[Z] and t₄[Z] = t₁[Z]

The tuples t₁, t₂, t₃, t₄ are not necessarily distinct.

To understand the concept of Multivalued Dependency – Click Here

Removal of Multivalued Dependency :

Decompose R using the same technique as for BCNF :

1. XY  is one of the decomposed relations.
2. All but Y – X  is the other decomposed relation.

For example : Consider the relation Person(Man(M), Phones(P), Dog_Likes(D)) defined in Multivalued Dependency post. The relation Person is changed to Person_Modify(Man(M), Phones(P), Dog_Likes(D), Address(A)).

Man(M)	Phones(P)	Dogs_Likes(D)	Address(A)
M1	P1	D1	49-ABC,Bhiwani(HR.)
M1	P2	D2	49-ABC,Bhiwani(HR.)
M2	P3	D2	36-XYZ,Rohtak(HR.)
M1	P1	D2	49-ABC,Bhiwani(HR.)
M1	P2	D1	49-ABC,Bhiwani(HR.)

In the relation Person_Modify, the shaded tuples increases the redundancy. The Key of the relation is MPD, and there are two multivalued dependencies exists in  the relation and one FD, which are –

1. FD1 : Man →→ Phones
2. FD2 : Man →→ Dogs_Like
3. FD3 : Man → Address

All dependencies violate 4NF. To remove MVDs, We decompose Person_Modify into relations as: In the above relations for both the MVD’s – ‘X’ is Man, which is again not the super key, but as X ∪ Y = R i.e. (Man & Phones) together make the relation. So, the above MVD’s are trivial and in FD 3, Address is functionally dependent on Man, where Man is the key in Person_Address, hence all the three relations are in 4NF. Point : Every nontrivial MVD is really an FD with a superkey on the left, if the relation is in 4NF.]]>