Extended ER (EER) — Inheritance, Specialization, Aggregation

Extended ER — generalization, specialization, aggregation

Read: ~40 min Medium 10 practice problems Live SQL runner

1. Why Plain ER Is Not Always Enough

Plain ER is wonderful for the common case — boxes, ovals, diamonds. But the moment your real-world domain starts looking like an “is-a kind of” hierarchy, plain ER falls short. Consider a Bangladeshi university: a Person might be a Student, a Teacher, or even a Staff member. All three share name, NID, phone, but each has private extras (Student has CGPA; Teacher has designation; Staff has department of work).

Extended ER (EER) adds three new tools to the original ER vocabulary so we can model exactly this kind of hierarchy: generalization, specialization, and aggregation.

সাধারণ ER ছবি আঁকার জন্য চমৎকার, কিন্তু যেখানে “is-a kind of” সম্পর্ক আসে — যেমন Person হতে পারে Student, Teacher, বা Staff — সেখানে সাধারণ ER কম পড়ে যায়। তিনজনেরই name, NID, phone থাকে, কিন্তু প্রত্যেকের কিছু আলাদা attribute-ও থাকে। Extended ER (EER) এই hierarchy আঁকতে তিনটি নতুন টুল যোগ করে: generalization, specialization ও aggregation।
Object-oriented déjà vu If you’ve seen Java or Python classes, EER will feel familiar — it brings inheritance, abstract classes, and even something like composition into ER diagrams. The big difference: in OOP your inheritance lives only in code; in EER it must eventually become tables.

2. Specialization vs Generalization

Specialization is the top-down process: start with a general entity (Person) and split it into more specific sub-entities (Student, Teacher). Generalization is the bottom-up process: notice that two entities (Student, Teacher) share a lot of common attributes, so factor them out into a superclass (Person).

Both produce the same picture — an ISA hierarchy — but the thinking direction is opposite. ER notation: an inverted triangle labelled ISA connects the superclass to the subclasses.

Specialization মানে ওপর থেকে নিচে: সাধারণ entity (Person) কে নির্দিষ্ট sub-entity-তে (Student, Teacher) ভাঙা। Generalization মানে নিচ থেকে ওপরে: একাধিক entity-র (Student, Teacher) মধ্যে একই attribute দেখলে সেগুলো superclass (Person)-এ তুলে দেওয়া। দুটোর ফলাফল একই — একটি ISA hierarchy।
Person name, nid, phone ISA Student student_id, cgpa Teacher teacher_id, designation, salary Staff staff_id, role disjoint, partial Figure 7.1 — Person ISA Student / Teacher / Staff (specialization)

2.1 Two Constraints — Disjoint vs Overlapping, Total vs Partial

Once an ISA hierarchy is drawn, two questions decide its semantics:

  • Disjoint or overlapping? Can a single Person be both a Student and a Teacher? If no, the specialization is disjoint; if yes, overlapping.
  • Total or partial? Must every Person be one of the sub-types? If yes, total; otherwise partial.
CombinationReal-world example (BD)SQL impact
Disjoint + TotalBankAccount → Savings / Current (every account is exactly one type)Use a discriminator column with NOT NULL and CHECK constraint
Disjoint + PartialEmployee → Manager (some employees are managers)Sub-row may not exist; LEFT JOIN required
Overlapping + TotalPerson → Student / Teacher (a TA is both)Multiple sub-rows allowed for the same superclass row
Overlapping + PartialVehicle → Car / Truck / Bike at a Pathao garageMost flexible; usually table-per-subclass mapping
ISA hierarchy আঁকার পর দুটি প্রশ্ন আসে: (১) Disjoint না Overlapping? একজন Person কি একই সাথে Student এবং Teacher হতে পারে (overlapping) নাকি পারে না (disjoint)? (২) Total না Partial? প্রতিটি Person কি কোনো না কোনো sub-type হতেই হবে (total) নাকি না-ও হতে পারে (partial)? এই দুটি constraint মিলিয়ে চারটি সংমিশ্রণ পাওয়া যায়।

3. Aggregation — When a Relationship Is Itself an Entity

In plain ER, only entities can take part in relationships. But sometimes a relationship needs to be related to another entity. The standard example: a Project is worked on by an Employee (relationship: works_on); we want to record which Manager is supervising that particular works_on instance — not the project itself, not the employee, but the assignment.

