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

Question

Consider a $1: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 need only $1$ relation
• B. If participation of "one-end" is mandatory then we need only $2$ relations
• C. If participation of "one-end" is optional then we need only $2$ relations
• D. If participation of both the entities are optional then we need $3$ relations

Comments

i think b and c should be the correct answer...as the relationship is one to many there would be only 2 relations required...and the total participation entity will contain the primary key of other entity

Answer 1

While converting from ER diagram to Relational Model, 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. Also, we should not introduce any redundancies.

If both the entities participate fully, we need two relations as one entity relates to multiple other entities. Here, we will have $2$ relations for both the entities and the relation for the entity at the "one end" will have the key of the entity at the "many-end" (we cannot have the key of the entity at one-end to the relation for the entity at many-end as this will cause multi-valued attributes since one entity in many side maps to multiple entries on the one-side).

If participation of "many-end" is mandatory/ we can just use the above $2$ relations and we won't have any NULL values.

If the participation of "many-end" is optional, the above construction fails due to NULLs and we need to have $3$ relations, one each for the $2$ entities and another for the relationship having the keys of the $2$ entities.

If participation of both the entities are optional, then also we need $3$ relations.

Now, both options B and C do not enforce that many-end is “mandatory”. So, both will require $3$ relations.

So, only option D is correct.

Comments

@gatecse can we consider recursive relationship in terms of these rules? If yes, in the ER diagram example of SUPERVISION relationship (Employee (Supervisee) to Employee (Supervisor)) which is one-to-many relation with optional participation on both sides, why don’t we have SUPERVISION relation?

---

@gatecse

https://gateoverflow.in/3717/gate2004-it-73 In this question, for one to many relationship with both sides having optional participation, they are taking 3 relations (2 + 1 for multivalued attribute) but if you go by approach given in this source https://www.cs.uct.ac.za/mit_notes/database/htmls/chp06.html#mandatory-and-optional-relationships 

4 relations will be required. what is the correct approach? i’m confused.

---

@gatecse , @Arjun sir

if “one end ” is optional that doesn’t mean many end is mandatory??? 

if yes then as you mentioned only 2 relation needed then option C also correct.

 

If participation of "many-end" is mandatory/ we can just use the above 2 relations and we won't have any NULL values.

Answer 2

As nothing is mentioned in question about null or redudancy by default we will follow 1 Nf.

Many to one :

1. Both side optional : 2 table.
2. Many side optional and other side total : 1 table.
3. One side partial and many side total : 2 table.
4. Both side total : 1 table.so only option A nd C are correct

Comments

yes you are right. In this test, all the four questions related to this have wrong answers.

---

That default is wrong. Please see standard reference: https://www.cs.uct.ac.za/mit_notes/database/htmls/chp06.html#mandatory-and-optional-relationships