new table with filed {p1,p2,n1,n2} can serve the same purpose, they are not empose any condition like normalization

The Gateway to Computer Science Excellence

+43 votes

Consider the following $ER$ diagram

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

- $2$
- $3$
- $4$
- $5$

+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

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

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

+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

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

+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

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)

???

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

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

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

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?

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

P1 should be the key of combined relation. P1 --> M1 and M1 --> M2 transitive dependency.

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 we require * only one table for M-R1-P* with

Correct me, if i am wrong.

0

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).**

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

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)

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)

+2 votes

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.

52,345 questions

60,469 answers

201,795 comments

95,271 users