Extended Entity-Relationship (EER) Diagrams

& Reduction to Tables

Database Design & Implementation

A Comprehensive Guide to Advanced ER Modeling

                What You'll Learn:
                Extended ER concepts and notation
Specialization and Generalization
Converting EER diagrams to relational tables
Practical examples and exercises

            

What is an Extended ER (EER) Diagram?

Extended Entity-Relationship diagrams enhance traditional ER diagrams with advanced modeling concepts to handle complex real-world scenarios.

Key Extensions:

Specialization: Process of defining subclasses of an entity
Generalization: Process of defining superclasses from existing entities
Inheritance: Subclasses inherit attributes and relationships
Constraints: Rules governing specialization/generalization

Real-world Example:

In a university database, Person can be specialized into Student, Faculty, and Staff - each with their own specific attributes while sharing common person attributes.

Specialization

Specialization is the process of defining a set of subclasses of an entity type (superclass).

Characteristics:

Subclasses inherit all attributes and relationships of superclass
Subclasses have their own specific attributes and relationships
Represented by triangle symbol pointing upward
Can be Disjoint or Overlapping
Can be Total or Partial

Specialization Notation:

PERSON
   |
   △ (triangle)
  / | \
STUDENT FACULTY STAFF

                Constraint Types:
                Disjoint (d): Entity can belong to at most one subclass
Overlapping (o): Entity can belong to multiple subclasses
Total: Every superclass entity must belong to at least one subclass
Partial: Some superclass entities may not belong to any subclass

            

Example: Employee Specialization

Superclass: EMPLOYEE (EmployeeID, Name, HireDate, Salary)

Specialized into:

FULL_TIME (EmployeeID*, Benefits, VacationDays)
PART_TIME (EmployeeID*, MaxHours, HourlyRate)
INTERN (EmployeeID*, University, EndDate)

Constraints: Disjoint (an employee can only be one type), Partial (not all employees need to be specialized)

Business Rules:

Full-time employees have benefits and fixed vacation days
Part-time employees have hourly rates and maximum weekly hours
Interns are associated with a university and have an end date

Generalization

Generalization is the reverse process of specialization - defining a generalized superclass from existing entity types.

Process:

Identify common attributes and relationships among entities
Create a superclass with common features
Make original entities subclasses of the new superclass
Remove redundant attributes from subclasses

Example: Vehicle Generalization

Before: CAR, TRUCK, MOTORCYCLE (separate entities)

After: VEHICLE (superclass) with CAR, TRUCK, MOTORCYCLE as subclasses

Common attributes: VehicleID, Make, Model, Year

Specific attributes: Car(NumDoors), Truck(CargoCapacity), Motorcycle(EngineType)

                Benefits of Generalization:
                Reduces redundancy
Improves data consistency
Simplifies database schema
Enables polymorphism in applications

            

Converting EER to Relational Tables

There are several strategies to convert EER diagrams into relational database tables:

Strategy 1: Multiple Relations - Superclass and Subclasses

Create separate relations for superclass and each subclass
Superclass relation contains shared attributes
Subclass relations contain specific attributes + foreign key to superclass

Strategy 2: Multiple Relations - Subclasses Only

Create relations only for subclasses
Each subclass relation contains all inherited + specific attributes
Used when specialization is total

Strategy 3: Single Relation with Type Discriminator

Create one relation for superclass and all subclasses
Include type discriminator attribute
Many attributes may have NULL values

Example 1: University Database EER

EER Diagram Description:

PERSON (PersonID, Name, Address, Phone)

Specialized into:

STUDENT (StudentID, Major, GPA)
FACULTY (EmployeeID, Department, Salary, Rank)
STAFF (EmployeeID, Department, Salary, Position)

Constraints: Disjoint, Total specialization

Strategy 1: Multiple Relations

PERSON(PersonID, Name, Address, Phone)

STUDENT(PersonID*, StudentID, Major, GPA)
  Foreign Key: PersonID references PERSON

FACULTY(PersonID*, EmployeeID, Department, Salary, Rank)
  Foreign Key: PersonID references PERSON

STAFF(PersonID*, EmployeeID, Department, Salary, Position)
  Foreign Key: PersonID references PERSON

Example 1: Alternative Strategies

Strategy 2: Subclass Relations Only

STUDENT(PersonID, Name, Address, Phone, StudentID, Major, GPA)

FACULTY(PersonID, Name, Address, Phone, EmployeeID, Department, Salary, Rank)

STAFF(PersonID, Name, Address, Phone, EmployeeID, Department, Salary, Position)

Strategy 3: Single Relation with Type Discriminator

PERSON(PersonID, Name, Address, Phone, PersonType, 
       StudentID, Major, GPA, 
       EmployeeID, Department, Salary, Rank, Position)

Constraints:
- PersonType ∈ {'Student', 'Faculty', 'Staff'}
- If PersonType = 'Student': StudentID, Major, GPA are NOT NULL
- If PersonType = 'Faculty': EmployeeID, Department, Salary, Rank are NOT NULL  
- If PersonType = 'Staff': EmployeeID, Department, Salary, Position are NOT NULL

                When to use each strategy:
                Strategy 1: When you need to query superclass frequently
Strategy 2: When specialization is total and you rarely need superclass data alone
Strategy 3: When subclasses have few specific attributes

            

Example 2: Vehicle Database with Overlapping Specialization

EER Diagram Description:

VEHICLE (VIN, Make, Model, Year, Color)

Specialized into:

CAR (NumDoors, FuelType)
TRUCK (CargoCapacity, NumAxles)
HYBRID (BatteryCapacity, MPG)

Constraints: Overlapping, Partial specialization

Note: A vehicle can be both a CAR and HYBRID

Recommended Strategy: Multiple Relations

VEHICLE(VIN, Make, Model, Year, Color)

CAR(VIN*, NumDoors, FuelType)
  Foreign Key: VIN references VEHICLE

TRUCK(VIN*, CargoCapacity, NumAxles)  
  Foreign Key: VIN references VEHICLE

HYBRID(VIN*, BatteryCapacity, MPG)
  Foreign Key: VIN references VEHICLE

-- A Toyota Prius would appear in both CAR and HYBRID tables

Complex Example: Hospital Management System

Multi-level Specialization:

PERSON → EMPLOYEE → MEDICAL_STAFF

↓

DOCTOR, NURSE

PERSON → PATIENT

-- Level 1: Person
PERSON(PersonID, Name, DOB, Address, Phone)

-- Level 2: Person specialization  
EMPLOYEE(PersonID*, EmployeeID, HireDate, Salary)
PATIENT(PersonID*, PatientID, InsuranceNumber)

-- Level 3: Employee specialization
MEDICAL_STAFF(PersonID*, LicenseNumber, Specialization)

-- Level 4: Medical Staff specialization
DOCTOR(PersonID*, MedicalDegree, YearsExperience)
NURSE(PersonID*, NursingDegree, Shift)

-- Administrative staff (direct from Employee)
ADMIN_STAFF(PersonID*, Department, AccessLevel)

Practice Question 1

Design EER and Convert to Tables

Scenario: Library Management System

ITEM has ItemID, Title, PublicationYear
Items can be BOOK (Author, ISBN, Pages) or MAGAZINE (IssueNumber, Frequency)
Books can be further specialized into TEXTBOOK (Subject, Edition) and NOVEL (Genre, SeriesName)
Specializations are disjoint and total at each level

Tasks:

Draw the EER diagram notation
Convert to relational tables using the most appropriate strategy
Justify your choice of conversion strategy

EER Diagram Structure:

ITEM → {BOOK, MAGAZINE} (d, total)
BOOK → {TEXTBOOK, NOVEL} (d, total)

Relational Tables (Strategy 2 - Subclasses only):

MAGAZINE(ItemID, Title, PublicationYear, IssueNumber, Frequency)

TEXTBOOK(ItemID, Title, PublicationYear, Author, ISBN, Pages, Subject, Edition)

NOVEL(ItemID, Title, PublicationYear, Author, ISBN, Pages, Genre, SeriesName)

Justification: Since all specializations are total, every item belongs to a leaf subclass. Strategy 2 eliminates the need for superclass tables while preserving all information.

Practice Question 2

E-commerce System Design

Scenario: Online Store

USER has UserID, Username, Email, RegistrationDate
Users can be CUSTOMER (ShippingAddress, PaymentInfo) and/or SELLER (BusinessLicense, CommissionRate)
Some users can be both customers and sellers
Not all users are necessarily customers or sellers (partial specialization)

Tasks:

What type of specialization constraint applies?
Convert to relational tables
Write SQL to find all users who are both customers and sellers

Specialization Type:

Overlapping, Partial - Users can be both customer and seller, and some may be neither.

Relational Tables:

USER(UserID, Username, Email, RegistrationDate)

CUSTOMER(UserID*, ShippingAddress, PaymentInfo)
  Foreign Key: UserID references USER

SELLER(UserID*, BusinessLicense, CommissionRate)  
  Foreign Key: UserID references USER

SQL Query:

SELECT u.UserID, u.Username, u.Email
FROM USER u
INNER JOIN CUSTOMER c ON u.UserID = c.UserID
INNER JOIN SELLER s ON u.UserID = s.UserID;

Summary & Best Practices

Key Concepts Covered:

Specialization and Generalization in EER diagrams
Disjoint vs. Overlapping constraints
Total vs. Partial constraints
Three strategies for EER-to-Relational conversion

                Conversion Strategy Guidelines:
                
                        Scenario
                        Recommended Strategy
                        Reason
                    
                        Disjoint, Total
                        Subclasses Only
                        No entity exists in superclass alone
                    
                        Overlapping
                        Multiple Relations
                        Allows entities in multiple subclasses
                    
                        Few specific attributes
                        Single Relation
                        Minimizes table count
                    
                        Frequent superclass queries
                        Multiple Relations
                        Efficient superclass access

Scenario	Recommended Strategy	Reason
Disjoint, Total	Subclasses Only	No entity exists in superclass alone
Overlapping	Multiple Relations	Allows entities in multiple subclasses
Few specific attributes	Single Relation	Minimizes table count
Frequent superclass queries	Multiple Relations	Efficient superclass access

Final Tips:

Always identify constraints clearly before conversion
Consider query patterns when choosing strategy
Document foreign key relationships carefully
Test with sample data to validate design