5k views
Consider an Entity-Relationship $(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 $ER$ model, then the minimum number of relations that would be generated if all relation are in $3NF$ is________________.

edited | 5k views
0
is my approach correct,

All keys in one table: <a11,a21,a31>

and 3 tables for 3 entities? @arjun sir
0
no...
+1

Following assumption is wrong (Thank You @@krish__  ji and @reena_kandari ji)

I think answer should be 5 because in "<a31, a32, a11> for E3 and E1-E3 relationship

on many side participation is not total. So some values of column a11 will NULL. And Primary Key for this combined relation will be (a11,a31) and in case of composite primary key any attribute can not take NULL value.

https://stackoverflow.com/questions/386040/whats-wrong-with-nullable-columns-in-composite-primary-keys

+1
@Chhotu, E3 is a relation wherein each row can be related to atmost one row in E1. Thus a31 which is the primary key of E3 doesn't need to repeat in the modified E3 table and it can still remain as the primary key. Thus NULLs in a11 wouldn't be an issue.
+3
@Chotu, remember :$1$ on the side of E1 and $n$ on the side of E3.

it means E1 can relate to atmost $n$ of E3... but E3 can relate to atmost $1$of E1.

now see..entries of E3   <a31,a11> will be like this (a31 is the only primary key here)

101, E1

102,null

103,E2

104,E2

........like this
0
What If the question was asked of 1NF instead of 3NF. Then what would be the answer?

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

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

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

$\langle a_{31}, a_{32}, a_{11}\rangle$ for $E3$ and $E1-E3$ relationship

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

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

http://cisnet.baruch.cuny.edu/holowczak/classes/9440/entityrelationship/

by Veteran (431k points)
edited by
0
there will be one more table for  E3 .i think relation for E1 nd E3 will be included in table for E3 itself as it is 1:n..else total =5 ,in the solution provided  by gateforum,they have not taken any table for E3 but decomposed the relation E1 E3 to avoid any transitive dependency between a11 and a32..that way they say ans to be 5..please explain
+1

There is no transitive dependency in the above 4 relations rt? I didn't get what you were telling. This is GATE 2015 question and hence we have the official key and answer is 4 only.

http://gate.iitk.ac.in/GATE2015/AnsKey2015/CS_S05.pdf

0
wont there be any table for E3 like E1 and E2?
0
Yes. But it also includes (a11) and thus R13.
+1
Ok got it..thank you.
+3

In the table for <a31, a32, a11> for E3 and E1-E3 relationship some a11 will remain null since E3 does not fully participate in the relation ..Right?

0
"not fully"

where is it mentioned?
0
So , Arjun Sir , for this question we are considering that there is total dependency between E1 and E3 through relation R3 hence we are considering a two relations for E1 and (E3 and R1) , ryt ? if we wouldn't have done that then there would have been 3 relations for E1 , R1 , E3 respectively ryt ?
0
@Arjun sir....what is meaning of "We cannot combine any relation here as it will give rise to partial functional dependency and thus violate 3NF."

+3

When we say R13 is 1:n from Eto E3, do we imply that for one value of the priary key (a11), we have n entries in E3? eg. for X11 value of attribute a11 there are tuples in R13 as (X11, P31), (X11,Q31) where P,Q are values of a31. And also is the participation on E3 total?

Only in this case will the relation R13 be in 3NF as the primary key shall be a31 alone since FDs are            a31-->a32(given) and a31-->a11(one to many)

+1

@Arjun Sir,

For relation R13 can we use primary key as combination of a31 and a11?

And a11 will be a foriegn key in R13 right?

+2
@Arjun Sir, SO we have to assume E1-E3 has total participation ?
0
Here, for 1:N relationship why we need seprate table, plz explain?
0

@Arjun

What is meaning of this statement "We cannot combine any relation here as it will give rise to partial functional dependency and thus violate 3NF." How could we have combined otherwise if the condition of 3NF was not given.

0
0
@Arjun Sir, If the question was asked of 1NF instead of 3NF. Then what would be the answer?
0
@arjun sir, in <a31, a32, a11>  for E3 and E1-E3 relationship what combination of attributes make up the key? I'm not getting the clarity here. we know a31 -> a32 in E3, then now even after combining E3 and E1-E3 relationship  is the key still {a31} i.e. a31 -> a11 as well? Because otherwise there will be partial dependency i think? Please help sir.
0

@  Queenia Agrawal, in $E3$ table, attributes are $A31, A32$ & $A11$ where $A31$ is the primary key & $A11$ is the foreign key

0

As we know that primary key is the minimal superkey , since in E3 we already have a primary key a31 and since cardinality of E3 is 1 hence it will combine with only 1 tuple of E1

So initially if E3 would have been

A31.       A32.

A31->A32

And suppose E3 after combining with E1 would have been

A31.        A32.     A11

Since the cardinality of E3 was 1 hence a31 must would have been combined with exactly 1 tuple from E1

Thus here A31 will be all distinct

A11 will be repeating

Hence here also

A31->A32,A11

Hence A31 will be the only primary key that's why this merging is also in 2NF since no partial dependency and also in 3NF since no transitive dependency.

And yes in E3 A11 will be the foreign key referring to A11 in E1
0
Even these 4 relations will be in bcnf also.

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}$

by Boss (30.8k points)
0
How does it violate atomicity?
–2
because of the cardinality ratios.
0

we have to keep a separate table for R12 because if we combine it with any other entity it will violate 1NF, as atomic values won't be there for some attribute.

Yes, it is true. if we add $a_{21}$ to $E_{1}$, $a_{21}$ may become multi valued beacuse of many to many participation. LIke wise if we add $a_{11}$ to $E_{2}$, $a_{11}$ may become multi valued. So we create new relation for $R_{12}$.

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.

by Active (1.8k points)
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 
by Active (1k points)
0

@heena singh
"Entity E2
a21  a22
--------
a22 is key"

Here a21 is key and that is given in the question.

correct it plz.

by Active (1.4k points)
0
how?
–1