Entity-Relationship (ER) Model & Database Design

TAKEAWAYS:

• ER diagrams visually represent real-world entities, attributes, and relationships in a database schema.
• Cardinality (1:1, 1:N, M:N) defines how entities relate, while participation (mandatory/optional) clarifies constraints.
• Weak entities rely on parent entities for identification (e.g., Order → Customer).
• Database design follows steps: requirement analysis → conceptual design (ERD) → logical design → physical implementation.
• Normalization (1NF, 2NF, 3NF) eliminates redundancy after ER modeling.
• Real-world systems (eSewa, Daraz) use ER concepts to model transactions, users, and inventory.

1. Introduction to Entity-Relationship (ER) Modeling

ER modeling is the first step in database design, translating real-world data into a structured schema. It uses entities (objects), attributes (properties), and relationships (connections) to represent business logic.

Key Definitions

• Entity: A distinct object (e.g., Customer, Product, Order).
• Attribute: Property of an entity (e.g., Name, Email, OrderDate).
• Primary Key: Unique identifier for an entity (e.g., CustomerID).
• Relationship: Association between entities (e.g., Customer places Order).

ER Diagram Symbols

erDiagram
  ENTITY "Customer" ||--o{ "Order" : "places"
  ENTITY "Order" ||--|| "Product" : "contains"
  ENTITY "Customer" {
    string custID PK
    string name
  }
  ENTITY "Order" {
    string orderID PK
    date orderDate
  }
  ENTITY "Product" {
    string prodID PK
    string name
    decimal price
  }

ER diagram with attributes included for clarity (1:M and M:1 relationships)

• Rectangle: Entity (e.g., Customer).
• Oval: Attribute (e.g., Name).
• Diamond: Relationship (e.g., places).
• Crow’s Foot: Cardinality (1:N, M:N).

2. Entities and Attributes

Entity Types

• Strong Entity: Independent existence (e.g., Employee).
• Weak Entity: Depends on another entity (e.g., Order depends on Customer).

Example: HR Database

erDiagram
    ENTITY "Employee" {
        string EmpID PK
        string FirstName
        string LastName
        decimal Salary
        string DeptID FK
    }
    ENTITY "Department" {
        string DeptID PK
        string DeptName
        string LocationID FK
    }

Key Points:

• EmpID is the primary key for Employee.
• DeptID is a foreign key linking to Department.
• Weak entities (e.g., Order) are shown with a diamond and dashed line.

3. Relationships and Cardinality

Types of Relationships

Cardinality Notation

erDiagram
  ENTITY "Student" ||--o{ "Enrollment" : "takes"
  ENTITY "Course" ||--o{ "Enrollment" : "offers"
  ENTITY "Enrollment" {
    string enrollID PK
    string studentID FK
    string courseID FK
    date enrollDate
  }

• 1:N: One Student takes many Courses (via Enrollment).
• M:N: Resolved via a junction entity (Enrollment).

Participation Constraints

• Mandatory (Total): Every entity must participate (e.g., Employee must have a DeptID).
• Optional (Partial): Participation is optional (e.g., Order may not have a Discount).

4. Weak Entities and Identifying Relationships

Weak entities cannot exist without a parent entity and require a partial key (e.g., Order needs OrderID + CustomerID).

Example: Daraz Order System

erDiagram
  ENTITY "Customer" {
    string CustID PK
    string Name
    string Contact
  }
  ENTITY "Order" {
    string OrderID PK
    string CustID FK
    date OrderDate
    decimal TotalAmount
  }

• Order is weak; it cannot exist without Customer.
• Identifying relationship: Customer → Order (dashed line).

5. Database Design Process

1. Requirement Analysis: Gather business rules (e.g., "A Customer can place multiple Orders").
2. Conceptual Design: Draw ERD (e.g., Customer → Order).
3. Logical Design: Convert ERD to relations (tables).
4. Physical Design: Choose DBMS (e.g., MySQL, PostgreSQL).

Worked Example: Bank Branch Database

Requirements:

• Each Bank has multiple Branches.
• Each Branch has multiple Accounts and Loans.

erDiagram
  ENTITY "Bank" {
    string BankID PK
    string Name
    string Headquarters
  }
  ENTITY "Branch" {
    string BranchID PK
    string BankID FK
    string Location
    string BranchManager
  }
  ENTITY "Account" {
    string AccountID PK
    string BranchID FK
    decimal Balance
    string AccountType
  }
  ENTITY "Loan" {
    string LoanID PK
    string BranchID FK
    decimal Amount
    date StartDate
  }

Added realistic attributes (Headquarters, BranchManager, AccountType) and dates Key Observations:

• Branch is a weak entity (depends on Bank).
• Account and Loan are strong entities linked via BranchID.

6. Normalization (Post-ER Design)

After ER modeling, normalize tables to eliminate redundancy:

• 1NF: No repeating groups (e.g., split Order into Order and OrderItem).
• 2NF: Remove partial dependencies (e.g., Salary depends on EmpID, not DeptID).
• 3NF: Remove transitive dependencies (e.g., Location should not depend on DeptID).

Example: Unnormalized vs. Normalized

Unnormalized:

erDiagram
  ENTITY "Order" {
    string OrderID PK
    string CustName
    string ProductName
    int Quantity
    decimal Price
    string Address
  }

Added Address attribute to show unnormalized data's real-world limitations Problem: Repeating ProductName for multiple items.

Normalized (3NF):

erDiagram
    ENTITY "Order" {
        string OrderID PK
        string CustID FK
    }
    ENTITY "OrderItem" {
        string OrderID FK
        string ProductID FK
        int Quantity
        decimal Price
    }
    ENTITY "Product" {
        string ProductID PK
        string Name
    }

In the Real World

1. eSewa Transaction System
   • Idea: Uses M:N relationships between User and Transaction.
   • How: Each User can have many Transactions, and each Transaction links to a Service (e.g., bill payment).
   • ER Snippet:

erDiagram
  ENTITY "User" {
    string UserID PK
    string Name
    string Email
  }
  ENTITY "Transaction" {
    string TransID PK
    string UserID FK
    date TransDate
    decimal Amount
  }
  ENTITY "Service" {
    string ServiceID PK
    string Name
    decimal Cost
  }

2. Daraz Inventory Management

   • Idea: Weak entities for OrderItem (depends on Order and Product).
   • How: OrderItem cannot exist without OrderID and ProductID.
   • Worked Example:
     • A Customer places an Order for 2 Laptops.
     • The system creates two OrderItem records (each with OrderID, ProductID, Quantity).

3. Pathao Driver Assignment

   • Idea: One-to-Many relationship between Driver and Ride.
   • How: One Driver can handle multiple Rides, but each Ride is assigned to exactly one Driver.
   • ER Snippet:

erDiagram
  ENTITY "Driver" {
    string DriverID PK
    string Name
    string Vehicle
  }
  ENTITY "Ride" {
    string RideID PK
    string DriverID FK
    date RideDate
    decimal Fare
    string StartLocation
    string EndLocation
  }

7. Database Architecture (Centralized vs. Distributed)

Exam Tip: For a multinational company, choose distributed for:

• Redundancy (backup servers).
• Performance (local data access).
• Security (data isolation by region).

8. Advanced ER Concepts

Binary vs. Ternary Relationships

• Binary: Two entities (e.g., Student → Course).
• Ternary: Three entities (e.g., Student, Course, Professor in a Teaches relationship).

erDiagram
  ENTITY "Student" {
    string StudentID PK
    string Name
  }
  ENTITY "Course" {
    string CourseID PK
    string Title
  }
  ENTITY "Professor" {
    string ProfID PK
    string Name
  }
  ENTITY "Enrollment" {
    string EnrollID PK
    string StudentID FK
    string CourseID FK
    string ProfID FK
    date Semester
  }

Added Enrollment entity with semester to properly show ternary relationship Why Ternary?

• Avoids M:N complexity (e.g., Student → Professor → Course).

Exam Tip

1. ER Diagram Questions:

   • Always label primary keys (PK), foreign keys (FK), and weak entities (dashed line).
   • Show cardinality (1:N, M:N) with crow’s foot.
   • Example: For a bank, draw Bank → Branch → Account.

2. Database Design:

   • Explain steps: requirements → ERD → normalization → implementation.
   • Compare centralized vs. distributed with real examples (e.g., NEPSE vs. global bank).

3. Normalization:

   • Identify repeating groups (1NF violation) and partial dependencies (2NF).
   • Example: Split Order into Order and OrderItem for 1NF.

4. Real-World Mapping:

   • Link ER concepts to apps (e.g., eSewa transactions = User → Transaction).
   • Use weak entities for scenarios like Order → Customer.

5. Common Pitfalls:

   • Forgetting mandatory vs. optional participation.
   • Misrepresenting M:N as two 1:N relationships (always use a junction entity).