The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
x
+34 votes
4.5k views

Consider the following $ER$ diagram

The minimum number of tables needed to represent $M$, $N$, $P$, $R1$, $R2$ is

  1. $2$
  2. $3$
  3. $4$
  4. $5$
asked in Databases by Veteran (59.5k points)
edited by | 4.5k views
0
why we can't make one table for p and n as they ask for minimum no of table.

new table with filed {p1,p2,n1,n2} can serve the same purpose, they are not empose any condition like normalization
0
from directed edge we can find for R1 that

 it can be one-to-one or it can be many to one

in both the cases we dont need any new table for R ,just add primary key p1 into TOTAL side which acts as a foreign key referring to p table
0
Which normal form should our relations satisfy?
0

FYI  ->For this kind of questions  for cross verification could be done via small example.

Link for $2^{nd}$ part  -> https://gateoverflow.in/87025/gate2008-83

0

Hi @prayas ji, Based on obtained tables and their respective keys it is in BCNF.

4 Answers

+53 votes
Best answer

First strong entity types are made to tables. So, we get two tables $M$ and $P$.

I assume $R1$ is $1:1$ or $1:n$ as that would minimize the number of tables as asked in question. 

Now participation of $M$ in $R1$ is total (indicated by double arrow) meaning every entity of $M$ participate in $R1$. Since $R1$ is not having an attribute, we can simple add the primary key of $P$ to the table $M$ and add a foreign key reference to $M$. This handles $R1$ and we don't need an extra table. So, $M$ becomes ${M1, M2, M3, P1}$. 

N here is a weak entity weakly related to $P$. So, we form a new table $N$, and includes the primary key of $P (P1)$ as foreign key reference. Now $(P1, N1)$ becomes the primary key of $N$. 

Thus we get $3$ tables.

$M: {M1, M2, M3, P1}$ - $M1$ primary key, $P1$ references $P$

$P: {P1, P2} - P1$ primary key

$N: {P1, N1, N2} - (P1, N1)$ primary key, $P1$ references $P$. 

So, answers is B.

answered by Veteran (355k points)
edited by
+1
@Arjun
If it's 1:1 relation from M to P, then M, R1 and P can be combined together, as it's total participation from the side of M.

So the answer of this question depends on whether R1 is 1:1 or M:1
0
@manu00x, even if it's 1:1 then if we combine those 2 entities then it will be in 1NF and no more in 3NF so can't combine.
0
@Rajesh where did they mention about the relations to be in 3 NF?
0
" if we combine those 2 entities then it will be in 1NF and no more in 3NF so can't combine. " will check thanx. No, no thanx 3nf problem is a different scenario i.e. transitivity. Then as a matter of fact even if u keep 2 tables with one table having p.k of the other to show the rel'n & all of the other(total participation side) then still in that case (wid 2 tables remember) transitivity is shown as it's all game of the p.k.s.
0
3nf doesn't help or reduce the problem here it's infact a separate issue also doing a table division 'duty'.
0
What if we were given that both R1 and R2 were having many to many relationships.

then we will have one more table for R1 , but will we have one more table for R2 also ? since it is a weak relation ..

what will be the answer then ? (min. number of tables )

4 or 5?
0
Which normal form should our relations satisfy?

In 1nf we can further minimise the tables
0
@Xylene as M1 is a key then M1 P1 cannot be primary key it will be a super key
+1
@Manu Thakur, sir please confirm this, If we assume R1 is 1:1 relation and combine all M, R1, P into one relation then this relation will be in 2nf as there is no partial dependency. But yes it is not 3nf as there is a transitive dependency.

P1 should be the key of combined relation. P1 --> M1 and M1 --> M2 transitive dependency.
+2
@Hemant Parihar, you are correct.
+8 votes

so we have 3 tables here
this imply that

Q.82 = option B
Q.83 = option A

answered by Boss (30.7k points)
+2 votes

 for 82 ---> 3 table will be there ----

 for 83 ---> ans is a--> cause the R1 (M1,P1) and M(M1,M2,M3) ----both have the same primary key ,so we can merge them , so new table will be (m1,m2,m3,p1)

answered by Veteran (59.5k points)
0
for 82

the double rectangale means its a weak entiry and can be combined with the relation R2

so isn't needs 4 tables to represent them ? i.e. M,P,R1 ,(R2,N)
0
@bikram sir

weak entity requires extra table

ur suggestion?
+1

@  hem chandra 

yes, weak entity requires extra table .

  • Weak Entity Set Cannot exists alone
  • To build a table/schema for weak entity set we construct a table with one column for each attribute in the weak entity set
0
Which normal form should our relations satisfy?
0
@bikram sir

why M and P is not merged here?? If it is 1:1 cardinality and total participation then we can merge both M and P.
0 votes

GATE | GATE CS 2008 

M, P are strong entities hence they must be represented by separate tables.

Many-to-one and one-to-many relationship sets that are total on the many-side can be represented by adding an extra attribute to the “many” side, containing the primary key of the “one” side. ( This way no extra table will be needed for Relationship sets )

M table is modified to include primary key of P side(i.e. P1). N is weak entity, and is modified to include primary key of P (i.e, P1).

Therefore there would be minimum of 3 tables with schema given below :

M ( M1, M2, M3, P1)
P ( P1, P2 )
N ( P1, N1, N2 )

Note: This modification of a table in the case of one-many or many-one to include relationship set at the many side works well, but only in the case when the relationship set doesn’t have its own attributes. If the relationship set has its own attribute then we need to make a separate table for the relationship set also.

answered by Junior (687 points)
Answer:

Related questions



Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true

38,018 questions
45,509 answers
131,698 comments
48,742 users