Database Management SystemsIdentifying Relationships

Identifying Relationships in ER Modeling

LevelIntermediate

Duration60 mins

TopicIdentifying Relationships

5 / 5

Examples

Identifying Relationships in Action: Comprehensive Case Studies

Theory becomes knowledge only through application. In this culminating page, we examine identifying relationships through the lens of complete, realistic case studies drawn from diverse domains. Each case study presents a business scenario, walks through the conceptual modeling decisions, shows the resulting ER diagram structure, provides complete SQL schema implementations, and analyzes the design trade-offs.

These examples are deliberately chosen to span different industries and complexity levels, demonstrating how the core concepts—owner entities, dependent entities, discriminators, and composite keys—manifest in practice. By the end of this page, you'll have internalized these patterns deeply enough to recognize and apply them in your own modeling work.

What You Will Learn

This page presents five comprehensive case studies covering: e-commerce order management, hospital patient records, property management, manufacturing bill of materials, and academic course systems. Each case study demonstrates owner-dependent relationships, discriminator selection, composite key design, and implementation considerations.

Case Study 1: E-Commerce Order Management

Business Scenario:

An e-commerce platform processes customer orders. Each order contains multiple line items representing products purchased. Each line item may have multiple fulfillment allocations when inventory is sourced from different warehouses. Additionally, line items may have multiple promotional discounts applied.

Entity Analysis:

E-Commerce Entity Classification
Entity	Classification	Owner	Discriminator	Rationale
Customer	Strong	—	—	Has unique customer_id globally
Order	Strong	—	—	Has unique order_id globally (though associated with Customer)
OrderLine	Dependent	Order	line_number	Line 1, 2, 3 repeat across orders
LineAllocation	Dependent	OrderLine	allocation_seq	Allocations numbered per line
LineDiscount	Dependent	OrderLine	discount_seq	Discounts numbered per line

Key Design Decisions:

Order is Strong, not Dependent on Customer: Although orders 'belong to' customers, each order has a globally unique order_id. The relationship Customer → Order is non-identifying (regular 1:N).
OrderLine is Dependent on Order: Line items are numbered within each order. 'Line 1' has no meaning without order context. This is an identifying relationship.
LineAllocation and LineDiscount are Dependent on OrderLine: These create a two-level dependency chain. Their keys include the full OrderLine key plus their own discriminators.

Composite Key Formation:

OrderLine PK:       (order_id, line_number)
LineAllocation PK:  (order_id, line_number, allocation_seq)
LineDiscount PK:    (order_id, line_number, discount_seq)

ecommerce_schema.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
-- E-Commerce Order Management Complete Schema
 
CREATE TABLE customer (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE orders (  -- named 'orders' to avoid SQL keyword
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'pending',
    shipping_address TEXT,
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);
 
-- DEPENDENT: OrderLine on Order
CREATE TABLE order_line (
    order_id INT NOT NULL,
    line_number INT NOT NULL,
    product_sku VARCHAR(20) NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10,2) NOT NULL,
    -- Composite Primary Key
    PRIMARY KEY (order_id, line_number),
    -- Identifying relationship FK
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE
);
 
-- DEPENDENT: LineAllocation on OrderLine (2-level chain)
CREATE TABLE line_allocation (
    order_id INT NOT NULL,
    line_number INT NOT NULL,
    allocation_seq INT NOT NULL,
    warehouse_id INT NOT NULL,
    allocated_quantity INT NOT NULL,
    allocation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- 3-component composite PK
    PRIMARY KEY (order_id, line_number, allocation_seq),
    -- FK to OrderLine (composite)
    FOREIGN KEY (order_id, line_number) 
        REFERENCES order_line(order_id, line_number) ON DELETE CASCADE
);
 
-- DEPENDENT: LineDiscount on OrderLine
CREATE TABLE line_discount (
    order_id INT NOT NULL,
    line_number INT NOT NULL,
    discount_seq INT NOT NULL,
    discount_code VARCHAR(20),
    discount_type VARCHAR(20) NOT NULL,  -- 'percentage', 'fixed'
    discount_value DECIMAL(10,2) NOT NULL,
    -- 3-component composite PK
    PRIMARY KEY (order_id, line_number, discount_seq),
    -- FK to OrderLine (composite)
    FOREIGN KEY (order_id, line_number)
        REFERENCES order_line(order_id, line_number) ON DELETE CASCADE
);
 
-- Example Queries
-- 1. Get all lines for an order
SELECT * FROM order_line WHERE order_id = 1001;
 
-- 2. Get all allocations across all lines of an order
SELECT la.* FROM line_allocation la
WHERE la.order_id = 1001;
 
-- 3. Calculate total discounts per line
SELECT order_id, line_number, SUM(discount_value) as total_discount
FROM line_discount
GROUP BY order_id, line_number;

Cascade Delete in Action

With ON DELETE CASCADE configured, deleting an Order cascades to all OrderLines, which cascades to all LineAllocations and LineDiscounts. The entire order subgraph is removed atomically, maintaining referential integrity.

Case Study 2: Hospital Patient Records

Business Scenario:

A hospital system tracks patients and their encounters (visits). Each encounter involves multiple procedures, and each procedure may generate multiple charges. Patients also have dependents (for insurance purposes) who must be tracked.

Entity Analysis:

Hospital Entity Classification
Entity	Classification	Owner	Discriminator	Rationale
Patient	Strong	—	—	MRN (Medical Record Number) is globally unique
InsuranceDependent	Dependent	Patient	dependent_name or dependent_seq	Dependents identified relative to patient
Encounter	Dependent	Patient	encounter_number	Encounters numbered per patient
Procedure	Dependent	Encounter	procedure_seq	Procedures sequenced within encounter
Charge	Dependent	Procedure	charge_seq	Multiple charges per procedure

Design Complexity:

This scenario creates a 4-level dependency chain:

Patient (MRN)
    └─→ Encounter (MRN, encounter_number)
            └─→ Procedure (MRN, encounter_number, procedure_seq)
                    └─→ Charge (MRN, encounter_number, procedure_seq, charge_seq)

The Charge entity has a 4-component composite key. This is at the edge of practical composite key depth.

Alternative Design Consideration:

In practice, many hospital systems use surrogate keys for Encounters and Procedures (globally unique IDs) while maintaining the logical dependency semantically. This simplifies the key structure but requires explicit relationship management.

hospital_schema.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
-- Hospital Patient Records Complete Schema
 
