One-to-One Relationships in ER Diagram to Database Schema

For Complete YouTube Video: Click Here

In this class, we will convert One-to-One Relationships in ER Diagram to Database Schema.

We have already discussed the conversion of the works_for relationship in the ER-Diagram to Database schema.

One-to-One Relationships in ER Diagram to Database Schema

The below image is the database schema that we have converted so far.

ER Diagram to Database Scheme 1 - Diagram 5
One-to-One Relationships in ER Diagram to Database Schema

The complete ER-Diagram is as shown below.

One-to-One Relationships in ER Diagram to Database Schema 2
One-to-One Relationships in ER Diagram to Database Schema

In the above ER-Diagram, we have only one One-to-One Relationship ‘manages.’

We got the manages relationship from the department manager attribute of the department table.

We convert that manages relationship to an attribute called mgr_ssn into the department table.

The mgr_ssn in the department table is referencing the ssn of the employee table, as shown below.

One-to-One Relationships in ER Diagram to Database Schema 3
One-to-One Relationships in ER Diagram to Database Schema 3

Shifting the attributes of a relationship type

From the ER-Diagram, we can see that the ‘manages’ relationship has a start_date attribute.

Where will the start_date attribute be shifted, whether to the employee table or the department table?

This point is essential to understand.

We can shift the attributes of a one-to-one relationship to the ‘full participating’ entity type.

In our case, it is the department entity type.

Why?

To understand this, we will consider the image shown below.

One-to-One Relationships in ER Diagram to Database Schema 4
One-to-One Relationships in ER Diagram to Database Schema 4

In the above image, the employees who are managers are 1, 2, and 4.

The image below is the employee table after shifting the start_date to the employee table.

One-to-One Relationships in ER Diagram to Database Schema 5
One-to-One Relationships in ER Diagram to Database Schema 5

Only three employees have the start_date values remaining employees’ values are null.

Assume that company has a thousand employees and ten departments.

Only ten employees will have the start_date values 990 employees’ values will be null.

Assume that company has a thousand employees and ten departments.

Only ten employees will have the start_date values 990 employees’ values will be null.

If we shift the start_date to the department table, there will be no null values, as shown below.

One-to-One Relationships in ER Diagram to Database Schema 6
One-to-One Relationships in ER Diagram to Database Schema 6

The database schema is as shown below.

One-to-One Relationships in ER Diagram to Database Schema 7
One-to-One Relationships ER Diagram to Database Schema 7