Conceptual to Logical Mapping - Logical Design: From Concepts to Schemas | OneNoughtOne

0/278

Database Management SystemsLogical Design

Logical Design: From Concepts to Schemas

LevelIntermediate

Duration90 mins

TopicLogical Design

1 / 5

Conceptual to Logical Mapping

The Bridge Between Abstraction and Implementation

The transition from conceptual design to logical design represents one of the most critical phases in the database development lifecycle. While conceptual models capture what data exists and how entities relate at a business-semantic level, logical design determines how this information will be structured within a specific data model—most commonly, the relational model.

This transformation is far from mechanical. It requires careful decision-making, trade-off analysis, and deep understanding of both the source model's semantics and the target model's capabilities and constraints. A poorly executed mapping can result in databases that are normalized but unusable, complete but unperformant, or technically correct but semantically distorted.

The stakes are significant:

Logical design errors propagate downstream into physical design, application development, and ultimately into production systems that may serve millions of users. Correcting structural flaws after deployment is orders of magnitude more expensive than addressing them during the design phase.

What You Will Learn

This page provides a comprehensive treatment of conceptual-to-logical mapping: the systematic algorithms that transform ER diagrams into relational schemas, the decision frameworks that guide mapping choices, the quality criteria that ensure fidelity, and the common pitfalls that even experienced designers encounter.

Understanding the Mapping Problem

Before diving into algorithms, we must understand what conceptual-to-logical mapping actually accomplishes. This understanding frames every subsequent decision.

The Semantic Preservation Imperative:

Conceptual models (typically Entity-Relationship diagrams) capture real-world semantics: entities represent distinguishable objects or concepts, attributes describe their properties, and relationships encode how entities interact. The fundamental goal of mapping is to preserve these semantics in a form executable by a database management system.

However, the relational model has a fundamentally different vocabulary:

Entities become relations (tables)
Attributes become columns with domain constraints
Relationships become foreign key references or junction tables
Cardinality constraints become uniqueness and nullability constraints
Participation constraints become NOT NULL specifications

The challenge lies in the fact that this is not a 1:1 translation. Multiple valid relational representations can encode the same conceptual model, and choosing among them requires understanding the trade-offs involved.

Conceptual vs. Logical Model Elements
Conceptual Element (ER)	Logical Element (Relational)	Mapping Complexity
Strong Entity	Base Table with Primary Key	Low — Direct translation
Weak Entity	Table with Composite Key (includes owner PK)	Medium — Dependency must be explicit
Simple Attribute	Column with Domain	Low — Direct translation
Composite Attribute	Multiple Columns OR Separate Table	Medium — Flattening decision required
Multivalued Attribute	Separate Table with Foreign Key	Medium — First Normal Form enforcement
Derived Attribute	Computed Column OR Application Logic	Medium — Storage vs. computation trade-off
1:1 Relationship	Foreign Key OR Table Merge	Medium — Multiple valid approaches
1:N Relationship	Foreign Key on N-side	Low — Standard pattern
M:N Relationship	Junction/Bridge Table	Medium — Additional table required
Supertype/Subtype	Single Table OR Multiple Tables	High — Three distinct strategies
N-ary Relationship (n>2)	Junction Table with Multiple FKs	High — Complex key design

Information Loss and Gain

Mapping should preserve all information from the conceptual model, but it often adds information through constraint specifications. Conversely, some semantic nuances (like relationship names describing purpose) may be lost unless captured in documentation or naming conventions. The mapping process should explicitly track both preservation and enhancement.

The Standard Mapping Algorithm

The canonical approach to ER-to-relational mapping follows a structured, seven-step algorithm. While variations exist, this algorithm forms the foundation of virtually all mapping methodologies and is essential knowledge for any database professional.

Step 1: Map Strong (Regular) Entity Types

For each strong entity type E:

Create a relation R that includes all simple attributes of E
Include simple components of composite attributes (flatten the structure)
Choose one candidate key as the primary key; others become alternate keys with UNIQUE constraints
Document all candidate keys for potential future use

Step 2: Map Weak Entity Types

