GATE Overflow Test Series | Databases | Test 1 | Question: 14

Question

Consider a $m:n$ relationship and an ER model using it. Which of the following is/are correct for converting this relationship to a relational instance? (Mark all the appropriate options)

• A. If participation of both the entities are mandatory then we only need $2$ relations
• B. If participation of both the entities are mandatory then we need $3$ relations
• C. If participation of only one entity is mandatory then we only need $2$ relations
• D. If participation of both the entities are optional then we need $3$ relations

Comments

Option C is also correct

A explanation given below
https://www.youtube.com/watch?v=pN0rFwz5bug&t=624s
11:03 / 17:15

Answer 1

We need to be certain that if we store an identifier for one entity in a relation representing another entity, that the identifier never has a null value.

For $m:n$ relationship irrespective of the participation we need to have $2$ separate relations for the $2$ entities and a separate relation for their relationship having the keys of both the entities.

So, options B and D only are correct.

Reference: https://gateoverflow.in/?qa=blob&qa_blobid=9717024449754071080

Comments

gatecse sir, 

m-to-n with both side total participation,we can do that in one table.

m-to-n with one side total participation, we can do that in 2 table(relation will go into total side)

---

D is not correct

---

Please see the given reference. We cant join together all relations while converting from ER to relational.

---

@gatecse It means if in question it is not mention that columns can contain NULL values then we need 3 table irrespective of participation.

Answer 2

m:n 

1. both end partial participation we need 3 table.
2. one end partial participation  : 2 table (merge at the side of total participation).
3. Both total participation : 1 table enough. so otion C and D are correct only. in these cases you may get partial dependency but as in question nothing is mentioned which form of normalization it should satisfy so by default we will take 1 NF and correct answer should be C and D.