ER Model & Database Design: Entities, Attributes, Relationships & Schemas

Core Concepts of the ER Model

1. Entities and Attributes

An entity is a real-world object or concept about which data is stored (e.g., Customer, Product). Each entity has attributes (properties) that describe it.

classDiagram
    class Entity {
        +name: String
        +attributes: List~Attribute~
    }
    class Attribute {
        +name: String
        +type: DataType
        +isKey: Boolean
    }
    Entity "1" --> "1..*" Attribute : contains

Example: For a Student entity, attributes might include:

• student_id (primary key)
• name (string)
• date_of_birth (date)
• contact_number (string)

Real-world tie: In eSewa, a User entity stores attributes like user_id, email, password_hash, and account_balance. The user_id acts as the primary key to uniquely identify each user.

2. Relationships Between Entities

Relationships describe how entities interact. They can be:

• One-to-One (1:1): One entity relates to exactly one other (e.g., Passport to Person).
• One-to-Many (1:N): One entity relates to many (e.g., Professor to Course).
• Many-to-Many (M:N): Many entities relate to many (e.g., Student to Course).

stateDiagram-v2
    [*] --> OneToOne: Passport --> Person
    [*] --> OneToMany: Professor --> Course
    [*] --> ManyToMany: Student --> Course

Worked Example: In Ncell’s billing system, a Customer (1) can have multiple Mobile_Plans (N), but each Mobile_Plan belongs to only one Customer. This is a 1:N relationship.

3. Constraints in ER Model

Constraints ensure data integrity:

• Primary Key (PK): Uniquely identifies an entity (e.g., student_id).
• Foreign Key (FK): Links to a primary key in another table (e.g., order_id in Order_Details references Order).
• Cardinality: Defines how many instances of one entity relate to another (e.g., 0..1 for optional, 1..* for mandatory).
• Participation: Whether an entity must participate in a relationship (e.g., a Student must enroll in at least one Course).

Step-by-Step Database Design Process

Step 1: Identify Entities

List all real-world objects. For a library system:

• Book, Member, Loan, Fine.

Step 2: Define Attributes

Assign attributes to each entity. For Book:

• book_id (PK)
• title
• author
• publication_year
• isbn

Step 3: Determine Relationships

Map how entities interact. For Member and Book:

• A Member can borrow many Books (1:N).
• A Book can be borrowed by many Members (N:1).