For each weak entity type W with identifying entity E:

Create a relation R that includes all simple attributes of W
Include the primary key of E as a foreign key in R
The primary key of R is the combination of W's partial key and E's primary key
Establish referential integrity with ON DELETE CASCADE (weak entities depend existentially on their identifying entity)

mapping_algorithm_steps_1_2.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
-- ==================================================
-- STEP 1: Strong Entity Mapping
-- ==================================================
 
-- ER Model:
-- Entity: EMPLOYEE
--   - Attributes: EmployeeID (PK), FirstName, LastName, 
--                 Address (Composite: Street, City, State, PostalCode),
--                 Email (Alternate Key)
 
-- Logical Schema:
CREATE TABLE Employee (
    EmployeeID      INT             PRIMARY KEY,
    FirstName       VARCHAR(50)     NOT NULL,
    LastName        VARCHAR(50)     NOT NULL,
    -- Composite attribute flattened:
    Street          VARCHAR(100),
    City            VARCHAR(50),
    State           CHAR(2),
    PostalCode      VARCHAR(10),
    -- Alternate key documented:
    Email           VARCHAR(100)    NOT NULL UNIQUE
);
 
-- ==================================================
-- STEP 2: Weak Entity Mapping  
-- ==================================================
 
-- ER Model:
-- Entity: DEPENDENT (Weak, identified by EMPLOYEE)
--   - Partial Key: DependentName
--   - Attributes: DateOfBirth, Relationship
 
