Core CS · DBMS
Every engineer is an employee, and some employees are engineers
Specialisation and generalisation are one diagram read in opposite directions. You will place five real employees into an EMPLOYEE hierarchy under two different constraint pairs, and watch the strict pair refuse two of them.
Place five employees, watch two get refused →01 The idea
Two directions, one diagram
Plain ER gives you flat entity types. EMPLOYEE has ssn, name and salary, and every employee has all three. Then the requirements arrive: secretaries have a typing speed, engineers have an engineering type, and only a manager may head a department. Keep all of that in one entity type and the diagram now claims every employee has a typing speed, which is false for most of them, and it has no way to say that heading a department is a manager thing.
The Enhanced ER model, EER, adds three constructs on top of plain ER. The first, and the one that answers the problem above, is the superclass and subclass pair, and it comes with a name for each direction you arrive from. Specialisation is top-down: EMPLOYEE exists, you notice subgroups inside it, you define SECRETARY, ENGINEER and MANAGER below it. Generalisation is bottom-up: CAR and TRUCK exist in two teams’ diagrams, you notice they share registration_no, model and price, and you factor those out into VEHICLE above them.
The finished picture is the same either way. A superclass, a circle below it, a line down to each subclass with a subset symbol on it. Nothing in the drawing records which end you started from, which is why a question asking you to compare the two resulting diagrams has no honest answer beyond that. What the two words name is the design activity, and interviewers use them to check you know how a schema actually gets built. The other two constructs, aggregation and the union type, come later in this lesson and are not about subclasses at all.
02 Worked example
One company, three subclasses, five employees
A small consultancy. EMPLOYEE holds ssn, name and salary, and all five people below have all three. The right-hand column is the extra fact the company also needs about each of them, and it is the reason this lesson exists.
| ssn | name | salary | The extra fact the company needs |
|---|---|---|---|
| 111 | Asha | 60,000 | types at 78 words per minute |
| 222 | Ravi | 75,000 | is a mechanical engineer |
| 333 | Meera | 90,000 | heads the Research department |
| 444 | Nair | 95,000 | is a civil engineer, and heads the Design department |
| 555 | Bose | 40,000 | drives the company van |
Count how far each extra fact reaches. typing_speed applies to 1 of 5, eng_type to 2 of 5, dept_managed to 2 of 5. Put all three on EMPLOYEE and the entity type asserts that every employee has a typing speed and heads a department. Five of those fifteen cells hold a value and the other ten are empty, and no constraint you can declare would explain why.
EMPLOYEE(ssn, name, salary) -- superclass · all 5 people have all 3 | ( ) -- the specialisation circle · the letter goes in here, section 03 / | \ -- each line carries a subset symbol pointing up / | \SECRETARY(typing_speed) -- 1 of 5 · AshaENGINEER(eng_type) -- 2 of 5 · Ravi, NairMANAGER(dept_managed) -- 2 of 5 · Meera, Nair -- SECRETARY does not list ssn, name or salary. It inherits all three.-- Asha in full: 111 · Asha · 60,000 · typing_speed 78 = 4 attributes,-- of which SECRETARY declares exactly one.Lines 6 to 8 are the whole construct. Each subclass box holds one attribute, and each of those attributes is one the other employees genuinely do not have. Everything else about a secretary arrives from line 1 without being written down anywhere else, which is what inheritance means and why the boxes stay this small.
Nodes 3 and 5 draw the same arrow. Node 3 got there because EMPLOYEE existed first and the requirements kept adding facts that fitted only some of its rows. Node 5 got there because three separate entity types turned out to share three attributes, which is what happens when two teams’ diagrams are merged. Neither leaves a mark on the finished picture. That is the single most asked thing about this topic, and the answer is direction of discovery, not structure.
Node 4 is the one to hold on to for the rest of the lesson. Asha has four attributes and SECRETARY declares one of them. Nair, once the diagram lets him sit in two subclasses, will have five and declare two. Bose will have three and declare none. In every case the entity is stored once and read through however many boxes it sits in.
03 Mechanics
Four combinations, and the two things they cannot say
The circle carries two independent decisions and every constraint question on this topic is one of them. Disjointness is a statement about the subclasses: may one entity sit in two of them at once? Totality is a statement about the superclass: may one entity sit in none of them? Two yes-or-no answers give four pairs. Here is what each pair does to the same five employees.
| The pair | Notation | The rule it enforces | Which of our five it refuses | A real case that needs it |
|---|---|---|---|---|
| Disjoint and total | d in the circle, double line up to the superclass |
Exactly one subclass. Not two, and not none. | Nair, who needs two · Bose, who needs none |
ACCOUNT into SAVINGS and CURRENT at a bank that offers only those two. Every account is exactly one of them, and the diagram is what lets a reader assume it. |
| Disjoint and partial | d in the circle, single line |
At most one subclass. Possibly none. | Nair, who needs two |
VEHICLE into CAR and TRUCK at a registration office that also registers trailers. Nothing is both; plenty is neither. |
| Overlapping and total | o in the circle, double line |
At least one subclass. Possibly several. | Bose, who needs none |
PART into MANUFACTURED_PART and PURCHASED_PART. Every part is one or the other, and a part made in-house that is also bought in when demand spikes is both. |
| Overlapping and partial | o in the circle, single line |
Any number of subclasses, including two, including none. | none of the five |
Our consultancy. Nair is an engineer who manages; Bose is neither. It refuses nothing, so it also promises nothing. |
Read the letter and the line separately and the four stop being a list to memorise. The letter is about how many subclasses one entity may be in. The line is about how many it must be in. Disjoint plus partial is the pair people forget exists, because “at most one, possibly none” sounds like one fact said twice until you notice that the two halves are talking about different ends of the diagram.
One note on notation, because sources genuinely differ here. This lesson uses the Chen and Elmasri convention that Indian university syllabuses and placement interviews use: a circle below the superclass, d or o inside it, a double line for total and a single line for partial. Some textbooks leave the circle empty to mean overlapping rather than writing o. UML draws a hollow triangle instead and writes the same two facts as {disjoint} and {complete} beside it. Say which convention you are drawing in and any interviewer will follow you; what is never optional is stating both halves of the pair.
Membership in a subclass has to be decided by something, and EER gives that decision three shapes.
-- attribute-defined: ONE attribute of the superclass decides the whole specialisationEMPLOYEE(ssn, name, salary, job_type) -- job_type written once, beside the circle SECRETARY job_type = 'Secretary' -- the defining predicate for this subclass ENGINEER job_type = 'Engineer' MANAGER job_type = 'Manager' -- predicate-defined: one subclass, one condition, no attribute shared with the others HOURLY_EMPLOYEE pay_type = 'hourly' -- user-defined: no condition anywhere. Whoever inserts the entity names its subclasses. Nair -> ENGINEER, MANAGER -- decided one entity at a timeLine 2 hides a constraint you did not draw. job_type holds one value per employee, so it can name one subclass per employee and no more: an attribute-defined specialisation whose defining attribute is single-valued is disjoint whether or not anybody wrote the d. Run that backwards and it tells you something more useful. An overlapping specialisation cannot be built on a single-valued defining attribute at all. It needs a multivalued one, or it has to be user-defined, which is why line 11 is the only line in that block that can put Nair in two boxes.
Two extensions of the same idea, both fair game in an interview. A subclass may itself be specialised, so ENGINEER can carry a circle of its own with SOFTWARE_ENGINEER and CIVIL_ENGINEER below it; attributes are inherited down every level, and the depth is not limited. And a subclass may have more than one superclass, in which case it is a shared subclass and inherits from all of them at once. That is multiple inheritance, and it turns the hierarchy into a lattice rather than a tree.
Now the construct that is not about subclasses at all. Every employee in the consultancy WORKS_ON projects, and each assignment is reviewed once by a manager with a review_date and a rating. Write that as a sentence and look at what is being reviewed. Not an employee, and not a project: the assignment, the fact that Ravi works on the metro project. The ER model has no way to draw a line from a relationship to another relationship, so REVIEWS has nothing to attach to.
-- what you need to say, and cannot drawEMPLOYEE ---< WORKS_ON >--- PROJECT | REVIEWS -- a line from a diamond to a diamond: not allowed | MANAGER -- aggregation: box the relationship together with the entity types it joins,-- and treat the whole box as if it were one entity type[ EMPLOYEE ---< WORKS_ON >--- PROJECT ] ---< REVIEWS >--- MANAGER | review_date, rating -- (Ravi, metro) is now a thing REVIEWS can point at.-- Meera reviews (Ravi, metro) on 12 March with rating 4.The design people reach for instead is a ternary relationship, REVIEWS(EMPLOYEE, PROJECT, MANAGER), and it is not the same diagram. A ternary relationship is a set of triples, and nothing in it requires the employee-and-project part of a triple to be an existing WORKS_ON assignment. You can record Meera reviewing Asha on the metro project when Asha has never worked on it, and you now hold the employee-to-project link in two places that are free to disagree. Aggregation makes the assignment itself the thing being reviewed, so a review of an assignment that does not exist cannot be written down.
One last construct, and it is the one aggregation gets confused with, because both are the parts of EER that are not plain specialisation. A category, also called a union type, is a subclass whose members are drawn from the union of several different superclasses. The registered OWNER of a vehicle may be a PERSON, a BANK or a COMPANY, so OWNER is a category over all three, drawn with a u for union in the circle and arcs to each superclass. The part that is tested: a category member inherits from the one superclass it actually came from and from no other, which is called selective inheritance. That is what separates it from a shared subclass, which has several superclasses and inherits from all of them at once. Aggregation turns a relationship into something an entity can relate to; a category turns several entity types into one subclass. They solve different problems, and the only thing they have in common is that neither one is a specialisation.
05 Cheat sheet
The whole of EER on one card
Notation is the Chen and Elmasri convention. The right-hand column is what the question is actually testing each time, and it is where marks are lost.
| Construct | What it asserts | Notation | The trap |
|---|---|---|---|
| Specialisation | Top-down: define subclasses of an entity type that already exists | circle below the superclass, subset symbol on each line | the subclass is a subset, not a copy and not a second entity |
| Generalisation | Bottom-up: factor the shared attributes of several entity types into a new superclass | the same finished diagram | asked as "the difference"; the answer is direction of discovery, not structure |
| Disjoint | An entity may sit in at most one subclass | d in the circle | d says at most one, never exactly one; the line says the rest |
| Overlapping | An entity may sit in several subclasses at once | o in the circle | some texts leave the circle empty for this instead of writing o |
| Total | Every superclass entity sits in at least one subclass | double line from superclass to circle | totality constrains the superclass; disjointness constrains the subclasses |
| Partial | A superclass entity may sit in no subclass at all | single line | the default in most diagrams, so an unmarked line is a claim too |
| Attribute-defined | One attribute of the superclass decides membership for the whole specialisation | the attribute name written beside the circle | a single-valued defining attribute makes it disjoint whether you drew d or not |
| Aggregation | A whole relationship, with the entity types it joins, treated as one entity type | a box drawn around the relationship and its participants | a ternary relationship is not a substitute; it cannot require the pair to exist |
| Category (union type) | A subclass whose members come from the union of several superclasses | u in the circle, arcs to each superclass | selective inheritance: a member inherits from the one superclass it came from |
06 Where & why
Where the hierarchy goes when it meets a real engine
No mainstream relational engine stores an EER hierarchy the way you drew it. What happens instead is that the hierarchy becomes a table layout and the constraint pair becomes a declared constraint, and the four engines below make that trade in four visibly different ways.
CREATE TABLE engineer (eng_type text) INHERITS (employee) is the closest any mainstream SQL engine comes to drawing your subclass directly, and a query on employee returns the child rows too. Read the caveats before using it: a UNIQUE or PRIMARY KEY declared on the parent is not enforced across the children, and a foreign key referencing the parent does not see rows that live only in a child. Most teams draw the hierarchy in the diagram and map it to ordinary tables.
CREATE TYPE engineer_t UNDER employee_t (eng_type VARCHAR2(20)) gives genuine attribute inheritance inside the engine, and substitutability means a table of the supertype can hold subtype instances. It is the one place where the EER picture survives into the database almost unchanged. Most production Oracle schemas still map to plain relational tables, because every tool downstream expects columns.
Neither engine has anything like a subclass, so the specialisation becomes one of three layouts and the constraint pair decides which is safe. Subclass tables with no superclass table cannot store an employee who is in no subclass, so that layout needs a total specialisation. One wide table with nullable subclass columns handles overlapping without complaint and needs a CHECK to stop combinations you never meant to allow.
A single table with job_type TEXT NOT NULL CHECK (job_type IN ('Secretary','Engineer','Manager')) is attribute-defined specialisation implemented literally. The column holds one value, so disjointness comes free; NOT NULL with no “none of them” value in the list is totality. What it cannot express is overlapping, because one column cannot hold two answers, which is the same limit the diagram already told you about.
07 Interview questions
What they actually ask
EER arrives right after ER in almost every DBMS interview, and it is asked with a diagram in front of you rather than as a definition. Expect to be handed a hierarchy and asked what the circle means, then what happens if you flip one half of it.
What is specialisation, and what is generalisation?
If the diagram is the same either way, why do both words exist?
What exactly does a subclass inherit from its superclass?
Is a subclass entity a separate entity from the superclass entity?
Disjoint or overlapping, total or partial. What do the four combinations mean?
Total and disjoint sound like the same constraint. Draw the distinction.
What is a defining predicate, and what makes a specialisation attribute-defined?
Is IS-A a relationship type like WORKS_ON?
What is aggregation, and when do you actually need it?
Why not just draw a ternary relationship instead of using aggregation?
How is a category, or union type, different from an ordinary subclass?
You are designing this for real. When do you leave the specialisation out?
08 Practice problems
Six to work through
For every one of these, name the constraint pair before you draw anything, and write the sentence of real-world policy you are relying on beside it. A pair with no sentence behind it is a guess that will read as a claim.