erDiagram
    Member ||--o{ Loan : "borrows"
    Loan ||--|| Book : "includes"
    Member {
        int member_id PK
        string name
        string contact
    }
    Book {
        int book_id PK
        string title
        string author
    }
    Loan {
        int loan_id PK
        int member_id FK
        int book_id FK
        date loan_date
    }

Real-world tie: In Daraz’s order system, an Order (1) can have many Order_Items (N), but each Order_Item belongs to exactly one Order. This is a 1:N relationship, optimized for fast order processing.

Step 4: Apply Constraints

Add constraints to ensure data validity:

• loan_date in Loan must be not null.
• book_id in Loan must reference an existing Book (FK constraint).

Step 5: Convert ER Diagram to Relational Schema

Transform entities and relationships into tables:

• Weak entities (e.g., Loan) become tables with a composite primary key (loan_id + book_id).
• M:N relationships become separate tables (e.g., Enrollment for Student and Course).

Example Schema for Library System:

CREATE TABLE Book (
    book_id INT PRIMARY KEY,
    title VARCHAR(100),
    author VARCHAR(100),
    isbn VARCHAR(20) UNIQUE
);

CREATE TABLE Member (
    member_id INT PRIMARY KEY,
    name VARCHAR(100),
    contact VARCHAR(20)
);

CREATE TABLE Loan (
    loan_id INT PRIMARY KEY,
    member_id INT REFERENCES Member(member_id),
    book_id INT REFERENCES Book(book_id),
    loan_date DATE NOT NULL,
    return_date DATE
);

Advanced Topics

1. Generalization and Specialization (Inheritance)

Used when entities share common attributes but have unique ones. Example:

• Vehicle (general) → Car, Bike (specialized).

classDiagram
    class Vehicle {
        +vehicle_id PK
        +model
        +year
    }
    class Car {
        +num_doors
    }
    class Bike {
        +has_sidecar
    }
    Vehicle <|-- Car
    Vehicle <|-- Bike

Real-world tie: In NTC’s vehicle registration system, a Vehicle entity is generalized into Car, Truck, and Motorcycle, each with unique attributes (e.g., num_wheels for Truck).

2. Ternary Relationships

Involves three entities (e.g., Student, Course, Professor). Example:

• A Student takes a Course taught by a Professor.

erDiagram
    Student ||--o{ Enrollment : "takes"
    Enrollment ||--|| Course : "enrolled_in"
    Enrollment ||--|| Professor : "taught_by"

Worked Example: In Pokhara University’s exam system, a Student enrolls in a Course with a specific Professor. This ternary relationship ensures accurate grading and attendance tracking.

3. Weak Entities

Entities that cannot exist without a strong entity (e.g., Order_Item depends on Order).

erDiagram
    Order ||--o{ Order_Item : "contains"
    Order {
        int order_id PK
        date order_date
    }
    Order_Item {
        int order_item_id PK, FK
        int order_id PK, FK
        int product_id FK
        int quantity
    }

Real-world tie: In Khalti’s transaction system, a Transaction (strong entity) has dependent Transaction_Details (weak entity), storing sub-transactions like refunds or splits.

Common Pitfalls and Best Practices

❌ Bad Designs

1. Missing Constraints: Allowing NULL in critical fields (e.g., student_id).
2. Overloading Attributes: Storing multiple values in one field (e.g., hobbies as a comma-separated string).
3. Redundant Data: Storing customer_name in both Customer and Order tables.

✅ Best Practices

1. Normalize Early: Avoid repeating data (e.g., store author_name in Author table, not in Book).
2. Use Descriptive Names: student_first_name > sfn.
3. Document Relationships: Clearly label cardinality (e.g., 1..* for mandatory one-to-many).

In the Real World

1. eSewa’s User System

   • Uses an ER model where User (1) has a 1:N relationship with Transaction (many).
   • The user_id (PK) in User is a FK in Transaction to link payments to users.
   • Why it matters: Ensures every transaction is traceable to a specific user, preventing fraud.

2. Ncell’s Billing Database

   • A Customer (1) can have multiple Mobile_Plans (N), modeled as a 1:N relationship.
   • The plan_id (FK in Customer_Plan) ensures accurate billing and plan upgrades.
   • Worked Example: If a customer upgrades from a Basic_Plan to a Premium_Plan, the system updates the FK in Customer_Plan without losing historical data.

3. Daraz’s Order Fulfillment

   • An Order (1) contains many Order_Items (N), stored in separate tables to optimize query performance.
   • Real-time use: When you place an order, Daraz’s system checks stock levels in Product and updates Order_Item instantly, reducing out-of-stock errors.

Exam Tip

What Examiners Look For

1. Accuracy in ER Diagrams:

   • Correctly label entities, attributes, and relationships (1:1, 1:N, M:N).
   • Use crow’s foot notation for clarity.
   • Example: For a University database, show Professor (1) teaches Course (N) with a 1:N arrow.

2. Conversion to Relational Schema:

   • Convert M:N relationships into a junction table (e.g., Enrollment for Student and Course).
   • Partial marks: Forgetting to include FK constraints (e.g., REFERENCES Student(student_id)).

3. Real-World Application:

   • 20% of marks often come from applying ER concepts to scenarios like:
     • A bank’s loan system (e.g., Loan (1) → Repayment (N)).
     • NEPSE’s stock trading (e.g., Trader (1) → Trade (N)).
   • Tip: Always draw the ER diagram first, then write the SQL schema.

4. Common Exam Questions:

   • "Design an ER diagram for a hospital management system."
     • Entities: Patient, Doctor, Appointment, Medicine.
     • Relationships: Doctor (1) → Appointment (N) → Patient (1).
   • "Convert this ER diagram to relational tables."
     • Focus on PK/FK, cardinality, and constraints.

5. Avoid These Mistakes:

   • Incorrect cardinality: Drawing a 1:1 instead of 1:N for Professor to Course.
   • Missing attributes: Forgetting date_of_birth in Patient entity.
   • Poor naming: Using E1, E2 instead of Employee, Department.