-- Logical Schema:
CREATE TABLE Dependent (
    -- Composite Primary Key includes owner's PK
    EmployeeID      INT             NOT NULL,
    DependentName   VARCHAR(100)    NOT NULL,
    DateOfBirth     DATE,
    Relationship    VARCHAR(20)     NOT NULL,
    
    PRIMARY KEY (EmployeeID, DependentName),
    
    -- Existential dependency: cascade deletion
    FOREIGN KEY (EmployeeID) 
        REFERENCES Employee(EmployeeID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Step 3: Map Binary 1:1 Relationship Types

For 1:1 relationships, three strategies exist:

Strategy 3A (Foreign Key Approach):

Choose one relation S (preferably the one with total participation)
Include the primary key of the other relation T as a foreign key in S
Include any relationship attributes in S
Apply UNIQUE constraint to the foreign key to enforce 1:1

Strategy 3B (Merged Relation Approach):

If both entities have total participation, merge them into a single relation
Useful when entities always exist together (strong semantic coupling)

Strategy 3C (Cross-Reference Table):

Create a separate relation with both primary keys
Rarely used for 1:1 (adds unnecessary indirection) but sometimes useful for optional-optional relationships

mapping_1_to_1_relationships.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
-- ==================================================
-- STEP 3: 1:1 Relationship Mapping Options
-- ==================================================
 
-- ER Model:
-- EMPLOYEE (1) --- manages --- (1) DEPARTMENT
-- Constraint: Each department has exactly one manager;
--             Not every employee manages a department
 
-- Strategy 3A: Foreign Key (Preferred)
-- Place FK on the side with total participation
CREATE TABLE Department (
    DepartmentID    INT             PRIMARY KEY,
    DepartmentName  VARCHAR(100)    NOT NULL,
    Budget          DECIMAL(15,2),
    -- Foreign key for 1:1 relationship
    ManagerID       INT             NOT NULL UNIQUE,
    ManagerStartDate DATE           NOT NULL,  -- Relationship attribute
    
    FOREIGN KEY (ManagerID) 
        REFERENCES Employee(EmployeeID)
        ON DELETE RESTRICT  -- Cannot delete manager without reassignment
);
 
-- Strategy 3B: Merged Relation
-- Used when entities ALWAYS exist together
CREATE TABLE EmployeeWithCredentials (
    EmployeeID      INT             PRIMARY KEY,
    FirstName       VARCHAR(50)     NOT NULL,
    LastName        VARCHAR(50)     NOT NULL,
    Email           VARCHAR(100)    NOT NULL,
    -- Credentials merged (1:1 total-total)
    PasswordHash    VARCHAR(256)    NOT NULL,
    LastLoginAt     TIMESTAMP,
    FailedAttempts  INT             DEFAULT 0
);
 
-- Strategy 3C: Cross-Reference (Rarely used for 1:1)
-- Useful for optional-optional with relationship attributes
CREATE TABLE ParkingAssignment (
    EmployeeID      INT             PRIMARY KEY,  -- Also UNIQUE
    ParkingSpaceID  INT             UNIQUE,       -- Ensures 1:1
    AssignedDate    DATE            NOT NULL,
    MonthlyFee      DECIMAL(8,2),
    
    FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID),
    FOREIGN KEY (ParkingSpaceID) REFERENCES ParkingSpace(SpaceID)
);

Mapping 1:N and M:N Relationships

Step 4: Map Binary 1:N Relationship Types

For 1:N (one-to-many) relationships S:T where S is the "one" side:

Include the primary key of S as a foreign key in the relation representing T (the "many" side)
Include any relationship attributes in T
Set the foreign key as NOT NULL if T has total participation in the relationship

This is the most common and straightforward mapping scenario. The logic is simple: each instance of T is associated with exactly one instance of S, so storing S's identifier in T directly establishes the connection without data duplication.

Step 5: Map Binary M:N Relationship Types

For M:N (many-to-many) relationships between S and T:

Create a new relation R (often called a junction, bridge, or associative table)
Include primary keys of both S and T as foreign keys
The combination of both foreign keys typically forms the primary key of R
Include any relationship attributes in R

This is necessary because neither S nor T can directly hold the other's key—each instance of S relates to multiple Ts, and vice versa.

mapping_1_n_and_m_n_relationships.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
-- ==================================================
-- STEP 4: 1:N Relationship Mapping
-- ==================================================
 
-- ER Model:
-- DEPARTMENT (1) --- employs --- (N) EMPLOYEE
-- Constraint: Every employee works in exactly one department
 
CREATE TABLE Department (
    DepartmentID    INT             PRIMARY KEY,
    DepartmentName  VARCHAR(100)    NOT NULL UNIQUE,
    Location        VARCHAR(100)
);
 
CREATE TABLE Employee (
    EmployeeID      INT             PRIMARY KEY,
    FirstName       VARCHAR(50)     NOT NULL,
    LastName        VARCHAR(50)     NOT NULL,
    Salary          DECIMAL(12,2),
    HireDate        DATE            NOT NULL,
    -- Foreign key captures 1:N relationship
    DepartmentID    INT             NOT NULL,  -- Total participation
    
    FOREIGN KEY (DepartmentID) 
        REFERENCES Department(DepartmentID)
        ON DELETE RESTRICT  -- Cannot delete dept with employees
        ON UPDATE CASCADE
);
 
-- ==================================================
-- STEP 5: M:N Relationship Mapping
-- ==================================================
 
-- ER Model:
-- EMPLOYEE (M) --- works_on --- (N) PROJECT
-- Relationship Attributes: Hours, Role
 
CREATE TABLE Project (
    ProjectID       INT             PRIMARY KEY,
    ProjectName     VARCHAR(100)    NOT NULL,
    StartDate       DATE,
    EndDate         DATE,
    Budget          DECIMAL(15,2)
);
 
-- Junction/Bridge table for M:N relationship
CREATE TABLE EmployeeProject (
    EmployeeID      INT             NOT NULL,
    ProjectID       INT             NOT NULL,
    -- Relationship attributes
    HoursPerWeek    DECIMAL(4,1)    DEFAULT 0,
    Role            VARCHAR(50)     NOT NULL DEFAULT 'Contributor',
    AssignedDate    DATE            NOT NULL DEFAULT CURRENT_DATE,
    
    -- Composite primary key
    PRIMARY KEY (EmployeeID, ProjectID),
    
    FOREIGN KEY (EmployeeID) 
        REFERENCES Employee(EmployeeID)
        ON DELETE CASCADE,  -- If employee leaves, remove assignments
    FOREIGN KEY (ProjectID) 
        REFERENCES Project(ProjectID)
        ON DELETE CASCADE   -- If project ends, remove assignments
);
 
-- Index for reverse lookups (find all employees on a project)
CREATE INDEX idx_empproject_project ON EmployeeProject(ProjectID);

Junction Table Primary Key Considerations

The compound primary key (FK1, FK2) assumes each employee works on each project at most once. If the relationship can have multiple instances (e.g., an employee works on the same project multiple times in different time periods), include a discriminator (like StartDate) in the primary key, or use a surrogate key with a unique constraint on the natural key combination.

Mapping Multivalued and Derived Attributes

Step 6: Map Multivalued Attributes

Multivalued attributes violate First Normal Form (1NF) if included as repeated columns or delimited values. The correct approach is to create a separate relation:

Create a new relation R for the multivalued attribute A of entity E
Include the primary key of E as a foreign key in R
Include a column for the attribute value
The primary key of R is typically the combination of the foreign key and the attribute value (or a portion sufficient for uniqueness)

Handling Derived Attributes:

Derived attributes (calculated from other attributes) present a design choice:

Option A: Store explicitly — Denormalize for read performance, accept maintenance overhead Option B: Compute on demand — Use views or computed columns, ensure consistency Option C: Materialized views — Precompute and refresh periodically

The choice depends on read/write ratios, computation complexity, and staleness tolerance.

mapping_multivalued_attributes.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
-- ==================================================
-- STEP 6: Multivalued Attribute Mapping
-- ==================================================
 
-- ER Model:
-- Entity: EMPLOYEE
--   - Multivalued Attribute: PhoneNumbers (can have multiple)
--   - Multivalued Composite: Skills (SkillName, ProficiencyLevel)
 
-- Wrong Approach (violates 1NF):
-- CREATE TABLE EmployeeBad (
--     EmployeeID INT PRIMARY KEY,
--     PhoneNumbers VARCHAR(500)  -- "555-1234,555-5678,555-9999"
-- );
 
-- Correct Approach: Separate table
CREATE TABLE EmployeePhone (
    EmployeeID      INT             NOT NULL,
    PhoneNumber     VARCHAR(20)     NOT NULL,
    PhoneType       VARCHAR(20)     DEFAULT 'Mobile',
    IsPrimary       BOOLEAN         DEFAULT FALSE,
    
    PRIMARY KEY (EmployeeID, PhoneNumber),
    
    FOREIGN KEY (EmployeeID) 
        REFERENCES Employee(EmployeeID)
        ON DELETE CASCADE,
        
    -- Ensure only one primary phone per employee
    CONSTRAINT chk_single_primary 
        CHECK (IsPrimary = FALSE OR 
               NOT EXISTS (
                   SELECT 1 FROM EmployeePhone ep2 
                   WHERE ep2.EmployeeID = EmployeeID 
                   AND ep2.IsPrimary = TRUE 
                   AND ep2.PhoneNumber != PhoneNumber
               ))
    -- Note: Complex constraint; often enforced in application layer
);
 
-- Multivalued Composite Attribute
CREATE TABLE EmployeeSkill (
    EmployeeID          INT             NOT NULL,
    SkillName           VARCHAR(100)    NOT NULL,
    ProficiencyLevel    INT             CHECK (ProficiencyLevel BETWEEN 1 AND 5),
    CertifiedDate       DATE,
    
    PRIMARY KEY (EmployeeID, SkillName),
    
    FOREIGN KEY (EmployeeID) 
        REFERENCES Employee(EmployeeID)
        ON DELETE CASCADE
);
 
-- ==================================================
-- DERIVED ATTRIBUTE OPTIONS
-- ==================================================
 
-- ER Model:
-- Entity: EMPLOYEE
--   - Derived Attribute: Age (calculated from DateOfBirth)
--   - Derived Attribute: TotalProjectHours (sum from EmployeeProject)
 
-- Option A: Stored/Denormalized (Update triggers or application logic needed)
ALTER TABLE Employee ADD COLUMN Age INT;
-- Must be updated by trigger or scheduled job
 
-- Option B: Computed Column (Database-supported)
-- PostgreSQL:
ALTER TABLE Employee ADD COLUMN Age INT 
    GENERATED ALWAYS AS (
        EXTRACT(YEAR FROM AGE(CURRENT_DATE, DateOfBirth))
    ) STORED;
 
-- SQL Server:
-- Age AS DATEDIFF(YEAR, DateOfBirth, GETDATE())
 
-- Option C: View for Derived Values
CREATE VIEW EmployeeWithCalculations AS
SELECT 
    e.*,
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, e.DateOfBirth)) AS Age,
    COALESCE(
        (SELECT SUM(HoursPerWeek) 
         FROM EmployeeProject ep 
         WHERE ep.EmployeeID = e.EmployeeID
        ), 0
    ) AS TotalWeeklyProjectHours
