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

and 3 tables for 3 entities? @arjun sir

The Gateway to Computer Science Excellence

+31 votes

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________________.

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________________.

0

is my approach correct,

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

and 3 tables for 3 entities? @arjun sir

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

and 3 tables for 3 entities? @arjun sir

+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.

Refer - Arjun sir's answer.

+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.

+60 votes

Best 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/

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.

+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

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."

Please explain !!!

Please explain !!!

+3

When we say R_{13 }is 1:n from E_{1 }to E_{3}, do we imply that for one value of the priary key (a_{11}), we have n entries in E_{3?} eg. for X11_{ }value of attribute a_{11 }there are tuples in R_{13} as (X11, P31), (X11,Q31) where P,Q are values of a_{31. }And also is the participation on E_{3} total?

Only in this case will the relation R_{13} 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 a_{31} and a11?

And a_{11} will be a foriegn key in R_{13} right?

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

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

Is this link still active?

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

@ayush updadyaya

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

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

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

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

+9 votes

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

If we try to merge relation R_{12} with either E_{1 }or E_{2} 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 R_{13 }is one-to-many relationship. Merging it with E_{3 }will not create a composite key and will not create any partial dependencies, so it can be merged.

Hence answer 4.

+2 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

0

@heena singh

"Entity E2

a21 a22

--------

a22 is key"

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

correct it plz.

- All categories
- General Aptitude 1.9k
- Engineering Mathematics 7.5k
- Digital Logic 2.9k
- Programming and DS 4.9k
- Algorithms 4.4k
- Theory of Computation 6.2k
- Compiler Design 2.1k
- Databases 4.1k
- CO and Architecture 3.4k
- Computer Networks 4.2k
- Non GATE 1.4k
- Others 1.4k
- Admissions 595
- Exam Queries 573
- Tier 1 Placement Questions 23
- Job Queries 72
- Projects 18

50,737 questions

57,355 answers

198,482 comments

105,249 users