0% completed
Converting an ER (Entity-Relationship) diagram to a relational model involves translating entities, attributes, and relationships into relational database tables. This process ensures that the structure defined in the ER diagram is accurately represented in a relational database, allowing data to be organized efficiently and consistently. In this lesson, we will convert an Employee Management System ER diagram into a relational model by following structured steps.
Based on the ER diagram provided, let’s go through each step to convert it into the relational model.
Step 1: Convert Entities into Tables
Each entity in the ER diagram is converted into a table. In our Employee Management System, we have two main entities:
- Employee: This entity will become an Employee table in the relational model.
- Payroll: This entity will be converted into a Payroll table.
Each table will have columns corresponding to the attributes of the respective entity, with one or more columns designated as primary keys to uniquely identify records.
Step 2: Define Attributes for Each Table
Employee Table
The Employee entity has several attributes, which will be converted into columns in the Employee table. Here's the table structure:
| Column Name | Data Type | Description |
|---|---|---|
| Employee_ID | INT (PK) | Primary key for each employee |
| First_Name | VARCHAR | First name of the employee |
| Middle_Initial | CHAR | Middle initial of the employee |
| Last_Name | VARCHAR | Last name of the employee |
| Birthdate | DATE | Date of birth of the employee |
| Gender | CHAR | Gender of the employee |
| Address | VARCHAR | Address of the employee |
| Position | VARCHAR | Job position/title of the employee |
Special Handling:
- Full_Name: As it is a composite attribute composed of First_Name, Middle_Initial, and Last_Name, these subcomponents are stored separately.
- Contact_Number: This is a multivalued attribute, meaning each employee may have multiple contact numbers. To represent this, we will create a separate table called Employee_Contact.
- Age: It is a derived attribute. So, ignore it to remove redundancy from the table.
Payroll Table
The Payroll entity will be converted into the Payroll table with the following structure:
| Column Name | Data Type | Description |
|---|---|---|
| Payroll_ID | INT (FK) | Primary key |
| Employee_ID | INT (FK) | Foreign key linking to Employee table |
| Monthly_Salary | DECIMAL | Monthly salary of the employee |
| Cash_Advance | DECIMAL | Cash advance taken by the employee |
| Tax | DECIMAL | Taxes that should be paid by employee. |
Special Handling:
- Net_Pay: It is a derived attribute. So, we are removing it.
Step 3: Handle Multivalued Attributes
Multivalued attributes need to be represented in separate tables to maintain the relational model structure. This step ensures that each unique value has its own row, preventing data redundancy.
Employee_Contact Table
To store multiple contact numbers for each employee, we create an Employee_Contact table, where each row represents a single contact number associated with an employee.
| Column Name | Data Type | Description |
|---|---|---|
| Employee_ID | INT (FK) | Foreign key linking to Employee table |
| Contact_Number | VARCHAR | Contact number of the employee |
Step 4: Define Relationships Between Tables
- In our Employee Management System ER diagram, we have a one-to-one (1:1) relationship between the Employee and Payroll entities. This relationship can be represented using a foreign key.
- Employee_ID in the Payroll table acts as a foreign key that references Employee_ID in the Employee table. This ensures that each payroll entry is associated with a valid employee, enforcing referential integrity.
- Furthermore, we have a one-to-many (1:M) relationship between the Employee and Employee_Contact entities.
Step 5: Final Relational Model Structure
After completing the conversion, our relational model for the Employee Management System will have the following tables:
-
Employee Table:
- Contains primary information about each employee, including Employee_ID, First_Name, Last_Name, Birthdate, Position, and Age (derived).
-
Payroll Table:
- Contains payroll information linked to each employee, including Employee_ID (foreign key), Monthly_Salary, Cash_Advance, and Net_Pay (derived).
-
Employee_Contact Table:
- Stores multiple contact numbers for each employee, with Employee_ID as a foreign key.
Here is the final relational diagram for the Employee Management System.
.....
.....
.....