Core CS · DBMS

The entity that cannot name itself

Some things in a schema have no identifier of their own. A dependent, an exam attempt, an instalment on a loan. This lesson shows you what they borrow, why the borrowing is drawn as a double diamond, and how to tell a genuinely weak entity from one that merely has a parent.

Insert two children called Anil, and watch one bounce →

Anil is not a key. Anil becomes a key only once you have said whose Anil you mean.

01 The idea

The entity with nothing unique about it

Every entity type you have drawn so far had a key. STUDENT had a roll number, COURSE had a course code, EMPLOYEE has an employee id. The key is what lets you point at one row and say which one you mean, and every relationship in the diagram is carried by it. Some entity types do not have one, and no amount of staring at their attributes will produce it.

A company records the dependents of its employees: the name of the child or spouse, the relationship, the date of birth. Ask what identifies a dependent and there is no answer inside DEPENDENT. Names repeat. Two employees can each have a son called Anil, born on the same day, and DEPENDENT has no attribute left to separate them. That is not a gap in the data collection. It is a property of the entity type, and it stays true however many rows you look at.

So DEPENDENT borrows. Its primary key is the primary key of EMPLOYEE with one or more of its own attributes added on the end: enough to say whose dependent, then enough to say which one of theirs. The entity type that lends the key is called the owner, the relationship along which it is lent is drawn with a double diamond, and the attribute that finishes the job is called the partial key. Three names, one idea, and the idea is that identity can come from outside.

A weak entity has no key of its own, so it takes the owner’s primary key and adds just enough of its own attributes to tell it apart from its siblings. Whose, then which one.

Weak entity typeAn entity type with no key attribute of its own. Two of its instances are allowed to agree on every attribute it has, so no subset of those attributes can ever be declared unique. DEPENDENT is the standard example.

Owner entity typeThe entity type a weak entity is identified through, also called the identifying entity type. Its primary key becomes the front half of the weak entity’s primary key, carried across by the identifying relationship.

Partial keyThe attribute, or the smallest set of them, that separates one owner’s weak entities from each other. Unique inside a single owner, and never guaranteed unique across the whole set. Silberschatz and Korth call the same thing the discriminator.

02 Worked example

Two employees, one name, no key

Two employees and three dependents. Asha has a son called Anil and a daughter called Nita. Ravi also has a son called Anil, born on the same day as Asha’s. Here is everything the two entity types know about themselves, with nothing borrowed from anywhere yet.

EMPLOYEE                          -- strong: empid identifies a row on its own  E1   Asha  E2   Ravi DEPENDENT                         -- everything it knows about itself  Dname    Relation    Birthdate  Anil     son         2015-04-02    -- Asha's son  Nita     daughter    2018-11-20    -- Asha's daughter  Anil     son         2015-04-02    -- Ravi's son -- lines 7 and 9 agree on every column. Nothing here tells them apart.

Lines 7 and 9 are the lesson in two lines. They agree on Dname, on Relation and on Birthdate, which is every column DEPENDENT owns. Now notice what would not have saved you. If Ravi’s son had been born a day later the two lines would differ, and DEPENDENT would still have no key. A key is a constraint on every row the set could ever hold, not a report on the rows it holds today. The moment two dependents are allowed to match, no declaration over DEPENDENT’s own attributes is legal, whether or not anybody has matched yet.

1 · List what it ownsDname, Relation, Birthdate. Ask of the whole set, not of today’s rows: is any combination guaranteed unique?

→

2 · Two instances matchAsha’s Anil and Ravi’s Anil agree on all three. No key exists, so DEPENDENT is weak

→

3 · Find the ownerAdd EMPLOYEE’s key. E1 and E2 now separate the two Anils, so EMPLOYEE is the owner

→

4 · Narrow to the partial keyInside one employee, does Dname separate the rows? Asha has Anil and Nita, so yes

→

5 · Assemble the keyOwner’s primary key, then the partial key: (Empid, Dname)

Read node 2 as the definition rather than as a fault to be corrected. DEPENDENT did not gain a key at node 3; it gained an owner, and the owner’s key came along with it. That borrowed column is why the identifying relationship gets a double diamond and a double line, and not because the notation enjoys decoration. A row of DEPENDENT physically contains a value it did not generate and cannot be read without.

Node 4 is the step candidates skip. The partial key is not “whatever is left over”. It is the smallest set of the entity’s own attributes that separates two things belonging to the same owner, and checking that it is smallest means removing one attribute and looking for a clash. Here Dname alone works, because Asha’s two dependents are Anil and Nita. If Asha had two sons both recorded as Anil, it would not, and the partial key would have to grow until it did.

