GATE | GATE-CS-2015 (Set 1) | Question 65

Consider an Entity-Relationship (ER) model in which entity sets E1 and E2 are connected by an m : n relationship R12, E1 and E3 are connected by a 1 : n (1 on the side of E1 and n on the side of E3) relationship R13.

E1 has two single-valued attributes a11 and a12 of which a11 is the key attribute. E2 has two single-valued attributes a21 and a22 is the key attribute. E3 has two single-valued attributes a31 and a32 of which a31 is the key attribute. The relationships do not have any attributes.

If a relational model is derived from the above ER model, then the minimum number of relations that would be generated if all the relations are in 3NF is ___________.
(A) 2
(B) 3
(C) 4
(D) 5


Answer: (C)

Explanation:



Entity E1.
a1  a12
--------
a11 is key

Entity E2
a21  a22
--------
a22 is key

Entity E3
a31  a32
--------
a31 is key

R12 is m:n Relationship between E1 and E2
R12
a11     a22
-------------
(a11, a22) is key.


R13 is 1:n Relationship between E1 and E3
R13
a11   a31
-----------
(a11, a31) is key.


We need minimum no. of tables. 
Can we remove any of the above tables without 
loosing information and keeping the relations in 3NF?


We can combine R13 and R12 into one.  
a11   a31   a22
------------------
(a11, a31, a22) is key.


The relation is still in 3NF as for every functional 
dependency X -> A, one of the following holds
1) X is a superkey or
2) A-X is prime attribute 


Quiz of this Question



My Personal Notes arrow_drop_up