Enhanced Entity-Relationship Model Overview

The 1980s and 1990s witnessed two powerful modeling paradigms evolving in parallel:

Object-Oriented Programming (OOP): Emerging from Simula and Smalltalk, OOP organized software around objects—encapsulated units combining data and behavior. By the 1990s, C++ and Java had made OOP mainstream.

Enhanced ER Modeling: Simultaneously, database researchers extended Chen's ER model with semantic constructs—specialization, generalization, inheritance—that bore striking resemblance to OOP concepts.

This was no coincidence. Both movements addressed the same fundamental challenge: modeling complex real-world phenomena in computationally tractable ways. The convergence eventually produced Object-Relational databases and, later, modern ORM frameworks.

Understanding how EER relates to object concepts illuminates both:

• Why EER extensions look the way they do
• How to map between EER models and object-oriented implementations
• The historical trajectory toward modern database paradigms

Before examining EER-OOP correspondences, let's establish the core object-oriented concepts. These will serve as reference points for mapping.

The Object Model Foundation

Object-oriented systems are built on several interlocking concepts:

1. Object: An identifiable entity with state (data) and behavior (methods)
2. Class: A template defining the structure and behavior for a type of object
3. Instance: A specific object belonging to a class
4. Attribute: A named property holding part of an object's state
5. Method: A named operation an object can perform

Object Identity

A crucial OOP concept is object identity—each object has a unique identity independent of its attribute values:

Person p1 = new Person("John", 30);
Person p2 = new Person("John", 30);

p1 == p2    // FALSE: different object identities
p1.equals(p2) // TRUE: same attribute values

Two objects with identical attributes remain distinct objects. This contrasts with the relational model where tuples are identified by their values (specifically, by their key values).

Complex Objects

OOP allows objects to contain other objects, creating complex nested structures:

class Address {
    String street;
    String city;
    String zipCode;
}

class Person {
    String name;
    Address homeAddress;    // Object containment
    Address workAddress;    // Another contained object
    List<Phone> phones;     // Collection of objects
}

This contrasts sharply with the flat, normalized tables of the relational model.

EER and OOP use different terminology but model similar concepts. Understanding this correspondence enables effective translation between paradigms.

Structural Correspondences

Inheritance Correspondence

EER inheritance and OOP inheritance serve similar purposes but with nuanced differences:

EER Specialization:              OOP Inheritance:

    EMPLOYEE                     class Employee {
       │                             String name;
      (d)                            double salary;
    ┌──┴──┐                      }
    │     │                      
HOURLLY SALARIED                 class SalariedEmployee 
                                       extends Employee {
                                     int bonusPercentage;
                                 }

Both express IS-A relationships with attribute inheritance. The key differences:

1. Behavior: OOP inheritance includes method inheritance; EER is data-only
2. Multiple Inheritance: EER handles it via categories; OOP varies by language
3. Constraints: EER has disjoint/overlapping, total/partial; OOP relies on language mechanisms

Object identity is a fundamental concept that illuminates deep differences between object and relational paradigms—and influences how EER maps to implementations.

Value-Based Identity (Relational)

In relational databases, entities are identified by key values:

-- Entity identity is its primary key value
SELECT * FROM Employee WHERE employee_id = 12345;

-- Two rows with identical non-key values are still distinct
-- if they have different primary keys

Change an entity's key value, and it becomes a different entity (and breaks referential integrity).

Object Identity (OOP)

In object systems, each object has an Object Identifier (OID) independent of its state:

Person p1 = new Person("John");  // OID: 0x7f3a
Person p2 = new Person("John");  // OID: 0x8b2c

p1.setName("Jane");  // p1 is still the same object (0x7f3a)
                      // despite attribute change

The OID remains constant throughout the object's lifetime, regardless of attribute modifications.

EER's Position on Identity

EER is conceptually closer to object identity than relational identity:

1. Entity = Distinguishable Object: The EER definition of entity emphasizes distinguishability independent of attribute values
2. Key Attributes ≠ Identity: In EER, keys identify entities for external reference, but the entity exists independently
3. Weak Entity Identity: Weak entities demonstrate identity dependency—they exist only in context of their owner

The Implementation Bridge

When mapping EER to relational schemas, we must reconcile these identity models:

EER Entity (conceptual identity)
        │
        ▼
Relational Table with surrogate key
   id (PK), name, address, ...
        │
        ▼  (in application)
OOP Object with persistent OID
   this.id = persistentId
   this.name = "..."

Surrogate keys (auto-generated IDs) serve as the bridge—they approximate object identity in a relational context by:

• Never changing throughout entity lifetime
• Being system-generated (not derived from attributes)
• Serving as stable foreign key targets

One of the starkest contrasts between object and relational models involves complex objects—structures with nested components, collections, and hierarchical relationships.

The Object Model's Natural Complexity

Objects can contain other objects, creating arbitrarily complex structures:

class University {
    String name;
    Address mainCampus;           // Contained object
    List<Department> departments; // Collection of objects
    
    class Department {
        String name;
        Professor chair;          // Reference to another object
        List<Course> courses;     // Nested collection
        
        class Course {
            String code;
            List<Section> sections; // Further nesting
        }
    }
}

This hierarchy is natural for programmers—it mirrors how we think about universities.

The Relational Model's Flatness

Relational databases require flattening:

CREATE TABLE University (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    main_campus_id INT REFERENCES Address(id)
);

CREATE TABLE Department (
    id INT PRIMARY KEY,
    university_id INT REFERENCES University(id),
    name VARCHAR(100),
    chair_id INT REFERENCES Professor(id)
);

CREATE TABLE Course (
    id INT PRIMARY KEY,
    department_id INT REFERENCES Department(id),
    code VARCHAR(20)
);

CREATE TABLE Section (
    id INT PRIMARY KEY,
    course_id INT REFERENCES Course(id),
    section_num INT
);

The nested structure becomes multiple tables with foreign key relationships.

EER's Approach to Complexity

EER handles complex structures through several mechanisms:

1. Composite Attributes: An attribute composed of sub-attributes

Address (composite)
├── Street
├── City
├── State
└── ZipCode

2. Multivalued Attributes: Attributes that hold collections

Person
├── Name (single-valued)
├── PhoneNumbers (multivalued) ← [555-1234, 555-5678, ...]
└── Email (single-valued)

3. Relationships: Connect entities, allowing object graph traversal

UNIVERSITY ─── has ─── DEPARTMENT ─── offers ─── COURSE

4. Weak Entities: Existence-dependent components

BUILDING ═══ contains ═══ ROOM
 (owner)                 (weak)

Mapping Complex Objects to Relations

When implementing EER in a relational database, complex structures require decomposition:

Polymorphism—the ability of different types to respond appropriately to the same operation—is central to OOP. How does this concept manifest in database modeling?

Behavioral Polymorphism (OOP)

In OOP, polymorphism typically involves method overriding:

abstract class Payment {
    abstract void process();
}

class CashPayment extends Payment {
    void process() {
        // Immediate processing
    }
}

class CheckPayment extends Payment {
    void process() {
        // Delayed clearing
    }
}

// Polymorphic call
Payment p = getPayment(); // could be Cash or Check
p.process();  // Correct implementation called based on actual type

Structural Polymorphism (EER)

EER doesn't model behavior, but it supports structural polymorphism—the same query can operate on subtypes with different structures:

Query: Find all employees earning over $100,000

Applies to:
- HourlyEmployee (calculated from hours × rate)
- SalariedEmployee (direct salary attribute)
- ContractEmployee (contract amount)

Each subtype has different attributes, but all participate in the query.

Inheritance Mapping Strategies

When mapping EER hierarchies to relational schemas, we choose from several strategies—each affecting how polymorphism is supported:

1. Single Table Inheritance (Table-Per-Hierarchy)

CREATE TABLE Employee (
    id INT PRIMARY KEY,
    type VARCHAR(20),        -- Discriminator
    name VARCHAR(100),       -- Common attribute
    hourly_rate DECIMAL,     -- HourlyEmployee only
    salary DECIMAL,          -- SalariedEmployee only
    manager_level INT        -- Manager only (NULL for others)
);

Polymorphic queries are simple (just query the table), but table is sparse.

2. Table-Per-Type (Joined Strategy)

