edited by
18,818 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

1 votes
1 votes
Three tables would be required for  three entities E1 ,E2,E3 now R12 is many to many relationship so new table will be created with(a11,a21) as key.R13 is one to many relationship so need of extra table

E1(a11,a12)

E2(a21,a22)

E3(a31,a32,a11') a11' is foreign key

R(a11,a21)
0 votes
0 votes

Let first relation $R_1$ be $R_1(A_{11}, A_{12}, A_{21}, A_{22})$

Let’s write its Functional Dependencies
$A_{11} → A_{12} \\ A_{21} → A_{22}$

The Candidate Key here is $A_{11}A_{21}$ as $(A_{11}A_{21})^{+} = R_1$

$A_{11} → A_{12} \\ A_{21} → A_{22}$

dependencies violate the rule of 3CNF, So easiest way is to append $A_{21}$ to first and ${A_{11}}$ to second dependency.

Now we have

$R_3(A_{11}, A_{21}, A_{12}) \\ R_4(A_{11}, A_{21}, A_{22})$

Note that here for every table the CK is $(A_{11}A_{21})$ which is its super key. So these are in 3CNF.


Coming to the second relation in the ER Diagram

$R_2(A_{11}, A_{12}, A_{31}, A_{32})$

Here we have
$A_{31} → A_{32}$

 

$A_{31} → A_{11}A_{12}$   Using $A_{31}$ you can get the one row in $E_1$ that you need.

 

$A_{11} → A_{12}$,



Candidate Key and Super Key is $A_{31}$

Problematic Dependency is $A_{11} → A_{12}$ as it is a partial dependency

So decompose it into a separate table


$R_5(A_{11}, A_{12})$ with $A_{11}$ as Super Key
and


$R_6(A_{31}, A_{32}, A_{11})$ with $A_{31}$ as Super Key

Therefore Valid BCNF decompositions are  $R_3, R_4, R_5$ and $R_6$.

Answer is 4

0 votes
0 votes

and remaining tables cannot combined if combined violates 2nf.

Answer:

Related questions

49 votes
49 votes
4 answers
1
makhdoom ghaya asked Feb 13, 2015
17,288 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
29,049 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_...
33 votes
33 votes
9 answers
3
makhdoom ghaya asked Feb 13, 2015
24,615 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_______________.