The Gateway to Computer Science Excellence
+43 votes
8.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$
in Databases by
edited by | 8.5k views
+3
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?
+1

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.

0
This Question needs improvement, Directed edge does not imply anything here without dot on the other end. More over they should have given min. max. cardinality to identify relationship status.
0
3NF

5 Answers

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

by
edited by
+1

@reena_kandari

 @Xylene is correct,

tupples in M are related to atmost one tupple of  P(1 side from M) and moreover every tupple in M is related to some tupple of P(total participation), combining both we can say that every tupple in M is exactly related to one tupple of P.

therefore there is no chance that P1 has NULL value for some M1

0
here M1 is primary key and P1 is foreign key which reference to M1

here M is total participation means every attributes M1 there should be entry so M1 shouldnot be NOT NULL but it need not mean that P1 cant be NULL , P1 may or may not be NULL
0
'M1 shouldnot be NOT NULL'  on your lines, have u analyzed what this means that m1 has full participation which's correctly correct but this also means there's a p1 to every m1. so p1 not null.
0
@arjun sir, Why cant we store [M R1 P] in 1 table, assuming 1:1 cardinality and total participation is also given?

Thanks!

Really 1:1 rel'n is the one who hs gone unnoticed. Is it that good that we can combine into 1 only (simply)

???
+3
@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
+4
@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.
0
but @Arjun sir, if R1 is in 1:M relation then relaiton must be given to the Many side which means to the table P na? please tell me where I am getting wrong.
0
I think R1 should be N:1 relation.
0

@Arjun Sir, What does the arrow indicate?

0
If arrow is present on both left and right side then it means one-one relationship.
0

@Arjun

I think we require only one table for M-R1-P with Primary key equal to P1 only. Coz if you make P1,M1 Primary Key, we can have Null values (consider a case where no. of elements in P is greater than no. of elements in M and mapping in such a way that atleast one elemnet of P is not mapped to any element of M).

Correct me, if i am wrong. 

0

@ayushsomani

No, we do require 2 tables for M-R1-P .

According to you, If 1 table and Primary key equal to P1......

Now, the problem that arises is as relation R1 is Many:One for a single value of P1 there might be cases that it can map to multiple values of M1, which is not allowed (multiple values in a single cell).

+13 votes

so we have 3 tables here
this imply that

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

by
+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)

by
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?
+3

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

by
0 votes
Relationship  between M & P is N:1 so solve Accordingly .

The Side which Has "Arrow" has Cardinality = 1 always.(Remember That).
by
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
52,345 questions
60,469 answers
201,795 comments
95,271 users