Aggregation lets us draw a dotted box around the relationship and treat it as if it were a single composite entity. From outside, the box behaves like an entity and can be related to others.

Employee works_on Project supervises Manager Figure 7.2 — Aggregation: (Employee works_on Project) is itself related to a Manager via supervises.
সাধারণ ER-এ শুধু entity-রা relationship-এ অংশ নিতে পারে। কিন্তু কখনো কখনো একটি relationship-কেও অন্য entity-র সাথে সম্পর্ক আঁকা লাগে। যেমন: Employee একটি Project-এ কাজ করে (works_on); এই কাজটিকে কোন Manager supervise করছেন — সেটা শুধু Employee বা Project-এর সাথে না, বরং “Employee+Project”-এর জোড়ের সাথে যুক্ত। Aggregation মানে এই relationship-কে dotted-বাক্সে ঘিরে একটি জোড়া-entity হিসেবে দেখা।
SQL recipe Aggregation is mapped by giving the relationship its own table (a junction table with a synthetic primary key), then letting other tables reference it via that PK.

SQL-এ aggregation-কে রূপান্তর করতে relationship-এর জন্য নিজস্ব table বানাতে হয় (synthetic PK-সহ), যাতে অন্য table এর FK রাখা যায়।

4. Three Strategies to Map an EER Hierarchy to SQL

Now the practical part. SQL has no built-in inheritance. To represent Person → Student / Teacher in tables, you have three options. Each one has trade-offs and fans.

① Single-table inheritance (এক টেবিল)

  • One table for superclass + all subclasses.
  • Discriminator column tells the type.
  • Many NULLs for non-applicable columns.
  • + Fastest reads (no joins).
  • − Wastes space, weak constraints.

② Table-per-subclass (class-per-table)

  • One table per level of the hierarchy.
  • Subclass tables share PK with superclass.
  • + Cleanest, most normalised.
  • − Reads need joins.

③ Table-per-concrete-class (concrete-only)

  • Only the leaves get tables; superclass is virtual.
  • + Each leaf table is self-contained.
  • − Hard to query “all Persons”; PKs must be globally unique.

4.1 Strategy ① — Single Table

single_table.sql
-- Strategy 1: everyone in one table, type column tells what they are
SELECT person_id, name, type, cgpa, designation, salary
FROM person
ORDER BY type, name;

4.2 Strategy ② — Table per Subclass

table_per_subclass.sql
-- Strategy 2: a row in person + a row in the matching child table
SELECT p.person_id, p.name,
       CASE
         WHEN s.person_id IS NOT NULL THEN 'Student'
         WHEN t.person_id IS NOT NULL THEN 'Teacher'
         WHEN st.person_id IS NOT NULL THEN 'Staff'
       END AS kind,
       s.cgpa, t.designation, t.salary, st.role
FROM person p
LEFT JOIN student s  ON s.person_id  = p.person_id
LEFT JOIN teacher t  ON t.person_id  = p.person_id
LEFT JOIN staff   st ON st.person_id = p.person_id;

4.3 Strategy ③ — Table per Concrete Class

table_per_concrete.sql
-- Strategy 3: get all 'Persons' via UNION ALL across the leaves
SELECT name, nid, 'Student' AS kind FROM student
UNION ALL
SELECT name, nid, 'Teacher' FROM teacher
UNION ALL
SELECT name, nid, 'Staff'   FROM staff
ORDER BY kind, name;
উপরের তিনটি ম্যাপিং কৌশল মনে রাখুন: (1) Single Table — সব এক টেবিলে, type column-এ পরিচয়, পড়ায় দ্রুত কিন্তু অনেক NULL; (2) Table per Subclass — প্রতিটি subclass-এর জন্য আলাদা table, সবাই superclass-এর PK ভাগাভাগি করে — সবচেয়ে clean কিন্তু join লাগে; (3) Table per Concrete Class — শুধু leaf-এর টেবিল, “সব Person” পেতে UNION ALL লাগে।

5. Choosing the Right Strategy

