edited by
18,574 views
56 votes
56 votes
Consider an Entity-Relationship $(\text{ER})$ model in which entity sets $E_{1}$ and $E_{2}$ are connected by an $m:n$ relationship $R_{12}$. $E_{1}$ and $E_{3}$ are connected by a $1 : n$ ($1$ on the side of $E_{1}$ and $n$ on the side of $E_{3}$) relationship $R_{13}$.

$E_{1}$ has two-singled attributes $a_{11}$ and $a_{12}$ of which $a_{11}$ is the key attribute. $E_{2}$ has two singled-valued attributes $a_{21}$ and $a_{22}$ of which $a_{21}$ is the key attribute. $E_{3}$ has two single-valued attributes $a_{31}$ and $a_{32}$ of which $a_{31}$ is the key attribute. The relationships do not have any attributes.

If a relational model is derived from the above $\text{ER}$ model, then the minimum number of relations that would be generated if all relation are in $\text{3NF}$ is________________.
edited by

8 Answers

Best answer
91 votes
91 votes

Answer is $4$. The relations are as shown:

$\langle a_{11}, a_{12}\rangle$ for $E_1$

$\langle a_{21}, a_{22}\rangle$ for $E_2$

$\langle a_{31}, a_{32}, a_{11}\rangle$ for $E_3$ and $E_1-E_3$ relationship

$\langle a_{11}, a_{21}\rangle$ for $m:n$ relationship $E_1-E_2$

We cannot combine any relation here as it will give rise to partial functional dependency and thus violate $\text{3NF}.$

Reference: MIT notes

edited by
17 votes
17 votes

We have 4 Tables generated from this ER Diagram:

$$\begin{align*} E_1\left(a_{11}, a_{12} \right )\\ E_2\left(a_{21}, a_{12} \right )\\ R_{12}\left(a_{11}, a_{21} \right )\\ R_{13}E_3\left(a_{31}, a_{32}, a_{11}\right ) \end{align*}$$

we have to keep a separate table for $R_{12}$ because if we combine it with any other entity it will violate 1NF, as atomic values won't be there for some attribute. So, to preserve the relationship between $E_1$ and $E_2$ we need to have a separate table $R_{12}$

14 votes
14 votes

For a relation to be in 3NF all functional dependencies should be fully on candidate key.


If we try to merge relation R12 with either E1 or E2 then the new relation will have a compostie key since it is a many-to-many relation. And this will lead to partial dependency. Hence it cannot be merged. 

Relation R13 is one-to-many relationship. Merging it with E3 will not create a composite key and will not create any partial dependencies, so it can be merged.

Hence answer 4.

4 votes
4 votes
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 
Answer:

Related questions

49 votes
49 votes
4 answers
1
makhdoom ghaya asked Feb 13, 2015
17,035 views
Consider the following relation:$$\overset{\text{Student}}{\begin{array}{|c|c|}\hline\\\underline{\textbf{Roll_No}}& \textbf{Student_Name}\\\hline1& \text{Raj} \\...
80 votes
80 votes
7 answers
2
makhdoom ghaya asked Feb 13, 2015
28,737 views
The least number of temporary variables required to create a three-address code in static single assignment form for the expression $q + r / 3 + s - t * 5 + u * v/w$ is_...
32 votes
32 votes
9 answers
3
makhdoom ghaya asked Feb 13, 2015
24,371 views
Let G be a connected planar graph with 10 vertices. If the number of edges on each face is three, then the number of edges in G is_______________.