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

Question

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

Comments

why option D is correct ?…. i have studied that if the relation is many to many then only there will be a relationship table created...and in other cases, a relationship table will not be created

---

as per gate pyq’s they always say 2 table for 1:1 relationship, and they can’t talk about mandatory or optional so both of mandatory and optional we van manage in 2 relation(table).

Answer 1

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

So, if both the entities participate fully, we just need a single relation representing both.

If one entity participation is optional, we need a separate relation for both the entities with the relation for the mandatory entity having a reference to the relation for the optional one (so that there is no NULL reference). This will be similar to an Employee - Manager relation where every Employer must have a Manager but a Manager may not have any Employee under him.

If participation of both the entities are optional, we need to have three relations - one each for the entities and a separate relation for the relationship having the primary keys of both the entities.

So, here only options B and C are correct.

Reference: MIT notes

Comments

@gatecse when nothing is mentioned about normal form and redundancies then also we should have to go with 3NF?

---

https://www.cs.uct.ac.za/mit_notes/database/htmls/chp06.html#relationship-participation-condition-membership-class

---

In this answer, tables are fewer which is mentioned here 
https://gateoverflow.in/123659/self-doubt