An Entity-Relationship (ER) diagram is a graphical representation of entities, their attributes, and the relationships between them in a database. In this lesson, we will explain the process of creating an ER diagram by using an Employee Management System as an example.

In this lesson, we will analyze the provided requirements and gradually build the diagram step by step. The requirements describe the key components of the system, including entities, attributes, and relationships, which we will translate into a final ER diagram.

Requirements for the Employee Management System

1. Record the information of each Employee.
2. Record the information of each Payroll.
3. Each Employee should receive the Payroll.
4. Each Employee must be associated with a unique Payroll record.

Steps to Create an ER Diagram

Step 1: Identify Entities

The first step in creating an ER diagram is to identify the main entities in the system. Entities represent real-world objects or concepts for which data needs to be stored.

• In this Employee Management System, the primary entities are:
  • Employee: Represents individual employees within the organization.
  • Payroll: Represents payroll information associated with each employee.

Step 2: Define Attributes for Each Entity

Each entity should have specific attributes that describe its properties. Attributes are represented as ellipses connected to their respective entities.

Employee Entity

The Employee entity includes the following attributes:

• Employee_ID: A unique identifier for each Employee. This acts as a key attribute.
• Full_Name: A composite attribute composed of First_Name, Middle_Initial, and Last_Name.
• Birthdate: A simple attribute representing the employee’s date of birth.
• Gender: Specifies the gender of the employee.
• Address: Stores the address of the employee.
• Position: The job position or title held by the employee.
• Contact_Number: Attributes that allow an employee to have multiple contact numbers.
• Age: A derived attribute, calculated based on the Birthdate.

Payroll Entity

The Payroll entity includes the following attributes:

• Payroll_ID: A unique identifier for each Payroll table. This acts as a key attribute.
• Monthly_Salary: A attribute that indicates the employee's monthly salary.
• Cash_Advance: Represents any cash advances taken by the employee.
• Net_Pay: A derived attribute that indicates the net pay after deductions.
• Tax: Attributes representing total taxe that apply to the payroll.

Step 3: Establish Relationships Between Entities

Next, we identify relationships between the entities and define the type of relationship. In the ER diagram, relationships are represented by diamonds connecting the related entities.

• Receives: The Employee entity has a one-to-one relationship with the Payroll entity, represented by the receives relationship. This indicates that each employee has a unique payroll record, and each payroll entry is associated with only one employee.

Step 4: Define the Degree of Relationship

The degree of relationship between Employee and Payroll is one-to-one (1:1), as each employee is linked to a single payroll record, and each payroll record corresponds to only one employee. This constraint is indicated by "1" on both ends of the receives relationship.

Step 5: Represent Derived and Multivalued Attributes

In ER diagrams, special types of attributes are indicated by distinct symbols:

• Derived Attributes: Attributes like Age, Monthly_Salary, and Net_Pay are derived attributes. They are shown with dashed ellipses, indicating that they are calculated from other attributes rather than stored directly in the database.
• Multivalued Attributes: Attributes like Contact_Number for Employee is multivalued, meaning it can hold multiple values. It is shown as double ellipses in the ER diagram.

.

In the Practical Projects lesson, you will learn to create complex ER diagrams.