FOURTH NORMAL FORM (4NF)

Multivalued Dependency-

• Multivalued dependencies are a consequence of first normal form (1NF) which disallows
an attribute in a tuple to have a set of values (Multiple values).
• Denoted by, A ->-> B, Means, for every value of A, there may exist more than one value
of B.
• If there is functional dependency from A-> B, then there will also a multivalued
functional dependency from A->->B.
• A trivial multivalued dependency X→→Y is one where either Y is a subset of X,
or X and Y together form the whole set of attributes of the relation.
• E.g. let the constraint specified by MVD in relation EMP as
Ename ->-> Pname
Ename ->-> Dname
EMP
Ename Pname Dname Redundancy due to two
independent
Smith X John
multivalued
Smith Y Anna
dependencies in same
Smith X Anna Relation.
Smith Y John

EMP_PROJECTS EMP_DEPENDENT

Ename Pname
Smith X Ename Dname
Smith Y Smith john
Smith anna

NOTE: The above EMP schema is in BCNF as no functional dependency holds on EMP, but
still redundancy due to MVD.

Hence 4NF is stricter than BCNF.

Consider the following example:
Pizza Delivery Permutations
Restaurant Pizza Variety Delivery Area
A1 Pizza Thick Crust Springfield
A1 Pizza Thick Crust Shelbyville
A1 Pizza Thick Crust Capital City
A1 Pizza Stuffed Crust Springfield
A1 Pizza Stuffed Crust Shelbyville
A1 Pizza Stuffed Crust Capital City
Elite Pizza Thin Crust Capital City
Elite Pizza Stuffed Crust Capital City
Vincenzo's Pizza Thick Crust Springfield
Vincenzo's Pizza Thick Crust Shelbyville
Vincenzo's Pizza Thin Crust Springfield
Vincenzo's Pizza Thin Crust Shelbyville
• Each row indicates that a given restaurant can deliver a given variety. The table has no
non-key attributes because its only key is {Restaurant, Pizza Variety, Delivery Area}.
Therefore, it meets all normal forms up to BCNF.
• If we assume, however, that pizza varieties offered by a restaurant are not affected by
delivery area (i.e. a restaurant offers all pizza varieties it makes to all areas it supplies),
then it does not meet 4NF. The problem is that the table features two non-trivial
multivalued dependencies on the {Restaurant} attribute (which is not a super key). The
dependencies are:
o {Restaurant} →→ {Pizza Variety}
o {Restaurant} →→ {Delivery Area}
• These non-trivial multivalued dependencies on a non-superkey reflect the fact that the
varieties of pizza a restaurant offers are independent from the areas to which the
restaurant delivers. This state of affairs leads to redundancy in the table:
• for example, we are told three times that A1 Pizza offers Stuffed Crust, and if A1 Pizza
starts producing Cheese Crust pizzas then we will need to add multiple rows, one for
each of A1 Pizza's delivery areas.
Varieties By Restaurant
Restaurant Pizza Variety
A1 Pizza Thick Crust
A1 Pizza Stuffed Crust
Elite Pizza Thin Crust
Elite Pizza Stuffed Crust
Vincenzo's Pizza Thick Crust
Vincenzo's Pizza Thin Crust
 Delivery Areas By Restaurant
Restaurant Delivery Area
A1 Pizza Springfield
A1 Pizza Shelbyville
A1 Pizza Capital City
Elite Pizza Capital City
Vincenzo's Pizza Springfield
Vincenzo's Pizza Shelbyville

• If we have two or more multivalued independent attributes in the same relation schema,
we get into a problem of having to repeat every value of one of the attributes with every
value of the other attribute to keep the relation state consistent and to maintain the
independence among the attributes involved. This constraint is specified by a
multivalued dependency.

4NF- A relation is in 4NF iff,

• It is in BCNF
• There must not exist any non-trivial multivalued dependency.
• Each MVD is decomposed in separate table, where it becomes trivial MVD.

• A 1992 paper by Margaret S. Wu notes that the teaching of database normalization

typically stops short of 4NF, perhaps because of a belief that tables violating 4NF (but
meeting all lower normal forms) are rarely encountered in business applications. This
belief may not be accurate, however. Wu reports that in a study of forty organizational
databases, over 20% contained one or more tables that violated 4NF while meeting all
lower normal forms.

Q Multi-valued dependency among attribute is checked at which level? (NET-JUNE-2005)

(A) 2 NF (B) 3 NF (C) 4 NF (D) 5 NF

Q Which of the following is false? (NET-DEC-2014)

