Entity-Relationship (ER) Model: Concepts, Diagrams & Weak Entities

TAKEAWAYS:

• The ER model visually represents real-world data as entities (objects), attributes (properties), and relationships (connections) between them.
• Cardinality (1:1, 1:N, N:M) defines how entities relate, while weak entities depend on other entities for existence (e.g., an order depends on a customer).
• Symbols like rectangles (entities), diamonds (relationships), and crow’s feet (cardinality) standardize ER diagrams for clarity.
• Weak entities require partial keys (e.g., an Order needs a Customer_ID + Order_ID to be unique).
• ER diagrams are mapped to relational tables by splitting N:M relationships into junction tables (e.g., Student_Course for Student–Course).
• Real-world use: eSewa’s transaction records (entities: User, Transaction; relationship: 1:N), Daraz’s order items (N:M: Order–Product–Quantity).

1. Introduction to the ER Model

The Entity-Relationship (ER) model is a high-level, conceptual data model that helps designers visualize real-world objects (entities), their properties (attributes), and interactions (relationships) before converting them into a database schema. It bridges the gap between human understanding and technical implementation.

Key Definitions

• Entity: A distinguishable object in the real world with unique attributes. Example: A Student in a university has attributes like Student_ID, Name, Department.
• Attribute: A property or characteristic of an entity. Example: For Student, Email is an attribute.
• Relationship: An association between entities. Example: A Student enrolls in a Course.

Why Use ER Models?

• Clarity: Graphical representation reduces ambiguity.
• Flexibility: Can model complex real-world scenarios (e.g., inheritance, weak entities).
• Foundation: Serves as a blueprint for relational databases.

FIGURE 1: Basic ER Model Components