03 Mechanics

Strong and weak, property by property

One row per property, with EMPLOYEE and DEPENDENT in the two columns. Everything below the first row is a consequence of the first row, which is why an interviewer will ask you for the definition and then check whether you can derive the rest of the column without being told.

Property	EMPLOYEE · strong	DEPENDENT · weak
Key of its own	`Empid, unique across the company`	`none: no set of Dname, Relation, Birthdate is guaranteed unique`
Primary key	`Empid`	`(Empid, Dname): the owner’s key, then the partial key`
How it is drawn	`single rectangle, solid underline on Empid`	`double rectangle, dashed underline on Dname`
The relationship joining them	`the owner side of the identifying relationship: it lends its key`	`the weak side of that same relationship, drawn as a double diamond; any other relationship DEPENDENT sits in is an ordinary single diamond`
Participation in that relationship	`partial: an employee may have no dependents at all`	`total: every dependent has exactly one employee, drawn as a double line`
Cardinality of that relationship	`the 1 side`	`the N side; 1:1 happens and then there is no partial key to find`
Can it exist on its own	`yes`	`no: half of its primary key would be missing, and no part of a primary key may be null`
When the other row is deleted	`nothing follows`	`it goes too, or the delete is refused: ON DELETE CASCADE, or the ANSI default of NO ACTION`

A word on notation, because sources genuinely disagree and an exam does not. This lesson uses the Chen convention as taught by Elmasri and Navathe, which is what Indian university papers and placement interviews ask for: a double rectangle for the weak entity type, a double diamond for the identifying relationship, a double line for total participation, and a dashed underline under the partial key. Silberschatz and Korth call the same attribute the discriminator and their later editions draw the weak entity set differently. The one mark every convention keeps is the double diamond, so if you can only remember one, remember that one and say which book you are drawing from.

Now the same two entity types as DDL. There is no keyword in any SQL dialect that says “weak”. The whole of it arrives as one composite primary key and one foreign key.

CREATE TABLE employee (  empid      TEXT PRIMARY KEY,                   -- strong: its own key  name       TEXT NOT NULL);CREATE TABLE dependent (  empid      TEXT,                                -- borrowed from the owner  dname      TEXT,                                -- the partial key  relation   TEXT,  birthdate  DATE,  PRIMARY KEY (empid, dname),                    -- owner's key + partial key  FOREIGN KEY (empid) REFERENCES employee(empid)    ON DELETE CASCADE                            -- total participation);

Line 10 is the whole idea. PRIMARY KEY (empid, dname) is the owner’s key followed by the partial key, in that order. No column was invented to make this work. The identifier was assembled out of one value borrowed through the identifying relationship and one value DEPENDENT already had, which is exactly what the ER diagram says.

Line 6 is NOT NULL and you never typed NOT NULL. Any column inside a PRIMARY KEY clause is implicitly not null in standard SQL. That single rule is total participation. You cannot store a dependent without naming an employee, because you cannot leave half a primary key blank. The double line on the diagram is not a second decision you make; it falls out of the first one.

Line 12 is the other half of the double line, and it is not free. The ANSI default for a foreign key is NO ACTION, which would refuse to delete an employee who still has dependents. That is also a correct reading of existence dependence: it says clear the children first. ON DELETE CASCADE says take them with you. Pick one deliberately. What no correct schema allows is the third outcome, a dependent row still sitting there with an empid that no longer exists. PostgreSQL, MySQL InnoDB and Oracle all honour CASCADE; SQLite parses it and ignores it unless the connection has run PRAGMA foreign_keys = ON.

The order of the two key columns is not arbitrary. PRIMARY KEY (empid, dname) and PRIMARY KEY (dname, empid) are the same constraint and forbid exactly the same rows. They are not the same index. The first is sorted on empid, so “every dependent of E1” is one range read on a structure you already declared; the second gives that query nothing. The ER model does not care about the order. The machine does, and the owner goes first.

One more thing before the console, because production schemas often disagree with the diagram and you should not be surprised by it. Many teams would write dependent_id BIGSERIAL PRIMARY KEY instead, keep empid as an ordinary NOT NULL foreign key, and put UNIQUE (empid, dname) beside it. That is a strong entity type with a surrogate key, and it is a defensible engineering choice rather than a misunderstanding of the model. Three reasons it wins: a one-column key is one column in every table that ever needs to point at a dependent; web routes and object mappers want a single opaque id such as /dependents/8412; and a natural partial key is a value a human can edit, so correcting a spelling would change a primary key, which is the one thing primary keys are supposed to never do.

If you go surrogate, the line that must not be dropped is UNIQUE (empid, dname). It is the only place left in the schema that says a dependent’s name is unique inside one employee, and without it you have a table rather than a model. Say both in an interview: the ER model calls DEPENDENT weak, the physical schema often gives it a surrogate key, and the partial key survives as a unique constraint.

05 Cheat sheet

Weak entities on one card

Every row here is something that gets asked out loud, and the right-hand column is the follow-up that catches people who learned the left two by heart.

Ask	The answer	The trap in the follow-up
What makes an entity type weak	`no subset of its own attributes is guaranteed unique`	Not “the rows happen to clash today”. A key is a rule about every row the set could ever hold.
Owner entity type	`the entity type it is identified through`	Exactly one owner per weak entity type. One owner can own several different weak entity types.
Partial key	`separates one owner’s weak entities from each other`	Never guaranteed unique across the whole set. Today’s rows may all happen to differ and it is still partial. If it is guaranteed, the entity type was strong all along.
Primary key of a weak entity	`owner’s primary key + partial key, owner first`	Two columns or more wherever there is a partial key to add, and wider if the owner’s own key is composite. Column order changes the index, not the constraint.
Identifying relationship	`double diamond, normally 1:N from owner to weak`	A weak entity may sit in other relationships too. Only the one it takes its key through is identifying.
Participation	`total on the weak side, drawn as a double line`	You do not choose it. It follows from half the primary key being borrowed and no part of a key being nullable.
What it looks like in SQL	`PRIMARY KEY (owner_key, partial_key) + FOREIGN KEY ... ON DELETE CASCADE`	There is no WEAK keyword. The ANSI default is NO ACTION, so CASCADE has to be typed.
Notation	`double rectangle, double diamond, dashed underline on the partial key`	Textbooks differ on the weak entity’s box. Every one of them keeps the double diamond.

“No key” is a rule, not an observationThe test is whether two instances are allowed to agree on everything the entity type owns, not whether any two currently do. This is the single most common way the question is failed: a candidate scans the sample rows, finds them all distinct, and declares the entity strong. Sample rows cannot prove a constraint.

Total participation comes freeHalf of a weak entity’s primary key is the owner’s key, and no part of a primary key may be null, so a weak entity without an owner cannot be stored at all. The double line is a consequence of the definition rather than a separate modelling decision, and that is the sentence to say when you are asked why it is always there.

The partial key can be wider, or missingMore than one attribute is allowed when one alone is not enough: a scheduled sitting of a course may need the day and the start time together, because neither on its own separates two sittings of the same course. And when the identifying relationship is 1:1 there are no siblings to separate, so there is no partial key at all and the weak entity’s primary key is just the owner’s. Elmasri and Navathe note that case.

06 Where & why

Where the double diamond ends up in DDL

No SQL dialect has ever had a keyword for this. A weak entity reaches a running database as a composite primary key whose leading columns belong to somebody else, plus a foreign key carrying those columns home. Each of these systems does something slightly different with that fact, and the differences are the parts an interviewer can tell you have met in person.

PostgreSQL

The composite key is the entire implementation

PRIMARY KEY (empid, dname) builds a unique btree index sorted on empid first, so “every dependent of E1” is a range read on a structure you already declared and did not have to ask for. Add REFERENCES employee(empid) ON DELETE CASCADE and the weak entity is complete: identification and existence dependence, in two lines, with no extra column anywhere.

MySQL · InnoDB

The key also decides where the rows live

InnoDB clusters every table on its primary key, so PRIMARY KEY (empid, dname) stores one employee’s dependents physically beside each other and reads them all in a single scan. Swap in a surrogate AUTO_INCREMENT id and the same rows are scattered in insert order. This is the clearest case of an ER decision turning into disk layout.

Oracle

Existence dependence, minus one clause

Oracle supports ON DELETE CASCADE and ON DELETE SET NULL but has no ON UPDATE CASCADE. The SET NULL option is unusable on a weak entity, because the borrowed column is part of the primary key and cannot be null. And with no update cascade, changing an employee id means fixing the dependents by hand, which is one more argument for owner keys that never move.

SQLite

Where the double line quietly disappears

SQLite parses REFERENCES ... ON DELETE CASCADE and then ignores it unless the connection has run PRAGMA foreign_keys = ON, which is off by default. The composite primary key still works, so identification survives and existence dependence does not. You end up with dependents whose employee was deleted years ago and no error anywhere to tell you.

The ER model hands you a shape to argue about at design time. SQL hands you a composite primary key and a foreign key to argue about at two in the morning. Learn the mapping in both directions, because an interviewer who asks you to draw a weak entity will almost always follow up by asking what it looks like in a CREATE TABLE.

07 Interview questions

What they actually ask

Weak entities are the part of the ER model that separates a candidate who memorised the shapes from one who understands where identity comes from. Almost every question below is really the same question asked from a different side, so answer each one from the definition rather than from a remembered sentence.

What is a weak entity type?

An entity type with no key attribute of its own: no subset of its attributes can be declared unique, because two of its instances are allowed to agree on all of them. It is identified instead through another entity type called the owner, and its primary key is the owner’s primary key plus a partial key of its own. DEPENDENT in an employee database is the standard example, because two employees can each have a son called Anil born on the same day.

How do you actually decide an entity type is weak? What is the test you run?

Ask one question of its attributes: could two instances anywhere in the system carry the same values in every attribute this entity type owns? If yes, it is weak. Do not ask whether it depends on a parent, because that question catches strong entity types as well. A passport belongs to exactly one citizen and cannot exist without one, and it is still strong, because the passport number is unique nationwide.

What is a partial key?

The attribute, or the smallest set of attributes, that tells apart the weak entities belonging to the same owner. Dname is unique among Asha’s dependents and is not unique in DEPENDENT as a whole, and that is precisely what “partial” means. Elmasri and Navathe call it the partial key; Silberschatz and Korth call the same thing the discriminator, and Indian question papers use both words. It is drawn with a dashed underline.

How is the primary key of a weak entity type formed?

Owner’s primary key first, then the partial key: (Empid, Dname). It is two columns or more wherever a partial key exists, and wider still if the owner’s own primary key is composite. Nothing is invented to make it work; the identifier is assembled from a value borrowed through the identifying relationship and a value the entity already had.

What is an identifying relationship?

The relationship that carries the owner’s key across to the weak entity, drawn as a double diamond. It is normally 1:N from owner to weak: one employee, many dependents, and each dependent belonging to exactly one employee. Every weak entity type has exactly one identifying relationship, however many other relationships it takes part in.

Why does a weak entity always have total participation in its identifying relationship?

Because half of its primary key is borrowed through that relationship, and no part of a primary key may be null. A dependent with no employee would have an incomplete key, which is not a row with a missing field but a row that cannot be identified at all. The double line on the diagram is not a separate design decision; it falls out of the definition, which is why you will never see a weak entity with partial participation on its own side.

A weak entity and a strong entity with a mandatory foreign key look almost the same in SQL. What is the difference?

Where the identifier comes from. A strong entity with a mandatory foreign key has a key of its own and merely requires a parent to exist; delete the parent and the row is orphaned but still perfectly identifiable. A weak entity has no key without the parent, so the parent’s key is physically part of its own. The tell in DDL is whether the foreign key column also appears inside the PRIMARY KEY clause.

Can a weak entity take part in a relationship that is not its identifying relationship?

Yes, and it usually does. A dependent might be related to an insurance plan or to a claim. Only the relationship it draws its key through is identifying, and only that one gets the double diamond; the rest are ordinary relationships with single diamonds. A weak entity type has exactly one identifying owner and any number of other connections.

Can a weak entity have no partial key at all?

Yes, when the identifying relationship is 1:1. If an employee may be issued at most one company locker, there are no siblings to separate, so there is nothing for a partial key to do and LOCKER’s primary key is just Empid. Elmasri and Navathe note that a weak entity type may have no partial key. It is rare in exam questions and worth knowing, because it is the one time a weak entity’s primary key is a single column.

In a real production schema, would you actually give DEPENDENT a composite primary key?

Hard

Company policy says every dependent ever declared must be retained for seven years, including after the employee has left and their EMPLOYEE row has been deleted. Produce a schema that satisfies both that policy and everything this lesson said about weak entities, then answer three things about it: what identifies a retained dependent row, which ER property you gave up to get there, and what you now have to enforce in application code that the database used to enforce for you.

Follow-up

The two requirements contradict each other, and not in SQL. The ER model says a dependent with no employee cannot be identified at all, so the instant you keep one you have changed what kind of entity it is. Your answer has to name the new kind.

Show the hint

You cannot borrow a key from a row that no longer exists, so settle first whether the employee row is genuinely being deleted or only being marked as departed, and work out what each of those two choices costs you.