12 - Entity-Relationship (ER) Model

🎯 Learning Objectives

• Identify entities, attributes, and relationships from a problem description
• Draw ER diagrams with proper symbols (rectangles, diamonds, ovals, lines)
• Distinguish between weak and strong entity sets
• Model specialization/generalization hierarchies
• Apply cardinality constraints correctly

📋 Prerequisites

• General understanding of what a database is
• 03 - Relational Model — Keys, domains, relations

📖 Core Content

12.1 Intuition: Designing Databases Visually

1. Identify what "things" (entities) exist in your system
2. Define what properties (attributes) they have
3. Specify how they relate to each other Analogy: Building a house. You wouldn't start laying bricks without an architectural plan. Similarly, don't start creating tables without an ER diagram.

Why This Matters: ER modeling is the most tested topic in DBMS exams (~16% of all questions!). Getting the design right early prevents costly schema changes later.

12.2 ER Diagram Notation

12.3 Entity Sets

• instructor — each instructor is an entity
• student — each student is an entity
• course — each course is an entity

Strong vs. Weak Entity Sets

12.4 Attributes

12.5 Relationships

Degree of a Relationship

Cardinality Constraints

Participation Constraints

Cardinality Limits

• (0,1) — at most one (partial)
• (1,1) — exactly one (total)
• (0,N) — zero or many
• (1,N) — at least one, possibly many

12.6 Specialization and Generalization

Specialization (Top-Down)

• Inheritance: Sub-entities inherit all attributes of the parent entity
• Overlapping vs. Disjoint: Can an employee be both a Secretary and Engineer? If yes → overlapping; if no → disjoint
• Total vs. Partial: Must every employee be one of these types? If yes → total; if no → partial

Generalization (Bottom-Up)

12.7 Aggregation

12.8 Worked Examples

Example 1: University ER Diagram (Partial)

Example 2: One-to-One Relationship

Example 3: Weak Entity

• Loan is strong (has loan_number as key)
• Payment is weak (has payment_number as discriminator)
• Payment's full key = payment_number + loan_number (from Loan)

12.9 Ternary and Higher-Order Relationships

sql
CREATE TABLE supplies (
    supplier_id INTEGER,
    part_id     INTEGER,
    project_id  INTEGER,
    quantity    INTEGER,
    PRIMARY KEY (supplier_id, part_id, project_id),
    FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id),
    FOREIGN KEY (part_id) REFERENCES part(part_id),
    FOREIGN KEY (project_id) REFERENCES project(project_id)
);

12.10 Complete ER Diagram Example: University Database

• instructor (ID, name, salary) — key: ID
• student (ID, name, tot_cred) — key: ID
• course (course_id, title, credits) — key: course_id
• department (dept_name, building, budget) — key: dept_name
• section (sec_id, semester, year) — weak, discriminator: (sec_id, semester, year) Relationships:
• instructor works_for department (N:1)
• student belongs_to department (N:1)
• instructor advises student (1:N)
• instructor teaches section (M:N)
• student takes section (M:N, attribute: grade)
• course has section (1:N)
• section is_offered_by course (identifying for weak entity) (Diagram) This ER diagram captures the main university domain and would translate to about 5-6 relational tables.

📐 Key Formulas / Concepts

ER Notation Quick Reference

⚠️ Common Pitfalls

Pitfall 1: Confusing Cardinality Direction

• "Each instructor advises MANY students" → N on student side
• "Each student has ONE advisor" → 1 on instructor side

Pitfall 2: Making Everything an Entity

Pitfall 3: Forgetting the Weak Entity's Full Key

📝 Practice Questions

Q1. What is the difference between a strong entity set and a weak entity set?

Answer

Q2. Explain cardinality constraints in relationships.

Answer

Cardinality constraints define how many entities of one set can relate to entities of another:

• 1:1: Each A relates to at most one B (e.g., person - passport)
• 1:N: Each A relates to many B; each B relates to at most one A (e.g., department - employee)
• M:N: Many A relate to many B (e.g., student - course)

The "1" side means total participation if mandatory (double line), partial if optional (single line).

Q3. What is the difference between specialization and generalization?

Answer

• Specialization (top-down): Start with a general entity (e.g., Employee) and divide into sub-types (Secretary, Engineer). Process of defining sub-groupings.
• Generalization (bottom-up): Start with specific entities (Car, Truck) and combine into a higher-level entity (Vehicle). Process of identifying commonalities.