FROM Employee e;

Multivalued Attribute Cardinality

If a multivalued attribute has known maximum cardinality (e.g., at most 3 phone numbers), you might choose to denormalize with columns Phone1, Phone2, Phone3. This trades flexibility for query simplicity but is generally discouraged unless the limit is truly fixed and queries predominantly need all values together.

Mapping N-ary and Recursive Relationships

Step 7: Map N-ary Relationship Types (n > 2)

For relationships involving three or more entity types:

Create a new relation R
Include the primary keys of all participating entity types as foreign keys
The primary key of R depends on the cardinality constraints
Include any relationship attributes in R

Determining the primary key for n-ary relationships requires careful analysis. Generally, include the primary keys of all entities that can participate multiple times for the same combination of the others.

Recursive (Unary) Relationships:

When an entity relates to itself (e.g., Employee supervises Employee):

For 1:N recursive:

Add a foreign key column in the same table that references the primary key
This creates a hierarchical structure

For M:N recursive:

Create a junction table with two foreign keys, both referencing the same entity
Use role-based naming to distinguish the two participants

mapping_nary_recursive_relationships.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
-- ==================================================
-- STEP 7A: Ternary Relationship Mapping
-- ==================================================
 
-- ER Model:
-- SUPPLIER --- supplies --- PART --- to --- PROJECT
-- Ternary Relationship: SUPPLY (Supplier, Part, Project)
-- Relationship Attribute: Quantity
 
