Mapping Weak Entities and Their Relationships

When converting an Entity-Relationship (ER) diagram into a relational schema, most entities follow a straightforward pattern: gather the attributes, designate the primary key, and create the table. Weak entities, however, demand special treatment. They exist in a fundamentally different condition from regular, or strong, entities — they cannot be uniquely identified by their own attributes alone. Understanding how to handle this condition correctly is one of the most important skills in database design, because getting it wrong produces tables that either allow duplicate rows, orphan records, or fail to capture the dependency that the ER model intended.

A weak entity is any entity type that does not possess sufficient attributes to form a primary key by itself. In an ER diagram, it is drawn with a double rectangle to visually distinguish it from strong entities. Its existence is always contingent on a owner entity (sometimes called the identifying or parent entity). The relationship that ties the weak entity to its owner is called the identifying relationship, and it is drawn with a double diamond in the ER diagram. A classic example is a DEPENDENT entity related to an EMPLOYEE entity. A dependent — a child or spouse covered under an employee's benefits plan — has no meaningful independent existence in the database; it exists only because a particular employee exists.

The participation of the weak entity in an identifying relationship is always total and mandatory. This means every single instance of the weak entity must be associated with exactly one owner entity instance. You cannot have a dependent floating in the database without a corresponding employee. This constraint is not merely a business rule — it is baked into the very definition of what a weak entity is.

Partial Keys and Their Role

Because a weak entity lacks a full primary key, the ER model provides a lesser construct called a partial key (sometimes called a discriminator). In the ER diagram, the partial key attribute is underlined with a dashed underline, contrasting with the solid underline used for a full primary key. The crucial property of a partial key is that it is only unique within the context of a single owner entity instance, not across the entire dataset.

Consider a concrete example. Suppose an EMPLOYEE with ID 101 has two dependents: one named Alex and one named Jordan. A second EMPLOYEE with ID 202 also has a dependent named Alex. The name "Alex" appears twice in the DEPENDENT table, but these are entirely distinct people. The partial key — the dependent's name — does not produce a collision within a single employee's family, but it certainly produces duplicates if you look at the table as a whole. This is precisely why the partial key alone cannot serve as the primary key of the relational table.

Before writing any SQL, a designer must locate the partial key in the ER diagram. It answers the question: "Given that we already know which owner this weak entity belongs to, what attribute distinguishes one instance from another?" That answer will become part of the composite primary key in the relational table.

Creating the Weak Entity's Table

The first step in mapping a weak entity is to create a new relational table for it, just as you would for any entity. All descriptive attributes of the weak entity become regular columns in that table. For a DEPENDENT entity, attributes such as Date_of_Birth, Relationship (e.g., child, spouse), and Gender would all become columns.

The critical addition that sets the weak entity's table apart is the inclusion of the owner entity's primary key as a foreign key column. If EMPLOYEE has a primary key called Employee_ID, then a column named Employee_ID (or similar) must be added to the DEPENDENT table. This foreign key column references the EMPLOYEE table, establishing at the database level the dependency relationship that the ER diagram depicts. Without it, the DEPENDENT table would have no way of knowing which employee each dependent belongs to.

The resulting table structure for our example looks like this before the primary key is finalized:

Forming the Composite Primary Key

Neither Dependent_Name alone nor Employee_ID alone can serve as the primary key of the DEPENDENT table. Dependent_Name is not globally unique — multiple employees can have a dependent named "Alex." Employee_ID is not unique within the DEPENDENT table either — a single employee can have multiple dependents, so the same Employee_ID value would appear in multiple rows. The solution is to combine them into a composite primary key: (Employee_ID, Dependent_Name).

This composite key guarantees global uniqueness across every row in the table because it encodes both which owner and which instance within that owner's scope. If employee 101 cannot have two dependents both named "Alex," then no two rows can ever share the same (101, 'Alex') combination. The composite key also enforces the business constraint captured by the partial key concept: the partial key must be unique per owner.

The SQL definition for this table would look like the following:

CREATE TABLE DEPENDENT (
    Employee_ID     INT           NOT NULL,
    Dependent_Name  VARCHAR(50)   NOT NULL,
    Date_of_Birth   DATE,
    Relationship    VARCHAR(20),
    Gender          CHAR(1),
    PRIMARY KEY (Employee_ID, Dependent_Name),
    FOREIGN KEY (Employee_ID)
        REFERENCES EMPLOYEE(Employee_ID)
        ON DELETE CASCADE
);

Notice that Employee_ID plays a dual role: it is simultaneously a foreign key (linking back to the EMPLOYEE table) and a component of the primary key (helping uniquely identify each DEPENDENT row). This duality is the defining structural characteristic of a weak entity's relational table and is what makes it different from a typical many-to-many relationship table or a regular entity table.

Mapping the Identifying Relationship

When mapping a regular binary relationship between two strong entities, a separate relationship table is sometimes needed (particularly for many-to-many relationships). With a weak entity and its identifying relationship, this extra table is never needed. Because the foreign key referencing the owner entity is already embedded within the weak entity's table — and is already part of its primary key — the identifying relationship is fully and completely represented by the weak entity table itself. Creating a separate table for the identifying relationship would be redundant and would introduce unnecessary complexity.

This is an important conceptual point: the act of including the owner's primary key as a foreign key is the mapping of the identifying relationship. No additional work is required to represent "EMPLOYEE has DEPENDENT" once the DEPENDENT table is constructed as described above. Any non-key attributes that belong to the identifying relationship itself — though this is rare in practice — can simply be added as additional columns in the weak entity's table, since that table already represents the relationship.

Referential Integrity and Deletion Behavior

The logical dependency of a weak entity on its owner must be enforced not just in the schema's structure but also in its referential integrity constraints. The most critical constraint concerns what happens when an owner entity instance is deleted. In the ER model, a weak entity cannot exist without its owner. If an employee is removed from the EMPLOYEE table, every dependent associated with that employee must also be removed from the DEPENDENT table. This behavior is enforced by declaring ON DELETE CASCADE on the foreign key constraint, as shown in the SQL above.

Without ON DELETE CASCADE, deleting an employee row would either be blocked by the database (if the foreign key constraint uses the default NO ACTION or RESTRICT behavior) or — in poorly configured systems — might leave orphaned DEPENDENT rows pointing to a nonexistent Employee_ID. Orphaned records violate the fundamental premise of what a weak entity is: they represent dependents with no owner, which the ER model explicitly forbids.

The table below summarizes the common foreign key deletion behaviors and their implications for weak entities:

Notice that SET NULL is structurally impossible for a weak entity's foreign key: because that column is part of the primary key, SQL forbids it from ever containing a NULL value. This is yet another reason the composite primary key design is self-reinforcing — it structurally prevents the kind of data corruption that would arise from a null owner reference.

Designers must configure the ON DELETE CASCADE constraint explicitly at schema implementation time. Databases do not infer it from the ER diagram or from the fact that a foreign key is part of a primary key. It is a deliberate declaration. Teams that skip this step frequently discover orphaned records months or years later, when data inconsistencies surface during reporting or auditing.

To consolidate the full mapping process, here is a step-by-step summary of the procedure applied to the EMPLOYEE–DEPENDENT example:

• Step 1 — Identify the weak entity: Locate the double-rectangle entity (DEPENDENT) and its double-diamond identifying relationship (DEPENDS_ON) in the ER diagram.
• Step 2 — Record the partial key: Note Dependent_Name as the partial key (dashed underline in the ER diagram).
• Step 3 — Create the table: Add all descriptive attributes of DEPENDENT as columns (Date_of_Birth, Relationship, Gender).
• Step 4 — Add the owner's primary key as a foreign key: Add Employee_ID as a column with a FOREIGN KEY constraint referencing EMPLOYEE(Employee_ID).
• Step 5 — Declare the composite primary key: Set PRIMARY KEY to (Employee_ID, Dependent_Name).
• Step 6 — Configure deletion behavior: Add ON DELETE CASCADE to the foreign key constraint to maintain logical consistency when an owner is removed.
• Step 7 — Omit a separate relationship table: Recognize that no additional table is needed for the identifying relationship because the foreign key already captures it entirely.

Following these steps faithfully ensures that the relational schema accurately mirrors the semantics of the ER model, preserves data integrity, and handles the lifecycle of weak entity instances correctly throughout the life of the database.