ER Model: Concepts, Design & Relationships

TAKEAWAYS:

• The ER model visually represents data as entities, attributes, and relationships (1:1, 1:M, M:N) to design databases before coding.
• Cardinality (e.g., "one doctor to many patients") defines how entities interact, while modality (optional/mandatory) ensures data integrity.
• Weak entities depend on strong entities (e.g., a Dependent needs a Patient to exist) and require an identifying relationship.
• ER diagrams convert to relational schemas via normalization (e.g., M:N becomes a junction table).
• Real-world use: ER models power apps like eSewa (user-transaction relationships) and hospital systems (patient-doctor appointments).
• Exam focus: Design ER diagrams for case studies (e.g., banks, universities) and explain relationship types with examples.

1. Core Concepts of the ER Model

The Entity-Relationship (ER) model is a conceptual tool to design databases by breaking real-world systems into:

• Entities: Objects with meaning (e.g., Patient, Doctor).
• Attributes: Properties of entities (e.g., Patient has PID, Name, Age).
• Relationships: How entities interact (e.g., Doctor treats Patient).

Key Definitions

classDiagram
    class Entity {
        +Name: String
        +Attributes: List[Attribute]
        +Relationships: List[Relationship]
    }
    class Attribute {
        +Name: String
        +Type: DataType (e.g., INT, VARCHAR)
        +Key: Primary/Foreign/Composite
    }
    class Relationship {
        +Name: String
        +Degree: 1:1, 1:M, M:N
        +Cardinality: Min/Max participation
    }
    Entity "1" --> "0..*" Attribute : contains
    Entity "1" --> "0..*" Relationship : participates in

2. Entities and Attributes

Entities

• Strong Entity: Exists independently (e.g., Doctor).
• Weak Entity: Depends on another entity for existence (e.g., Appointment depends on Doctor and Patient).
  • Requires a partial key (e.g., AppointmentID + DoctorID).

Attributes

Worked Example: Hospital System

• Entity: Patient
  • Attributes:
    • Simple: PID (PK), Name, Age
    • Composite: Address → Street, City
    • Multi-valued: PhoneNumbers
    • Derived: InsuranceEligibility (calculated from Income and PolicyID).

3. Relationships: Types and Cardinality

Degrees of Relationships

Cardinality

Defines how many instances of one entity relate to another:

• 1:1 (One-to-One): One doctor has one office.
• 1:M (One-to-Many): One doctor treats many patients.
• M:N (Many-to-Many): Many doctors prescribe many medicines.

Visualizing Cardinality:

erDiagram
    Doctor ||--o{ Prescription : "prescribes"
    Prescription {
        int PK PrescriptionID
        string MedicineName
        int Dosage
    }
    Doctor {
        int PK DoctorID
        string Name
    }
    Patient {
        int PK PatientID
        string Name
    }
    Doctor ||--o{ Patient : "treats" "1:M"

Real-World Tie-In: eSewa

• Relationship: User makes Transaction (1:M).
  • One user (e.g., UID: 12345) can make multiple transactions (e.g., bill payments, transfers).
  • Cardinality: 1 user → M transactions.

4. Modality (Optional vs. Mandatory)

• Mandatory (Total): Every Doctor must have an Office (minimum cardinality = 1).
• Optional (Partial): A Patient may or may not have an Insurance (minimum = 0).

Example: Ncell Customer-Plan Relationship

• Mandatory: Every Customer must have one Plan (minimum = 1).
• Optional: A Plan can have zero or more Add-ons (e.g., extra data).

5. Weak Entities and Identifying Relationships

Weak Entity: Cannot exist without a strong entity.

• Example: Appointment depends on both Doctor and Patient.
  • Partial Key: AppointmentID (unique only within a Doctor-Patient pair).
  • Identifying Relationship: The link between Doctor and Patient is double-lined in ER diagrams.

erDiagram
    Doctor ||--o{ Appointment : "has"
    Patient ||--o{ Appointment : "has"
    Appointment {
        int PK AppointmentID
        date AppointmentDate
        string Status
    }
    Appointment }|--|| Doctor : "identifying"
    Appointment }|--|| Patient : "identifying"

Real-World Tie-In: Pathao Rider-Order System

• Weak Entity: OrderItem (depends on Order and Rider).
  • Partial Key: ItemID + OrderID (to distinguish between items in the same order).

6. Converting ER Diagrams to Relational Schemas

Worked Example: Hospital ER → SQL

-- Entities
CREATE TABLE Doctor (
    DoctorID INT PRIMARY KEY,
    Name VARCHAR(100),
    Specialization VARCHAR(50)
);

CREATE TABLE Patient (
    PatientID INT PRIMARY KEY,
    Name VARCHAR(100),
    Age INT
);

-- 1:M Relationship (Doctor treats Patient)
CREATE TABLE Appointment (
    AppointmentID INT PRIMARY KEY,
    DoctorID INT,
    PatientID INT,
    AppointmentDate DATE,
    FOREIGN KEY (DoctorID) REFERENCES Doctor(DoctorID),
    FOREIGN KEY (PatientID) REFERENCES Patient(PatientID)
);

-- M:N Relationship (Doctor prescribes Medicine)
CREATE TABLE Prescription (
    PrescriptionID INT PRIMARY KEY,
    DoctorID INT,
    MedicineID INT,
    Dosage INT,
    FOREIGN KEY (DoctorID) REFERENCES Doctor(DoctorID),
    FOREIGN KEY (MedicineID) REFERENCES Medicine(MedicineID)
);

7. Advantages and Limitations of ER Model

8. Practical Applications

Case Study 1: Bank Loan System

ER Model:

• Entities: Customer, Loan, Branch.
• Relationships:
  • Customer applies for Loan (1:M).
  • Loan is processed by Branch (1:M).
• Weak Entity: LoanInstallment (depends on Loan and Customer).

Real-World Use: Nabil Bank’s loan portal uses this to track customer loans and branches.

Case Study 2: University Management System

ER Model:

• Entities: Student, Course, Professor.
• Relationships:
  • Student enrolls in Course (M:N → junction table Enrollment).
  • Course is taught by Professor (1:M).

Real-World Use: TU’s internal system manages student-course enrollments via ER-to-SQL conversion.

## In the Real World

1. eSewa (Nepal)

   • ER Concept Used: 1:M Relationship (User → Transaction).
   • How: Each user (e.g., UID: ABC123) can make multiple transactions (bill payments, transfers). The system stores:
     • User(UserID, Name, Email)
     • Transaction(TransactionID, UserID, Amount, Date, Type)
     • Foreign key UserID links transactions to users.

2. Khalti (Digital Wallet)

   • ER Concept Used: M:N Relationship (User ↔ Merchant).
   • How: Users can transact with multiple merchants, and merchants accept payments from multiple users. A junction table Transaction resolves this:

     CREATE TABLE Transaction (
         TransactionID INT PRIMARY KEY,
         UserID INT,
         MerchantID INT,
         Amount DECIMAL,
         FOREIGN KEY (UserID) REFERENCES User(UserID),
         FOREIGN KEY (MerchantID) REFERENCES Merchant(MerchantID)
     );
     

3. NTC (Telecom Billing)

   • ER Concept Used: Weak Entity (Bill depends on Customer and Service).
   • How: A Bill cannot exist without a Customer and a Service (e.g., internet, landline). The partial key is BillID + CustomerID.

## Exam Tip

1. Diagram Design (30% of marks)

   • Must-haves:
     • Rectangles for entities, ovals for attributes, diamonds for relationships.
     • Label cardinality (e.g., "1:M") and modality (e.g., "0..1" for optional).
     • Use double lines for identifying relationships (weak entities).
   • Common Mistakes:
     • Forgetting to mark primary keys (underline or PK).
     • Misrepresenting M:N as 1:M (always use a junction table in SQL).

2. Relationship Types (20% of marks)

   • Memorize examples:
     • 1:1: Person has one Passport.
     • 1:M: Department has many Employees.
     • M:N: Student enrolls in Course (via Enrollment table).
   • Exam Question: "Give an example of a 1:1 relationship in a hospital system." Answer: Doctor has one Office (and vice versa).

3. ER to SQL Conversion (30% of marks)

   • Step-by-Step:
     1. Convert entities to tables (PK as primary key).
     2. For 1:M, add FK to the "many" side.
     3. For M:N, create a junction table with both FKs.
     4. For weak entities, include the strong entity’s PK + partial key.
   • Example Trace: ER: Doctor treats Patient (1:M). SQL:

     CREATE TABLE Patient (PatientID INT PRIMARY KEY, Name VARCHAR(100));
     CREATE TABLE Doctor (DoctorID INT PRIMARY KEY, Name VARCHAR(100));
     CREATE TABLE Appointment (
         AppointmentID INT PRIMARY KEY,
         DoctorID INT,
         PatientID INT,
         FOREIGN KEY (DoctorID) REFERENCES Doctor(DoctorID),
         FOREIGN KEY (PatientID) REFERENCES Patient(PatientID)
     );
     

4. Case Study Questions (20% of marks)

   • Approach:
     1. Identify entities (e.g., in a university system: Student, Course, Professor).
     2. Draw relationships (e.g., Student enrolls in Course is M:N).
     3. Note weak entities (e.g., Grade depends on Student and Course).
   • Past Exam Question: "Design an ER diagram for a Hospital Management System." Answer Structure:
     • Entities: Patient, Doctor, Department, Appointment.
     • Relationships:
       • Doctor works in Department (1:1).
       • Doctor treats Patient (1:M).
       • Appointment is a weak entity (depends on Doctor and Patient).

## Quick Revision Checklist

Before the exam, verify you can: ✅ Draw an ER diagram for any given scenario (e.g., library, bank, university). ✅ Distinguish between 1:1, 1:M, M:N relationships with examples. ✅ Convert an ER diagram to SQL tables (including junction tables). ✅ Identify weak entities and identifying relationships. ✅ Explain cardinality and modality with real-world ties (e.g., eSewa, Ncell).