-- The relationship means: A supplier supplies a particular part
-- to a particular project in a specific quantity
 
CREATE TABLE Supplier (
    SupplierID      INT             PRIMARY KEY,
    SupplierName    VARCHAR(100)    NOT NULL,
    ContactEmail    VARCHAR(100)
);
 
CREATE TABLE Part (
    PartID          INT             PRIMARY KEY,
    PartName        VARCHAR(100)    NOT NULL,
    UnitPrice       DECIMAL(10,2)
);
 
CREATE TABLE Project (
    ProjectID       INT             PRIMARY KEY,
    ProjectName     VARCHAR(100)    NOT NULL
);
 
-- Junction table for ternary relationship
CREATE TABLE Supply (
    SupplierID      INT             NOT NULL,
    PartID          INT             NOT NULL,
    ProjectID       INT             NOT NULL,
    Quantity        INT             NOT NULL CHECK (Quantity > 0),
    SupplyDate      DATE            DEFAULT CURRENT_DATE,
    
    -- Primary key includes all three FKs
    -- (assumes same supplier can supply same part to same project once)
    PRIMARY KEY (SupplierID, PartID, ProjectID),
    
    FOREIGN KEY (SupplierID) REFERENCES Supplier(SupplierID),
    FOREIGN KEY (PartID) REFERENCES Part(PartID),
    FOREIGN KEY (ProjectID) REFERENCES Project(ProjectID)
);
 
-- If multiple supplies allowed (e.g., different dates):
-- PRIMARY KEY (SupplierID, PartID, ProjectID, SupplyDate)
-- OR use surrogate key with unique constraint
 
-- ==================================================
-- STEP 7B: Recursive (Unary) Relationship Mapping
-- ==================================================
 
