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.
The complete ER-Diagram is as shown below.
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.
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.
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.
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.
The database schema is as shown below.
