CS2855-ER

ER Modelling Revision Notes (CS2855)

Core Concepts from 2.ER_modelling-basic.pdf & 2.ER_modelling-full.pdf

1. Entity-Relationship (ER) Model Overview

• Purpose: High-level conceptual model for database design (preliminary step)
• Components:
  • Entities: Objects (e.g., Customer, Loan). Represented as rectangles
  • Relationships: Associations between entities (e.g., Borrower links Customer and Loan). Represented as diamonds
  • Attributes: Properties (e.g., customer_id, amount). Represented as ellipses
  • Constraints: Rules to enforce data integrity (e.g., cardinality, keys)

2. Entity Sets & Attributes

• Entities:
  • Strong Entity: Independently identifiable (e.g., Customer with primary key customer_id)
  • Weak Entity: Depends on a parent entity (e.g., Payment depends on Loan). Use double rectangles and partial keys
• Attributes:
  • Composite: Subdivided (e.g., address → street, city)
  • Multivalued: Multiple values (e.g., phone_number). Double ellipses
  • Derived: Computed from others (e.g., age from date_of_birth). Dashed ellipses
  • Key Attribute: Uniquely identifies entities (e.g., loan_number). Underlined

3. Relationships

• Relationship Sets: Binary (2 entity sets), ternary (3+), recursive (self-referencing)
• Cardinality Constraints (Symbols: → for “one”, — for “many”):
  • One-to-One: Customer → Account
  • One-to-Many: Loan → Customer
  • Many-to-Many: Customer — Borrower — Loan
• Participation Constraints:
  • Total: Every entity participates (e.g., all Loan entities linked to Borrower)
  • Partial: Some entities may not participate

4. Constraints & Design Considerations

• Key Constraints: Ensure uniqueness (primary keys)
• ISA Hierarchies: Specialization/Generalization (e.g., Employee ISA Person). Use triangles
• Aggregation: Treat relationships as entities (e.g., Works_On as an entity for Manages)

---

Common Exam Questions (Based on Past Papers: CS2855-22/23/24)

Question Type 1: Design an ER Diagram

Example (2022 Q1):

• A software company database requires tracking projects, employees, and dependencies between projects.

Solution Steps:

1. Identify Entities:
   • Project (attributes: project_id, name)
   • Employee (attributes: emp_id, role)
2. Identify Relationships:
   • Works_On (links Employee and Project)
   • Depends_On (recursive relationship on Project)
3. Define Constraints:
   • Cardinality: An employee works on many projects (N:M)
   • Participation: A project must have at least one employee
   • Weak Entities: None in this case

Sample ER Diagram:

[Project] ---- <Depends_On> --- [Project]
|
<Works_On>
|
[Employee]

Question Type 2: Convert ER Diagram to Relational Schema

Example (2023 Q2):

• Convert an ER model with Customer, Account, and Depositor into tables.

Solution Steps:

1. Strong Entities → Tables:
   • Customer(customer_id, name, street, city)
   • Account(account_number, balance)
2. Weak Entities:
   • If Payment depends on Loan, include loan_number as foreign key.
3. Relationships:
   • For N:M Depositor, create a new table: Depositor(customer_id, account_number, access_date)

Question Type 3: Cardinality Constraints & Participation

Example (2024 Q1):

• Given an ER diagram, determine whether the participation of Employee in Manages is total or partial.

Solution Steps:

1. Check if every Employee must be a manager.
2. If yes → total participation (double line between Employee and Manages).
3. If no → partial participation.

---

Key Takeaways for Exams

1. Weak Entities: Always depend on a strong entity (include foreign key in table).
2. N-ary Relationships: Convert to a separate table (e.g., ternary Works_On → table Works_On(emp_id, project_id, role)).
3. Composite Keys: Use when no single attribute is unique (e.g., course_id, student_id together).
4. Avoid Redundancy: Normalize tables to BCNF/3NF during conversion.