ENTITY–RELATIONSHIP MODELLING (FULL PG-LEVEL
VERSION)
1. WHAT IS ER MODELLING? (PG LEVEL)
ER (Entity–Relationship) Modelling is the conceptual design phase of a database.
Its purpose is to:
• Capture real-world data requirements
• Model entities, attributes, and relationships
• Avoid redundancy before building relational schema
• Provide a blueprint for the database
Created by Peter Chen (1976).
ER models are high-level, semantic, and DBMS-independent.
2. ENTITIES (DETAILED)
Entity: Something that exists and is distinguishable.
Examples:
• Student
• Course
• Hotel
• Transaction
Types of Entities:
• Strong Entity: Has its own primary key. Exists independently.
Example: Student(StudentID)
• Weak Entity: Cannot exist without a strong entity. Identified using a partial
key + strong entity’s key. Uses identifying relationship (double diamond).
Example: Order → OrderItems, Apartment → TenantContracts
When exam asks “identify weak entity”:
👉 Must mention partial key and identifying relationship.
3. ATTRIBUTES (FULL LIST)
Attributes describe properties of an entity.
Types:
• Simple: atomic value
Example: Name, Age
• Composite: can be divided
Example: Address = Street + City + Country
• Multivalued: multiple values
Example: Phone Numbers {+971…, +44…}
• Derived: computed
Example: Age from DOB
• Key Attributes: uniquely identify entity
Underline these in ER diagrams.
4. RELATIONSHIPS (THE HEART OF ER MODELLING)
Relationships show how entities relate.
Types:
• Unary: entity related to itself
Example: Employee supervises Employee
• Binary (most common): Student — Enrolls — Course
• Ternary: Supplier — Supplies — Product — Warehouse
(Important in advanced modelling)
5. CARDINALITY (VERY IMPORTANT IN EXAMS)
Shows how many entity instances participate.
Types:
• One-to-One (1:1):
Example: Manager ↔ Office
→ Each manager has one office.
• One-to-Many (1:N):
Example: Customer 1:N Orders
• Many-to-Many (M:N):
Example: Students M:N Courses
→ Requires associative/junction entity when mapping to relational schema.
6. PARTICIPATION CONSTRAINTS (MANDATORY VS OPTIONAL)
Participation = whether entity must exist in a relationship.
• Total (Mandatory): represented by double line
Example: Every Order must belong to a Customer
• Partial (Optional): single line
Example: Employee may have a Manager
Exam Tip:
If asked “explain participation”, mention existence dependency.
7. SPECIALISATION / GENERALISATION (PG-LEVEL)
Generalisation: Bottom-up approach → merging similar entities
Example:
Car, Truck → Vehicle
Specialisation: Top-down → subdividing entity
Example:
Employee → Manager, Engineer
Constraints:
• Disjoint vs Overlapping:
Disjoint: instance → only ONE subtype
Overlapping: instance → can belong to MULTIPLE subtypes
• Total vs Partial:
Total: every supertype member MUST belong to subtype
Partial: optional subtype assignment
8. ER MODELLING PROBLEMS
Fan Trap:
Occurs when:
1:N → 1:N relationships cause incorrect paths
Middle entity not connected properly
Solution:
Introduce associative entity
Redraw relationships
Chasm Trap:
Occurs when:
Optional relationship breaks connectivity
Missing mandatory link
Solution:
Strengthen participation
Add required relationship
These appear frequently in exam diagrams.
9. ER → RELATIONAL MODEL MAPPING (VERY HIGH PRIORITY)
1. Strong Entity → Table
PK becomes table PK.
2. Weak Entity → Table
PK = (PartialKey + StrongEntityKey)
3. 1:1 Relationship
Option A: Put FK in either table (prefer total side)
Option B: Combine into one table if tightly coupled
4. 1:N Relationship
Place FK on N-side.
5. M:N Relationship
Create associative table with:
FK to Entity A
FK to Entity B
Composite PK (AID, BID)
6. Multivalued Attribute
Create separate table:
PK = original PK + attribute value
7. Composite Attribute:
Break into atomic components
Exam Tip:
Mention: All relations must satisfy 1NF.
10. COMMON EXAM SCENARIOS & HOW TO ANSWER THEM
• Scenario 1: Draw ER Diagram from Narrative
Include:
Entities
Relationship names
Cardinalities
Participation
Keys
Derived attributes (optional)
• Scenario 2: Identify errors in ER diagram
Check for:
Fan traps
Chasm traps
Missing keys
Incorrect cardinality
Weak entity misuse
• Scenario 3: Convert ERD to relational model
Steps:
List all entities
Convert relationships
Add PKs and FKs
Resolve multivalued attributes
Ensure 1NF
11. PG-LEVEL EXAM QUESTIONS (WITH MODEL ANSWERS)
Q1 (15 marks)
“Construct an ERD for a university system: Students, Modules, Lecturers,
Departments.”
You MUST include:
• Student (StudentID PK)
• Module (ModuleCode PK)
• Lecturer (StaffID PK)
• Department (DeptID PK)
• Student M:N Module → Enrollment
• Lecturer 1:N Module
• Department 1:N Lecturer
• Attributes: derived, composite optional
• Participation: Modules must have Lecturer (total)
Include cardinalities:
Student 1:N Enrollment N:1 Module
Q2 (12 marks)
“Explain the difference between weak and strong entities.”
Model:
• Definition
• Partial key
• Identifying relationship
• Example with notation
• Mapping to relational schema
Q3 (10 marks)
“Explain fan trap & chasm trap with diagrams.”
Include:
• Incorrect diagram
• Explanation
• Corrected diagram
Q4 (15 marks)
“Convert ERD to fully normalised relational schema.”
You MUST:
• Write each table
• Show PKs
• Mark FKs
• Indicate composite keys
• For M:N → create associative table
Q5 (12 marks)
“Discuss specialisation/generalisation and constraints.”
Answer must mention:
• Top-down vs bottom-up
• Disjoint vs overlapping
• Total vs partial
• Use real example (Employee → Manager, Engineer)