-- ER Model:
-- EMPLOYEE (1) --- supervises --- (N) EMPLOYEE
 
-- 1:N Recursive: Self-referencing foreign key
CREATE TABLE Employee (
    EmployeeID      INT             PRIMARY KEY,
    FirstName       VARCHAR(50)     NOT NULL,
    LastName        VARCHAR(50)     NOT NULL,
    -- Recursive relationship: supervisor
    SupervisorID    INT,            -- NULL for top-level employees
    
    FOREIGN KEY (SupervisorID) 
        REFERENCES Employee(EmployeeID)
        ON DELETE SET NULL  -- If supervisor leaves, set to NULL
);
 
-- Index for efficient hierarchy queries
CREATE INDEX idx_employee_supervisor ON Employee(SupervisorID);
 
-- ER Model:
-- PART (M) --- component_of --- (N) PART
-- Relationship Attribute: Quantity
 
-- M:N Recursive: Junction table
CREATE TABLE PartComposition (
    ParentPartID    INT             NOT NULL,
    ChildPartID     INT             NOT NULL,
    Quantity        INT             NOT NULL CHECK (Quantity > 0),
    
    PRIMARY KEY (ParentPartID, ChildPartID),
    
    -- Prevent part being component of itself
    CHECK (ParentPartID != ChildPartID),
    
    FOREIGN KEY (ParentPartID) REFERENCES Part(PartID),
    FOREIGN KEY (ChildPartID) REFERENCES Part(PartID)
);
 
-- Note: Preventing circular references requires application logic
-- or recursive constraints (database-specific)

N-ary Relationship Key Selection

For n-ary relationships, identifying the correct primary key is crucial. Analyze the cardinality constraints carefully: if an entity on the '1' side determines the others, it may not need to be in the primary key. Document your reasoning, as incorrect key selection leads to either lost relationships or unintended duplicates.

Mapping Generalization/Specialization Hierarchies

Supertype/subtype (generalization/specialization or IS-A) hierarchies present the most complex mapping scenario. Three primary strategies exist, each with distinct trade-offs:

Strategy A: Single Table (Table-Per-Hierarchy)

Create one table containing all attributes from the supertype and all subtypes:

Include a discriminator column to identify the subtype
Subtype-specific attributes are nullable
Simple queries, single table scan
Wastes space when subtypes have many specific attributes
Nullability obscures constraint enforcement

Strategy B: Multiple Tables (Table-Per-Type)

Create a table for the supertype and one for each subtype:

Supertype table contains common attributes
Subtype tables contain specific attributes plus foreign key to supertype
Subtype table's primary key is also the foreign key
Requires joins to retrieve complete entity
Clean normalization, clear constraint enforcement

Strategy C: Subtype Tables Only (Table-Per-Concrete-Class)

Create tables only for subtypes, duplicating supertype attributes:

Each subtype table has complete attribute set
No joins required for individual subtype queries
Supertype queries require UNION ALL
Attribute changes must propagate to all subtype tables

Generalization Mapping Strategy Comparison
Criteria	Single Table	Multiple Tables	Subtype Tables Only
Storage Efficiency	Low (many NULLs)	High (no NULLs)	Medium (duplication)
Query Simplicity (Subtype)	Medium	Medium (join needed)	High (single table)
Query Simplicity (Supertype)	High	High (single table)	Low (UNION required)
Constraint Enforcement	Weak	Strong	Strong
Schema Evolution	Easy	Moderate	Difficult
Best For	Few subtypes, few specific attrs	Many specific attrs, strong typing	Rarely queried at supertype level

mapping_generalization_specialization.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
111
112
113
114
115
116
-- ==================================================
-- GENERALIZATION/SPECIALIZATION MAPPING
-- ==================================================
 
-- ER Model:
-- VEHICLE (supertype)
--   |-- CAR (subtype): NumberOfDoors, TrunkCapacity
--   |-- TRUCK (subtype): CargoCapacity, NumberOfAxles
--   |-- MOTORCYCLE (subtype): EngineDisplacement, HasSidecar
 