(A) Every binary relation is never be in BCNF.
(B) Every BCNF relation is in 3NF.
(C) 1 NF, 2 NF, 3 NF and BCNF are based on functional dependencies.
(D) Multivalued Dependency (MVD) is a special case of Join Dependency (JD).
Q Match the following: (NET-DEC-2005)
(i) 5 NF (a) Transitive dependencies eliminated
(ii) 2 NF (b) Multivalued attribute removed
(iii) 3 NF (c) Contains no partial functional
dependencies
(iv) 4 NF (d) Contains no join dependency
(A) i-a, ii-c, iii-b, iv-d (B) i-d, ii-c, iii-a, iv-b
(C) i-d, ii-c, iii-b, iv-a (D) i-a, ii-b, iii-c, iv-d

Lossy/Lossless-Dependency Preserving Decomposition

• Because of a normalization a table is Decomposed into two or more tables, but during
this decomposition we must ensure satisfaction of some properties out of which the
most important is lossless join property/decomposition.
• if we decompose a table r into two tables r1 and r2 because of normalization then at
some later stage if we want to join(combine) (natural join) these tables r1 and r2, then
we must get back the original table r, without any extra or less tuple. But some
information may be lost during retrieval of original relation or table. For e.g.

R (A, B, C)
A B C
1 a p
2 b q
3 a r

R1 (A, B) R2 (B, C)
A B B C
1 a a p
2 b b q
3 a a r

A B C
1 a p
1 a r
 2 b q
3 a p
3 a r

• Decomposition is lossy if R1 ⋈ R2 ⊃ R
• Decomposition is lossy if R ⊃ R1 ⋈ R2

• Decomposition is lossless if R1 ⋈ R2 = R "The decomposition of relation R into R1 and R2

is lossless when the join of R1 and R2 yield the same relation as in R." which guarantees
that the spurious (extra or less) tuple generation problem does not occur with respect to
the relation schemas created after decomposition.
• This property is extremely critical and must be achieved at any cost.

A B C D E
A 122 1 W A
E 236 4 X B
A 199 1 Y C
B 213 2 Z D
How to check for lossless join decomposition using FD set, following conditions must
hold:

• Union of Attributes of R1 and R2 must be equal to attribute of R. Each attribute of R must

be either in R1 or in R2. Att(R1) U Att(R2) = Att(R)
• Intersection of Attributes of R1 and R2 must not be NULL. Att(R1) ∩ Att(R2) ≠ Φ
• Common attribute must be a key for at least one relation (R1 or R2)
• Att(R1) ∩ Att(R2) -> Att(R1) or Att(R1) ∩ Att(R2) -> Att(R2)
• If X ∩ Y forms a superkey of either X or Y, the decomposition of R is a lossless
decomposition.

E.g. Detailed example Explaination:-

5 NF
A Relational table R is said to be in 5th normal form if
a) it is in 4 NF
B) it cannot be further non-loss decomposed
 Dependency Preserving Decomposition
Let relation R be decomposed into Relations R1, R2, R3…………. RN with their respective
functional Dependencies set as F1, F2, F3…………. FN, then the Decomposition is Dependency
Preserving iff-

{F1 ∪ F2 ∪ F3 ∪ F4………. ∪ FN }+ = F+

Dependency preservation property, although desirable, is sometimes sacrificed.

Q R (A, B, C)
A--> B, B→C, C-→A
R1(A, B) AND R2(B, C)
LOSSLESS AND FD PREVERSING

Q R (A, B, C, D)
AB→CD, D→A
R1(A, D), R2(B, C, D)
LOSSLESS AND NOT FD PREVERSING
Q R(A,B,C,D) is a relation. Which of the following does not have a lossless join, dependency
preserving BCNF decomposition? (Gate - 2001) (2 Marks)
(A) A->B, B->CD (B) A->B, B->C, C->D
(C) AB->C, C->AD (D) A ->BCD

Q Consider a schema R (A, B, C, D) and functional dependencies A->B and C->D

Then the decomposition of R into R1(AB) and R2(CD) is (GATE-2001) (2 Marks)
(A) dependency preserving and lossless join
(B) lossless join but not dependency preserving
(C) dependency preserving but not lossless join
(D) not dependency preserving and not lossless join

Q Let the set of functional dependencies F = {QR → S, R → P, S → Q} hold on a relation

schema X = (PQRS). X is not in BCNF. Suppose X is decomposed into two schemas Y and Z,
where Y = (PR) and Z = (QRS).
Consider the two statements given below.
I. Both Y and Z are in BCNF
II. Decomposition of X into Y and Z is dependency preserving and lossless
Which of the above statements is/are correct? (GATE- 2019) (1 Marks)
(a) I only (b) Neither I nor II
(c) II only (d) Both I and II