CREATE TABLE patient (
    mrn CHAR(10) PRIMARY KEY,  -- Medical Record Number
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    date_of_birth DATE NOT NULL,
    ssn_last4 CHAR(4),
    primary_phone VARCHAR(20)
);
 
-- DEPENDENT: InsuranceDependent on Patient
CREATE TABLE insurance_dependent (
    mrn CHAR(10) NOT NULL,
    dependent_seq INT NOT NULL,  -- 1=spouse, 2=child1, etc.
    dependent_name VARCHAR(100) NOT NULL,
    relationship VARCHAR(20) NOT NULL,  -- 'spouse', 'child', etc.
    date_of_birth DATE,
    PRIMARY KEY (mrn, dependent_seq),
    FOREIGN KEY (mrn) REFERENCES patient(mrn) ON DELETE CASCADE
);
 
-- DEPENDENT: Encounter on Patient
CREATE TABLE encounter (
    mrn CHAR(10) NOT NULL,
    encounter_number INT NOT NULL,
    encounter_date DATE NOT NULL,
    encounter_type VARCHAR(30) NOT NULL,  -- 'inpatient', 'outpatient', 'emergency'
    admitting_physician_id INT,
    discharge_date DATE,
    PRIMARY KEY (mrn, encounter_number),
    FOREIGN KEY (mrn) REFERENCES patient(mrn)
);
 
-- DEPENDENT: Procedure on Encounter (3-level)
CREATE TABLE procedure_record (
    mrn CHAR(10) NOT NULL,
    encounter_number INT NOT NULL,
    procedure_seq INT NOT NULL,
    cpt_code VARCHAR(10) NOT NULL,  -- Standard procedure code
    procedure_name VARCHAR(200) NOT NULL,
    performing_physician_id INT,
    procedure_datetime TIMESTAMP NOT NULL,
    notes TEXT,
    PRIMARY KEY (mrn, encounter_number, procedure_seq),
    FOREIGN KEY (mrn, encounter_number) 
        REFERENCES encounter(mrn, encounter_number)
);
 
-- DEPENDENT: Charge on Procedure (4-level)
CREATE TABLE charge (
    mrn CHAR(10) NOT NULL,
    encounter_number INT NOT NULL,
    procedure_seq INT NOT NULL,
    charge_seq INT NOT NULL,
    charge_code VARCHAR(20) NOT NULL,
    description VARCHAR(200),
    amount DECIMAL(10,2) NOT NULL,
    charge_date DATE NOT NULL,
    billed_to VARCHAR(50),  -- 'patient', 'insurance', etc.
    PRIMARY KEY (mrn, encounter_number, procedure_seq, charge_seq),
    FOREIGN KEY (mrn, encounter_number, procedure_seq)
        REFERENCES procedure_record(mrn, encounter_number, procedure_seq)
);
 
-- Query: Patient's complete billing history
SELECT 
    p.mrn, p.last_name, p.first_name,
    e.encounter_number, e.encounter_date,
    pr.procedure_seq, pr.cpt_code, pr.procedure_name,
    c.charge_seq, c.charge_code, c.amount
FROM patient p
JOIN encounter e ON p.mrn = e.mrn
JOIN procedure_record pr ON e.mrn = pr.mrn AND e.encounter_number = pr.encounter_number
JOIN charge c ON pr.mrn = c.mrn AND pr.encounter_number = c.encounter_number 
    AND pr.procedure_seq = c.procedure_seq
WHERE p.mrn = 'PAT0001234'
ORDER BY e.encounter_number, pr.procedure_seq, c.charge_seq;

4-Level Key Complexity

The 4-component key on Charge (mrn, encounter_number, procedure_seq, charge_seq) creates wide indexes and complex joins. In high-volume systems, consider surrogate keys for Encounter and/or Procedure with UNIQUE constraints on the natural composite keys. This preserves semantic integrity while simplifying physical implementation.

Case Study 3: Property Management

Business Scenario:

A property management company manages multiple buildings. Each building contains floors, and each floor contains units (apartments/offices). Each unit may have multiple lease agreements over time, and each lease has multiple scheduled payments.

Entity Analysis:

Property Management Entity Classification
Entity	Classification	Owner	Discriminator	Rationale
Building	Strong	—	—	Building has unique ID/code
Floor	Dependent	Building	floor_number	Floor numbers repeat across buildings
Unit	Dependent	Floor	unit_number	Unit numbers may be per-floor (e.g., '1A', '1B')
Lease	Dependent	Unit	lease_sequence	Leases numbered per unit over time
PaymentSchedule	Dependent	Lease	payment_number	Monthly payments numbered within lease

Hierarchical Identity Path:

Building (building_id)
    └─→ Floor (building_id, floor_number)
            └─→ Unit (building_id, floor_number, unit_number)
                    └─→ Lease (building_id, floor_number, unit_number, lease_seq)
                            └─→ Payment (building_id, floor_number, unit_number, lease_seq, payment_num)

5-Level Hierarchy Challenge:

This creates extremely wide keys at the leaf level. Payment has a 5-component key. This is a strong signal to reconsider the design:

Alternative Design:

Keep Building, Floor, Unit as dependent chain (natural identification)
Give Lease a surrogate PK with UNIQUE(unit FK, lease_seq)
Give Payment a surrogate PK with UNIQUE(lease FK, payment_number)

property_schema.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
-- Property Management Schema: Hybrid Approach
-- Uses natural composite keys for physical hierarchy (Building→Floor→Unit)
-- Uses surrogate keys for transactional entities (Lease, Payment)
 
CREATE TABLE building (
    building_id CHAR(8) PRIMARY KEY,  -- 'BLDG-001'
    name VARCHAR(100) NOT NULL,
    address VARCHAR(200) NOT NULL,
    total_floors INT NOT NULL
);
 
-- DEPENDENT on Building
CREATE TABLE floor (
    building_id CHAR(8) NOT NULL,
    floor_number INT NOT NULL,
    floor_name VARCHAR(50),  -- 'Ground Floor', 'Penthouse'
    total_units INT NOT NULL,
    PRIMARY KEY (building_id, floor_number),
    FOREIGN KEY (building_id) REFERENCES building(building_id)
);
 
-- DEPENDENT on Floor
CREATE TABLE unit (
    building_id CHAR(8) NOT NULL,
    floor_number INT NOT NULL,
    unit_number VARCHAR(10) NOT NULL,  -- 'A', 'B', '101', '102'
    unit_type VARCHAR(30) NOT NULL,  -- '1BR', '2BR', 'studio'
    square_feet INT,
    monthly_rate DECIMAL(10,2),
    PRIMARY KEY (building_id, floor_number, unit_number),
    FOREIGN KEY (building_id, floor_number) 
        REFERENCES floor(building_id, floor_number)
);
 