-- Common Vehicle attributes: VehicleID, Make, Model, Year, LicensePlate
 
-- ============================================
-- STRATEGY A: Single Table (Table-Per-Hierarchy)
-- ============================================
CREATE TABLE Vehicle_SingleTable (
    VehicleID           INT             PRIMARY KEY,
    Make                VARCHAR(50)     NOT NULL,
    Model               VARCHAR(50)     NOT NULL,
    Year                INT             NOT NULL,
    LicensePlate        VARCHAR(20)     UNIQUE,
    -- Discriminator
    VehicleType         VARCHAR(20)     NOT NULL 
                        CHECK (VehicleType IN ('CAR', 'TRUCK', 'MOTORCYCLE')),
    -- Car-specific (nullable)
    NumberOfDoors       INT,
    TrunkCapacity       DECIMAL(5,2),
    -- Truck-specific (nullable)
    CargoCapacity       DECIMAL(10,2),
    NumberOfAxles       INT,
    -- Motorcycle-specific (nullable)
    EngineDisplacement  INT,
    HasSidecar          BOOLEAN
);
 
-- Partial constraint enforcement via CHECK
ALTER TABLE Vehicle_SingleTable ADD CONSTRAINT chk_vehicle_attrs CHECK (
    (VehicleType = 'CAR' AND NumberOfDoors IS NOT NULL) OR
    (VehicleType = 'TRUCK' AND CargoCapacity IS NOT NULL) OR
    (VehicleType = 'MOTORCYCLE' AND EngineDisplacement IS NOT NULL)
);
 
-- ============================================
-- STRATEGY B: Multiple Tables (Table-Per-Type)
-- ============================================
CREATE TABLE Vehicle (
    VehicleID           INT             PRIMARY KEY,
    Make                VARCHAR(50)     NOT NULL,
    Model               VARCHAR(50)     NOT NULL,
    Year                INT             NOT NULL,
    LicensePlate        VARCHAR(20)     UNIQUE
);
 
CREATE TABLE Car (
    VehicleID           INT             PRIMARY KEY,
    NumberOfDoors       INT             NOT NULL CHECK (NumberOfDoors BETWEEN 2 AND 5),
    TrunkCapacity       DECIMAL(5,2)    NOT NULL,
    
    FOREIGN KEY (VehicleID) REFERENCES Vehicle(VehicleID)
        ON DELETE CASCADE
);
 
CREATE TABLE Truck (
    VehicleID           INT             PRIMARY KEY,
    CargoCapacity       DECIMAL(10,2)   NOT NULL,
    NumberOfAxles       INT             NOT NULL CHECK (NumberOfAxles >= 2),
    
    FOREIGN KEY (VehicleID) REFERENCES Vehicle(VehicleID)
        ON DELETE CASCADE
);
 
CREATE TABLE Motorcycle (
    VehicleID           INT             PRIMARY KEY,
    EngineDisplacement  INT             NOT NULL,
    HasSidecar          BOOLEAN         NOT NULL DEFAULT FALSE,
    
    FOREIGN KEY (VehicleID) REFERENCES Vehicle(VehicleID)
        ON DELETE CASCADE
);
 
-- View to reconstruct complete Car entity
CREATE VIEW CarComplete AS
SELECT v.*, c.NumberOfDoors, c.TrunkCapacity
FROM Vehicle v
JOIN Car c ON v.VehicleID = c.VehicleID;
 
-- ============================================
-- STRATEGY C: Subtype Tables Only
-- ============================================
CREATE TABLE Car_Standalone (
    VehicleID           INT             PRIMARY KEY,
    Make                VARCHAR(50)     NOT NULL,
    Model               VARCHAR(50)     NOT NULL,
    Year                INT             NOT NULL,
    LicensePlate        VARCHAR(20)     UNIQUE,
    NumberOfDoors       INT             NOT NULL,
    TrunkCapacity       DECIMAL(5,2)    NOT NULL
);
 