erDiagram
    ENTITY "Student" ||--o{ "Course" : "enrolls_in"
    ENTITY "Student" {
        string Student_ID PK
        string Name
        string Department
    }
    ENTITY "Course" {
        string Course_ID PK
        string Title
        int Credits
    }

Caption: A simple ER diagram showing a Student enrolling in multiple Courses (1:N relationship).

2. ER Model Symbols

Standard symbols ensure consistency in ER diagrams. Below are the most common ones:

Symbol	Meaning	Example
	Entity	Student, Professor
	Attribute	Name, Email
	Relationship	enrolls_in
	1:1 Relationship	Professor teaches one Course
	1:N Relationship	Student enrolls in many Courses
	N:M Relationship	Student takes many Courses
	Weak Entity (dashed rectangle)	Order (depends on Customer)

WORKED EXAMPLE 1: ER Diagram for a School Scenario: A school has Teachers, Students, and ClassRooms. Each ClassRoom has one Teacher, and Students attend ClassRooms.

Solution:

1. Identify Entities:

   • Teacher (Teacher_ID, Name, Subject)
   • Student (Student_ID, Name, Grade)
   • ClassRoom (Class_ID, Room_Number)

2. Define Relationships:

   • Teacher teaches ClassRoom (1:1)
   • Student attends ClassRoom (N:M)

3. Draw the ER Diagram:

erDiagram
    ENTITY "Teacher" {
        string Teacher_ID PK
        string Name
        string Subject
    }
    ENTITY "ClassRoom" {
        string Class_ID PK
        string Room_Number
    }
    ENTITY "Student" {
        string Student_ID PK
        string Name
        string Grade
    }
    "Teacher" ||--|| "ClassRoom" : "teaches" { o "1", o "1" }
    "Student" }|--o{ "ClassRoom" : "attends" { o "N", "M" }

Caption: ER diagram for a school with 1:1 and N:M relationships.

3. Cardinality and Participation

Cardinality defines how many instances of one entity relate to instances of another. Participation indicates whether the relationship is mandatory or optional.

Types of Cardinality

Type	Notation	Example	Description
1:1		Professor teaches Course	One Professor teaches one Course.
1:N		Department has Students	One Department has many Students.
N:M		Student takes Courses	Many Students take many Courses.

Participation Constraints

• Total (Mandatory): All entities must participate in the relationship. Example: Every Student must attend at least one ClassRoom.
• Partial (Optional): Entities may or may not participate. Example: A Professor may not teach any Course (e.g., on leave).

WORKED EXAMPLE 2: Cardinality in eSewa Transactions Scenario: eSewa tracks Users and their Transactions.

• Each User makes many Transactions (1:N).
• Each Transaction belongs to one User (N:1).

ER Diagram:

erDiagram
    ENTITY "User" {
        string User_ID PK
        string Name
        string Email
    }
    ENTITY "Transaction" {
        string Txn_ID PK
        decimal Amount
        string Txn_Date
    }
    "User" ||--o{ "Transaction" : "makes" { o "1", "N" }
    "Transaction" ||--| "User" : "belongs_to" { o "N", "1" }

Caption: eSewa’s 1:N relationship between User and Transaction.

4. Weak Entities and Identifying Relationships

A weak entity cannot be uniquely identified by its attributes alone; it depends on another entity (called its owner entity) for existence. Weak entities require:

1. A partial key (attributes that, combined with the owner’s key, form a unique identifier).
2. An identifying relationship (a mandatory 1:N link to the owner).

Example: Order System

• Owner Entity: Customer
• Weak Entity: Order (cannot exist without a Customer).
• Partial Key: Order_ID (unique per Customer).

ER Diagram:

erDiagram
    ENTITY "Customer" {
        string Customer_ID PK
        string Name
        string Address
    }
    ENTITY "Order" {
        string Order_ID PK
        string Order_Date
    }
    "Customer" }|--o{ "Order" : "places" || "Order_ID" : "Order_ID"

Caption: Weak entity Order depends on Customer for existence.

FIGURE 2: Weak Entity vs. Strong Entity

Caption: Weak entities rely on owner entities for identity.

5. ER-to-Relational Mapping

ER diagrams are converted into tables (relations) for implementation. Key rules:

Worked Example: Mapping N:M

Scenario: Student takes Courses (N:M). Solution:

1. Create tables for Student and Course.
2. Add a junction table Enrollment with foreign keys.

Relational Schema:

CREATE TABLE Student (
    Student_ID INT PRIMARY KEY,
    Name VARCHAR(50)
);

CREATE TABLE Course (
    Course_ID INT PRIMARY KEY,
    Title VARCHAR(50)
);

CREATE TABLE Enrollment (
    Student_ID INT,
    Course_ID INT,
    PRIMARY KEY (Student_ID, Course_ID),
    FOREIGN KEY (Student_ID) REFERENCES Student(Student_ID),
    FOREIGN KEY (Course_ID) REFERENCES Course(Course_ID)
);

Caption: Junction table for N:M relationship.

FIGURE 3: ER-to-Relational Mapping

Caption: Mapping N:M ER relationship to relational tables.

6. Advantages and Limitations of ER Model

In the Real World

1. eSewa Transactions

   • Idea Used: Weak entities and 1:N relationships.
   • How: Each Transaction (weak entity) depends on a User (owner). The system tracks User–Transaction links to audit payments.

2. Daraz Order Items

   • Idea Used: N:M relationships and junction tables.
   • How: A Customer can order many Products, and each Product can be ordered by many Customers. The Order_Item table (junction) stores quantities and prices.

3. Pathao Ride Bookings

   • Idea Used: Weak entities and cardinality.
   • How: A Ride (weak entity) cannot exist without a Driver (owner). The system enforces 1:N (Driver–Ride) and N:M (Customer–Ride) relationships.

WORKED EXAMPLE 3: Daraz Order Queue (N:M) Scenario: Daraz sells Products to Customers via Orders. Each Order can contain multiple Products, and each Product can appear in multiple Orders. ER Diagram:

erDiagram
    ENTITY "Customer" {
        string Customer_ID PK
        string Name
    }
    ENTITY "Product" {
        string Product_ID PK
        string Name
        decimal Price
    }
    ENTITY "Order" {
        string Order_ID PK
        string Order_Date
    }
    "Customer" }|--o{ "Order" : "places"
    "Product" }|--o{ "Order" : "included_in"
    "Order" }|--|| "Order_Item" : "contains"
    "Order_Item" {
        string Order_ID FK
        string Product_ID FK
        int Quantity
        PRIMARY KEY (Order_ID, Product_ID)
    }

Caption: Daraz’s N:M relationship between Order and Product via Order_Item.

Exam Tip

1. Define Terms Clearly:

   • Always start with precise definitions of entity, attribute, and relationship in your answers. Use examples like Student–Course enrollments.

2. Draw ER Diagrams:

   • For design questions, sketch the diagram first (even if hand-drawn). Label entities, attributes, and relationships with cardinality. Partial keys for weak entities are often tested.

3. Map ER to Relational Tables:

   • Practice converting ER diagrams to SQL tables. Focus on:
     • Splitting N:M into junction tables.
     • Adding foreign keys for 1:N relationships.
     • Including the owner’s key for weak entities.

4. Cardinality Pitfalls:

   • Avoid mixing up 1:N and N:1. Remember: "N" is the side with multiple instances (e.g., Students enroll in many Courses, but each Course has one Professor).

5. Weak Entities:

   • Highlight the owner entity, partial key, and identifying relationship in your diagrams. Example: In a hospital system, Patient (owner) → Appointment (weak entity with Appointment_ID as partial key).

6. Real-World Tie-Ins:

   • Link abstract concepts to apps you use daily. For example:
     • WhatsApp Groups: N:M (User–Group membership).
     • Ncell Bill Payments: Weak entity Payment depends on User.