Conversion to Relational Model Part-3

Conversion to Relational Model 

(Many-many relationship)

Create an additional relation for the relationship.

Define in the additional relation the primary keys of the other relations as well as all attributes of the relationship.

Note: The primary key of the relation modeled from the relationship needs to be determined after careful inspection

Example:

The above ER diagram is transformed to the following relational model:

Patient:

pNr  |  pName  | address 

Doctor:

dNr | dName

operation:

pNr | dNr | date | type | medication | sideEffects

In operation relationship, date needs to be part of the primary key. The same patient can be operated by the same doctor on two different dates for example. In that case values for pNr and dNr become the same in two different tuples. We know that the value of primary key has to be different in every tuple. So we need to add date as a part of the primary key for the relation operation in order to make the key unique.

 

Converting ER diagrams to Relational Model Part 2

Converting ER diagrams to Relational Model

(One-many relationship)

Add the primary key of the relation with the “1” as foreign key to the relation with the “n”.

The above 1-n relationship in the ER diagram can be converted to a relational model as follows:

Employee:

famName | givenName | salary | dName

          primary key (pk)               foreign key (fk)

Department:

dName | address

 pk