CREATE TABLE Truck_Standalone (
    VehicleID           INT             PRIMARY KEY,
    Make                VARCHAR(50)     NOT NULL,
    Model               VARCHAR(50)     NOT NULL,
    Year                INT             NOT NULL,
    LicensePlate        VARCHAR(20)     UNIQUE,
    CargoCapacity       DECIMAL(10,2)   NOT NULL,
    NumberOfAxles       INT             NOT NULL
);
 
-- View to query all vehicles (supertype perspective)
CREATE VIEW AllVehicles AS
SELECT VehicleID, Make, Model, Year, LicensePlate, 'CAR' AS VehicleType
FROM Car_Standalone
UNION ALL
SELECT VehicleID, Make, Model, Year, LicensePlate, 'TRUCK' AS VehicleType
FROM Truck_Standalone;

Quality Assurance and Validation

A rigorous mapping process includes systematic validation to ensure the logical schema correctly and completely represents the conceptual model.

Semantic Completeness Check:

Verify that every element of the conceptual model has a corresponding representation:

☐ Every entity type maps to at least one relation
☐ Every attribute (including components of composite attributes) maps to column(s)
☐ Every relationship is represented (FK, junction table, or merged relation)
☐ Every cardinality constraint is enforced (UNIQUE, NOT NULL, CHECK)
☐ Every participation constraint is represented (nullability)

Referential Integrity Verification:

Ensure all foreign key relationships are properly defined with appropriate actions:

☐ All FKs reference existing PKs or UNIQUE constraints
☐ ON DELETE behavior matches relationship semantics
☐ ON UPDATE behavior is consistent
☐ Circular references are identified and handled

Naming Convention Compliance:

Consistent naming aids maintenance and understanding:

☐ Table names reflect entity names (singular vs. plural convention chosen)
☐ Column names are unambiguous and descriptive
☐ Foreign key columns indicate their reference
☐ Junction tables use compound naming (Entity1Entity2 or Entity1_Entity2)

Post-Mapping Validation Checklist

•No Information Loss: Can the original ER diagram be reconstructed from the relational schema?
•No Spurious Information: Does the schema prevent data that wasn't possible in the ER model?
•Minimal Redundancy: Is each fact stored exactly once (subject to denormalization decisions)?
•Constraint Preservation: Are all ER constraints expressible and enforced in the schema?
•Query Feasibility: Can required queries be expressed without excessive complexity?
•Update Feasibility: Can data be modified without anomalies or complex multi-table operations?
•Performance Viability: Does the structure support expected access patterns efficiently?

Documentation Imperative

Always document mapping decisions, especially where multiple valid approaches exist. Record why Strategy A was chosen over Strategy B for a particular generalization. This documentation is invaluable during maintenance, audits, and when onboarding new team members.

Summary and Key Takeaways

Conceptual-to-logical mapping is both a systematic process and a design discipline. Let's consolidate the essential knowledge:

Core Mapping Principles

•Strong entities map directly to relations with their attributes as columns and candidate keys preserved.
•Weak entities include the identifying owner's primary key in their composite primary key.
•1:1 relationships have three strategies: foreign key (preferred), merged table, or cross-reference table.
•1:N relationships place the foreign key on the 'many' side, enforcing the relationship naturally.
•M:N relationships require junction tables with composite primary keys from both participating entities.
•Multivalued attributes become separate tables to satisfy First Normal Form requirements.
•N-ary relationships require junction tables with careful primary key determination based on cardinalities.
•Generalization hierarchies have three strategies with distinct trade-offs for storage, queries, and constraints.

What Comes Next:

With mapping fundamentals established, the next logical step is understanding relational schema notation and representation. We'll examine how to formally document logical schemas, the conventions used in industry and academia, and how schema diagrams communicate structure to stakeholders.

Subsequent pages will dive deep into normalization (ensuring minimal redundancy), constraint specification (encoding business rules), and the iterative refinement process that transforms initial schemas into production-ready designs.

Page Complete

You now understand the systematic process of transforming conceptual ER models into logical relational schemas. The seven-step algorithm, combined with strategies for complex constructs like generalizations and n-ary relationships, equips you to handle real-world database design challenges with confidence.