-- HYBRID: Surrogate PK with natural UNIQUE constraint
CREATE TABLE lease (
    lease_id INT PRIMARY KEY AUTO_INCREMENT,  -- Surrogate
    building_id CHAR(8) NOT NULL,
    floor_number INT NOT NULL,
    unit_number VARCHAR(10) NOT NULL,
    lease_sequence INT NOT NULL,  -- 1, 2, 3... per unit
    tenant_id INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    monthly_rent DECIMAL(10,2) NOT NULL,
    -- Natural uniqueness preserved
    UNIQUE (building_id, floor_number, unit_number, lease_sequence),
    FOREIGN KEY (building_id, floor_number, unit_number)
        REFERENCES unit(building_id, floor_number, unit_number)
);
 
-- HYBRID: Surrogate PK with natural UNIQUE constraint
CREATE TABLE payment_schedule (
    payment_id INT PRIMARY KEY AUTO_INCREMENT,  -- Surrogate
    lease_id INT NOT NULL,  -- Simple FK!
    payment_number INT NOT NULL,  -- 1 = first month, 2 = second, etc.
    due_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    paid_date DATE,
    paid_amount DECIMAL(10,2),
    -- Natural uniqueness preserved
    UNIQUE (lease_id, payment_number),
    FOREIGN KEY (lease_id) REFERENCES lease(lease_id) ON DELETE CASCADE
);
 
-- Query: Simple with surrogate keys
SELECT l.*, ps.payment_number, ps.due_date, ps.amount, ps.paid_date
FROM lease l
JOIN payment_schedule ps ON l.lease_id = ps.lease_id
WHERE l.building_id = 'BLDG-001' AND l.floor_number = 5 AND l.unit_number = 'A'
ORDER BY l.lease_sequence, ps.payment_number;

Hybrid Design Rationale

The physical hierarchy (Building→Floor→Unit) benefits from composite keys—it matches how people and systems reference locations. But transactional entities (Lease, Payment) benefit from surrogate keys because they're frequently referenced from other tables (tenant records, accounting entries, etc.). The hybrid approach captures both benefits.

Case Study 4: Manufacturing Bill of Materials

Business Scenario:

A manufacturing company maintains Bills of Materials (BOM) for its products. Each product has a BOM that lists component items and quantities. Some components are themselves assemblies with their own BOMs, creating a recursive structure. Additionally, each BOM line may have multiple approved suppliers.

Entity Analysis:

Manufacturing BOM Entity Classification
Entity	Classification	Owner	Discriminator	Notes
Product	Strong	—	—	SKU is globally unique
BillOfMaterials	Dependent	Product	version_number	Multiple BOM versions per product
BOMLine	Dependent	BillOfMaterials	line_sequence	Lines numbered within BOM version
ApprovedSupplier	Dependent	BOMLine	supplier_sequence	Multiple suppliers per line

Recursive Complexity:

The BOMLine references a component Product, which may itself have a BillOfMaterials. This creates a recursive structure that's not about identifying relationships but about assembly composition.

Product A (finished good)
    └─→ BOM v1
            └─→ BOMLine 1: Component B × 2
            └─→ BOMLine 2: Component C × 1
                                └─→ (Component C has its own BOM)
                                        └─→ BOMLine 1: Part D × 4
                                        └─→ BOMLine 2: Part E × 2

Key Structure:

BillOfMaterials PK: (product_sku, version_number)
BOMLine PK: (product_sku, version_number, line_sequence)
ApprovedSupplier PK: (product_sku, version_number, line_sequence, supplier_seq)

manufacturing_bom_schema.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
-- Manufacturing Bill of Materials Schema
 
CREATE TABLE product (
    product_sku VARCHAR(20) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    product_type VARCHAR(20) NOT NULL,  -- 'finished', 'subassembly', 'component', 'raw'
    unit_of_measure VARCHAR(10) NOT NULL  -- 'ea', 'kg', 'm', etc.
);
 
-- DEPENDENT on Product: BOM versions per product
CREATE TABLE bill_of_materials (
    product_sku VARCHAR(20) NOT NULL,
    version_number INT NOT NULL,
    version_status VARCHAR(20) DEFAULT 'draft',  -- 'draft', 'active', 'obsolete'
    effective_date DATE,
    created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    notes TEXT,
    PRIMARY KEY (product_sku, version_number),
    FOREIGN KEY (product_sku) REFERENCES product(product_sku)
);
 
-- DEPENDENT on BOM: Components list
CREATE TABLE bom_line (
    product_sku VARCHAR(20) NOT NULL,     -- Parent product
    version_number INT NOT NULL,
    line_sequence INT NOT NULL,
    component_sku VARCHAR(20) NOT NULL,   -- Component product (FK)
    quantity DECIMAL(10,4) NOT NULL,
    unit_of_measure VARCHAR(10) NOT NULL,
    optional_flag BOOLEAN DEFAULT FALSE,
    notes TEXT,
    PRIMARY KEY (product_sku, version_number, line_sequence),
    FOREIGN KEY (product_sku, version_number) 
        REFERENCES bill_of_materials(product_sku, version_number),
    -- Reference to component (non-identifying)
    FOREIGN KEY (component_sku) REFERENCES product(product_sku)
);
 
-- DEPENDENT on BOMLine: Approved suppliers per line
CREATE TABLE bom_approved_supplier (
    product_sku VARCHAR(20) NOT NULL,
    version_number INT NOT NULL,
    line_sequence INT NOT NULL,
    supplier_sequence INT NOT NULL,
    supplier_id INT NOT NULL,
    unit_cost DECIMAL(10,4),
    lead_time_days INT,
    preference_rank INT,  -- 1 = preferred
    PRIMARY KEY (product_sku, version_number, line_sequence, supplier_sequence),
    FOREIGN KEY (product_sku, version_number, line_sequence)
        REFERENCES bom_line(product_sku, version_number, line_sequence) ON DELETE CASCADE
);
 
-- Query: Explode BOM to all components (single level)
SELECT 
    bl.line_sequence,
    p.name as component_name,
    bl.quantity,
    bl.unit_of_measure
FROM bom_line bl
JOIN product p ON bl.component_sku = p.product_sku
WHERE bl.product_sku = 'PROD-WIDGET-001' 
  AND bl.version_number = 1
ORDER BY bl.line_sequence;
 
-- Query: Find all products using a specific component
SELECT DISTINCT bl.product_sku, bl.version_number
FROM bom_line bl
WHERE bl.component_sku = 'PART-SCREW-M5'
AND EXISTS (
    SELECT 1 FROM bill_of_materials bom
    WHERE bom.product_sku = bl.product_sku
      AND bom.version_number = bl.version_number
      AND bom.version_status = 'active'
);

Recursive BOM Traversal

Full BOM 'explosion' (traversing all levels of subassemblies) typically requires recursive CTEs in SQL or application-level recursion. The identifying relationship structure enables efficient single-level queries, but multi-level traversal needs additional techniques.

Case Study 5: Academic Course System

Business Scenario:

A university system manages courses, sections, enrollments, and grades. Courses belong to departments. Sections are offerings of courses in specific semesters. Students enroll in sections and receive grades. This classic academic scenario demonstrates both identifying relationships and associative entities.

Entity Analysis:

Academic System Entity Classification
Entity	Classification	Owner(s)	Discriminator	Key
Department	Strong	—	—	dept_code
Course	Dependent	Department	course_number	(dept_code, course_number)
Semester	Strong	—	—	semester_id
Section	Multi-Dependent	Course, Semester	section_number	(dept_code, course_number, semester_id, section_number)
Student	Strong	—	—	student_id
Enrollment	Associative	Section, Student	—	(section PK, student_id)

Multi-Dependency Analysis:

This scenario shows interesting patterns:

Course depends on Department: Classic identifying relationship.
Section depends on BOTH Course AND Semester: This is a multi-owner dependent. Its identity comes from which course it offers AND which semester it runs in.
Enrollment is an Associative Entity: It connects Section and Student. It's 'owned' by both in the sense that it exists only in the context of both.

Key Design:

Course PK: (dept_code, course_number)
Section PK: (dept_code, course_number, semester_id, section_number)
Enrollment PK: (dept_code, course_number, semester_id, section_number, student_id)

The Enrollment key has 5 components, which signals potential for surrogate key optimization.

academic_schema.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
-- Academic Course System Complete Schema
 
CREATE TABLE department (
    dept_code CHAR(4) PRIMARY KEY,  -- 'CSCI', 'MATH'
    name VARCHAR(100) NOT NULL,
    building VARCHAR(50),
    phone VARCHAR(20)
);
 
-- DEPENDENT on Department
CREATE TABLE course (
    dept_code CHAR(4) NOT NULL,
    course_number CHAR(4) NOT NULL,  -- '1010', '3500'
    title VARCHAR(100) NOT NULL,
    credits INT NOT NULL CHECK (credits BETWEEN 1 AND 6),
    description TEXT,
    PRIMARY KEY (dept_code, course_number),
    FOREIGN KEY (dept_code) REFERENCES department(dept_code)
);
 
CREATE TABLE semester (
    semester_id CHAR(6) PRIMARY KEY,  -- '2024FA', '2025SP'
    name VARCHAR(30) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL
);
 
-- MULTI-DEPENDENT on Course AND Semester
CREATE TABLE section (
    dept_code CHAR(4) NOT NULL,
    course_number CHAR(4) NOT NULL,
    semester_id CHAR(6) NOT NULL,
    section_number CHAR(3) NOT NULL,  -- '001', '002'
    instructor_id INT,
    room VARCHAR(30),
    schedule VARCHAR(100),  -- 'MWF 10:00-10:50'
    capacity INT DEFAULT 30,
    PRIMARY KEY (dept_code, course_number, semester_id, section_number),
    FOREIGN KEY (dept_code, course_number) REFERENCES course(dept_code, course_number),
    FOREIGN KEY (semester_id) REFERENCES semester(semester_id)
);
 
CREATE TABLE student (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    admission_year INT NOT NULL,
    major_dept_code CHAR(4),
    FOREIGN KEY (major_dept_code) REFERENCES department(dept_code)
);
 
-- ASSOCIATIVE: Connects Section and Student
CREATE TABLE enrollment (
    dept_code CHAR(4) NOT NULL,
    course_number CHAR(4) NOT NULL,
    semester_id CHAR(6) NOT NULL,
    section_number CHAR(3) NOT NULL,
    student_id INT NOT NULL,
    enrollment_date DATE DEFAULT (CURRENT_DATE),
    grade CHAR(2),  -- 'A', 'B+', etc.
    status VARCHAR(20) DEFAULT 'enrolled',  -- 'enrolled', 'dropped', 'completed'
    PRIMARY KEY (dept_code, course_number, semester_id, section_number, student_id),
    FOREIGN KEY (dept_code, course_number, semester_id, section_number)
        REFERENCES section(dept_code, course_number, semester_id, section_number),
    FOREIGN KEY (student_id) REFERENCES student(student_id)
);
 
-- Query: Student's transcript
SELECT 
    e.semester_id,
    e.dept_code || ' ' || e.course_number as course,
    c.title,
    c.credits,
    e.grade
FROM enrollment e
JOIN course c ON e.dept_code = c.dept_code AND e.course_number = c.course_number
WHERE e.student_id = 12345
  AND e.grade IS NOT NULL
ORDER BY e.semester_id, e.dept_code, e.course_number;
 
-- Query: Section roster
SELECT s.student_id, s.first_name, s.last_name, e.grade
FROM enrollment e
JOIN student s ON e.student_id = s.student_id
WHERE e.dept_code = 'CSCI' 
  AND e.course_number = '3500'
  AND e.semester_id = '2024FA'
  AND e.section_number = '001'
ORDER BY s.last_name, s.first_name;

Pattern Recognition: When to Use Identifying Relationships

Across these case studies, consistent patterns emerge. Use this quick reference to recognize identifying relationship opportunities in your own modeling work.

Indicators for Identifying Relationships

•Sequential Numbering Within Parent — 'Line 1, 2, 3' of an order; 'Visit 1, 2, 3' of a patient. The sequence resets for each parent.
•Physical Containment — Rooms in buildings, seats in theaters, bins in warehouses. Physical location inherently requires parent context.
•Document Line Items — Order lines, invoice items, receipt entries. The document owns the lines completely.
•Version History — Document versions, BOM versions, software releases. Versions are numbered per parent entity.
•Scheduled Payments — Loan payments, lease payments, subscription billing. Payments are sequenced per agreement.
•Hierarchical Classification — University → Department → Course → Section. Each level scopes the next.
•User-Stated Composite Identity — When users say 'Room 101 in Building A' or 'Question 5 on Exam 3', they're indicating composite identity.

The Acid Test

Ask: 'If I told you just the discriminator value (e.g., line_number = 3), would you know which entity instance I mean?' If the answer is 'No, you'd have to tell me which order (or building, or patient, etc.),' then you have an identifying relationship.

Summary: Identifying Relationships Mastery

Through five comprehensive case studies, we've seen identifying relationships in action across diverse domains. Let's consolidate the key insights from this module:

Module Key Takeaways

•Owner entities provide identity context — They have self-sufficient keys and bestow identity components to their dependents through identifying relationships.
•Dependent entities require owner context for uniqueness — They exhibit both existence dependency and identification dependency, with discriminators providing within-owner uniqueness.
•Discriminators distinguish instances within an owner's scope — Sequential integers, meaningful codes, timestamps, and FK references are common discriminator patterns.
•Composite keys combine owner key + discriminator — This composition propagates through hierarchies, with key width growing at each level.
•Hybrid designs balance purity with practicality — Surrogate keys with natural UNIQUE constraints offer simpler FKs while preserving domain semantics.
•Pattern recognition accelerates modeling — Line items, physical containment, versioning, and hierarchical classification are reliable identifying relationship indicators.

Module Complete:

You've now mastered identifying relationships—the fundamental mechanism for modeling entities that cannot stand alone, from the owner entity's role as identity provider through discriminator selection, composite key formation, and practical implementation across diverse domains.

This knowledge enables you to accurately model real-world scenarios where entities exist in hierarchical, ownership, or containment relationships, and to translate those models into efficient, semantically correct database schemas.

Module Complete

Congratulations! You've completed the Identifying Relationships module. You now possess comprehensive knowledge of owner entities, dependent entities, discriminators, composite key formation, and their practical application. This foundational understanding prepares you for advanced ER modeling concepts including ternary relationships, self-referential relationships, and aggregation.

5 / 5

Loading learning content...

Database Management SystemsIdentifying Relationships

Identifying Relationships in ER Modeling

LevelIntermediate

Duration60 mins

TopicIdentifying Relationships

5 / 5

Examples

Identifying Relationships in Action: Comprehensive Case Studies

What You Will Learn

Case Study 1: E-Commerce Order Management

Business Scenario:

Entity Analysis:

E-Commerce Entity Classification
Entity	Classification	Owner	Discriminator	Rationale
Customer	Strong	—	—	Has unique customer_id globally
Order	Strong	—	—	Has unique order_id globally (though associated with Customer)
OrderLine	Dependent	Order	line_number	Line 1, 2, 3 repeat across orders
LineAllocation	Dependent	OrderLine	allocation_seq	Allocations numbered per line
LineDiscount	Dependent	OrderLine	discount_seq	Discounts numbered per line

Key Design Decisions:

Order is Strong, not Dependent on Customer: Although orders 'belong to' customers, each order has a globally unique order_id. The relationship Customer → Order is non-identifying (regular 1:N).
OrderLine is Dependent on Order: Line items are numbered within each order. 'Line 1' has no meaning without order context. This is an identifying relationship.
LineAllocation and LineDiscount are Dependent on OrderLine: These create a two-level dependency chain. Their keys include the full OrderLine key plus their own discriminators.

Composite Key Formation:

OrderLine PK:       (order_id, line_number)
LineAllocation PK:  (order_id, line_number, allocation_seq)
LineDiscount PK:    (order_id, line_number, discount_seq)

ecommerce_schema.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
-- E-Commerce Order Management Complete Schema
 
CREATE TABLE customer (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE orders (  -- named 'orders' to avoid SQL keyword
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'pending',
    shipping_address TEXT,
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);
 
-- DEPENDENT: OrderLine on Order
CREATE TABLE order_line (
    order_id INT NOT NULL,
    line_number INT NOT NULL,
    product_sku VARCHAR(20) NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10,2) NOT NULL,
    -- Composite Primary Key
    PRIMARY KEY (order_id, line_number),
    -- Identifying relationship FK
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE
);
 
-- DEPENDENT: LineAllocation on OrderLine (2-level chain)
CREATE TABLE line_allocation (
    order_id INT NOT NULL,
    line_number INT NOT NULL,
    allocation_seq INT NOT NULL,
    warehouse_id INT NOT NULL,
    allocated_quantity INT NOT NULL,
    allocation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- 3-component composite PK
    PRIMARY KEY (order_id, line_number, allocation_seq),
    -- FK to OrderLine (composite)
    FOREIGN KEY (order_id, line_number) 
        REFERENCES order_line(order_id, line_number) ON DELETE CASCADE
);
 
-- DEPENDENT: LineDiscount on OrderLine
CREATE TABLE line_discount (
    order_id INT NOT NULL,
    line_number INT NOT NULL,
    discount_seq INT NOT NULL,
    discount_code VARCHAR(20),
    discount_type VARCHAR(20) NOT NULL,  -- 'percentage', 'fixed'
    discount_value DECIMAL(10,2) NOT NULL,
    -- 3-component composite PK
    PRIMARY KEY (order_id, line_number, discount_seq),
    -- FK to OrderLine (composite)
    FOREIGN KEY (order_id, line_number)
        REFERENCES order_line(order_id, line_number) ON DELETE CASCADE
);
 
-- Example Queries
-- 1. Get all lines for an order
SELECT * FROM order_line WHERE order_id = 1001;
 
-- 2. Get all allocations across all lines of an order
SELECT la.* FROM line_allocation la
WHERE la.order_id = 1001;
 
-- 3. Calculate total discounts per line
SELECT order_id, line_number, SUM(discount_value) as total_discount
FROM line_discount
GROUP BY order_id, line_number;

Cascade Delete in Action

Case Study 2: Hospital Patient Records

Business Scenario:

Entity Analysis:

Hospital Entity Classification
Entity	Classification	Owner	Discriminator	Rationale
Patient	Strong	—	—	MRN (Medical Record Number) is globally unique
InsuranceDependent	Dependent	Patient	dependent_name or dependent_seq	Dependents identified relative to patient
Encounter	Dependent	Patient	encounter_number	Encounters numbered per patient
Procedure	Dependent	Encounter	procedure_seq	Procedures sequenced within encounter
Charge	Dependent	Procedure	charge_seq	Multiple charges per procedure

Design Complexity:

This scenario creates a 4-level dependency chain:

Patient (MRN)
    └─→ Encounter (MRN, encounter_number)
            └─→ Procedure (MRN, encounter_number, procedure_seq)
                    └─→ Charge (MRN, encounter_number, procedure_seq, charge_seq)

The Charge entity has a 4-component composite key. This is at the edge of practical composite key depth.

Alternative Design Consideration:

hospital_schema.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
-- Hospital Patient Records Complete Schema
 
CREATE TABLE patient (
    mrn CHAR(10) PRIMARY KEY,  -- Medical Record Number
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    date_of_birth DATE NOT NULL,
    ssn_last4 CHAR(4),
    primary_phone VARCHAR(20)
);
 
-- DEPENDENT: InsuranceDependent on Patient
CREATE TABLE insurance_dependent (
    mrn CHAR(10) NOT NULL,
    dependent_seq INT NOT NULL,  -- 1=spouse, 2=child1, etc.
    dependent_name VARCHAR(100) NOT NULL,
    relationship VARCHAR(20) NOT NULL,  -- 'spouse', 'child', etc.
    date_of_birth DATE,
    PRIMARY KEY (mrn, dependent_seq),
    FOREIGN KEY (mrn) REFERENCES patient(mrn) ON DELETE CASCADE
);
 
-- DEPENDENT: Encounter on Patient
CREATE TABLE encounter (
    mrn CHAR(10) NOT NULL,
    encounter_number INT NOT NULL,
    encounter_date DATE NOT NULL,
    encounter_type VARCHAR(30) NOT NULL,  -- 'inpatient', 'outpatient', 'emergency'
    admitting_physician_id INT,
    discharge_date DATE,
    PRIMARY KEY (mrn, encounter_number),
    FOREIGN KEY (mrn) REFERENCES patient(mrn)
);
 
-- DEPENDENT: Procedure on Encounter (3-level)
CREATE TABLE procedure_record (
    mrn CHAR(10) NOT NULL,
    encounter_number INT NOT NULL,
    procedure_seq INT NOT NULL,
    cpt_code VARCHAR(10) NOT NULL,  -- Standard procedure code
    procedure_name VARCHAR(200) NOT NULL,
    performing_physician_id INT,
    procedure_datetime TIMESTAMP NOT NULL,
    notes TEXT,
    PRIMARY KEY (mrn, encounter_number, procedure_seq),
    FOREIGN KEY (mrn, encounter_number) 
        REFERENCES encounter(mrn, encounter_number)
);
 
-- DEPENDENT: Charge on Procedure (4-level)
CREATE TABLE charge (
    mrn CHAR(10) NOT NULL,
    encounter_number INT NOT NULL,
    procedure_seq INT NOT NULL,
    charge_seq INT NOT NULL,
    charge_code VARCHAR(20) NOT NULL,
    description VARCHAR(200),
    amount DECIMAL(10,2) NOT NULL,
    charge_date DATE NOT NULL,
    billed_to VARCHAR(50),  -- 'patient', 'insurance', etc.
    PRIMARY KEY (mrn, encounter_number, procedure_seq, charge_seq),
    FOREIGN KEY (mrn, encounter_number, procedure_seq)
        REFERENCES procedure_record(mrn, encounter_number, procedure_seq)
);
 
-- Query: Patient's complete billing history
SELECT 
    p.mrn, p.last_name, p.first_name,
    e.encounter_number, e.encounter_date,
    pr.procedure_seq, pr.cpt_code, pr.procedure_name,
    c.charge_seq, c.charge_code, c.amount
FROM patient p
JOIN encounter e ON p.mrn = e.mrn
JOIN procedure_record pr ON e.mrn = pr.mrn AND e.encounter_number = pr.encounter_number
JOIN charge c ON pr.mrn = c.mrn AND pr.encounter_number = c.encounter_number 
    AND pr.procedure_seq = c.procedure_seq
WHERE p.mrn = 'PAT0001234'
ORDER BY e.encounter_number, pr.procedure_seq, c.charge_seq;

4-Level Key Complexity

Case Study 3: Property Management

Business Scenario:

Entity Analysis:

Property Management Entity Classification
Entity	Classification	Owner	Discriminator	Rationale
Building	Strong	—	—	Building has unique ID/code
Floor	Dependent	Building	floor_number	Floor numbers repeat across buildings
Unit	Dependent	Floor	unit_number	Unit numbers may be per-floor (e.g., '1A', '1B')
Lease	Dependent	Unit	lease_sequence	Leases numbered per unit over time
PaymentSchedule	Dependent	Lease	payment_number	Monthly payments numbered within lease

Hierarchical Identity Path:

Building (building_id)
    └─→ Floor (building_id, floor_number)
            └─→ Unit (building_id, floor_number, unit_number)
                    └─→ Lease (building_id, floor_number, unit_number, lease_seq)
                            └─→ Payment (building_id, floor_number, unit_number, lease_seq, payment_num)

5-Level Hierarchy Challenge:

This creates extremely wide keys at the leaf level. Payment has a 5-component key. This is a strong signal to reconsider the design:

Alternative Design:

Keep Building, Floor, Unit as dependent chain (natural identification)
Give Lease a surrogate PK with UNIQUE(unit FK, lease_seq)
Give Payment a surrogate PK with UNIQUE(lease FK, payment_number)

property_schema.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
-- Property Management Schema: Hybrid Approach
-- Uses natural composite keys for physical hierarchy (Building→Floor→Unit)
-- Uses surrogate keys for transactional entities (Lease, Payment)
 
CREATE TABLE building (
    building_id CHAR(8) PRIMARY KEY,  -- 'BLDG-001'
    name VARCHAR(100) NOT NULL,
    address VARCHAR(200) NOT NULL,
    total_floors INT NOT NULL
);
 
-- DEPENDENT on Building
CREATE TABLE floor (
    building_id CHAR(8) NOT NULL,
    floor_number INT NOT NULL,
    floor_name VARCHAR(50),  -- 'Ground Floor', 'Penthouse'
    total_units INT NOT NULL,
    PRIMARY KEY (building_id, floor_number),
    FOREIGN KEY (building_id) REFERENCES building(building_id)
);
 
-- DEPENDENT on Floor
CREATE TABLE unit (
    building_id CHAR(8) NOT NULL,
    floor_number INT NOT NULL,
    unit_number VARCHAR(10) NOT NULL,  -- 'A', 'B', '101', '102'
    unit_type VARCHAR(30) NOT NULL,  -- '1BR', '2BR', 'studio'
    square_feet INT,
    monthly_rate DECIMAL(10,2),
    PRIMARY KEY (building_id, floor_number, unit_number),
    FOREIGN KEY (building_id, floor_number) 
        REFERENCES floor(building_id, floor_number)
);
 