Question to askSingle-tablePer-subclassPer-concrete
Subclasses have many extra columns?❌ NULL bloat✅✅
Most queries need all subtypes together?✅⚠️ joins❌ unions
Need strong NOT NULL on subtype columns?❌✅✅
Polymorphic FK (any subtype can be referenced)?✅ trivial✅ via super PK❌ painful
Total + disjoint constraint enforced?✅ via CHECK⚠️ trigger needed⚠️ trigger needed
Real exampleVehicle types in BRTC fleetUniversity Person hierarchyDaraz Product → Book / Phone / Clothing (very different schemas)
Industry wisdom Most production systems start with single-table for speed, and migrate to table-per-subclass when the column count gets out of hand. Table-per-concrete is rare and usually a smell that the subclasses are not really one family.

6. Aggregation in SQL — A Worked Example

Recall Figure 7.2 — the Employee works_on Project relationship is supervised by a Manager. We promote works_on to its own table called assignment, with a synthetic PK, and then let the supervises FK point at assignment directly.

aggregation.sql
-- Who supervises which (employee, project) pair?
SELECT e.name AS employee, p.title AS project,
       a.hours, m.name AS manager
FROM assignment a
JOIN employee   e ON e.emp_id  = a.emp_id
JOIN project    p ON p.proj_id = a.proj_id
JOIN supervises s ON s.asg_id  = a.asg_id
JOIN manager    m ON m.mgr_id  = s.mgr_id
ORDER BY employee, project;
Aggregation-এর SQL রূপান্তর: relationship-কে আলাদা table বানিয়ে নিজস্ব PK (asg_id) দিন; এরপর যেকোনো অন্য entity (এখানে Manager) সরাসরি সেই PK-এর FK রাখতে পারে। এতে একই (Employee, Project) জোড়ার সাথে Manager-এর সম্পর্ক পরিষ্কারভাবে আঁকা যায়।

7. Common Pitfalls

Pitfall 1 — Forcing every domain into inheritance. Not every shared attribute means inheritance. If Customer and Supplier both have name and phone, that does not mean they’re both subclasses of BusinessParty — unless the system actually treats them polymorphically.
Pitfall 2 — Single-table without a discriminator + CHECK. If you forget the type column or skip the CHECK, two students will inevitably end up with a salary filled in. The constraint is the only thing that keeps the data sane.
Pitfall 3 — Overlapping ISA but disjoint mapping. If a Person can be both Student and Teacher (TA case), you cannot use a single discriminator column. Use the table-per-subclass strategy — a person can have rows in both student and teacher simultaneously.

8. Practice Problems

প্রথমে নিজে চেষ্টা করুন, তারপর Show Answer-এ ক্লিক করে মিলিয়ে নিন। কয়েকটি উত্তর সরাসরি এই পেজেই চালানোর জন্য SQL সহ দেওয়া আছে।
  1. Define specialization in one sentence and give a Bangladeshi example.
    এক বাক্যে specialization-এর সংজ্ঞা দিন এবং বাংলাদেশী উদাহরণ লিখুন।
    ✨ Show Answer (উত্তর দেখুন)

    Specialization is the top-down process of splitting a more general entity into more specific subclasses based on distinguishing attributes. Example: at Pathao, the entity Vehicle can be specialized into Bike, Car, and Truck.

  2. In the bank, every account is exactly one of Savings or Current. Which two ISA constraints apply?
    ব্যাংকের প্রতিটি account ঠিক একটি Savings বা Current — কোন দুটি ISA constraint প্রযোজ্য?
    ✨ Show Answer (উত্তর দেখুন)

    Disjoint (an account is one or the other, not both) and Total (every account must be one of them).

  3. Show a single-table inheritance design for a BRTC vehicle hierarchy: Vehicle has Bus and Truck. Buses have seat_count; trucks have load_capacity_ton.
    BRTC-এর জন্য single-table strategy লিখুন।
    ✨ Show Answer (উত্তর দেখুন)
    brtc.sql
    SELECT * FROM vehicle;
  4. When would you choose table-per-concrete-class over the other two strategies?
    table-per-concrete কৌশল কখন বেছে নেবেন?
    ✨ Show Answer (উত্তর দেখুন)

    When the subclasses are very different (few or no shared attributes), and you almost never query them together. Example: Daraz Product with subtypes Book, Phone, Clothing — each one has wildly different attributes (ISBN vs IMEI vs size).

  5. Map this overlapping ISA to SQL: a Person can be both a Student and a Teacher at once (the famous TA case).
    overlapping ISA কে SQL-এ ম্যাপ করুন: একই Person একই সাথে Student এবং Teacher হতে পারে।
    ✨ Show Answer (উত্তর দেখুন)
    overlapping.sql
    SELECT p.name,
       CASE WHEN s.person_id IS NOT NULL THEN 'yes' ELSE 'no' END AS is_student,
       CASE WHEN t.person_id IS NOT NULL THEN 'yes' ELSE 'no' END AS is_teacher
FROM person p
LEFT JOIN student s ON s.person_id = p.person_id
LEFT JOIN teacher t ON t.person_id = p.person_id;
  6. Explain when aggregation should be preferred over modelling the relationship as a plain entity.
    কখন aggregation ব্যবহার করা ভালো, আর কখন একটি plain entity দিয়েই কাজ চলে?
    ✨ Show Answer (উত্তর দেখুন)

    Use aggregation when the original relationship is genuinely an association between entities (e.g. works_on between Employee and Project) and you also need to relate that association to a third entity. If you simply elevate the relationship to an entity, you lose the semantic that it’s a pairing; aggregation preserves that meaning while still letting you connect a third party.

  7. Add a CHECK constraint to the single-table person from §4.1 that ensures only TEACHER rows have a non-null salary.
    single-table person-এ এমন CHECK constraint যোগ করুন যাতে শুধু TEACHER row-এ salary থাকে।
    ✨ Show Answer (উত্তর দেখুন) 
    CHECK (
  (type = 'TEACHER' AND salary IS NOT NULL)
  OR (type IN ('STUDENT','STAFF') AND salary IS NULL)
)
  8. For a Daraz product catalogue with very different subtypes (Book, Phone, Clothing), which mapping strategy makes the most sense and why?
    Daraz-এর Book/Phone/Clothing-এর জন্য কোন strategy বেছে নেবেন?
    ✨ Show Answer (উত্তর দেখুন)

    Either table-per-subclass (with a shared product superclass for SKU, name, price, seller_id) or table-per-concrete-class. Single-table is bad here because each subtype has many specialised fields (ISBN, IMEI, size) that would all be NULL most of the time.

  9. In ER diagrams, which symbol represents an ISA relationship?
    ER ডায়াগ্রামে ISA-কে কোন চিহ্ন দিয়ে আঁকা হয়?
    ✨ Show Answer (উত্তর দেখুন)

    An inverted triangle (▽) labelled ISA, with the superclass connected to the top of the triangle and subclasses connected to the bottom.

  10. Implement aggregation for a hospital scenario: a Doctor treats a Patient (relationship); the treatment is paid for by an Insurer.
    হাসপাতালের জন্য aggregation: Doctor → Patient সম্পর্কের জন্য Insurer যুক্ত করুন।
    ✨ Show Answer (উত্তর দেখুন)
    hospital_agg.sql
    SELECT d.name AS doctor, p.name AS patient,
       i.name AS insurer, pf.amount
FROM treatment t
JOIN doctor   d  ON d.doctor_id = t.doctor_id
JOIN patient  p  ON p.patient_id = t.patient_id
JOIN pays_for pf ON pf.treatment_id = t.treatment_id
JOIN insurer  i  ON i.insurer_id = pf.insurer_id;

Summary — Module 07

EER extends plain ER with three power tools: generalization/specialization (ISA hierarchies), two ISA constraints (disjoint vs overlapping, total vs partial), and aggregation for relationships that need their own associations. Mapping the hierarchy to SQL costs you one trade-off — single-table is fastest but NULL-heavy; table-per-subclass is cleanest but joins more; table-per-concrete is best when subclasses share little. Pick deliberately, then defend your choice with the data.

EER সাধারণ ER-কে তিনটি শক্তিশালী টুল দেয়: generalization/specialization (ISA hierarchy), দুটি ISA constraint (disjoint/overlapping, total/partial), এবং aggregation। SQL-এ ম্যাপ করার তিনটি কৌশল আছে — single-table (দ্রুত, NULL বেশি), table-per-subclass (সবচেয়ে clean, join লাগে), table-per-concrete (subclass আলাদা হলে ভালো)। ব্যবহারের আগে ভালো করে ভাবুন।

Next Module → Relational Algebra I — Set & Unary Operators.

Previous Next: Relational Algebra I