Q Consider a schema R(A, B, C, D) and functional dependencies A → B and

C → D. Then the decomposition R1(A, B) and R2(C, D) is (NET-JUNE-2012) (Gate - 2001) (2
Marks)
(A) Dependency preserving but not lossless join
(B) Dependency preserving and lossless join
(C) Lossless Join but not dependency preserving
(D) Lossless Join

Q Consider a schema R(A, B, C, D) and following functional dependencies.

A→B
B→C
C→D
D→B
Then decomposition of R into R1(A, B), R2(B, C) and R3(B, D) is __________. (NET-NOV-
2017)
(1) Dependency preserving and lossless join.
(2) Lossless join but not dependency preserving.
(3) Dependency preserving but not lossless join.
(4) Not dependency preserving and not lossless join.

Q Consider a schema R(MNPQ) and functional dependencies M → N, P → Q. Then the

decomposition of R into R1(MN) and R2(PQ) is ________. (NET-JAN-2017)
(1) Dependency preserving but not lossless join
(2) Dependency preserving and lossless join
(3) Lossless join but not dependency preserving
(4) Neither dependency preserving nor lossless join.

Q Which of the following statements is TRUE? (NET-JULY-2016)

D1: The decomposition of the schema R(A, B, C) into R1(A, B) and R2 (A, C) is always
lossless.
D2: The decomposition of the schema R(A, B, C, D, E) having AD → B, C → DE, B → AE and
AE → C, into R1 (A, B, D) and R2 (A, C, D, E) is lossless.
(1) Both D1 and D2 (2) Neither D1 nor D2
(3) Only D1 (4) Only D2

Q Consider the table R with attributes A, B and C. The functional dependencies that hold on
R are : A → B, C → AB. Which of the following statements is/are True? (NET-AUG-2016)
I. The decomposition of R into R1(C, A) and R2(A, B) is lossless.
II. The decomposition of R into R1(A, B) and R2(B, C) is lossy.
(1) Only I (2) Only II (3) Both I and II (4) Neither I nor II

Q
Q (NET-DEC-2010)

Q The dependency preservation decomposition is a property to decompose database

schema D, in which each functional dependency X → Y specified in F, (NET-DEC-2010)
(A) appeared directly in one of the relation schemas Ri in the decomposed D.
(B) could be inferred from dependencies that appear in some Ri.
(C) both (A) and (B)
(D) None of these

Q The relation schemas R1 and R2 form a Lossless join decomposition of R if and only if:
(NET-JUNE-2015)
(a) R1 ∩ R2 ↠ (R1 - R2) (b) R1 → R2
(c) R1 ∩ R2 ↠ (R2 - R1) (d) (R2 → R1) ∩ R2
(1) (a) and (b) happens (2) (a) and (d) happens
(3) (a) and (c) happens (4) (b) and (c) happens

Q Relation R is decomposed using a set of functional dependencies, F and relation S is

decomposed using another set of functional dependencies G. One decomposition is
definitely BCNF, the other is definitely 3NF, but it is not known which is which. To make a
guaranteed identification, which one of the following tests should be used on the
decompositions? (Assume that the closures of F and G are available).(Gate-2002) (2 Marks)
(A) Dependency-preservation (B) Lossless-join
(C) BCNF definition (D) 3NF definition

Q Suppose R is a relation schema and F is a set of functional dependencies on R. Further,

suppose R1 and R2 form a decomposition of R. Then the decomposition is a lossless join
decomposition of R provided that: (NET-DEC-2008)
(A) R1∩R2→R1 is in F+
(B) R1∩R2→R2 is in F+
(C) both R1∩R2→R1 and R1∩R2→R2 functional dependencies are in F+
(D) at least one from R1∩R2→R1 and R1∩R2→R2 is in F+

Q Select the 'False' statement from the following statements about Normal Forms: (NET-
JUNE-2015)
(1) Lossless preserving decomposition into 3NF is always possible
(2) Lossless preserving decomposition into BCNF is always possible
(3) Any Relation with two attributes is in BCNF
(4) BCNF is stronger than 3NF

Q Which one of the following statements about normal forms is FALSE? (GATE-2005) (2
Marks)
(A) BCNF is stricter than 3NF
(B) Lossless, dependency-preserving decomposition into 3NF is always possible
(C) Lossless, dependency-preserving decomposition into BCNF is always possible
(D) Any relation with two attributes is in BCNF