Entity Relationship (ER) modeling

Entity-Relationship (ER) Modeling in Database Design

ER modeling is a conceptual design approach used to structure data by defining entities, attributes, and relationships. It helps in visualizing database structure before actual implementation.

---

1. Components of ER Modeling

Entities (Tables)

An entity represents a real-world object or concept that stores data. It is typically converted into a table in a relational database.
- Strong Entity: Exists independently (e.g., Employee, Department).
- Weak Entity: Depends on another entity (e.g., OrderItem needs Order).

Attributes (Columns)

Attributes store properties of an entity.
- Simple Attribute: Stores a single value (e.g., name, salary).
- Composite Attribute: Can be broken down (e.g., full_name → first_name + last_name).
- Derived Attribute: Calculated from other attributes (e.g., age derived from birth_date).
- Multivalued Attribute: Can have multiple values (e.g., phone_numbers).

Relationships (Links Between Entities)

Defines how two or more entities interact.
- One-to-One (1:1): Each entity instance relates to one other (e.g., Person ↔ Passport).
- One-to-Many (1:M): One entity instance relates to many others (e.g., Department ↔ Employees).
- Many-to-Many (M:N): Multiple instances relate to each other (e.g., Students ↔ Courses).

Primary Key (PK) & Foreign Key (FK)

• Primary Key (PK): Unique identifier for a table (e.g., employee_id).
• Foreign Key (FK): References the primary key of another table (e.g., department_id in Employee).

---

2. ER Diagram Example

📌 Entities:
- Employee (id, name, age, salary, department_id)
- Department (id, name)

📌 Relationships:
- One Department has many Employees (1:M).

📌 ER Diagram Representation:

Department (1)  ----- (M) Employee

• PK: id in both tables.
• FK: department_id in Employee referencing Department.id.

---

3. Converting ER Model to Relational Schema

1. Entities → Tables
   

   CREATE TABLE Department (
       id INT PRIMARY KEY,
       name VARCHAR(100) NOT NULL
   );
   
   CREATE TABLE Employee (
       id INT PRIMARY KEY,
       name VARCHAR(100) NOT NULL,
       age INT CHECK (age > 18),
       salary DECIMAL(10,2),
       department_id INT,
       FOREIGN KEY (department_id) REFERENCES Department(id)
   );
   

2. Many-to-Many → Join Table
   If Students enroll in Courses, we need a join table:
   

   CREATE TABLE Enrollment (
       student_id INT,
       course_id INT,
       PRIMARY KEY (student_id, course_id),
       FOREIGN KEY (student_id) REFERENCES Students(id),
       FOREIGN KEY (course_id) REFERENCES Courses(id)
   );
   

---

4. Best Practices in ER Modeling

✔ Use meaningful entity names (e.g., Employee instead of Emp).
✔ Normalize data to avoid redundancy, but denormalize if necessary for performance.
✔ Define relationships clearly using PKs and FKs.
✔ Consider indexing on FKs for performance improvement.