Weak Entities - Learning Module

Loading content...

0/252

Mapping Weak Entities to Relational Schema

From Conceptual Model to Physical Tables

Throughout this module, we've built a comprehensive understanding of weak entities: their definition, identifying relationships, partial keys, and total participation constraints. Now comes the critical practical step: translating these conceptual constructs into actual database tables.

Mapping weak entities to relational schemas is more nuanced than mapping strong entities. The composite key structure, cascading foreign keys, and existence dependencies all require careful implementation. A misstep in this translation can result in databases that fail to enforce the semantics we've carefully modeled.

This page provides a systematic algorithm for weak entity mapping, complete with SQL implementations, handling of complex scenarios, and best practices from production database design.

What You Will Learn

By the end of this page, you will understand the complete algorithm for mapping weak entities to tables, how to construct composite primary keys, proper foreign key constraint syntax, handling of multi-level weak entity hierarchies, real-world SQL implementation patterns, and common pitfalls to avoid.

The Weak Entity Mapping Algorithm

Mapping a weak entity to a relational schema follows a well-defined algorithm. This systematic approach ensures all semantic constraints are properly captured in the physical implementation.

Step-by-Step Mapping Algorithm

•Create a Relation (Table) for the Weak Entity — The relation will have the same name as the weak entity (or a suitable variation following naming conventions).
•Include All Simple Attributes — Add a column for each simple attribute of the weak entity. Apply appropriate data types and constraints (NOT NULL, CHECK, etc.).
•Handle Composite Attributes — Flatten composite attributes into their component simple attributes as separate columns.
•Handle Multivalued Attributes — Create a separate table for each multivalued attribute, with a foreign key back to the weak entity (using its full composite key).
•Add Owner's Primary Key Columns — Include all columns that form the primary key of the owner entity. These will serve as both foreign key (to owner) and part of the primary key (for identification).
•Define the Composite Primary Key — The primary key is the combination of: Owner's primary key column(s) + Weak entity's partial key column(s).
•Add Foreign Key Constraint — Create a foreign key constraint on the owner's key columns, referencing the owner table, with ON DELETE CASCADE and ON UPDATE CASCADE.
•Add Other Foreign Keys (if any) — If the weak entity has relationships with other entities, add appropriate foreign key columns (these are NOT part of the primary key unless it's another identifying relationship).

Order of Table Creation

When creating tables in SQL, owner (strong) entity tables must be created before weak entity tables. The foreign key constraint requires the referenced table to exist. In migration scripts, order your CREATE TABLE statements accordingly.

Mapping Simple Weak Entities

Let's apply the mapping algorithm to a simple case: the classic Employee-Dependent example with a single-attribute partial key.

ER Model:

Owner Entity: EMPLOYEE
- Attributes: EmployeeID (PK), Name, Department, HireDate
Weak Entity: DEPENDENT
- Attributes: DependentName (partial key), BirthDate, Relationship
- Identifying Relationship: HAS (with EMPLOYEE)

Applying the Algorithm:

Create table DEPENDENT
Add columns: dependent_name, birth_date, relationship
No composite attributes to flatten
No multivalued attributes
Add owner's PK column: employee_id
Primary Key: (employee_id, dependent_name)
Foreign Key: employee_id → Employee(employee_id)
No other relationships

simple_weak_entity_mapping.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
-- ════════════════════════════════════════════════════════════════
-- STEP 1: Create Owner (Strong) Entity Table
-- ════════════════════════════════════════════════════════════════
CREATE TABLE Employee (
    employee_id     VARCHAR(10)     NOT NULL,
    name            VARCHAR(100)    NOT NULL,
    department      VARCHAR(50),
    hire_date       DATE,
    
    -- Primary key: simple, single column
    CONSTRAINT pk_employee 
        PRIMARY KEY (employee_id),
    
    -- Optional: Check constraint for valid ID format
    CONSTRAINT chk_employee_id 
        CHECK (employee_id LIKE 'E%')
);
 
-- ════════════════════════════════════════════════════════════════
-- STEP 2: Create Weak Entity Table
-- ════════════════════════════════════════════════════════════════
CREATE TABLE Dependent (
    -- Column from owner's primary key (step 5)
    -- NOT NULL is implicit since it's part of PK
    employee_id     VARCHAR(10)     NOT NULL,
    
    -- Partial key / discriminator (step 2)
    dependent_name  VARCHAR(100)    NOT NULL,
    
    -- Other simple attributes (step 2)
    birth_date      DATE,
    relationship    VARCHAR(20),
    
    -- Composite Primary Key (step 6)
    -- Combines owner's PK + partial key
    CONSTRAINT pk_dependent 
        PRIMARY KEY (employee_id, dependent_name),
    
    -- Foreign Key to Owner (step 7)
    -- CASCADE ensures existence dependency
    CONSTRAINT fk_dependent_employee 
        FOREIGN KEY (employee_id) 
        REFERENCES Employee(employee_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    
    -- Optional: Check constraint for valid relationship values
    CONSTRAINT chk_relationship 
        CHECK (relationship IN ('Spouse', 'Son', 'Daughter', 'Parent', 'Other'))
);
 
-- ════════════════════════════════════════════════════════════════
-- Create indexes for common query patterns
-- ════════════════════════════════════════════════════════════════
-- Index on relationship for filtering queries
CREATE INDEX idx_dependent_relationship ON Dependent(relationship);
 
-- Index on birth_date for age-based queries
CREATE INDEX idx_dependent_birthdate ON Dependent(birth_date);
 
-- ════════════════════════════════════════════════════════════════
-- Sample Data Insertion
-- ════════════════════════════════════════════════════════════════
-- Must insert owner first
INSERT INTO Employee VALUES ('E001', 'Alice Johnson', 'Engineering', '2020-01-15');
INSERT INTO Employee VALUES ('E002', 'Bob Smith', 'Marketing', '2019-06-01');
 
-- Now can insert dependents
INSERT INTO Dependent VALUES ('E001', 'Sarah Johnson', '2015-03-20', 'Daughter');
INSERT INTO Dependent VALUES ('E001', 'Michael Johnson', '2018-07-10', 'Son');
INSERT INTO Dependent VALUES ('E002', 'Emily Smith', '1990-11-05', 'Spouse');
 
-- ════════════════════════════════════════════════════════════════
-- Verify mapping with query
-- ════════════════════════════════════════════════════════════════
SELECT 
    e.employee_id,
    e.name AS employee_name,
    d.dependent_name,
    d.relationship,
    d.birth_date
FROM Employee e
LEFT JOIN Dependent d ON e.employee_id = d.employee_id
ORDER BY e.employee_id, d.dependent_name;

Mapping with Composite Partial Keys

When a weak entity has a composite partial key (multiple attributes forming the discriminator), the resulting primary key becomes larger. Let's examine the warehouse storage bin example.

ER Model:

Owner Entity: WAREHOUSE
- Attributes: WarehouseID (PK), Name, Address, Capacity
Weak Entity: STORAGE_BIN
- Partial Key: (Aisle, Shelf, Position) — three attributes!
- Other Attributes: MaxWeight, CurrentProduct, Quantity
- Identifying Relationship: CONTAINS (with WAREHOUSE)

Primary Key Composition:

Owner's PK: WarehouseID (1 column)
Partial Key: Aisle, Shelf, Position (3 columns)
Total PK Columns: 4

composite_partial_key_mapping.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
-- Owner Entity
CREATE TABLE Warehouse (
    warehouse_id    VARCHAR(10)     PRIMARY KEY,
    name            VARCHAR(100)    NOT NULL,
    address         VARCHAR(200),
    capacity        INT             NOT NULL
);
 
-- Weak Entity with Composite Partial Key
CREATE TABLE StorageBin (
    -- Owner's primary key
    warehouse_id    VARCHAR(10)     NOT NULL,
    
    -- Composite partial key (all three form the discriminator)
    aisle           CHAR(2)         NOT NULL,
    shelf           INT             NOT NULL,
    position        VARCHAR(10)     NOT NULL,
    
    -- Other attributes
    max_weight      DECIMAL(10,2)   NOT NULL DEFAULT 100.00,
    current_product VARCHAR(50),
    quantity        INT             DEFAULT 0,
    
    -- Four-column composite primary key!
    CONSTRAINT pk_storage_bin 
        PRIMARY KEY (warehouse_id, aisle, shelf, position),
    
    -- Foreign key to owner
    CONSTRAINT fk_bin_warehouse 
        FOREIGN KEY (warehouse_id) 
        REFERENCES Warehouse(warehouse_id)
        ON DELETE CASCADE,
    
    -- Business rules
    CONSTRAINT chk_quantity 
        CHECK (quantity >= 0),
    CONSTRAINT chk_position 
        CHECK (position IN ('LEFT', 'CENTER', 'RIGHT'))
);
 
-- Another entity that references the weak entity
-- Note: Must include ALL four PK columns in the foreign key!
CREATE TABLE InventoryMovement (
    movement_id         INT             PRIMARY KEY AUTO_INCREMENT,
    
    -- Reference to StorageBin requires ALL FOUR columns
    from_warehouse_id   VARCHAR(10),
    from_aisle          CHAR(2),
    from_shelf          INT,
    from_position       VARCHAR(10),
    
    to_warehouse_id     VARCHAR(10),
    to_aisle            CHAR(2),
    to_shelf            INT,
    to_position         VARCHAR(10),
    
    product_id          VARCHAR(50)     NOT NULL,
    quantity            INT             NOT NULL,
    movement_date       TIMESTAMP       DEFAULT CURRENT_TIMESTAMP,
    
    -- Foreign keys reference ALL columns of the weak entity's PK
    CONSTRAINT fk_from_bin 
        FOREIGN KEY (from_warehouse_id, from_aisle, from_shelf, from_position)
        REFERENCES StorageBin(warehouse_id, aisle, shelf, position),
    
    CONSTRAINT fk_to_bin 
        FOREIGN KEY (to_warehouse_id, to_aisle, to_shelf, to_position)
        REFERENCES StorageBin(warehouse_id, aisle, shelf, position)
);

Complexity Trade-off

Notice how InventoryMovement requires eight columns just to reference two storage bins! This illustrates why composite keys can become unwieldy. Consider surrogate keys when weak entities will be heavily referenced by other tables.

Mapping Multi-Level Weak Entity Hierarchies

When weak entities form a hierarchy—a weak entity is owner to another weak entity—the keys cascade. Each level inherits all key columns from its owner. Let's map the University → Department → Course → Section hierarchy.

Converting Mermaid diagram...

multi_level_hierarchy_mapping.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
-- ════════════════════════════════════════════════════════════════
-- LEVEL 0: Strong Entity (Root)
-- ════════════════════════════════════════════════════════════════
CREATE TABLE University (
    university_id   CHAR(5)         PRIMARY KEY,
    name            VARCHAR(100)    NOT NULL,
    location        VARCHAR(100),
    founded_year    INT
);
 
-- ════════════════════════════════════════════════════════════════
-- LEVEL 1: First Weak Entity (1 owner key + 1 partial key = 2 PK cols)
-- ════════════════════════════════════════════════════════════════
CREATE TABLE Department (
    -- From owner (University)
    university_id   CHAR(5)         NOT NULL,
    
    -- Partial key
    dept_code       VARCHAR(10)     NOT NULL,
    
    -- Attributes
    dept_name       VARCHAR(100)    NOT NULL,
    building        VARCHAR(50),
    budget          DECIMAL(12,2),
    
    -- 2-column PK
    PRIMARY KEY (university_id, dept_code),
    
    -- FK to owner
    FOREIGN KEY (university_id) 
        REFERENCES University(university_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);
 
-- ════════════════════════════════════════════════════════════════
-- LEVEL 2: Second Weak Entity (2 owner keys + 1 partial key = 3 PK cols)
-- ════════════════════════════════════════════════════════════════
CREATE TABLE Course (
    -- From grandparent + parent (Department's full PK)
    university_id   CHAR(5)         NOT NULL,
    dept_code       VARCHAR(10)     NOT NULL,
    
    -- Partial key
    course_number   VARCHAR(10)     NOT NULL,
    
    -- Attributes
    title           VARCHAR(100)    NOT NULL,
    credits         INT             NOT NULL,
    description     TEXT,
    
    -- 3-column PK
    PRIMARY KEY (university_id, dept_code, course_number),
    
    -- FK to owner (Department) - references its full composite PK
    FOREIGN KEY (university_id, dept_code) 
        REFERENCES Department(university_id, dept_code)
        ON DELETE CASCADE ON UPDATE CASCADE,
    
    CONSTRAINT chk_credits CHECK (credits BETWEEN 1 AND 6)
);
 
-- ════════════════════════════════════════════════════════════════
-- LEVEL 3: Third Weak Entity (3 owner keys + 1 partial key = 4 PK cols)
-- ════════════════════════════════════════════════════════════════
CREATE TABLE Section (
    -- From great-grandparent + grandparent + parent (Course's full PK)
    university_id   CHAR(5)         NOT NULL,
    dept_code       VARCHAR(10)     NOT NULL,
    course_number   VARCHAR(10)     NOT NULL,
    
    -- Partial key
    section_number  CHAR(3)         NOT NULL,
    
    -- Attributes
    semester        VARCHAR(20)     NOT NULL,
    year            INT             NOT NULL,
    instructor      VARCHAR(100),
    room            VARCHAR(20),
    max_enrollment  INT             DEFAULT 30,
    
    -- 4-column PK
    PRIMARY KEY (university_id, dept_code, course_number, section_number),
    
    -- FK to owner (Course) - references its full 3-column composite PK
    FOREIGN KEY (university_id, dept_code, course_number) 
        REFERENCES Course(university_id, dept_code, course_number)
        ON DELETE CASCADE ON UPDATE CASCADE
);
 
-- ════════════════════════════════════════════════════════════════
-- Querying the Hierarchy with Natural Keys
-- ════════════════════════════════════════════════════════════════
-- The beauty of natural composite keys: self-documenting queries
 
SELECT 
    s.university_id,
    s.dept_code,
    s.course_number,
    s.section_number,
    c.title AS course_title,
    d.dept_name,
    u.name AS university_name,
    s.instructor,
    CONCAT(s.semester, ' ', s.year) AS term
FROM Section s
JOIN Course c USING (university_id, dept_code, course_number)
JOIN Department d USING (university_id, dept_code)
JOIN University u USING (university_id)
WHERE s.semester = 'Fall' AND s.year = 2024
ORDER BY u.name, d.dept_name, c.title, s.section_number;

USING Clause Advantage

When column names match across tables (as they do with cascaded keys), the USING clause simplifies joins significantly. Instead of writing ON a.col1 = b.col1 AND a.col2 = b.col2, you can write USING (col1, col2).

Mapping Weak Entities with Multivalued Attributes

When a weak entity has a multivalued attribute (an attribute that can have multiple values), we create a separate table for that attribute. This separate table references the weak entity using its full composite primary key.

Example Scenario:

Consider a Course entity (weak, under Department) with a multivalued attribute Prerequisites (a course can have multiple prerequisites).

Course PK: (university_id, dept_code, course_number)
Multivalued Attribute: Prerequisites (list of prerequisite course codes)

The multivalued attribute becomes its own table that references Course's full composite key.

multivalued_in_weak_entity.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
-- Weak entity (from previous example)
CREATE TABLE Course (
    university_id   CHAR(5)         NOT NULL,
    dept_code       VARCHAR(10)     NOT NULL,
    course_number   VARCHAR(10)     NOT NULL,
    title           VARCHAR(100)    NOT NULL,
    credits         INT             NOT NULL,
    PRIMARY KEY (university_id, dept_code, course_number),
    FOREIGN KEY (university_id, dept_code) 
        REFERENCES Department(university_id, dept_code)
        ON DELETE CASCADE
);
 
-- ════════════════════════════════════════════════════════════════
-- Multivalued Attribute: Course Prerequisites
-- This is essentially a "weak entity of a weak entity"
-- ════════════════════════════════════════════════════════════════
CREATE TABLE CoursePrerequisite (
    -- Full PK of the owning weak entity (Course)
    university_id       CHAR(5)         NOT NULL,
    dept_code           VARCHAR(10)     NOT NULL,
    course_number       VARCHAR(10)     NOT NULL,
    
    -- The prerequisite reference (could be from any department)
    prereq_university   CHAR(5)         NOT NULL,
    prereq_dept         VARCHAR(10)     NOT NULL,
    prereq_course       VARCHAR(10)     NOT NULL,
    
    -- Additional attributes of the relationship
    min_grade           CHAR(2)         DEFAULT 'D',  -- Minimum grade required
    is_concurrent       BOOLEAN         DEFAULT FALSE, -- Can be taken concurrently
    
    -- PK: The course + each prereq forms a unique pair
    PRIMARY KEY (university_id, dept_code, course_number, 
                 prereq_university, prereq_dept, prereq_course),
    
    -- FK to the weak entity (Course) that owns this attribute
    FOREIGN KEY (university_id, dept_code, course_number)
        REFERENCES Course(university_id, dept_code, course_number)
        ON DELETE CASCADE,
    
    -- FK to the prerequisite course (also a weak entity!)
    FOREIGN KEY (prereq_university, prereq_dept, prereq_course)
        REFERENCES Course(university_id, dept_code, course_number)
        ON DELETE RESTRICT,  -- Don't allow deleting prereq if referenced
    
    -- Prevent course from being its own prerequisite
    CONSTRAINT chk_no_self_prereq CHECK (
        NOT (university_id = prereq_university 
             AND dept_code = prereq_dept 
             AND course_number = prereq_course)
    )
);
 
-- Example: CS 201 requires CS 101 and MATH 101
INSERT INTO CoursePrerequisite VALUES 
    ('MIT01', 'CS', '201', 'MIT01', 'CS', '101', 'C', FALSE),
    ('MIT01', 'CS', '201', 'MIT01', 'MATH', '101', 'D', FALSE);
 
-- Query: Find all prerequisites for a course
SELECT 
    CONCAT(p.prereq_dept, ' ', p.prereq_course) AS prerequisite,
    prereq_c.title AS prereq_title,
    p.min_grade AS min_grade_required
FROM CoursePrerequisite p
JOIN Course prereq_c 
    ON p.prereq_university = prereq_c.university_id
    AND p.prereq_dept = prereq_c.dept_code
    AND p.prereq_course = prereq_c.course_number
WHERE p.university_id = 'MIT01' 
  AND p.dept_code = 'CS' 
  AND p.course_number = '201';

Mapping Weak Entities with Multiple Owners

In some designs, a weak entity may have multiple owner entities—its identity depends on two or more strong entities simultaneously. This often occurs with intersection entities that represent M:N relationships where the relationship itself has attributes.

Example: Student Enrollment in Course Sections

Consider an Enrollment entity that represents a student's registration in a specific section:

Owner 1: Student (strong entity with StudentID)
Owner 2: Section (weak entity with 4-column PK from previous example)

Enrollment is weak because:

An enrollment has no meaning without both the student AND the section
Its identity is the combination of who enrolled and in what
Enrollment attributes (grade, enrollment_date) are properties of this specific combination

multiple_owners_mapping.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
-- Owner 1: Strong Entity
CREATE TABLE Student (
    student_id      VARCHAR(10)     PRIMARY KEY,
    name            VARCHAR(100)    NOT NULL,
    email           VARCHAR(100)    UNIQUE,
    enrollment_year INT
);
 
-- Owner 2: Weak Entity (from previous hierarchy)
-- Section with PK: (university_id, dept_code, course_number, section_number)
 
-- ════════════════════════════════════════════════════════════════
-- Weak Entity with TWO Owners
-- PK combines keys from BOTH owners
-- ════════════════════════════════════════════════════════════════
CREATE TABLE Enrollment (
    -- From Owner 1: Student
    student_id          VARCHAR(10)     NOT NULL,
    
    -- From Owner 2: Section (all 4 columns of its composite PK!)
    university_id       CHAR(5)         NOT NULL,
    dept_code           VARCHAR(10)     NOT NULL,
    course_number       VARCHAR(10)     NOT NULL,
    section_number      CHAR(3)         NOT NULL,
    
    -- Attributes of the enrollment itself
    enrollment_date     DATE            NOT NULL DEFAULT (CURRENT_DATE),
    grade               CHAR(2),
    status              VARCHAR(20)     DEFAULT 'ACTIVE',
    
    -- FIVE-column composite primary key!
    -- Combines Student's PK (1) + Section's PK (4) = 5 columns
    PRIMARY KEY (student_id, university_id, dept_code, course_number, section_number),
    
    -- FK to Owner 1: Student
    FOREIGN KEY (student_id)
        REFERENCES Student(student_id)
        ON DELETE CASCADE,
    
    -- FK to Owner 2: Section (references all 4 columns)
    FOREIGN KEY (university_id, dept_code, course_number, section_number)
        REFERENCES Section(university_id, dept_code, course_number, section_number)
        ON DELETE CASCADE,
    
    CONSTRAINT chk_grade CHECK (
        grade IS NULL OR grade IN ('A+','A','A-','B+','B','B-','C+','C','C-','D+','D','D-','F','W','I')
    ),
    CONSTRAINT chk_status CHECK (
        status IN ('ACTIVE', 'WITHDRAWN', 'COMPLETED')
    )
);
 
-- Useful indexes for common query patterns
CREATE INDEX idx_enrollment_section 
    ON Enrollment(university_id, dept_code, course_number, section_number);
CREATE INDEX idx_enrollment_student 
    ON Enrollment(student_id);
CREATE INDEX idx_enrollment_grade 
    ON Enrollment(grade) WHERE grade IS NOT NULL;
 
-- Example data
INSERT INTO Enrollment (student_id, university_id, dept_code, course_number, section_number)
VALUES ('S12345', 'MIT01', 'CS', '101', '001');
 
-- Query: Student's transcript
SELECT 
    e.student_id,
    s.name AS student_name,
    CONCAT(e.dept_code, ' ', e.course_number) AS course,
    c.title AS course_title,
    c.credits,
    e.section_number,
    e.grade,
    CONCAT(sec.semester, ' ', sec.year) AS term
FROM Enrollment e
JOIN Student s USING (student_id)
JOIN Course c USING (university_id, dept_code, course_number)
JOIN Section sec USING (university_id, dept_code, course_number, section_number)
WHERE e.student_id = 'S12345'
ORDER BY sec.year DESC, sec.semester DESC;

Key Size Warning

The Enrollment table has a 5-column primary key. Any entity referencing Enrollment would need all 5 columns in its foreign key. This is where natural keys become impractical. Consider adding a surrogate key (enrollment_id) while keeping the natural key as a unique constraint.

Alternative Mapping Strategies

While the standard mapping algorithm preserves full semantic meaning, practical considerations sometimes lead to alternative approaches. Understanding these trade-offs helps you make informed design decisions.

Adding Surrogate Keys to Weak Entities:

Instead of using the full composite natural key as PK, add a synthetic identifier:

CREATE TABLE Dependent (
    dependent_id    INT             PRIMARY KEY AUTO_INCREMENT,
    employee_id     VARCHAR(10)     NOT NULL,
    dependent_name  VARCHAR(100)    NOT NULL,
    birth_date      DATE,
    relationship    VARCHAR(20),
    
    -- Natural key as UNIQUE constraint
    UNIQUE (employee_id, dependent_name),
    
    FOREIGN KEY (employee_id) REFERENCES Employee(employee_id)
        ON DELETE CASCADE
);

Pros:

Simple, single-column references to weak entity
Easier ORM integration
Stable PK (doesn't change if dependent_name changes)

Cons:

Loses semantic meaning in the key
Must also add the natural unique constraint
Slightly more storage (additional column)

When to Use:

Weak entity is heavily referenced by other tables
Key chain exceeds 3 levels
ORM or framework strongly prefers simple keys

Summary: Mapping Weak Entities

We've completed our comprehensive exploration of weak entities with this final page on relational mapping. Let's consolidate all the essential concepts from this module:

Key Mapping Takeaways

•Create tables with owner first — Weak entity tables reference owner tables, so owners must be created first.
•Primary key = Owner's PK + Partial key — The composite primary key always combines these two components.
•Foreign key with CASCADE — Use ON DELETE CASCADE to maintain existence dependency.
•Multi-level hierarchies cascade keys — Each level adds to the key length; manage complexity carefully.
•Multivalued attributes become separate tables — These tables reference the weak entity's full composite key.
•Multiple owners multiply key components — All owner keys contribute to the weak entity's PK.
•Consider surrogate keys for complex cases — When composite keys become unwieldy, hybrid approaches work well.

Module Complete!

Congratulations! You've mastered weak entities—one of the most important advanced concepts in ER modeling. You now understand weak entity definition, identifying relationships, partial keys, total participation, and relational mapping. These concepts are essential for accurate database design and will serve you throughout your career in database management.

Module Summary - Weak Entities:

Concept	Key Point
Weak Entity	Cannot be uniquely identified by own attributes
Identifying Relationship	Transfers identity from owner to weak entity
Partial Key	Discriminates within owner's scope
Total Participation	Every weak entity must have an owner
Mapping	Composite PK + FK with CASCADE

What's Next in Chapter 10:

The next module covers Identifying Relationships in greater depth, exploring the owner-dependent relationship dynamic, discriminator patterns, and how identifying relationships manifest across different modeling scenarios.

Mapping Weak Entities to Relational Schema

From Conceptual Model to Physical Tables

This page provides a systematic algorithm for weak entity mapping, complete with SQL implementations, handling of complex scenarios, and best practices from production database design.

What You Will Learn

The Weak Entity Mapping Algorithm

Mapping a weak entity to a relational schema follows a well-defined algorithm. This systematic approach ensures all semantic constraints are properly captured in the physical implementation.

Step-by-Step Mapping Algorithm

•Create a Relation (Table) for the Weak Entity — The relation will have the same name as the weak entity (or a suitable variation following naming conventions).
•Include All Simple Attributes — Add a column for each simple attribute of the weak entity. Apply appropriate data types and constraints (NOT NULL, CHECK, etc.).
•Handle Composite Attributes — Flatten composite attributes into their component simple attributes as separate columns.
•Handle Multivalued Attributes — Create a separate table for each multivalued attribute, with a foreign key back to the weak entity (using its full composite key).
•Add Owner's Primary Key Columns — Include all columns that form the primary key of the owner entity. These will serve as both foreign key (to owner) and part of the primary key (for identification).
•Define the Composite Primary Key — The primary key is the combination of: Owner's primary key column(s) + Weak entity's partial key column(s).
•Add Foreign Key Constraint — Create a foreign key constraint on the owner's key columns, referencing the owner table, with ON DELETE CASCADE and ON UPDATE CASCADE.
•Add Other Foreign Keys (if any) — If the weak entity has relationships with other entities, add appropriate foreign key columns (these are NOT part of the primary key unless it's another identifying relationship).

Order of Table Creation

Mapping Simple Weak Entities

Let's apply the mapping algorithm to a simple case: the classic Employee-Dependent example with a single-attribute partial key.

ER Model:

Owner Entity: EMPLOYEE
- Attributes: EmployeeID (PK), Name, Department, HireDate
Weak Entity: DEPENDENT
- Attributes: DependentName (partial key), BirthDate, Relationship
- Identifying Relationship: HAS (with EMPLOYEE)

Applying the Algorithm:

Create table DEPENDENT
Add columns: dependent_name, birth_date, relationship
No composite attributes to flatten
No multivalued attributes
Add owner's PK column: employee_id
Primary Key: (employee_id, dependent_name)
Foreign Key: employee_id → Employee(employee_id)
No other relationships

simple_weak_entity_mapping.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
-- ════════════════════════════════════════════════════════════════
-- STEP 1: Create Owner (Strong) Entity Table
-- ════════════════════════════════════════════════════════════════
CREATE TABLE Employee (
    employee_id     VARCHAR(10)     NOT NULL,
    name            VARCHAR(100)    NOT NULL,
    department      VARCHAR(50),
    hire_date       DATE,
    
    -- Primary key: simple, single column
    CONSTRAINT pk_employee 
        PRIMARY KEY (employee_id),
    
    -- Optional: Check constraint for valid ID format
    CONSTRAINT chk_employee_id 
        CHECK (employee_id LIKE 'E%')
);
 
-- ════════════════════════════════════════════════════════════════
-- STEP 2: Create Weak Entity Table
-- ════════════════════════════════════════════════════════════════
CREATE TABLE Dependent (
    -- Column from owner's primary key (step 5)
    -- NOT NULL is implicit since it's part of PK
    employee_id     VARCHAR(10)     NOT NULL,
    
    -- Partial key / discriminator (step 2)
    dependent_name  VARCHAR(100)    NOT NULL,
    
    -- Other simple attributes (step 2)
    birth_date      DATE,
    relationship    VARCHAR(20),
    
    -- Composite Primary Key (step 6)
    -- Combines owner's PK + partial key
    CONSTRAINT pk_dependent 
        PRIMARY KEY (employee_id, dependent_name),
    
    -- Foreign Key to Owner (step 7)
    -- CASCADE ensures existence dependency
    CONSTRAINT fk_dependent_employee 
        FOREIGN KEY (employee_id) 
        REFERENCES Employee(employee_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    
    -- Optional: Check constraint for valid relationship values
    CONSTRAINT chk_relationship 
        CHECK (relationship IN ('Spouse', 'Son', 'Daughter', 'Parent', 'Other'))
);
 
-- ════════════════════════════════════════════════════════════════
-- Create indexes for common query patterns
-- ════════════════════════════════════════════════════════════════
-- Index on relationship for filtering queries
CREATE INDEX idx_dependent_relationship ON Dependent(relationship);
 
-- Index on birth_date for age-based queries
CREATE INDEX idx_dependent_birthdate ON Dependent(birth_date);
 
-- ════════════════════════════════════════════════════════════════
-- Sample Data Insertion
-- ════════════════════════════════════════════════════════════════
-- Must insert owner first
INSERT INTO Employee VALUES ('E001', 'Alice Johnson', 'Engineering', '2020-01-15');
INSERT INTO Employee VALUES ('E002', 'Bob Smith', 'Marketing', '2019-06-01');
 
-- Now can insert dependents
INSERT INTO Dependent VALUES ('E001', 'Sarah Johnson', '2015-03-20', 'Daughter');
INSERT INTO Dependent VALUES ('E001', 'Michael Johnson', '2018-07-10', 'Son');
INSERT INTO Dependent VALUES ('E002', 'Emily Smith', '1990-11-05', 'Spouse');
 
-- ════════════════════════════════════════════════════════════════
-- Verify mapping with query
-- ════════════════════════════════════════════════════════════════
SELECT 
    e.employee_id,
    e.name AS employee_name,
    d.dependent_name,
    d.relationship,
    d.birth_date
FROM Employee e
LEFT JOIN Dependent d ON e.employee_id = d.employee_id
ORDER BY e.employee_id, d.dependent_name;

Mapping with Composite Partial Keys

When a weak entity has a composite partial key (multiple attributes forming the discriminator), the resulting primary key becomes larger. Let's examine the warehouse storage bin example.

ER Model:

Owner Entity: WAREHOUSE
- Attributes: WarehouseID (PK), Name, Address, Capacity
Weak Entity: STORAGE_BIN
- Partial Key: (Aisle, Shelf, Position) — three attributes!
- Other Attributes: MaxWeight, CurrentProduct, Quantity
- Identifying Relationship: CONTAINS (with WAREHOUSE)

Primary Key Composition:

Owner's PK: WarehouseID (1 column)
Partial Key: Aisle, Shelf, Position (3 columns)
Total PK Columns: 4

composite_partial_key_mapping.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
-- Owner Entity
CREATE TABLE Warehouse (
    warehouse_id    VARCHAR(10)     PRIMARY KEY,
    name            VARCHAR(100)    NOT NULL,
    address         VARCHAR(200),
    capacity        INT             NOT NULL
);
 
-- Weak Entity with Composite Partial Key
CREATE TABLE StorageBin (
    -- Owner's primary key
    warehouse_id    VARCHAR(10)     NOT NULL,
    
    -- Composite partial key (all three form the discriminator)
    aisle           CHAR(2)         NOT NULL,
    shelf           INT             NOT NULL,
    position        VARCHAR(10)     NOT NULL,
    
    -- Other attributes
    max_weight      DECIMAL(10,2)   NOT NULL DEFAULT 100.00,
    current_product VARCHAR(50),
    quantity        INT             DEFAULT 0,
    
    -- Four-column composite primary key!
    CONSTRAINT pk_storage_bin 
        PRIMARY KEY (warehouse_id, aisle, shelf, position),
    
    -- Foreign key to owner
    CONSTRAINT fk_bin_warehouse 
        FOREIGN KEY (warehouse_id) 
        REFERENCES Warehouse(warehouse_id)
        ON DELETE CASCADE,
    
    -- Business rules
    CONSTRAINT chk_quantity 
        CHECK (quantity >= 0),
    CONSTRAINT chk_position 
        CHECK (position IN ('LEFT', 'CENTER', 'RIGHT'))
);
 
-- Another entity that references the weak entity
-- Note: Must include ALL four PK columns in the foreign key!
CREATE TABLE InventoryMovement (
    movement_id         INT             PRIMARY KEY AUTO_INCREMENT,
    
    -- Reference to StorageBin requires ALL FOUR columns
    from_warehouse_id   VARCHAR(10),
    from_aisle          CHAR(2),
    from_shelf          INT,
    from_position       VARCHAR(10),
    
    to_warehouse_id     VARCHAR(10),
    to_aisle            CHAR(2),
    to_shelf            INT,
    to_position         VARCHAR(10),
    
    product_id          VARCHAR(50)     NOT NULL,
    quantity            INT             NOT NULL,
    movement_date       TIMESTAMP       DEFAULT CURRENT_TIMESTAMP,
    
    -- Foreign keys reference ALL columns of the weak entity's PK
    CONSTRAINT fk_from_bin 
        FOREIGN KEY (from_warehouse_id, from_aisle, from_shelf, from_position)
        REFERENCES StorageBin(warehouse_id, aisle, shelf, position),
    
    CONSTRAINT fk_to_bin 
        FOREIGN KEY (to_warehouse_id, to_aisle, to_shelf, to_position)
        REFERENCES StorageBin(warehouse_id, aisle, shelf, position)
);

Complexity Trade-off

Mapping Multi-Level Weak Entity Hierarchies

Converting Mermaid diagram...

multi_level_hierarchy_mapping.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
-- ════════════════════════════════════════════════════════════════
-- LEVEL 0: Strong Entity (Root)
-- ════════════════════════════════════════════════════════════════
CREATE TABLE University (
    university_id   CHAR(5)         PRIMARY KEY,
    name            VARCHAR(100)    NOT NULL,
    location        VARCHAR(100),
    founded_year    INT
);
 
-- ════════════════════════════════════════════════════════════════
-- LEVEL 1: First Weak Entity (1 owner key + 1 partial key = 2 PK cols)
-- ════════════════════════════════════════════════════════════════
CREATE TABLE Department (
    -- From owner (University)
    university_id   CHAR(5)         NOT NULL,
    
    -- Partial key
    dept_code       VARCHAR(10)     NOT NULL,
    
    -- Attributes
    dept_name       VARCHAR(100)    NOT NULL,
    building        VARCHAR(50),
    budget          DECIMAL(12,2),
    
    -- 2-column PK
    PRIMARY KEY (university_id, dept_code),
    
    -- FK to owner
    FOREIGN KEY (university_id) 
        REFERENCES University(university_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);
 
-- ════════════════════════════════════════════════════════════════
-- LEVEL 2: Second Weak Entity (2 owner keys + 1 partial key = 3 PK cols)
-- ════════════════════════════════════════════════════════════════
CREATE TABLE Course (
    -- From grandparent + parent (Department's full PK)
    university_id   CHAR(5)         NOT NULL,
    dept_code       VARCHAR(10)     NOT NULL,
    
    -- Partial key
    course_number   VARCHAR(10)     NOT NULL,
    
    -- Attributes
    title           VARCHAR(100)    NOT NULL,
    credits         INT             NOT NULL,
    description     TEXT,
    
    -- 3-column PK
    PRIMARY KEY (university_id, dept_code, course_number),
    
    -- FK to owner (Department) - references its full composite PK
    FOREIGN KEY (university_id, dept_code) 
        REFERENCES Department(university_id, dept_code)
        ON DELETE CASCADE ON UPDATE CASCADE,
    
    CONSTRAINT chk_credits CHECK (credits BETWEEN 1 AND 6)
);
 
-- ════════════════════════════════════════════════════════════════
-- LEVEL 3: Third Weak Entity (3 owner keys + 1 partial key = 4 PK cols)
-- ════════════════════════════════════════════════════════════════
CREATE TABLE Section (
    -- From great-grandparent + grandparent + parent (Course's full PK)
    university_id   CHAR(5)         NOT NULL,
    dept_code       VARCHAR(10)     NOT NULL,
    course_number   VARCHAR(10)     NOT NULL,
    
    -- Partial key
    section_number  CHAR(3)         NOT NULL,
    
    -- Attributes
    semester        VARCHAR(20)     NOT NULL,
    year            INT             NOT NULL,
    instructor      VARCHAR(100),
    room            VARCHAR(20),
    max_enrollment  INT             DEFAULT 30,
    
    -- 4-column PK
    PRIMARY KEY (university_id, dept_code, course_number, section_number),
    
    -- FK to owner (Course) - references its full 3-column composite PK
    FOREIGN KEY (university_id, dept_code, course_number) 
        REFERENCES Course(university_id, dept_code, course_number)
        ON DELETE CASCADE ON UPDATE CASCADE
);
 
-- ════════════════════════════════════════════════════════════════
-- Querying the Hierarchy with Natural Keys
-- ════════════════════════════════════════════════════════════════
-- The beauty of natural composite keys: self-documenting queries
 
SELECT 
    s.university_id,
    s.dept_code,
    s.course_number,
    s.section_number,
    c.title AS course_title,
    d.dept_name,
    u.name AS university_name,
    s.instructor,
    CONCAT(s.semester, ' ', s.year) AS term
FROM Section s
JOIN Course c USING (university_id, dept_code, course_number)
JOIN Department d USING (university_id, dept_code)
JOIN University u USING (university_id)
WHERE s.semester = 'Fall' AND s.year = 2024
ORDER BY u.name, d.dept_name, c.title, s.section_number;

USING Clause Advantage

Mapping Weak Entities with Multivalued Attributes

Example Scenario:

Consider a Course entity (weak, under Department) with a multivalued attribute Prerequisites (a course can have multiple prerequisites).

Course PK: (university_id, dept_code, course_number)
Multivalued Attribute: Prerequisites (list of prerequisite course codes)

The multivalued attribute becomes its own table that references Course's full composite key.

multivalued_in_weak_entity.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
-- Weak entity (from previous example)
CREATE TABLE Course (
    university_id   CHAR(5)         NOT NULL,
    dept_code       VARCHAR(10)     NOT NULL,
    course_number   VARCHAR(10)     NOT NULL,
    title           VARCHAR(100)    NOT NULL,
    credits         INT             NOT NULL,
    PRIMARY KEY (university_id, dept_code, course_number),
    FOREIGN KEY (university_id, dept_code) 
        REFERENCES Department(university_id, dept_code)
        ON DELETE CASCADE
);
 
-- ════════════════════════════════════════════════════════════════
-- Multivalued Attribute: Course Prerequisites
-- This is essentially a "weak entity of a weak entity"
-- ════════════════════════════════════════════════════════════════
CREATE TABLE CoursePrerequisite (
    -- Full PK of the owning weak entity (Course)
    university_id       CHAR(5)         NOT NULL,
    dept_code           VARCHAR(10)     NOT NULL,
    course_number       VARCHAR(10)     NOT NULL,
    
    -- The prerequisite reference (could be from any department)
    prereq_university   CHAR(5)         NOT NULL,
    prereq_dept         VARCHAR(10)     NOT NULL,
    prereq_course       VARCHAR(10)     NOT NULL,
    
    -- Additional attributes of the relationship
    min_grade           CHAR(2)         DEFAULT 'D',  -- Minimum grade required
    is_concurrent       BOOLEAN         DEFAULT FALSE, -- Can be taken concurrently
    
    -- PK: The course + each prereq forms a unique pair
    PRIMARY KEY (university_id, dept_code, course_number, 
                 prereq_university, prereq_dept, prereq_course),
    
    -- FK to the weak entity (Course) that owns this attribute
    FOREIGN KEY (university_id, dept_code, course_number)
        REFERENCES Course(university_id, dept_code, course_number)
        ON DELETE CASCADE,
    
    -- FK to the prerequisite course (also a weak entity!)
    FOREIGN KEY (prereq_university, prereq_dept, prereq_course)
        REFERENCES Course(university_id, dept_code, course_number)
        ON DELETE RESTRICT,  -- Don't allow deleting prereq if referenced
    
    -- Prevent course from being its own prerequisite
    CONSTRAINT chk_no_self_prereq CHECK (
        NOT (university_id = prereq_university 
             AND dept_code = prereq_dept 
             AND course_number = prereq_course)
    )
);
 
-- Example: CS 201 requires CS 101 and MATH 101
INSERT INTO CoursePrerequisite VALUES 
    ('MIT01', 'CS', '201', 'MIT01', 'CS', '101', 'C', FALSE),
    ('MIT01', 'CS', '201', 'MIT01', 'MATH', '101', 'D', FALSE);
 
-- Query: Find all prerequisites for a course
SELECT 
    CONCAT(p.prereq_dept, ' ', p.prereq_course) AS prerequisite,
    prereq_c.title AS prereq_title,
    p.min_grade AS min_grade_required
FROM CoursePrerequisite p
JOIN Course prereq_c 
    ON p.prereq_university = prereq_c.university_id
    AND p.prereq_dept = prereq_c.dept_code
    AND p.prereq_course = prereq_c.course_number
WHERE p.university_id = 'MIT01' 
  AND p.dept_code = 'CS' 
  AND p.course_number = '201';

Mapping Weak Entities with Multiple Owners

Example: Student Enrollment in Course Sections

Consider an Enrollment entity that represents a student's registration in a specific section:

Owner 1: Student (strong entity with StudentID)
Owner 2: Section (weak entity with 4-column PK from previous example)

Enrollment is weak because:

An enrollment has no meaning without both the student AND the section
Its identity is the combination of who enrolled and in what
Enrollment attributes (grade, enrollment_date) are properties of this specific combination

multiple_owners_mapping.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
-- Owner 1: Strong Entity
CREATE TABLE Student (
    student_id      VARCHAR(10)     PRIMARY KEY,
    name            VARCHAR(100)    NOT NULL,
    email           VARCHAR(100)    UNIQUE,
    enrollment_year INT
);
 
-- Owner 2: Weak Entity (from previous hierarchy)
-- Section with PK: (university_id, dept_code, course_number, section_number)
 
-- ════════════════════════════════════════════════════════════════
-- Weak Entity with TWO Owners
-- PK combines keys from BOTH owners
-- ════════════════════════════════════════════════════════════════
CREATE TABLE Enrollment (
    -- From Owner 1: Student
    student_id          VARCHAR(10)     NOT NULL,
    
    -- From Owner 2: Section (all 4 columns of its composite PK!)
    university_id       CHAR(5)         NOT NULL,
    dept_code           VARCHAR(10)     NOT NULL,
    course_number       VARCHAR(10)     NOT NULL,
    section_number      CHAR(3)         NOT NULL,
    
    -- Attributes of the enrollment itself
    enrollment_date     DATE            NOT NULL DEFAULT (CURRENT_DATE),
    grade               CHAR(2),
    status              VARCHAR(20)     DEFAULT 'ACTIVE',
    
    -- FIVE-column composite primary key!
    -- Combines Student's PK (1) + Section's PK (4) = 5 columns
    PRIMARY KEY (student_id, university_id, dept_code, course_number, section_number),
    
    -- FK to Owner 1: Student
    FOREIGN KEY (student_id)
        REFERENCES Student(student_id)
        ON DELETE CASCADE,
    
    -- FK to Owner 2: Section (references all 4 columns)
    FOREIGN KEY (university_id, dept_code, course_number, section_number)
        REFERENCES Section(university_id, dept_code, course_number, section_number)
        ON DELETE CASCADE,
    
    CONSTRAINT chk_grade CHECK (
        grade IS NULL OR grade IN ('A+','A','A-','B+','B','B-','C+','C','C-','D+','D','D-','F','W','I')
    ),
    CONSTRAINT chk_status CHECK (
        status IN ('ACTIVE', 'WITHDRAWN', 'COMPLETED')
    )
);
 
-- Useful indexes for common query patterns
CREATE INDEX idx_enrollment_section 
    ON Enrollment(university_id, dept_code, course_number, section_number);
CREATE INDEX idx_enrollment_student 
    ON Enrollment(student_id);
CREATE INDEX idx_enrollment_grade 
    ON Enrollment(grade) WHERE grade IS NOT NULL;
 
-- Example data
INSERT INTO Enrollment (student_id, university_id, dept_code, course_number, section_number)
VALUES ('S12345', 'MIT01', 'CS', '101', '001');
 
-- Query: Student's transcript
SELECT 
    e.student_id,
    s.name AS student_name,
    CONCAT(e.dept_code, ' ', e.course_number) AS course,
    c.title AS course_title,
    c.credits,
    e.section_number,
    e.grade,
    CONCAT(sec.semester, ' ', sec.year) AS term
FROM Enrollment e
JOIN Student s USING (student_id)
JOIN Course c USING (university_id, dept_code, course_number)
JOIN Section sec USING (university_id, dept_code, course_number, section_number)
WHERE e.student_id = 'S12345'
ORDER BY sec.year DESC, sec.semester DESC;

Key Size Warning

Alternative Mapping Strategies

Adding Surrogate Keys to Weak Entities:

Instead of using the full composite natural key as PK, add a synthetic identifier:

CREATE TABLE Dependent (
    dependent_id    INT             PRIMARY KEY AUTO_INCREMENT,
    employee_id     VARCHAR(10)     NOT NULL,
    dependent_name  VARCHAR(100)    NOT NULL,
    birth_date      DATE,
    relationship    VARCHAR(20),
    
    -- Natural key as UNIQUE constraint
    UNIQUE (employee_id, dependent_name),
    
    FOREIGN KEY (employee_id) REFERENCES Employee(employee_id)
        ON DELETE CASCADE
);

Pros:

Simple, single-column references to weak entity
Easier ORM integration
Stable PK (doesn't change if dependent_name changes)

Cons:

Loses semantic meaning in the key
Must also add the natural unique constraint
Slightly more storage (additional column)

When to Use:

Weak entity is heavily referenced by other tables
Key chain exceeds 3 levels
ORM or framework strongly prefers simple keys

Summary: Mapping Weak Entities

We've completed our comprehensive exploration of weak entities with this final page on relational mapping. Let's consolidate all the essential concepts from this module:

Key Mapping Takeaways

•Create tables with owner first — Weak entity tables reference owner tables, so owners must be created first.
•Primary key = Owner's PK + Partial key — The composite primary key always combines these two components.
•Foreign key with CASCADE — Use ON DELETE CASCADE to maintain existence dependency.
•Multi-level hierarchies cascade keys — Each level adds to the key length; manage complexity carefully.
•Multivalued attributes become separate tables — These tables reference the weak entity's full composite key.
•Multiple owners multiply key components — All owner keys contribute to the weak entity's PK.
•Consider surrogate keys for complex cases — When composite keys become unwieldy, hybrid approaches work well.

Module Complete!

Module Summary - Weak Entities:

Concept	Key Point
Weak Entity	Cannot be uniquely identified by own attributes
Identifying Relationship	Transfers identity from owner to weak entity
Partial Key	Discriminates within owner's scope
Total Participation	Every weak entity must have an owner
Mapping	Composite PK + FK with CASCADE

What's Next in Chapter 10: