Mapping Overview

You've applied the mapping algorithm and made thoughtful design decisions. The relational schema is defined, constraints are specified, and documentation is written. But how do you know the mapping is actually correct?

Mapping quality isn't subjective—it can be systematically evaluated against well-defined criteria. A high-quality mapping:

• Preserves all semantics from the original ER diagram
• Enforces all constraints that the business requires
• Avoids anomalies that cause data inconsistency
• Supports efficient operations for expected access patterns
• Maintains clarity for developers who will query and maintain the schema

This page establishes a comprehensive quality framework for evaluating relational schema mappings. By the end, you'll have a validation checklist that ensures mappings meet professional standards.

Mapping quality can be decomposed into four fundamental dimensions, each addressing different aspects of schema correctness and utility:

The Quality Pillars:

Inter-Pillar Trade-offs:

These pillars sometimes conflict:

• Normalization (Structural Integrity) may hurt Operational Efficiency due to joins
• Denormalization for performance may reduce Semantic Correctness (redundancy risks)
• Highly constrained schemas ensure correctness but may reduce Maintainability (rigid change process)

The goal is not to maximize every pillar but to achieve an appropriate balance for your system's requirements. A data warehouse may sacrifice normalization for query speed; an OLTP system may prioritize integrity above all.

Quality Assessment Approach:

For each pillar, we'll define:

1. Specific criteria to evaluate
2. Validation procedures to verify
3. Common defects and their symptoms
4. Remediation strategies

Semantic correctness ensures the relational schema accurately represents all information and constraints from the ER model. This is the most fundamental quality criterion—without it, the database cannot serve its purpose.

Semantic Correctness Criteria:

Validation Procedure:

To verify semantic correctness, perform a mapping audit:

1. Create Entity Mapping Matrix:

   ER Entity	Mapped Table	Key Preserved?	All Attrs?
   EMPLOYEE	EMPLOYEE	✓ (SSN → PK)	✓
   DEPENDENT	DEPENDENT	✓ (Composite)	✓

2. Create Relationship Mapping Matrix:

   ER Relationship	Type	Mapping Approach	Cardinality Enforced?	Participation Enforced?
   WORKS_IN	1:N	FK in EMPLOYEE	✓ (no UNIQUE)	✓ (nullable)
   MANAGES	1:1	FK in DEPARTMENT	✓ (UNIQUE)	✓ (NOT NULL)

3. Trace Each Constraint: For every constraint in the ER diagram, identify the corresponding relational enforcement mechanism.

Structural integrity ensures the schema avoids redundancy and the associated update anomalies. This pillar is closely aligned with normalization theory—a well-mapped schema should achieve at least Third Normal Form (3NF) without explicit denormalization decisions.

Anomaly Types:

Normalization Verification:

A correct ER-to-relational mapping should produce a schema that is at least 3NF:

1NF Criteria:

• All tables have atomic values (no repeating groups, no multivalued cells)
• Primary key defined for every table

2NF Criteria (if applicable):

• 1NF plus no partial dependencies (non-key attributes depend on full key, not part of key)
• Relevant only for composite keys

3NF Criteria:

• 2NF plus no transitive dependencies (non-key attributes don't depend on other non-key attributes)
• Every non-key attribute depends on "the key, the whole key, and nothing but the key"

BCNF Criteria:

• Every determinant is a candidate key
• Stricter than 3NF; may conflict with dependency preservation

Structural Integrity Checklist:

While mapping focuses on correctness, the resulting schema must also support efficient database operations. Efficiency criteria evaluate whether the structure enables good query performance.

Efficiency Factors:

Efficiency Evaluation Process:

1. Enumerate Expected Queries: List the most common and most critical queries the system will execute.

2. Trace Query Paths: For each query, identify which tables are accessed, which joins required, which indexes useful.

3. Estimate Access Patterns:

   • Point lookups (single row by key)
   • Range scans (rows within range)
   • Full table scans (all rows)
   • Join cardinalities

4. Identify Hotspots: Tables or joins that appear in many queries are optimization targets.

5. Consider Index Strategies: Can indexes on the mapped tables support common access patterns?

Example Efficiency Analysis:

Query: Find all employees in a department with their skills

  Tables needed: EMPLOYEE, DEPARTMENT, EMPLOYEE_SKILLS
  Joins: EMPLOYEE ⟕ DEPARTMENT (on DeptID)
         EMPLOYEE ⟕ EMPLOYEE_SKILLS (on SSN)
  Indexes needed: 
    - DEPARTMENT(DeptID) — for join
    - EMPLOYEE(DeptID) — for lookup
    - EMPLOYEE_SKILLS(EmployeeSSN) — for join
  
  Assessment: 2 joins required, all indexable ✓

Schemas live for years or decades. Maintainability measures how understandable, modifiable, and evolvable the schema is over its lifetime.

Maintainability Criteria:

Maintainability Anti-Patterns:

Evolution Readiness:

A maintainable schema anticipates change:

1. Additive Changes Should Be Easy:

   • New optional columns → Simple ALTER TABLE ADD
   • New entity types → New tables with FKs
   • New relationships → FK additions or junction tables

2. Structural Changes Should Be Plannable:

   • Splitting tables → View for backward compatibility
   • Merging tables → Data migration with integrity checks
   • Key changes → Careful FK cascade planning

3. Data Type Expansions Should Be Possible:

   • VARCHAR(50) → VARCHAR(100) trivial
   • INT → BIGINT possible but requires FK updates
   • String → Enum requires data validation

Experienced reviewers recognize recurring patterns of mapping defects. This catalog helps identify and remediate common errors:

Defect Catalog:

Defect Detection Techniques:

1. Constraint Testing: Attempt to insert data that should violate ER constraints. If the database accepts it, there's a mapping defect.

   -- Test: 1:1 should prevent second manager for same dept
   -- If this succeeds, UNIQUE is missing
   INSERT INTO DEPARTMENT (DeptID, DeptName, ManagerSSN) 
   VALUES (1, 'Sales', '111-11-1111');
   INSERT INTO DEPARTMENT (DeptID, DeptName, ManagerSSN) 
   VALUES (2, 'Marketing', '111-11-1111');  -- Same manager!
   

2. Anomaly Probing: Execute sequences of DML that would reveal anomalies:

   • Insert entity without required relationship
   • Delete referenced row and check cascades
   • Update data that should appear in multiple places

3. Coverage Analysis: Check that every ER element maps to something:

   • Every entity → table or merged
   • Every attribute → column
   • Every relationship → FK or junction
   • Every constraint → DDL clause

The following checklist consolidates all quality criteria into a practical validation tool. Use this checklist to verify any ER-to-relational mapping before considering it complete.

Quality assessment transforms mapping from a completed task to a verified deliverable. Let's consolidate the key insights:

What's Next:

The final page of this module explores Common Patterns—recurring mapping scenarios that appear across different domains. These patterns provide templates that accelerate mapping for familiar structures and guide handling of complex cases.