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

First time here? Checkout the FAQ!

x

+32 votes

Consider the following $ER$ diagram

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

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

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

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

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

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

+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

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?

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

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

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

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

- All categories
- General Aptitude 1.3k
- Engineering Mathematics 5.2k
- Digital Logic 2k
- Programming & DS 3.7k
- Algorithms 3.2k
- Theory of Computation 4k
- Compiler Design 1.6k
- Databases 3k
- CO & Architecture 2.6k
- Computer Networks 3k
- Non GATE 1k
- Others 1.3k
- Admissions 487
- Exam Queries 436
- Tier 1 Placement Questions 18
- Job Queries 56
- Projects 9

36,194 questions

43,647 answers

124,088 comments

42,928 users