Both are represented the same way in ER diagrams (is-a hierarchy).

Q4. What is a derived attribute? Give an example.

Answer

A derived attribute is computed from other stored attributes. It's not physically stored in the database.

Example: age is derived from date_of_birth. Instead of storing age (which changes every year), store date_of_birth and compute age when needed.

In ER diagrams, derived attributes are shown with dashed ovals.

Q5. What is a multi-valued attribute? How is it handled in ER diagrams?

Answer

A multi-valued attribute can have multiple values for a single entity. Example: a person can have multiple phone numbers.

In ER diagrams, they're shown as double ovals. In the relational model, they're typically handled by creating a separate table for the multi-valued attribute.

Example: person(ssn, name) + person_phone(ssn, phone_number)

Q6. What is aggregation in ER modeling?

Answer

Aggregation treats a relationship as a higher-level entity so it can participate in other relationships.

Example: An Instructor evaluates a Project. The Evaluates relationship (which involves both Instructor and Project) then participates in a Uses relationship with Software.

Aggregation is useful when a relationship itself needs to be related to another entity.

Q7. How do you determine the minimum number of tables needed for a given ER diagram?

Answer

General rules:

• Each strong entity set → 1 table
• Each weak entity set → 1 table (includes the strong entity's key)
• Many-to-many relationship → 1 separate table
• Many-to-one/One-to-many → Add FK on the "many" side; no separate table needed
• One-to-one → Merge into one table (if total participation on both sides)
• Multi-valued attributes → 1 separate table

Q8. In an ER diagram, what do double lines and double diamonds represent?

Answer

• Double line (on relationship): Total participation — every entity in the set MUST participate in the relationship
• Double diamond: Identifying relationship — connects a weak entity set to its identifying strong entity set
• Double rectangle: Weak entity set

Q9. Design an ER diagram for a library with entities: Book (ISBN, title, author), Member (member_id, name), Loan (date_borrowed, date_returned). A member can borrow many books, a book can be borrowed by many members over time.

Answer

Entities:

• Book(ISBN, title, author) — key: ISBN
• Member(member_id, name) — key: member_id

Relationship:

• borrows between Member and Book (M:N relationship with attributes date_borrowed, date_returned)

The M:N relationship requires a junction table borrows(member_id, ISBN, date_borrowed, date_returned).

Diagram

(Diagram)

Q10. Explain the difference between total and partial participation with examples.

Answer

• Total participation: Every entity in the set MUST participate. Example: Every student must have an advisor (double line from Student to advisor relationship)
• Partial participation: Some entities may NOT participate. Example: Not every instructor advises a student (single line from Instructor to advisor relationship)

In ER diagrams, total = double line, partial = single line.

Q11. What is a recursive relationship? Give an example.

Answer

A recursive relationship is when an entity set relates to itself. Example: A course can have another course as a prerequisite.

Diagram

(Diagram)

This means: a course has at most one prerequisite, but a course can be a prerequisite for many courses.

Q12. Given the following description, identify entities, attributes, relationships, and cardinalities: "A hospital has many doctors and many patients. Each doctor is assigned to a single department. Each doctor can treat many patients. A patient is treated by exactly one doctor. Each patient has a name, address, phone, and date of birth."

Answer

Entities and attributes:

• Doctor(doctor_id, name, specialization) — key: doctor_id
• Patient(patient_id, name, address, phone, DOB) — key: patient_id
• Department(dept_id, dept_name) — key: dept_id

Relationships:

• works_in: Doctor → Department (N:1) — many doctors per department
• treats: Doctor → Patient (1:N) — one doctor treats many patients

Cardinalities:

• Department (1) — works_in — (N) Doctor: Each dept has many doctors; each doctor belongs to one dept
• Doctor (1) — treats — (N) Patient: Each doctor treats many patients; each patient treated by one doctor

🔗 Cross-References

• Next Topic: 13 - ER-to-Relational Mapping
• Previous Topic: 11 - Relational Calculus
• Related: BSCS2003 (MAD 1) — Schema design for web applications
• Related: BSMS2001 (BDM) — Business data modeling
• Textbook: Silberschatz, Korth, Sudarshan — Chapter 7 (Database Design and the ER Model) Join Discord Previous11 - Relational CalculusNext13 - ER-to-Relational Mapping