ER Model and ER-to-Relational Mapping

Syllabus tag: Paper-II, Section-B, Topic IV.4 — ER Model, ER-to-Relational Mapping

Definition

The Entity-Relationship (ER) Model is a conceptual design tool that represents a database as entities (real-world objects), their attributes (properties), and relationships (associations) between entities — expressed graphically as an ER diagram.

Core Constructs

• Entity: a distinguishable real-world object (e.g., Student). An entity set is a collection of similar entities.
• Weak entity: an entity that has no primary key of its own and depends on an identifying/owner entity for existence (e.g., Dependent identified only in combination with Employee); shown with a double-outlined box, linked via an identifying relationship (double diamond).
• Attribute types:
  • Simple (atomic, e.g. Age) vs Composite (divisible, e.g. Address → Street, City)
  • Single-valued (one value, e.g. DOB) vs Multi-valued (many values, e.g. PhoneNumbers) — shown as a double oval
  • Stored vs Derived (computed from others, e.g. Age from DOB) — shown as a dashed oval
  • Key attribute: uniquely identifies entity instances (underlined)
• Relationship: an association among two or more entities (e.g., "Enrolls In" between Student and Course). Degree: unary (recursive), binary, ternary.

Cardinality & Participation

• Cardinality ratio: 1:1, 1:N, M:N — how many instances of one entity associate with instances of another
• Participation constraint:
  • Total participation (double line): every entity instance MUST participate in the relationship (e.g., every Dependent must have an Employee)
  • Partial participation (single line): participation is optional

Generalization, Specialization & Aggregation

• Specialization: top-down process — dividing a higher-level entity (superclass) into lower-level subclasses with distinct attributes (e.g., Employee → Manager, Engineer)
• Generalization: bottom-up process — combining common attributes of several entities into a higher-level superclass
• Constraints on specialization: disjoint (an entity belongs to at most one subclass) vs overlapping; total (every superclass entity must belong to some subclass) vs partial
• Aggregation: treats a relationship itself as a higher-level abstraction so it can participate in further relationships (used when a relationship needs to relate to another entity)

Gerund / Associative Entity (FPSC 2021, Q6(b))

• A gerund is a relationship that is treated as an entity in its own right — typically because the relationship itself needs its own attributes (e.g., Registration holding Marks/Grade for a Student–Course enrollment) or needs to participate in further relationships.
• It is not essential to eliminate every gerund from a design: converting a relationship to a gerund is done specifically when that relationship needs its own descriptive attributes or must relate to a third entity — otherwise a plain M:N relationship (mapped to its own table) is sufficient.

ER-to-Relational Mapping Algorithm

1. Strong entity → its own table; entity's key becomes the table's primary key.
2. Weak entity → its own table with the owner entity's primary key included as a foreign key, combined with the weak entity's partial key to form the composite primary key.
3. Binary 1:1 relationship → post the primary key of one side as a foreign key into the other (prefer the side with total participation).
4. Binary 1:N relationship → post the primary key of the "1" side as a foreign key into the "N" side's table.
5. Binary M:N relationship → create a new junction/bridge table containing foreign keys to both participating entities (composite primary key = both FKs); any relationship attributes go here (this is the gerund case).
6. Multi-valued attribute → create a separate table with a foreign key back to the owning entity's primary key.
7. N-ary relationship (degree ≥ 3) → always create a separate table with foreign keys to all participating entities.

Numerical — Worked Example (Hospital ER, FPSC 2020 Q6(b) style)

Entities: Patient(PatientID, Name), Doctor(DoctorID, Name); Relationship: Examines (M:N, with attributes Date, Diagnosis).

• Mapping: Patient(PatientID, Name), Doctor(DoctorID, Name), and a bridge table Examines(PatientID FK, DoctorID FK, Date, Diagnosis) with composite primary key (PatientID, DoctorID, Date).

MCQ Trap

• A weak entity's primary key is always composite: owner's key + weak entity's own partial key.
• 1:1 and 1:N relationships do not need a separate table; only M:N and n-ary relationships do.
• Total participation is drawn with a double line, not to be confused with a double-outlined box (which marks a weak entity).

CSS Exam Angle

• FPSC frequently gives a real-world scenario ("design an ER diagram for a hospital/library/university") and expects both the diagram AND correct cardinality/participation annotations (2019, 2020).
• The "gerund" question (2021, 12 marks) shows FPSC tests when to apply a mapping rule, not just the rule itself — always justify the design choice.

Likely Questions

1. Demonstrate the use of the ER Model in database design with an example.
2. Construct an ER diagram for a given scenario (e.g., hospital with patients and doctors), showing cardinality and participation.
3. What is a weak entity? How is it mapped to a relational table?
4. Explain the concept of a gerund/associative entity with an example. Is it always necessary to eliminate it?
5. Differentiate generalization, specialization, and aggregation.
6. List and briefly justify the seven rules for mapping an ER diagram to relational tables.

---

📘 Enrichment — Full Worked Design (Library Management System)

Scenario (typical FPSC wording): A library has members and books. Each book is published by one publisher; a publisher publishes many books. A book can have several authors and an author can write several books. The library keeps several physical copies of each book, identified by a copy number within that book. A member may borrow many copies; each loan records issue date and return date. Every member has one or more phone numbers.

Step 1 — Identify the Constructs

Step 2 — ER Diagram (crow's-foot form)

erDiagram
    PUBLISHER ||--o{ BOOK : publishes
    AUTHOR }o--o{ BOOK : writes
    BOOK ||--|{ BOOK_COPY : "has (identifying)"
    MEMBER ||--o{ LOAN : makes
    BOOK_COPY ||--o{ LOAN : "is lent in"
    MEMBER ||--|{ MEMBER_PHONE : has
    PUBLISHER {
        int PubID PK
        string Name
    }
    BOOK {
        string ISBN PK
        string Title
        int PubID FK
    }
    BOOK_COPY {
        string ISBN PK
        int CopyNo PK
        string Shelf
    }
    MEMBER {
        int MemberID PK
        string Name
    }
    LOAN {
        int MemberID FK
        string ISBN FK
        int CopyNo FK
        date IssueDate
        date ReturnDate
    }

• In a Chen-notation answer sheet draw: rectangles (entities), double rectangle (BookCopy), diamonds (relationships), double diamond (HAS), ovals (attributes), double oval (Phone), underline keys, dashed underline for the partial key CopyNo, double line for total participation.

Step 3 — Map to Relations (apply the 7 rules)

• Why IssueDate is in Loan's key: the same member may borrow the same copy again later; without the date, the second loan would violate the key.
• Result: 8 tables. Check: every table is in 3NF — a nice closing line linking ER design to normalization.

Worked Example 2 — Specialization Mapping Options

Employee(EmpID, Name) specialised into Engineer(Discipline) and Manager(Budget).

Worked Example 3 — Unary (Recursive) and Ternary Relationships

• Unary: Employee SUPERVISES Employee (1:N) → add SupervisorID → Employee.EmpID inside Employee.
• Ternary: Supplier SUPPLIES Part to Project → table Supplies(SupplierID, PartID, ProjectID, Qty) — cannot be replaced by three binary tables without losing meaning (links to 5NF on the normalization page).

Notation Cheat Sheet — Chen vs Crow's Foot

---

✍️ Model Answers — Likely Questions

Q1. Demonstrate the use of the ER Model in database design.

Role: the ER model is the conceptual design step between requirements and tables — it captures the business in a diagram non-technical users can verify.

Design lifecycle: Requirements analysis → Conceptual design (ER) → Logical design (ER-to-relational mapping) → Normalization → Physical design (indexes, storage).

Demonstration: use the Library example above — identify entities, attributes, relationships, cardinality/participation → draw → map to 8 tables.

Benefits: clear communication with users, early detection of missing requirements, a systematic route to a normalized schema.

Q2. Construct an ER diagram for a hospital (patients, doctors, wards).

Assumptions (always state them): a doctor treats many patients and a patient may see many doctors (M:N, attributes Date, Diagnosis); each patient is admitted to at most one ward at a time and a ward holds many patients (1:N, partial for outpatients); a doctor belongs to one department (N:1, total).

Entities: Patient(PatientID, Name, DOB, Age-derived, Phone-multi), Doctor(DoctorID, Name, Specialization), Ward(WardNo, Type, Beds), Department(DeptID, Name).

Relations after mapping: Patient(PatientID, Name, DOB, WardNo FK) · PatientPhone(PatientID, Phone) · Doctor(DoctorID, Name, Specialization, DeptID FK) · Department(DeptID, Name) · Ward(WardNo, Type, Beds) · Treats(PatientID, DoctorID, Date, Diagnosis) with key (PatientID, DoctorID, Date).

Draw it in Chen notation with cardinalities and double lines for Doctor–Department total participation.

Q3. What is a weak entity and how is it mapped?

A weak entity has no key of its own and cannot exist without its owner (identifying) entity; it has only a partial key (discriminator). It always has total participation in the identifying relationship.

Examples: Dependent of Employee; BookCopy of Book; Installment of Loan.

Mapping: create a table containing the weak entity's attributes + the owner's PK as FK; the PK = owner's PK + partial key, typically with ON DELETE CASCADE. E.g., BookCopy(ISBN, CopyNo, Shelf), PK (ISBN, CopyNo).

Q4. Explain a gerund/associative entity. Is it always necessary to eliminate it?

A gerund (associative entity) is a relationship treated as an entity — drawn as a diamond inside a rectangle. Use one when the relationship (a) has its own attributes (Loan: IssueDate, ReturnDate; Registration: Grade), or (b) must itself participate in another relationship (e.g., a Loan generates a Fine).

Not necessary to eliminate it — at mapping time it simply becomes a junction table whose key combines the participating keys (plus a date if repeats are allowed). Converting every relationship into a gerund would only clutter the diagram, so use it when justified.

Q5. Differentiate generalization, specialization and aggregation.

• Specialization (top-down): Employee → Engineer, Manager; driven by attributes unique to subgroups.
• Generalization (bottom-up): Car and Truck → Vehicle; driven by shared attributes.
• Both create an IS-A hierarchy with inheritance; constraints: disjoint/overlapping, total/partial.
• Aggregation: treats a relationship set as an entity so it can relate further — e.g., the (Employee WORKS-ON Project) relationship is MONITORED-BY a Manager.

Conclusion: the first two model type hierarchies; aggregation models relationships about relationships.

Q6. List and justify the ER-to-relational mapping rules.

Reproduce the 7-step algorithm from the notes above, giving a one-line justification per rule, e.g.: 1:N → FK on N side because each N-side row has exactly one partner, so one column suffices without NULL-heavy duplication; M:N → separate table because neither side can hold a multi-valued FK in 1NF; multi-valued attribute → separate table to keep 1NF. Finish with the Library mapping table as the illustration.

---

⚡ 60-Second Revision

• Double rectangle = weak entity · double diamond = identifying relationship · double oval = multi-valued · dashed oval = derived · double line = total participation.
• Only M:N, n-ary relationships and multi-valued attributes get their own tables.
• Weak entity PK = owner PK + partial key.
• Gerund = relationship with attributes → becomes the junction table.

📜 Past-Paper Questions & MCQs (CSS 2016–2026)

2019 · Paper-II · Q8(a) · 8 marks

Q. Demonstrate the use of the ER model in the database design process using an example.

Ans.

• ER model describes data as entities (things), attributes (their properties) and relationships (associations), with cardinalities (1:1, 1:N, M:N). It is the conceptual design step before building tables.
• Process:
  • Step 1: Collect requirements.
  • Step 2: Identify entities, attributes and keys.
  • Step 3: Identify relationships and cardinality and participation.
  • Step 4: Draw the ER diagram.
  • Step 5: Map to relational tables (an entity becomes a table, 1:N puts a foreign key on the "many" side, M:N becomes a separate table).
  • Step 6: Normalise.
• Example (university):

erDiagram
    STUDENT ||--o{ ENROLLMENT : registers
    COURSE  ||--o{ ENROLLMENT : has
    TEACHER ||--o{ COURSE : teaches
    STUDENT {
        int RegNo PK
        string Name
    }
    COURSE {
        string Code PK
        string Title
    }
    TEACHER {
        int TeacherID PK
        string Name
    }
    ENROLLMENT {
        int RegNo FK
        string Code FK
        string Grade
    }

• Mapping: Student(RegNo, Name), Teacher(TeacherID, Name), Course(Code, Title, TeacherID FK), Enrollment(RegNo, Code, Grade).

2020 · Paper-II · Q6(b) · 7 marks

Q. Construct an E-R diagram for a hospital with a set of patients and a set of medical doctors. Associate with each patient a log of the various tests and examinations conducted.

Ans.

erDiagram
    PATIENT ||--o{ TEST_LOG : "has log of"
    DOCTOR  }o--o{ PATIENT : treats
    PATIENT {
        int PatientID PK
        string Name
        date DOB
        string Address
    }
    DOCTOR {
        int DoctorID PK
        string Name
        string Specialization
    }
    TEST_LOG {
        int PatientID FK
        int TestNo PK
        date TestDate
        string TestType
        string Result
    }

• Entities: PATIENT, DOCTOR, and TEST_LOG (a weak entity, identified by PatientID plus TestNo, so it cannot exist without a patient).
• Relationships: a doctor treats many patients and a patient may see many doctors (M:N). A patient has many log entries (1:N, total participation of the weak entity).

2021 · Paper-II · Q6(b) · 12 marks

Q. What is the concept of a gerund in a database? Give one example. Is it essential to eliminate gerunds from the database? If yes, how, and if no, why?

Ans.

• Gerund (reified relationship): a relationship that is treated as an entity so that it can have its own attributes or take part in other relationships. It is the idea of aggregation in ER modelling.
• Example: the relationship Enrolls between Student and Course carries attributes (grade, date) and is itself linked to Instructor (who graded it). We model it as the entity Enrollment(StudentID, CourseID, Grade, Date).
• Is it essential to eliminate it?
  • No, it is not essential to eliminate it. A gerund is a deliberate design choice, used when a relationship has its own attributes or must relate to another entity.
  • At the mapping stage it is converted, not removed. The relational model has only tables, so the gerund becomes an associative (junction) table whose primary key combines the foreign keys of the participating entities. Enrollment(StudentID FK, CourseID FK, Grade, Date, PRIMARY KEY (StudentID, CourseID)). Other tables then reference this table as needed.
• Benefit: the M:N relationship is correctly stored, redundancy is avoided and integrity is enforced with foreign keys.

MCQs

2024 P-II · Q11. A child entity in ER diagrams is:

• (A) The entity on the one side of a one-to-many relationship
• (B) An entity that inherits attributes and relations from another entity ✅
• (C) A row of a table
• (D) None of these
• Answer: (B).
• Why: in the extended ER model, a child (subclass) entity inherits the attributes and relationships of its parent (superclass), as in specialisation. (A) describes the "one" side (parent) of a 1:N relationship, and a row is a table concept, not an ER one.