ER Model: Entities, Relationships, Cardinalities & Specialization

TAKEAWAYS:

• The ER model visually represents real-world data as entities, attributes, and relationships using diagrams.
• Cardinalities (1:1, 1:N, M:N) define how many instances of one entity relate to another, critical for database design.
• Weak entities depend on strong entities (e.g., Order_Line depends on Order) and require partial keys.
• Generalization/specialization organizes hierarchical data (e.g., Vehicle → Car, Bike) to reduce redundancy.
• ER diagrams map directly to relational schemas via mapping rules (e.g., M:N → junction table).
• Constraints (e.g., min/max cardinality, overlapping/covering) enforce data integrity in designs.

Core Concepts of the ER Model

The Entity-Relationship (ER) model is a high-level conceptual tool for designing databases. It focuses on:

1. Entities: Real-world objects (e.g., Customer, Product).
2. Attributes: Properties of entities (e.g., Customer.name, Product.price).
3. Relationships: Associations between entities (e.g., places_order).

1. Entities and Attributes

Entities are nouns representing objects, events, or concepts. Attributes describe their properties.

classDiagram
    class Entity {
        +name: String
        +attributes: List~Attribute~
    }
    class Attribute {
        <<enumeration>>
        SIMPLE
        COMPOSITE
        DERIVED
        MULTIVALUED
    }
    Entity "1" --> "*" Attribute : contains

Types of Attributes:

Worked Example: For a hospital management system, identify entities and attributes:

• Entity: Patient Attributes:
  • patient_id (simple, primary key)
  • name (simple)
  • diagnoses (multivalued: {"Fever", "Hypertension"})
  • age (derived from dob).

2. Relationships and Cardinalities

Relationships connect entities. Cardinality defines how many instances of one entity relate to another.

Types of Relationships

Mapping Cardinalities to Relational Tables

Worked Example: Daraz Order System

• Entities: Customer, Order, Product
• Relationships:
  • Customer places Order (1:N: one customer can place many orders).
  • Order contains Product (M:N: one order can have multiple products, one product can be in multiple orders).
• ER Diagram:

  erDiagram
    Customer ||--o{ Order : places
    Order ||--o{ Product : contains

• Relational Tables:

  -- Junction table for M:N
  CREATE TABLE Order_Items (
      order_id INT PRIMARY KEY,
      product_id INT PRIMARY KEY,
      quantity INT
  );
  

3. Weak Entities and Identifying Relationships

Weak entities cannot exist without a strong entity (e.g., Order_Line depends on Order).

• Require a partial key (e.g., line_number in Order_Line).
• Represented with a double rectangle in ER diagrams.

Example: Ncell Bill Payment

• Strong Entity: Customer
• Weak Entity: Bill_Payment (depends on Customer and has a partial key payment_id).
• Relationship: Customer has Bill_Payment (1:N).

erDiagram
    Customer ||--o{ Bill_Payment : has
    Bill_Payment {
        payment_id PK
        amount
        date
    }

4. Generalization and Specialization

Used to model hierarchical relationships (e.g., Vehicle → Car, Bike).

• Generalization: Bottom-up (specific → general).
• Specialization: Top-down (general → specific).

Constraints:

Example: NEPSE Stock Market

• Superclass: Trader
• Subclasses:
  • Retail_Trader (disjoint, total)
  • Institutional_Trader (disjoint, total)
• ER Diagram:

  erDiagram
    Trader ||--|{ Retail_Trader : IS_A
    Trader ||--|{ Institutional_Trader : IS_A

5. Extended ER Features

• Ternary Relationships: Involve 3+ entities (e.g., Student, Course, Professor in a Teaches relationship).
• N-ary Relationships: Generalized for N entities.
• Composite Attributes: Grouped attributes (e.g., Address → street, city).

Example: Pathao Ride System

• Ternary Relationship: Driver, Passenger, Ride (all three participate in Ride).

erDiagram
    Driver ||--o{ Ride : provides
    Passenger ||--o{ Ride : takes
    Ride {
        ride_id PK
        distance
        fare
    }

In the Real World

1. eSewa (Nepal)

   • ER Concept: Weak Entities and 1:N Relationships.
   • How: User (strong entity) has many Transactions (weak entity, depends on User via user_id). The Transaction table includes a partial key (transaction_id) and foreign keys (user_id, service_id).

2. Khalti (Digital Payments)

   • ER Concept: Generalization/Specialization and M:N Relationships.
   • How:
     • Superclass: User (general).
     • Subclasses: Merchant, Customer (specialized).
     • M:N: User ↔ Transaction (one user can have many transactions, one transaction involves multiple users like payer/payee).

3. NTC (Telecom Billing)

   • ER Concept: Ternary Relationships and Derived Attributes.
   • How:
     • Entities: Customer, Plan, Usage_Record.
     • Ternary Relationship: Customer subscribes to Plan and generates Usage_Record.
     • Derived Attribute: Remaining_Data (calculated as Plan.data_limit - Usage_Record.data_used).

Exam Tip

1. Diagrams Are Mandatory:

   • Always draw an ER diagram for design questions. Label:
     • Entities (rectangles).
     • Attributes (ovals).
     • Relationships (diamonds) with cardinalities (e.g., 1:N).
   • Use crow’s foot notation for clarity.

2. Mapping Rules:

   • For M:N, create a junction table with composite primary key.
   • For 1:1, decide whether to merge tables or use a foreign key.

3. Constraints Matter:

   • Questions often ask about disjoint/overlapping or total/partial constraints. Use real-world analogies (e.g., "A person can be only a student or an employee" = disjoint, total).

4. Weak Entities:

   • Identify them by asking: "Can this entity exist without another?" If no, it’s weak. Include a partial key in your answer.

5. SQL Conversion:

   • Practice converting ER diagrams to SQL. Example:

     -- From ER: Patient (1:N) Prescription
     CREATE TABLE Patient (
         patient_id INT PRIMARY KEY,
         name VARCHAR(100)
     );
     CREATE TABLE Prescription (
         prescription_id INT PRIMARY KEY,
         patient_id INT REFERENCES Patient(patient_id),
         medicine VARCHAR(100),
         dosage VARCHAR(50)
     );
     

6. Common Pitfalls:

   • Multivalued attributes: Never store as separate rows; use arrays or separate tables.
   • Derived attributes: Document them but don’t store redundantly (e.g., age from dob).