ER to Relational Conversion Algorithm for KIT712 – II

ER to Relational
Conversion Algorithm
for KIT712 – II

Special Case

Step 2.5 One-to-one relationship

This case occurs rarely but do occur. This step is to be done after Step 2 and before Step 3 of the algorithm; therefore consider it as an intermediate step.

Case 1: Relationship is not mandatory for both the entity.

HAS      (Student_id[fk1], ContactID[fk2],[pk])

Give the relation the same name as the relationship

The primary key of the relation is formed by concatenating the identifiers of the entities
on either end of the relationship line which represents the one-to-one relationship

Each of these identifiers becomes a foreign key

The relation will have a composite primary key
which consists of two foreign keys

Any attributes belonging to the relationship
are added to the relation

Case 2: Relationship is mandatory for only one of the entity

STUDENT (StudentID,[pk]…. ContactID[fk])

For each relation formed in Step 1,
if the relation was formed from an entity
which is at the non-mandatory end of a relationship line which represents a one-to-one relationship,
then we update the relation

The primary key of the relation formed from
the entity at the mandatory end of the relationship line becomes a foreign key in the relation formed from
the entity at the non-mandatory end of the relationship line

If the primary key of the relation formed from
the entity at the mandatory end of the relationship line
already exists in the relation formed from
the entity at the non-mandatory end of the relationship line,
then we designate it as a foreign key

If the primary key of the relation formed from
the entity at the mandatory end of the relationship line
does not already exist in the relation formed from the entity at the non-mandatory end of the relationship line, then we add it as a foreign key at the end of the list of attributes for the relation formed from
the entity at the non-mandatory end of the relationship line

Case 3: Relationship is mandatory for both of the entities

HAS      (Student_id[fk1], ContactID[fk2],[pk])

This is the trickiest relationship and need little intuition to handle this. In this case, when both the ends of relationship are mandatory, you do not need two relations. You can have only one. However, it may lead to breaking of previous algorithm as each entities may have relationship with other entities.

Therefore, here we handle the issue same as when this relationship is not mandatory for both the entities, i.e. Case 1.