Database Management SystemsWeak Entities

Weak Entities in ER Modeling

LevelIntermediate

Duration60 mins

TopicWeak Entities

3 / 5

Partial Key (Discriminator)

The Weak Entity's Contribution to Identity

We've established that weak entities cannot uniquely identify themselves using only their own attributes. Their primary key is formed by combining the owner's primary key with something from the weak entity itself. But what is that "something"?

The partial key (also called the discriminator) is the weak entity's contribution to its own identity. It's the attribute or set of attributes that uniquely identifies weak entity instances within the context of a single owner. While not globally unique, the partial key, when combined with the owner's key, creates a globally unique composite identifier.

Understanding partial keys is essential because they determine how weak entities are distinguished from each other and how the composite primary key is structured in the relational model.

What You Will Learn

By the end of this page, you will understand the formal definition of partial keys, how they differ from regular primary keys, criteria for selecting appropriate partial keys, how to handle simple and composite partial keys, and the notation used to represent them in ER diagrams.

Formal Definition of Partial Key

A partial key (also known as a discriminator or weak key) is a set of attributes of a weak entity type W such that:

Local Uniqueness — For any instance o of the owner entity O, the partial key value is unique among all instances of W that are associated with o through the identifying relationship.
Global Non-Uniqueness — The partial key value alone is NOT unique across all instances of W. Instances associated with different owners may have the same partial key value.
Minimality — The partial key should be minimal—no subset of the partial key attributes satisfies the local uniqueness property.

Formal Notation:

Let W be a weak entity type with attributes A₁, A₂, ..., Aₙ, owner entity O with primary key K_O, and identifying relationship R.

A subset D ⊆ {A₁, A₂, ..., Aₙ} is a partial key of W if and only if:

∀o ∈ O, ∀w₁, w₂ ∈ W: [(w₁, o) ∈ R ∧ (w₂, o) ∈ R ∧ w₁.D = w₂.D] → w₁ = w₂

(For any owner o, if two weak entities share the same partial key value and both are related to o, they must be the same entity)
∃o ∈ O, ∃w₁, w₂ ∈ W: [(w₁, o) ∈ R ∧ ¬(w₂, o) ∈ R ∧ w₁.D = w₂.D]

(There exist weak entities with the same partial key value that are related to different owners)

The primary key of W is then: PK(W) = K_O ∪ D

The Scoping Metaphor

Think of the partial key as a local address within a neighborhood. "123 Main Street" might exist in many cities. Within one city, this address is unique. But globally, you need the city name + street address for uniqueness. The owner's key is the "city" and the partial key is the "local address."

Partial Key vs. Primary Key

Understanding the distinction between partial keys and primary keys is crucial for accurate database modeling. While both contribute to entity identification, they operate at different scopes.

Comparison: Partial Key vs. Primary Key
Property	Primary Key	Partial Key
Entity Type	Strong entity	Weak entity only
Uniqueness Scope	Global (entire entity set)	Local (within one owner's scope)
Standalone Identification	Yes, sufficient alone	No, requires owner's key
ER Notation (Chen)	Solid underline	Dashed underline
NULL Allowed?	Never	Never (part of composite PK)
Composed of	Own attributes only	Own attributes only
Results In	Simple or composite PK	Composite PK when combined with owner's key
Minimal Requirement	Yes, no redundant attributes	Yes, no redundant attributes

Example: Primary Key (Strong Entity)

Entity: Employee Candidate Keys: EmployeeID, SSN Selected Primary Key: EmployeeID

EmployeeID uniquely identifies ANY employee in the system, regardless of department, location, or any other context.

Employee E12345 - Always means the same person
Employee E67890 - Always means the same person

No additional context needed.

Example: Partial Key (Weak Entity)

Entity: Dependent (of Employee) Partial Key: DependentName

DependentName uniquely identifies a dependent ONLY within one employee's dependents.

(E12345, Sarah) - Sarah of Employee E12345
(E67890, Sarah) - Sarah of Employee E67890
                  (Different person!)

Owner context is essential.

Common Confusion

Don't confuse partial keys with partial participation. Partial keys relate to identification (the discriminator in a weak entity). Partial participation relates to whether every entity instance must participate in a relationship. They are entirely different concepts despite the similar terminology.

Selecting Appropriate Partial Keys

Choosing the right partial key is a critical design decision. A poor choice can lead to data integrity issues, update anomalies, or unnecessary complexity. Follow these criteria when selecting partial keys:

Essential Selection Criteria

•Business Meaning — The partial key should have natural significance in the business domain. DependentName is meaningful; a random number is not. Business users should understand what the discriminator represents.
•Stability — Choose attributes that don't change frequently. If DependentName changes (name change after marriage), you'll need to update the primary key—a complex operation affecting referential integrity.
•Natural Uniqueness Within Scope — The attribute should naturally be unique within the owner's context. Room numbers are naturally unique within a building. Multiple rooms with the same number in one building would be confusing.
•Minimality — Use the fewest attributes necessary for uniqueness. If RoomNumber alone is unique within a Building, don't include Floor in the partial key (though Floor can remain as a regular attribute).
•Non-Null Values — Partial key attributes must never be null. They're part of the primary key. Ensure the business domain guarantees a value will always exist.
•Reasonable Size — Avoid very long text fields as partial keys if possible. They impact index size, comparison performance, and storage. A short code or number is often preferable.

When Natural Choices Are Problematic

If no natural attribute satisfies these criteria, consider: (1) A sequence number within the owner (LineNumber 1, 2, 3...), (2) A short code assigned by the system, (3) A timestamp if temporal uniqueness is appropriate. These are acceptable when natural business attributes don't provide adequate uniqueness.

Simple (Single-Attribute) Partial Keys

The most common scenario is a weak entity with a single attribute serving as the partial key. Let's examine several examples across different domains:

Owner Entity: Employee

Primary Key: EmployeeID

Weak Entity: Dependent

Partial Key: DependentName
Other Attributes: BirthDate, Relationship, SSN

Why DependentName?

It's natural—employees refer to dependents by name
It's unique within family context (unlikely to have two children named "Sarah")
It's relatively stable (though legal name changes occur)

Composite Primary Key: (EmployeeID, DependentName)

SQL Implementation:

CREATE TABLE Dependent (
    employee_id VARCHAR(10) NOT NULL,
    dependent_name VARCHAR(100) NOT NULL,
    birth_date DATE,
    relationship VARCHAR(20),
    PRIMARY KEY (employee_id, dependent_name),
    FOREIGN KEY (employee_id) REFERENCES Employee(employee_id)
        ON DELETE CASCADE
);

Composite (Multi-Attribute) Partial Keys

Sometimes a single attribute isn't sufficient to discriminate weak entity instances within an owner's scope. In these cases, multiple attributes combine to form a composite partial key. This is more complex but sometimes necessary for accurate modeling.

When Composite Partial Keys Are Needed

•No single attribute is unique — An apartment might have multiple bedrooms, and "bedroom" alone isn't unique. You might need (Floor, Position) to identify "the northeast bedroom on floor 2."
•Multi-dimensional identification — A warehouse bin location might require (Aisle, Shelf, Position) to be unique within a warehouse.
•Temporal scoping — A class schedule entry might need (DayOfWeek, StartTime) to be unique within a room's schedule.
•Inherited discrimination — The weak entity itself might be owner to another weak entity, requiring extension of the key chain.

Detailed Example: Warehouse Bin Location

A warehouse management system tracks storage locations. Each warehouse contains many storage bins organized by aisle, shelf, and position.

Owner Entity: Warehouse

Primary Key: WarehouseID

Weak Entity: StorageBin

Partial Key: (Aisle, Shelf, Position) — Composite!
Other Attributes: MaxWeight, CurrentProduct, Quantity

Why Composite Partial Key?

A single Aisle name like "A" exists on multiple shelves
A single Shelf number like "3" exists in multiple aisles
A single Position like "Left" exists on multiple shelves
Only the combination (A, 3, Left) is unique within a warehouse

Composite Primary Key: (WarehouseID, Aisle, Shelf, Position) — Four attributes!

composite_partial_key.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
-- Owner Entity
CREATE TABLE Warehouse (
    warehouse_id VARCHAR(10) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    address VARCHAR(200),
    total_capacity INT
);
 
-- Weak Entity with Composite Partial Key
CREATE TABLE StorageBin (
    -- Owner's key (part of PK)
    warehouse_id VARCHAR(10) NOT NULL,
    
    -- Composite partial key (all three together discriminate)
    aisle CHAR(2) NOT NULL,
    shelf INT NOT NULL,
    position VARCHAR(10) NOT NULL,  -- 'LEFT', 'CENTER', 'RIGHT'
    
    -- Other attributes
    max_weight DECIMAL(10,2),
    current_product VARCHAR(20),
    quantity INT DEFAULT 0,
    
    -- Composite Primary Key: Owner's key + All partial key attributes
    PRIMARY KEY (warehouse_id, aisle, shelf, position),
    
    FOREIGN KEY (warehouse_id) 
        REFERENCES Warehouse(warehouse_id)
        ON DELETE CASCADE
);
 
-- Example data
INSERT INTO Warehouse VALUES ('WH001', 'East Distribution Center', '123 Industrial Ave', 50000);
INSERT INTO StorageBin VALUES ('WH001', 'A', 1, 'LEFT', 100.0, 'WIDGET-A', 50);
INSERT INTO StorageBin VALUES ('WH001', 'A', 1, 'RIGHT', 100.0, 'WIDGET-B', 30);
INSERT INTO StorageBin VALUES ('WH001', 'A', 2, 'LEFT', 150.0, NULL, 0);
INSERT INTO StorageBin VALUES ('WH001', 'B', 1, 'LEFT', 100.0, 'GADGET-X', 75);
 
-- Each bin is uniquely identified:
-- Warehouse WH001, Aisle A, Shelf 1, Position LEFT
-- Warehouse WH001, Aisle A, Shelf 1, Position RIGHT (different bin!)

Complexity Trade-off

Composite partial keys increase primary key size linearly. If StorageBin were owner to another weak entity (say, BinCompartment), the grandchild would have a five-attribute primary key. Consider using surrogate keys when nesting exceeds 2-3 levels or when key size impacts performance.

Cascading Keys in Multi-Level Hierarchies

When weak entities are nested—a weak entity serves as owner to another weak entity—the keys cascade. Each level adds its partial key to the growing composite, creating a hierarchical key structure.

Three-Level Example: University → Department → Course → Section

Consider a university system with the following hierarchy:

Level 0 (Strong): University

PK: UniversityID

Level 1 (Weak): Department

Partial Key: DeptCode
Full PK: (UniversityID, DeptCode)

Level 2 (Weak): Course

Partial Key: CourseNumber
Full PK: (UniversityID, DeptCode, CourseNumber)

Level 3 (Weak) [Optional]: Section

Partial Key: SectionNumber
Full PK: (UniversityID, DeptCode, CourseNumber, SectionNumber)

Each level's primary key includes ALL ancestor keys plus its own discriminator.

Converting Mermaid diagram...

cascading_keys.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
-- Level 0: Strong Entity
CREATE TABLE University (
    university_id CHAR(5) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    location VARCHAR(100)
);
 
-- Level 1: Weak Entity (owner: University)
CREATE TABLE Department (
    university_id CHAR(5) NOT NULL,
    dept_code VARCHAR(4) NOT NULL,
    dept_name VARCHAR(100) NOT NULL,
    building VARCHAR(50),
    PRIMARY KEY (university_id, dept_code),
    FOREIGN KEY (university_id) REFERENCES University(university_id)
        ON DELETE CASCADE
);
 
-- Level 2: Weak Entity (owner: Department, which is weak!)
CREATE TABLE Course (
    university_id CHAR(5) NOT NULL,
    dept_code VARCHAR(4) NOT NULL,
    course_number VARCHAR(4) NOT NULL,  -- e.g., '101', '350'
    title VARCHAR(100) NOT NULL,
    credits INT NOT NULL,
    PRIMARY KEY (university_id, dept_code, course_number),
    -- Foreign key references the composite key of Department
    FOREIGN KEY (university_id, dept_code) 
        REFERENCES Department(university_id, dept_code)
        ON DELETE CASCADE
);
 
-- Level 3: Weak Entity (owner: Course, which is weak!)
CREATE TABLE Section (
    university_id CHAR(5) NOT NULL,
    dept_code VARCHAR(4) NOT NULL,
    course_number VARCHAR(4) NOT NULL,
    section_number CHAR(3) NOT NULL,  -- e.g., '001', '002'
    instructor VARCHAR(100),
    room VARCHAR(20),
    semester VARCHAR(20),
    PRIMARY KEY (university_id, dept_code, course_number, section_number),
    FOREIGN KEY (university_id, dept_code, course_number)
        REFERENCES Course(university_id, dept_code, course_number)
        ON DELETE CASCADE
);
 
-- Example: Insert MIT CS 6.001 Section 001
INSERT INTO University VALUES ('MIT01', 'Massachusetts Institute of Technology', 'Cambridge, MA');
INSERT INTO Department VALUES ('MIT01', 'CS', 'Computer Science', 'Stata Center');
INSERT INTO Course VALUES ('MIT01', 'CS', '6001', 'Structure and Interpretation', 4);
INSERT INTO Section VALUES ('MIT01', 'CS', '6001', '001', 'Dr. Sussman', 'Room 34-101', 'Fall 2024');

Practical Limit

While theoretically unlimited, cascading keys beyond 3-4 levels becomes impractical. Foreign key references become unwieldy, and queries require many join conditions. Beyond this depth, strongly consider surrogate keys or flattening the hierarchy.

ER Notation for Partial Keys

Properly representing partial keys in ER diagrams ensures clear communication of the weak entity structure. The notation differs from primary key representation to visually distinguish these different concepts.

Notation by Standard

•Chen Notation — The partial key attribute is underlined with a dashed line (- - - -), contrasting with the solid underline used for primary keys of strong entities. The attribute is shown in an ellipse connected to the double-bordered weak entity rectangle.
•IE/Crow's Foot Notation — Tools vary, but common approaches include: (1) Showing "PK" marker with a distinguishing symbol, (2) Using a special color or style for discriminator attributes, (3) Listing partial key attributes at the top of the entity box with "PK,FK" notation.
•UML Class Diagrams — The partial key may be marked with a stereotype «discriminator» or shown with a special constraint annotation. Some modelers simply note the composite key in the class documentation.
•IDEF1X Notation — Uses a different approach: the weak entity (dependent entity) is shown with rounded corners, and discriminator attributes are listed above a horizontal line within the entity box.

Visual Elements by Notation Style
Element	Chen Notation	Crow's Foot/IE	IDEF1X
Weak Entity Shape	Double rectangle	Rectangle (with marker)	Rounded corners
Partial Key Indicator	Dashed underline	PK marker or color	Above line in box
Identifying Relationship	Double diamond	Solid line style	Solid line with dot
Owner Connection	Double line to relationship	Mandatory marker	Connection line

Reading Example (Chen Notation):

┌─────────────────────┐       ╔═════════════╗       ╔═══════════════════════╗
│      EMPLOYEE       │ ══════║   SUPPORTS  ║══════ ║      DEPENDENT        ║
│─────────────────────│       ╚═════════════╝       ║───────────────────────║
│ EmployeeID (PK)     │                             ║ DependentName (disc.) ║
│ Name                │                             ║ BirthDate             ║
│ Department          │                             ║ Relationship          ║
└─────────────────────┘                             ╚═══════════════════════╝
      ▲                                                       ▲
      │                                                       │
 Single border = Strong Entity                    Double border = Weak Entity
                                                  "disc." = Discriminator (partial key)
                                                  Shown with dashed underline

The double border around DEPENDENT, double border around SUPPORTS, and dashed underline on DependentName all signal the weak entity structure.

Summary: Partial Keys

We've thoroughly explored partial keys—the weak entity's contribution to its composite identity. Let's consolidate the essential concepts:

Key Takeaways

•Partial keys provide local uniqueness — They distinguish weak entity instances within the scope of one owner, but not globally.
•Combined with owner's key, they form the primary key — PK(WeakEntity) = PK(Owner) + PartialKey. This composite key is globally unique.
•Selection criteria matter — Choose partial keys that are natural, stable, minimal, non-null, and reasonably sized.
•Simple partial keys are common — A single attribute (like DependentName or RoomNumber) often suffices.
•Composite partial keys handle complex cases — When no single attribute is unique within scope, multiple attributes combine.
•Keys cascade in hierarchies — Each level of weak entity nesting adds to the key length. Manage complexity carefully.
•Notation distinguishes partial keys — Chen uses dashed underlines; other notations have their own conventions.

What's Next:

We've established the identity structure of weak entities: the owner's key plus the partial key. Now we'll examine total participation—the participation constraint that requires every weak entity instance to be associated with exactly one owner. We'll explore why this constraint is mandatory, how it's enforced, and its implications for data integrity.

Page Complete

You now understand partial keys: their definition, how they differ from primary keys, selection criteria, simple and composite forms, cascading in hierarchies, and proper notation. Next, we'll examine total participation—the mandatory relationship constraint for weak entities.

3 / 5

Loading learning content...

Database Management SystemsWeak Entities

Weak Entities in ER Modeling

LevelIntermediate

Duration60 mins

TopicWeak Entities

3 / 5

Partial Key (Discriminator)

The Weak Entity's Contribution to Identity

Understanding partial keys is essential because they determine how weak entities are distinguished from each other and how the composite primary key is structured in the relational model.

What You Will Learn

Formal Definition of Partial Key

A partial key (also known as a discriminator or weak key) is a set of attributes of a weak entity type W such that:

Local Uniqueness — For any instance o of the owner entity O, the partial key value is unique among all instances of W that are associated with o through the identifying relationship.
Global Non-Uniqueness — The partial key value alone is NOT unique across all instances of W. Instances associated with different owners may have the same partial key value.
Minimality — The partial key should be minimal—no subset of the partial key attributes satisfies the local uniqueness property.

Formal Notation:

Let W be a weak entity type with attributes A₁, A₂, ..., Aₙ, owner entity O with primary key K_O, and identifying relationship R.

A subset D ⊆ {A₁, A₂, ..., Aₙ} is a partial key of W if and only if:

∀o ∈ O, ∀w₁, w₂ ∈ W: [(w₁, o) ∈ R ∧ (w₂, o) ∈ R ∧ w₁.D = w₂.D] → w₁ = w₂

(For any owner o, if two weak entities share the same partial key value and both are related to o, they must be the same entity)
∃o ∈ O, ∃w₁, w₂ ∈ W: [(w₁, o) ∈ R ∧ ¬(w₂, o) ∈ R ∧ w₁.D = w₂.D]

(There exist weak entities with the same partial key value that are related to different owners)

The primary key of W is then: PK(W) = K_O ∪ D

The Scoping Metaphor

Partial Key vs. Primary Key

Understanding the distinction between partial keys and primary keys is crucial for accurate database modeling. While both contribute to entity identification, they operate at different scopes.

Comparison: Partial Key vs. Primary Key
Property	Primary Key	Partial Key
Entity Type	Strong entity	Weak entity only
Uniqueness Scope	Global (entire entity set)	Local (within one owner's scope)
Standalone Identification	Yes, sufficient alone	No, requires owner's key
ER Notation (Chen)	Solid underline	Dashed underline
NULL Allowed?	Never	Never (part of composite PK)
Composed of	Own attributes only	Own attributes only
Results In	Simple or composite PK	Composite PK when combined with owner's key
Minimal Requirement	Yes, no redundant attributes	Yes, no redundant attributes

Example: Primary Key (Strong Entity)

Entity: Employee Candidate Keys: EmployeeID, SSN Selected Primary Key: EmployeeID

EmployeeID uniquely identifies ANY employee in the system, regardless of department, location, or any other context.

Employee E12345 - Always means the same person
Employee E67890 - Always means the same person

No additional context needed.

Example: Partial Key (Weak Entity)

Entity: Dependent (of Employee) Partial Key: DependentName

DependentName uniquely identifies a dependent ONLY within one employee's dependents.

(E12345, Sarah) - Sarah of Employee E12345
(E67890, Sarah) - Sarah of Employee E67890
                  (Different person!)

Owner context is essential.

Common Confusion

Selecting Appropriate Partial Keys

Essential Selection Criteria

•Business Meaning — The partial key should have natural significance in the business domain. DependentName is meaningful; a random number is not. Business users should understand what the discriminator represents.
•Stability — Choose attributes that don't change frequently. If DependentName changes (name change after marriage), you'll need to update the primary key—a complex operation affecting referential integrity.
•Natural Uniqueness Within Scope — The attribute should naturally be unique within the owner's context. Room numbers are naturally unique within a building. Multiple rooms with the same number in one building would be confusing.
•Minimality — Use the fewest attributes necessary for uniqueness. If RoomNumber alone is unique within a Building, don't include Floor in the partial key (though Floor can remain as a regular attribute).
•Non-Null Values — Partial key attributes must never be null. They're part of the primary key. Ensure the business domain guarantees a value will always exist.
•Reasonable Size — Avoid very long text fields as partial keys if possible. They impact index size, comparison performance, and storage. A short code or number is often preferable.

When Natural Choices Are Problematic

Simple (Single-Attribute) Partial Keys

The most common scenario is a weak entity with a single attribute serving as the partial key. Let's examine several examples across different domains:

Owner Entity: Employee

Primary Key: EmployeeID

Weak Entity: Dependent

Partial Key: DependentName
Other Attributes: BirthDate, Relationship, SSN

Why DependentName?

It's natural—employees refer to dependents by name
It's unique within family context (unlikely to have two children named "Sarah")
It's relatively stable (though legal name changes occur)

Composite Primary Key: (EmployeeID, DependentName)

SQL Implementation:

CREATE TABLE Dependent (
    employee_id VARCHAR(10) NOT NULL,
    dependent_name VARCHAR(100) NOT NULL,
    birth_date DATE,
    relationship VARCHAR(20),
    PRIMARY KEY (employee_id, dependent_name),
    FOREIGN KEY (employee_id) REFERENCES Employee(employee_id)
        ON DELETE CASCADE
);

Composite (Multi-Attribute) Partial Keys

When Composite Partial Keys Are Needed

•No single attribute is unique — An apartment might have multiple bedrooms, and "bedroom" alone isn't unique. You might need (Floor, Position) to identify "the northeast bedroom on floor 2."
•Multi-dimensional identification — A warehouse bin location might require (Aisle, Shelf, Position) to be unique within a warehouse.
•Temporal scoping — A class schedule entry might need (DayOfWeek, StartTime) to be unique within a room's schedule.
•Inherited discrimination — The weak entity itself might be owner to another weak entity, requiring extension of the key chain.

Detailed Example: Warehouse Bin Location

A warehouse management system tracks storage locations. Each warehouse contains many storage bins organized by aisle, shelf, and position.

Owner Entity: Warehouse

Primary Key: WarehouseID

Weak Entity: StorageBin

Partial Key: (Aisle, Shelf, Position) — Composite!
Other Attributes: MaxWeight, CurrentProduct, Quantity

Why Composite Partial Key?

A single Aisle name like "A" exists on multiple shelves
A single Shelf number like "3" exists in multiple aisles
A single Position like "Left" exists on multiple shelves
Only the combination (A, 3, Left) is unique within a warehouse

Composite Primary Key: (WarehouseID, Aisle, Shelf, Position) — Four attributes!

composite_partial_key.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
-- Owner Entity
CREATE TABLE Warehouse (
    warehouse_id VARCHAR(10) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    address VARCHAR(200),
    total_capacity INT
);
 
-- Weak Entity with Composite Partial Key
CREATE TABLE StorageBin (
    -- Owner's key (part of PK)
    warehouse_id VARCHAR(10) NOT NULL,
    
    -- Composite partial key (all three together discriminate)
    aisle CHAR(2) NOT NULL,
    shelf INT NOT NULL,
    position VARCHAR(10) NOT NULL,  -- 'LEFT', 'CENTER', 'RIGHT'
    
    -- Other attributes
    max_weight DECIMAL(10,2),
    current_product VARCHAR(20),
    quantity INT DEFAULT 0,
    
    -- Composite Primary Key: Owner's key + All partial key attributes
    PRIMARY KEY (warehouse_id, aisle, shelf, position),
    
    FOREIGN KEY (warehouse_id) 
        REFERENCES Warehouse(warehouse_id)
        ON DELETE CASCADE
);
 
-- Example data
INSERT INTO Warehouse VALUES ('WH001', 'East Distribution Center', '123 Industrial Ave', 50000);
INSERT INTO StorageBin VALUES ('WH001', 'A', 1, 'LEFT', 100.0, 'WIDGET-A', 50);
INSERT INTO StorageBin VALUES ('WH001', 'A', 1, 'RIGHT', 100.0, 'WIDGET-B', 30);
INSERT INTO StorageBin VALUES ('WH001', 'A', 2, 'LEFT', 150.0, NULL, 0);
INSERT INTO StorageBin VALUES ('WH001', 'B', 1, 'LEFT', 100.0, 'GADGET-X', 75);
 
-- Each bin is uniquely identified:
-- Warehouse WH001, Aisle A, Shelf 1, Position LEFT
-- Warehouse WH001, Aisle A, Shelf 1, Position RIGHT (different bin!)

Complexity Trade-off

Cascading Keys in Multi-Level Hierarchies

Three-Level Example: University → Department → Course → Section

Consider a university system with the following hierarchy:

Level 0 (Strong): University

PK: UniversityID

Level 1 (Weak): Department

Partial Key: DeptCode
Full PK: (UniversityID, DeptCode)

Level 2 (Weak): Course

Partial Key: CourseNumber
Full PK: (UniversityID, DeptCode, CourseNumber)

Level 3 (Weak) [Optional]: Section

Partial Key: SectionNumber
Full PK: (UniversityID, DeptCode, CourseNumber, SectionNumber)

Each level's primary key includes ALL ancestor keys plus its own discriminator.

Converting Mermaid diagram...

cascading_keys.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
-- Level 0: Strong Entity
CREATE TABLE University (
    university_id CHAR(5) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    location VARCHAR(100)
);
 
-- Level 1: Weak Entity (owner: University)
CREATE TABLE Department (
    university_id CHAR(5) NOT NULL,
    dept_code VARCHAR(4) NOT NULL,
    dept_name VARCHAR(100) NOT NULL,
    building VARCHAR(50),
    PRIMARY KEY (university_id, dept_code),
    FOREIGN KEY (university_id) REFERENCES University(university_id)
        ON DELETE CASCADE
);
 
-- Level 2: Weak Entity (owner: Department, which is weak!)
CREATE TABLE Course (
    university_id CHAR(5) NOT NULL,
    dept_code VARCHAR(4) NOT NULL,
    course_number VARCHAR(4) NOT NULL,  -- e.g., '101', '350'
    title VARCHAR(100) NOT NULL,
    credits INT NOT NULL,
    PRIMARY KEY (university_id, dept_code, course_number),
    -- Foreign key references the composite key of Department
    FOREIGN KEY (university_id, dept_code) 
        REFERENCES Department(university_id, dept_code)
        ON DELETE CASCADE
);
 
-- Level 3: Weak Entity (owner: Course, which is weak!)
CREATE TABLE Section (
    university_id CHAR(5) NOT NULL,
    dept_code VARCHAR(4) NOT NULL,
    course_number VARCHAR(4) NOT NULL,
    section_number CHAR(3) NOT NULL,  -- e.g., '001', '002'
    instructor VARCHAR(100),
    room VARCHAR(20),
    semester VARCHAR(20),
    PRIMARY KEY (university_id, dept_code, course_number, section_number),
    FOREIGN KEY (university_id, dept_code, course_number)
        REFERENCES Course(university_id, dept_code, course_number)
        ON DELETE CASCADE
);
 
-- Example: Insert MIT CS 6.001 Section 001
INSERT INTO University VALUES ('MIT01', 'Massachusetts Institute of Technology', 'Cambridge, MA');
INSERT INTO Department VALUES ('MIT01', 'CS', 'Computer Science', 'Stata Center');
INSERT INTO Course VALUES ('MIT01', 'CS', '6001', 'Structure and Interpretation', 4);
INSERT INTO Section VALUES ('MIT01', 'CS', '6001', '001', 'Dr. Sussman', 'Room 34-101', 'Fall 2024');

Practical Limit

ER Notation for Partial Keys

Notation by Standard

•Chen Notation — The partial key attribute is underlined with a dashed line (- - - -), contrasting with the solid underline used for primary keys of strong entities. The attribute is shown in an ellipse connected to the double-bordered weak entity rectangle.
•IE/Crow's Foot Notation — Tools vary, but common approaches include: (1) Showing "PK" marker with a distinguishing symbol, (2) Using a special color or style for discriminator attributes, (3) Listing partial key attributes at the top of the entity box with "PK,FK" notation.
•UML Class Diagrams — The partial key may be marked with a stereotype «discriminator» or shown with a special constraint annotation. Some modelers simply note the composite key in the class documentation.
•IDEF1X Notation — Uses a different approach: the weak entity (dependent entity) is shown with rounded corners, and discriminator attributes are listed above a horizontal line within the entity box.

Visual Elements by Notation Style
Element	Chen Notation	Crow's Foot/IE	IDEF1X
Weak Entity Shape	Double rectangle	Rectangle (with marker)	Rounded corners
Partial Key Indicator	Dashed underline	PK marker or color	Above line in box
Identifying Relationship	Double diamond	Solid line style	Solid line with dot
Owner Connection	Double line to relationship	Mandatory marker	Connection line

Reading Example (Chen Notation):

┌─────────────────────┐       ╔═════════════╗       ╔═══════════════════════╗
│      EMPLOYEE       │ ══════║   SUPPORTS  ║══════ ║      DEPENDENT        ║
│─────────────────────│       ╚═════════════╝       ║───────────────────────║
│ EmployeeID (PK)     │                             ║ DependentName (disc.) ║
│ Name                │                             ║ BirthDate             ║
│ Department          │                             ║ Relationship          ║
└─────────────────────┘                             ╚═══════════════════════╝
      ▲                                                       ▲
      │                                                       │
 Single border = Strong Entity                    Double border = Weak Entity
                                                  "disc." = Discriminator (partial key)
                                                  Shown with dashed underline

The double border around DEPENDENT, double border around SUPPORTS, and dashed underline on DependentName all signal the weak entity structure.

Summary: Partial Keys

We've thoroughly explored partial keys—the weak entity's contribution to its composite identity. Let's consolidate the essential concepts:

Key Takeaways

•Partial keys provide local uniqueness — They distinguish weak entity instances within the scope of one owner, but not globally.
•Combined with owner's key, they form the primary key — PK(WeakEntity) = PK(Owner) + PartialKey. This composite key is globally unique.
•Selection criteria matter — Choose partial keys that are natural, stable, minimal, non-null, and reasonably sized.
•Simple partial keys are common — A single attribute (like DependentName or RoomNumber) often suffices.
•Composite partial keys handle complex cases — When no single attribute is unique within scope, multiple attributes combine.
•Keys cascade in hierarchies — Each level of weak entity nesting adds to the key length. Manage complexity carefully.
•Notation distinguishes partial keys — Chen uses dashed underlines; other notations have their own conventions.

What's Next:

Page Complete

3 / 5