Strong and Weak Entity Sets

DSA, Database System & Operating System — Data Models, Normalization, and SQL, NEC licence examination syllabus (Nepal Engineering Council).

Strong and Weak Entity Sets

An entity that cannot be identified on its own — it borrows part of its identity from an owner.

🌍 Where this lives: weak entities are everywhere once you recognise them. An order line item has no meaning without its order — "line 3" identifies nothing by itself. A comment belongs to a post. A payment instalment belongs to a loan. In every case the child's identity includes the parent's, which is why deleting the parent must delete the children: an orphaned line item is not data, it is garbage. This is exactly the semantics behind Django's on_delete=CASCADE and Rails' dependent: :destroy. Search "aggregate root domain driven design" — DDD's "aggregate" is the same concept arrived at from a software-design direction.

The definition

STRONG ENTITY (regular entity) · has its OWN key attribute that uniquely identifies it · exists independently · drawn as a SINGLE rectangle Student(roll) Course(code) Department(code) WEAK ENTITY · has NO key attribute of its own · is identified only in combination with its OWNER · CANNOT exist without the owner · drawn as a DOUBLE rectangle Dependant, OrderLine, Instalment, RoomInHotel PARTIAL KEY (discriminator) · an attribute that distinguishes weak entities WITHIN one owner, but not globally · drawn with a DASHED underline Dependant.name — unique among ONE employee's dependants, but many employees may have a dependant called "Sita" IDENTIFYING RELATIONSHIP · the relationship connecting a weak entity to its owner · drawn as a DOUBLE diamond · the weak entity's participation is ALWAYS TOTAL (double line) — by definition it cannot exist unowned FULL KEY OF A WEAK ENTITY = owner's primary key + partial key Dependant: (emp_id, name) OrderLine: (order_no, line_no) Instalment: (loan_id, instalment_no) THE TEST — is this entity weak? Ask: "can I identify one instance without naming its parent?" "dependant Sita" → ambiguous. WEAK. "student roll 101" → unambiguous. STRONG. "line item 3" → ambiguous. WEAK. "invoice INV-2026-0042" → unambiguous. STRONG.

Converting a weak entity to a table

weak_entities.sql

-- ===== the strong owner =====
CREATE TABLE employee (
    emp_id INTEGER     PRIMARY KEY,
    name   VARCHAR(60) NOT NULL,
    salary NUMERIC(10,2)
);

-- ===== the weak entity =====
-- PK = owner's key + partial key
-- ON DELETE CASCADE is REQUIRED by the semantics
CREATE TABLE dependant (
    emp_id       INTEGER     NOT NULL,
    name         VARCHAR(60) NOT NULL,      -- partial key
    relationship VARCHAR(20) NOT NULL,
    dob          DATE,
    PRIMARY KEY (emp_id, name),
    FOREIGN KEY (emp_id) REFERENCES employee(emp_id)
        ON DELETE CASCADE
);

-- ===== a second, very common example =====
CREATE TABLE orders (
    order_no   INTEGER     PRIMARY KEY,
    order_date DATE        NOT NULL,
    customer   VARCHAR(60) NOT NULL
);

CREATE TABLE order_line (
    order_no   INTEGER      NOT NULL,
    line_no    SMALLINT     NOT NULL,       -- partial key
    item       VARCHAR(60)  NOT NULL,
    qty        INTEGER      NOT NULL CHECK (qty > 0),
    unit_price NUMERIC(10,2) NOT NULL CHECK (unit_price >= 0),
    PRIMARY KEY (order_no, line_no),
    FOREIGN KEY (order_no) REFERENCES orders(order_no)
        ON DELETE CASCADE
);

-- ===== data showing why the partial key is only partial =====
INSERT INTO employee VALUES
    (1, 'Ram Bahadur', 68000),
    (2, 'Sita Devi',   72000);

INSERT INTO dependant VALUES
    (1, 'Gita',  'daughter', '2015-03-12'),
    (1, 'Hari',  'son',      '2018-07-05'),
    (2, 'Gita',  'daughter', '2016-01-20');
-- 'Gita' appears TWICE. Legal, because the PK is
-- (emp_id, name): (1,'Gita') and (2,'Gita') are distinct.
-- The name ALONE cannot identify a dependant.

INSERT INTO dependant VALUES
    (1, 'Gita', 'niece', '2019-01-01');
-- ERROR: duplicate key value violates unique constraint
--        "dependant_pkey"
-- Employee 1 cannot have two dependants both called Gita.

INSERT INTO dependant VALUES (99, 'Ghost', 'son', '2020-01-01');
-- ERROR: violates foreign key constraint — no employee 99.
-- A weak entity cannot exist without its owner.

-- ===== CASCADE in action =====
DELETE FROM employee WHERE emp_id = 1;
-- Employee 1 and BOTH of their dependants are removed.
-- Without CASCADE this DELETE would fail, and without the
-- FK it would leave orphaned rows identifying nothing.

SELECT * FROM dependant;
--  emp_id | name | relationship |    dob
--  -------+------+--------------+-----------
--       2 | Gita | daughter     | 2016-01-20

The 'Gita' example is the whole concept in one table. (1, 'Gita') and (2, 'Gita') → BOTH legal (1, 'Gita') twice → REJECTED So `name` distinguishes dependants WITHIN one employee and not across employees. That is precisely the definition of a partial key, and the composite primary key is what enforces it. THE THREE CONVERSION REQUIREMENTS, all visible above: 1. PRIMARY KEY (owner_key, partial_key) — not just the partial key, and not a new surrogate id 2. FOREIGN KEY (owner_key) REFERENCES owner — with NOT NULL, because participation is total 3. ON DELETE CASCADE — because the weak entity has no independent existence Omit (3) and deleting an owner fails or orphans rows. Omit the owner's key from (1) and you have wrongly modelled the entity as strong. THE ORDER-LINE CASE shows why line_no is not globally unique: (1001, 1), (1001, 2), (1001, 3) order 1001's lines (1002, 1), (1002, 2) order 1002 starts at 1 Line numbers restart per order. A global surrogate key would work mechanically but loses the meaning that "line 2" is the second line OF THIS ORDER.

There is a real design debate here. Many teams give every table a surrogate id — including weak entities — because ORMs and REST APIs are simpler with single-column keys. That works, but it does not remove the need for a UNIQUE (order_no, line_no) constraint; without it you can insert two "line 2"s in the same order. The natural key still has to be enforced, whether or not it is the primary key.

Worked example — three levels of weakness

nested_weak.sql

-- A weak entity can itself own another weak entity, giving a
-- chain of identifying relationships. The key grows at each
-- level.

-- LEVEL 1: strong
CREATE TABLE building (
    bldg_code CHAR(4)     PRIMARY KEY,
    name      VARCHAR(60) NOT NULL
);

-- LEVEL 2: weak, owned by building
-- room 101 exists in many buildings, so room_no is partial
CREATE TABLE room (
    bldg_code CHAR(4)  NOT NULL,
    room_no   SMALLINT NOT NULL,           -- partial key
    capacity  SMALLINT NOT NULL CHECK (capacity > 0),
    PRIMARY KEY (bldg_code, room_no),
    FOREIGN KEY (bldg_code) REFERENCES building(bldg_code)
        ON DELETE CASCADE
);

-- LEVEL 3: weak, owned by room (which is itself weak)
-- The key is now THREE columns.
CREATE TABLE seat (
    bldg_code CHAR(4)  NOT NULL,
    room_no   SMALLINT NOT NULL,
    seat_no   SMALLINT NOT NULL,           -- partial key
    is_broken BOOLEAN  NOT NULL DEFAULT FALSE,
    PRIMARY KEY (bldg_code, room_no, seat_no),
    FOREIGN KEY (bldg_code, room_no)
        REFERENCES room(bldg_code, room_no)
        ON DELETE CASCADE
);

INSERT INTO building VALUES ('PULC', 'Pulchowk Campus Block A'),
                            ('CTVR', 'Thapathali Block');
INSERT INTO room VALUES ('PULC', 101, 60), ('PULC', 102, 40),
                        ('CTVR', 101, 80);
-- room 101 exists in BOTH buildings — legal

INSERT INTO seat VALUES
    ('PULC', 101, 1, FALSE), ('PULC', 101, 2, TRUE),
    ('CTVR', 101, 1, FALSE);
-- seat 1 exists in PULC-101 and CTVR-101 — legal

-- one CASCADE removes rooms AND their seats, transitively
DELETE FROM building WHERE bldg_code = 'PULC';

SELECT b.bldg_code, r.room_no, COUNT(s.seat_no) AS seats
FROM   building b
JOIN   room r ON r.bldg_code = b.bldg_code
LEFT   JOIN seat s ON s.bldg_code = r.bldg_code
                  AND s.room_no  = r.room_no
GROUP  BY b.bldg_code, r.room_no
ORDER  BY b.bldg_code, r.room_no;
--  bldg_code | room_no | seats
--  ----------+---------+-------
--  CTVR      |     101 |     1
-- Only Thapathali survives; PULC's 2 rooms and 2 seats
-- were cascaded away by ONE delete.

The key growing at each level is the signature of a weak chain: building : (bldg_code) 1 column room : (bldg_code, room_no) 2 columns seat : (bldg_code, room_no, seat_no) 3 columns And note seat's foreign key references TWO columns together: FOREIGN KEY (bldg_code, room_no) REFERENCES room(...) A composite foreign key, because the owner's key is composite. Referencing only room_no would be meaningless — room 101 of which building? WHEN THIS GETS PAINFUL: at level 4 or 5 the keys become unwieldy and every join carries four or five columns. That is the practical argument for surrogate keys in deep hierarchies: CREATE TABLE seat ( seat_id SERIAL PRIMARY KEY, -- surrogate room_id INTEGER NOT NULL REFERENCES room(room_id), seat_no SMALLINT NOT NULL, UNIQUE (room_id, seat_no) -- natural key, still ); enforced Joins become single-column, and the UNIQUE preserves the weak-entity semantics. This is what most production schemas actually look like — but you must still be able to explain the pure weak-entity form, because that is what the exam asks for and it is what the surrogate version is emulating.

🌍 Go further: the weak-entity idea reappears as the aggregate in Domain-Driven Design: a cluster of objects with one aggregate root through which all access flows, and whose lifecycle governs the rest. Order is the root; OrderLines are inside the boundary and are never loaded or saved independently. The rule "only aggregate roots may be referenced from outside" is exactly "only strong entities have global identity", derived from software-design reasoning rather than data modelling. Search "DDD aggregate root boundary rules".

💡 Exam angle: define a weak entity as one with no key of its own, identified by owner key + partial key (also called a discriminator), and know all four notation elements — double rectangle, double diamond, dashed underline, and total participation on the weak side. The conversion rule is the marks-earner: PK = (owner PK, partial key) with a foreign key to the owner and ON DELETE CASCADE. Give a concrete pair — Employee/Dependant or Order/OrderLine — and show data where the partial key repeats across owners.

Syllabus points

Strong vs weak entity; identifying relationship

Create a free account to tick topics off, take notes as you read, watch the video lessons and get a day-by-day study plan built around your exam date.