CREATE TABLE Employee (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE HourlyEmployee (id INT PRIMARY KEY REFERENCES Employee, hourly_rate DECIMAL);
CREATE TABLE SalariedEmployee (id INT PRIMARY KEY REFERENCES Employee, salary DECIMAL);

Polymorphic queries require joins, but no sparse columns.

3. Table-Per-Concrete-Class

CREATE TABLE HourlyEmployee (id INT PRIMARY KEY, name VARCHAR(100), hourly_rate DECIMAL);
CREATE TABLE SalariedEmployee (id INT PRIMARY KEY, name VARCHAR(100), salary DECIMAL);
-- No common Employee table

Polymorphic queries require UNION; attribute updates on common fields require multiple statements.

The recognition that object and relational models each had strengths led to the Object-Relational (OR) database paradigm—an attempt to combine the best of both worlds.

The Database Manifesto Wars

In the late 1980s and early 1990s, database researchers debated the future:

1. Relational Purists: Argued relational model was sufficient; extensions unnecessary
2. Object Database Advocates: Argued pure OODBMS should replace relational
3. Object-Relational Synthesizers: Argued relational core with object extensions

The third perspective prevailed, leading to SQL:1999 and subsequent standards.

Object-Relational Features (SQL:1999+)

Modern SQL standards incorporate object-oriented features:

User-Defined Types (UDT):

CREATE TYPE Address AS (
    street VARCHAR(100),
    city VARCHAR(50),
    zip_code VARCHAR(10)
);

CREATE TABLE Person (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    home_address Address,   -- Using complex type
    work_address Address
);

Type Inheritance:

CREATE TYPE Person AS (
    name VARCHAR(100),
    birth_date DATE
) NOT FINAL;

CREATE TYPE Employee UNDER Person AS (
    salary DECIMAL,
    hire_date DATE
);

Object References (REF types):

CREATE TYPE Department AS (
    name VARCHAR(100),
    manager REF(Employee)  -- Reference to Employee object
);

Collection Types:

CREATE TYPE PhoneList AS VARCHAR(20) ARRAY[10];

CREATE TABLE Contact (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    phones PhoneList  -- Array of phone numbers
);

EER occupies a strategic position in the modeling landscape: it's abstract enough to capture domain semantics without implementation bias, yet concrete enough to map to multiple implementation paradigms.

The Multi-Target Mapping

A single EER model can be implemented in multiple ways:

                    ┌─────────────────┐
                    │   EER Schema    │
                    └────────┬────────┘
                             │
           ┌─────────────────┼─────────────────┐
           ▼                 ▼                 ▼
   ┌───────────────┐ ┌───────────────┐ ┌───────────────┐
   │  Relational   │ │Object-Relational│ │   Object DB   │
   │   Schema      │ │    Schema       │ │    Schema     │
   └───────────────┘ └───────────────┘ └───────────────┘
           │                 │                 │
           ▼                 ▼                 ▼
      MySQL/PostgreSQL  PostgreSQL 15+     MongoDB/OODB
       (tables only)   (with UDTs/arrays)  (documents)

The same EMPLOYEE specialization hierarchy maps to:

• Relational: Multiple tables with foreign keys
• Object-Relational: Type hierarchy with inheritance
• Document: Embedded subdocuments with discriminator field

EER to ORM Mapping

Modern development often uses Object-Relational Mappers (ORMs) to bridge application objects and database tables. EER provides the conceptual design that ORMs implement:

Example: EER to Prisma ORM

// EER: EMPLOYEE supertype
model Employee {
  id        Int      @id @default(autoincrement())
  name      String
  hireDate  DateTime
  type      String   // Discriminator
  
  // Relationships
  department   Department @relation(fields: [deptId], references: [id])
  deptId       Int
  
  // Subtypes (via type discriminator)
  manager   Manager?
  engineer  Engineer?
}

// EER: MANAGER subtype
model Manager {
  employee     Employee @relation(fields: [employeeId], references: [id])
  employeeId   Int      @unique
  budgetAuth   Decimal
  teamSize     Int
}

When designing EER models with eventual OOP implementation in mind, several guidelines improve the mapping:

1. Mirror Class Hierarchies in Specialization

If your application has a class hierarchy, model it as EER specialization:

App Class Structure:          EER Specialization:

abstract class Product         PRODUCT
  │                               │
  ├── PhysicalProduct            (d)
  │                             ┌─┴─┐
  └── DigitalProduct      PHYSICAL  DIGITAL

2. Choose Appropriate Constraint Levels

• Use disjoint when objects can't simultaneously be multiple types
• Use overlapping when classes use mixins or multiple inheritance
• Use total when abstract classes require concrete subtype
• Use partial when not all instances need subtype classification

3. Handle Multiple Inheritance Carefully

When objects need multiple supertypes:

Option A: Use EER category (if types are heterogeneous)
  Person, Company → (U) → AccountHolder

Option B: Use overlapping specialization (if same root type)
  Person → (o) → [Student, Employee] (can be both)

Option C: Refactor to composition (often cleaner)
  Person has-a StudentRole, has-a EmployeeRole

4. Align Entity and Collection Granularity

OOP collections (List, Set, Map) need corresponding EER patterns:

We have explored the relationship between EER and object-oriented concepts, examining how these complementary paradigms converged. Let's consolidate our understanding.

What's Next

Having explored EER's relationship to object-oriented concepts, the next page examines UML Relationship—how EER relates to the Unified Modeling Language, the standard notation for object-oriented modeling, and how to leverage both notations effectively.