405 Converting ER Diagrams to Relational Model PDF

405 Converting ER Diagrams to Relational Model Converting an ER (Entity-Relationship) diagram to a relational model involves translating ** ** ** ** entities, attributes, and relationships into relational database...

405 Converting ER Diagrams to Relational Model 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: 1. Employee Table: ` ` Contains primary information about each employee , including ** ` ` ** ** ` Employee_ID , ` ** ** ` First_Name , ` ** ** ` Last_Name , ` ** ** ` Birthdate , ` ** ** ` Position , and ` ** ** ` Age (derived). ` ** *** *** 2. Payroll Table: ` ` Contains payroll information linked to each employee , including ` ` ** ` ` ** ** ` Employee_ID (foreign key), ` ** *** *** ** ` Monthly_Salary , ` ** ** ` Cash_Advance , and ` ** ** ` Net_Pay (derived). ` ** *** *** 3. 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. * == ** **== * ** ** Notice that the Employee_Contract table has a composite primary key comprised of ` ` ** ** ` {Employee_ID, Contact _Number} ` Remember that Full_Name is a composite attribute comprised of {First _Name, ` ` *** *** ` Middle_Name, Last_Name} `