-- HYBRID: Surrogate PK with natural UNIQUE constraint
CREATE TABLE lease (
    lease_id INT PRIMARY KEY AUTO_INCREMENT,  -- Surrogate
    building_id CHAR(8) NOT NULL,
    floor_number INT NOT NULL,
    unit_number VARCHAR(10) NOT NULL,
    lease_sequence INT NOT NULL,  -- 1, 2, 3... per unit
    tenant_id INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    monthly_rent DECIMAL(10,2) NOT NULL,
    -- Natural uniqueness preserved
    UNIQUE (building_id, floor_number, unit_number, lease_sequence),
    FOREIGN KEY (building_id, floor_number, unit_number)
        REFERENCES unit(building_id, floor_number, unit_number)
);
 
-- HYBRID: Surrogate PK with natural UNIQUE constraint
CREATE TABLE payment_schedule (
    payment_id INT PRIMARY KEY AUTO_INCREMENT,  -- Surrogate
    lease_id INT NOT NULL,  -- Simple FK!
    payment_number INT NOT NULL,  -- 1 = first month, 2 = second, etc.
    due_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    paid_date DATE,
    paid_amount DECIMAL(10,2),
    -- Natural uniqueness preserved
    UNIQUE (lease_id, payment_number),
    FOREIGN KEY (lease_id) REFERENCES lease(lease_id) ON DELETE CASCADE
);
 
-- Query: Simple with surrogate keys
SELECT l.*, ps.payment_number, ps.due_date, ps.amount, ps.paid_date
FROM lease l
JOIN payment_schedule ps ON l.lease_id = ps.lease_id
WHERE l.building_id = 'BLDG-001' AND l.floor_number = 5 AND l.unit_number = 'A'
ORDER BY l.lease_sequence, ps.payment_number;

Hybrid Design Rationale

Case Study 4: Manufacturing Bill of Materials

Business Scenario:

Entity Analysis:

Manufacturing BOM Entity Classification
Entity	Classification	Owner	Discriminator	Notes
Product	Strong	—	—	SKU is globally unique
BillOfMaterials	Dependent	Product	version_number	Multiple BOM versions per product
BOMLine	Dependent	BillOfMaterials	line_sequence	Lines numbered within BOM version
ApprovedSupplier	Dependent	BOMLine	supplier_sequence	Multiple suppliers per line

Recursive Complexity:

The BOMLine references a component Product, which may itself have a BillOfMaterials. This creates a recursive structure that's not about identifying relationships but about assembly composition.

Product A (finished good)
    └─→ BOM v1
            └─→ BOMLine 1: Component B × 2
            └─→ BOMLine 2: Component C × 1
                                └─→ (Component C has its own BOM)
                                        └─→ BOMLine 1: Part D × 4
                                        └─→ BOMLine 2: Part E × 2

Key Structure:

BillOfMaterials PK: (product_sku, version_number)
BOMLine PK: (product_sku, version_number, line_sequence)
ApprovedSupplier PK: (product_sku, version_number, line_sequence, supplier_seq)

manufacturing_bom_schema.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
-- Manufacturing Bill of Materials Schema
 
CREATE TABLE product (
    product_sku VARCHAR(20) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    product_type VARCHAR(20) NOT NULL,  -- 'finished', 'subassembly', 'component', 'raw'
    unit_of_measure VARCHAR(10) NOT NULL  -- 'ea', 'kg', 'm', etc.
);
 
-- DEPENDENT on Product: BOM versions per product
CREATE TABLE bill_of_materials (
    product_sku VARCHAR(20) NOT NULL,
    version_number INT NOT NULL,
    version_status VARCHAR(20) DEFAULT 'draft',  -- 'draft', 'active', 'obsolete'
    effective_date DATE,
    created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    notes TEXT,
    PRIMARY KEY (product_sku, version_number),
    FOREIGN KEY (product_sku) REFERENCES product(product_sku)
);
 
-- DEPENDENT on BOM: Components list
CREATE TABLE bom_line (
    product_sku VARCHAR(20) NOT NULL,     -- Parent product
    version_number INT NOT NULL,
    line_sequence INT NOT NULL,
    component_sku VARCHAR(20) NOT NULL,   -- Component product (FK)
    quantity DECIMAL(10,4) NOT NULL,
    unit_of_measure VARCHAR(10) NOT NULL,
    optional_flag BOOLEAN DEFAULT FALSE,
    notes TEXT,
    PRIMARY KEY (product_sku, version_number, line_sequence),
    FOREIGN KEY (product_sku, version_number) 
        REFERENCES bill_of_materials(product_sku, version_number),
    -- Reference to component (non-identifying)
    FOREIGN KEY (component_sku) REFERENCES product(product_sku)
);
 
-- DEPENDENT on BOMLine: Approved suppliers per line
CREATE TABLE bom_approved_supplier (
    product_sku VARCHAR(20) NOT NULL,
    version_number INT NOT NULL,
    line_sequence INT NOT NULL,
    supplier_sequence INT NOT NULL,
    supplier_id INT NOT NULL,
    unit_cost DECIMAL(10,4),
    lead_time_days INT,
    preference_rank INT,  -- 1 = preferred
    PRIMARY KEY (product_sku, version_number, line_sequence, supplier_sequence),
    FOREIGN KEY (product_sku, version_number, line_sequence)
        REFERENCES bom_line(product_sku, version_number, line_sequence) ON DELETE CASCADE
);
 
-- Query: Explode BOM to all components (single level)
SELECT 
    bl.line_sequence,
    p.name as component_name,
    bl.quantity,
    bl.unit_of_measure
FROM bom_line bl
JOIN product p ON bl.component_sku = p.product_sku
WHERE bl.product_sku = 'PROD-WIDGET-001' 
  AND bl.version_number = 1
ORDER BY bl.line_sequence;
 
-- Query: Find all products using a specific component
SELECT DISTINCT bl.product_sku, bl.version_number
FROM bom_line bl
WHERE bl.component_sku = 'PART-SCREW-M5'
AND EXISTS (
    SELECT 1 FROM bill_of_materials bom
    WHERE bom.product_sku = bl.product_sku
      AND bom.version_number = bl.version_number
      AND bom.version_status = 'active'
);

Recursive BOM Traversal

Case Study 5: Academic Course System

Business Scenario:

Entity Analysis:

Academic System Entity Classification
Entity	Classification	Owner(s)	Discriminator	Key
Department	Strong	—	—	dept_code
Course	Dependent	Department	course_number	(dept_code, course_number)
Semester	Strong	—	—	semester_id
Section	Multi-Dependent	Course, Semester	section_number	(dept_code, course_number, semester_id, section_number)
Student	Strong	—	—	student_id
Enrollment	Associative	Section, Student	—	(section PK, student_id)

Multi-Dependency Analysis:

This scenario shows interesting patterns:

Course depends on Department: Classic identifying relationship.
Section depends on BOTH Course AND Semester: This is a multi-owner dependent. Its identity comes from which course it offers AND which semester it runs in.
Enrollment is an Associative Entity: It connects Section and Student. It's 'owned' by both in the sense that it exists only in the context of both.

Key Design:

Course PK: (dept_code, course_number)
Section PK: (dept_code, course_number, semester_id, section_number)
Enrollment PK: (dept_code, course_number, semester_id, section_number, student_id)

The Enrollment key has 5 components, which signals potential for surrogate key optimization.

academic_schema.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
-- Academic Course System Complete Schema
 
CREATE TABLE department (
    dept_code CHAR(4) PRIMARY KEY,  -- 'CSCI', 'MATH'
    name VARCHAR(100) NOT NULL,
    building VARCHAR(50),
    phone VARCHAR(20)
);
 
-- DEPENDENT on Department
CREATE TABLE course (
    dept_code CHAR(4) NOT NULL,
    course_number CHAR(4) NOT NULL,  -- '1010', '3500'
    title VARCHAR(100) NOT NULL,
    credits INT NOT NULL CHECK (credits BETWEEN 1 AND 6),
    description TEXT,
    PRIMARY KEY (dept_code, course_number),
    FOREIGN KEY (dept_code) REFERENCES department(dept_code)
);
 
CREATE TABLE semester (
    semester_id CHAR(6) PRIMARY KEY,  -- '2024FA', '2025SP'
    name VARCHAR(30) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL
);
 
-- MULTI-DEPENDENT on Course AND Semester
CREATE TABLE section (
    dept_code CHAR(4) NOT NULL,
    course_number CHAR(4) NOT NULL,
    semester_id CHAR(6) NOT NULL,
    section_number CHAR(3) NOT NULL,  -- '001', '002'
    instructor_id INT,
    room VARCHAR(30),
    schedule VARCHAR(100),  -- 'MWF 10:00-10:50'
    capacity INT DEFAULT 30,
    PRIMARY KEY (dept_code, course_number, semester_id, section_number),
    FOREIGN KEY (dept_code, course_number) REFERENCES course(dept_code, course_number),
    FOREIGN KEY (semester_id) REFERENCES semester(semester_id)
);
 
CREATE TABLE student (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    admission_year INT NOT NULL,
    major_dept_code CHAR(4),
    FOREIGN KEY (major_dept_code) REFERENCES department(dept_code)
);
 
-- ASSOCIATIVE: Connects Section and Student
CREATE TABLE enrollment (
    dept_code CHAR(4) NOT NULL,
    course_number CHAR(4) NOT NULL,
    semester_id CHAR(6) NOT NULL,
    section_number CHAR(3) NOT NULL,
    student_id INT NOT NULL,
    enrollment_date DATE DEFAULT (CURRENT_DATE),
    grade CHAR(2),  -- 'A', 'B+', etc.
    status VARCHAR(20) DEFAULT 'enrolled',  -- 'enrolled', 'dropped', 'completed'
    PRIMARY KEY (dept_code, course_number, semester_id, section_number, student_id),
    FOREIGN KEY (dept_code, course_number, semester_id, section_number)
        REFERENCES section(dept_code, course_number, semester_id, section_number),
    FOREIGN KEY (student_id) REFERENCES student(student_id)
);
 
-- Query: Student's transcript
SELECT 
    e.semester_id,
    e.dept_code || ' ' || e.course_number as course,
    c.title,
    c.credits,
    e.grade
FROM enrollment e
JOIN course c ON e.dept_code = c.dept_code AND e.course_number = c.course_number
WHERE e.student_id = 12345
  AND e.grade IS NOT NULL
ORDER BY e.semester_id, e.dept_code, e.course_number;
 
-- Query: Section roster
SELECT s.student_id, s.first_name, s.last_name, e.grade
FROM enrollment e
JOIN student s ON e.student_id = s.student_id
WHERE e.dept_code = 'CSCI' 
  AND e.course_number = '3500'
  AND e.semester_id = '2024FA'
  AND e.section_number = '001'
ORDER BY s.last_name, s.first_name;

Pattern Recognition: When to Use Identifying Relationships

Across these case studies, consistent patterns emerge. Use this quick reference to recognize identifying relationship opportunities in your own modeling work.

Indicators for Identifying Relationships

•Sequential Numbering Within Parent — 'Line 1, 2, 3' of an order; 'Visit 1, 2, 3' of a patient. The sequence resets for each parent.
•Physical Containment — Rooms in buildings, seats in theaters, bins in warehouses. Physical location inherently requires parent context.
•Document Line Items — Order lines, invoice items, receipt entries. The document owns the lines completely.
•Version History — Document versions, BOM versions, software releases. Versions are numbered per parent entity.
•Scheduled Payments — Loan payments, lease payments, subscription billing. Payments are sequenced per agreement.
•Hierarchical Classification — University → Department → Course → Section. Each level scopes the next.
•User-Stated Composite Identity — When users say 'Room 101 in Building A' or 'Question 5 on Exam 3', they're indicating composite identity.

The Acid Test

Summary: Identifying Relationships Mastery

Through five comprehensive case studies, we've seen identifying relationships in action across diverse domains. Let's consolidate the key insights from this module:

Module Key Takeaways

•Owner entities provide identity context — They have self-sufficient keys and bestow identity components to their dependents through identifying relationships.
•Dependent entities require owner context for uniqueness — They exhibit both existence dependency and identification dependency, with discriminators providing within-owner uniqueness.
•Discriminators distinguish instances within an owner's scope — Sequential integers, meaningful codes, timestamps, and FK references are common discriminator patterns.
•Composite keys combine owner key + discriminator — This composition propagates through hierarchies, with key width growing at each level.
•Hybrid designs balance purity with practicality — Surrogate keys with natural UNIQUE constraints offer simpler FKs while preserving domain semantics.
•Pattern recognition accelerates modeling — Line items, physical containment, versioning, and hierarchical classification are reliable identifying relationship indicators.

Module Complete:

Module Complete

5 / 5