Enhanced entity–relationship model

The Enhanced Entity–Relationship (EER) model is an extended version of the Entity–Relationship (ER) model used in database design. It incorporates additional semantic constructs and modeling capabilities beyond the basic ER model, enabling more accurate representation of complex real-world scenarios through concepts such as specialization, generalization, union types, and inheritance.

The EER model was developed in the 1980s by database researchers including Ramez Elmasri and Shamkant Navathe to address the limitations of the basic ER model when modeling complex databases in domains such as engineering design, manufacturing systems (CAD/CAM), telecommunications networks, geographic information systems (GIS), and complex software applications.

Overview

The EER model extends the basic ER model by adding support for more complex data relationships and semantic information. While the traditional ER model effectively represents entities, attributes, and relationships, it lacks the ability to model hierarchies, inheritance, and classification structures that are common in real-world databases.

The EER model retains all concepts from the basic ER model—including entities, attributes, relationships, keys, and cardinality constraints, while adding new constructs to handle:

• Subclass and superclass relationships: Enabling classification hierarchies
• Specialization and generalization: Top-down and bottom-up abstraction mechanisms
• Attribute and relationship inheritance: Automatic propagation of properties to subclasses
• Union types (categories): Entities that can belong to multiple distinct superclasses
• Constraints on hierarchies: Rules governing membership in specialization hierarchies

These enhancements make the EER model particularly suitable for modeling complex enterprise databases, object-oriented applications, and systems requiring sophisticated classification schemes.

Key concepts and features

Subclass and superclass

A subclass (or subtype) is an entity type that has distinct attributes or relationships in addition to those inherited from another entity type, called the superclass (or supertype). The subclass is a subset of the superclass, meaning every instance of a subclass is also an instance of its superclass.

Example: In a university database,

• Person might be a superclass with subclasses
  • Student
  • Professor
  • Staff

Each subclass inherits common attributes like

• Name
• Address
• DateOfBirth

from Person, while having specific attributes such as

• StudentID
• Major
• Student
• Rank
• Department

for Professor.

Formally, if S is a subclass of C (the superclass), then:

• S ⊆ C (every member of S is also a member of C)
• S inherits all attributes and relationships of C
• S may have additional specific attributes and relationships

Specialization

Specialization is a top-down conceptual refinement process where a higher-level entity type (superclass) is subdivided into lower-level, more specialized entity types (subclasses) based on distinguishing characteristics. It emphasizes differences among entities within the superclass.

Process:

1. Start with a general entity type
2. Identify distinguishing characteristics
3. Define subclasses based on these characteristics
4. Assign specific attributes and relationships to each subclass

Example: Starting with an Employee entity, specialization might identify distinct types based on job function:

• Employee (superclass) specializes into:
  • Secretary (with attribute TypingSpeed)
  • Engineer (with attribute EngineeringType)
  • Technician (with attribute TechGrade)

Specialization can be:

• Attribute-defined: Based on a specific attribute value (e.g., EmployeeType)
• User-defined: Not based on any specific attribute condition

Generalization

Generalization is a bottom-up abstraction process where multiple entity types with common characteristics are combined into a higher-level, generalized entity type (superclass). It emphasizes similarities among entities.

Process:

1. Identify entity types with common attributes
2. Extract common attributes into a new superclass
3. Establish subclass relationships
4. Retain specialized attributes in subclasses

Example: Separate entities Car, Truck, and Motorcycle can be generalized into a superclass Vehicle containing common attributes like VehicleID, Color, Year, and Manufacturer, while specific attributes remain with subclasses.

Specialization and generalization are inverse processes: specialization creates subclasses from a superclass, while generalization creates a superclass from existing entity types.

Attribute and relationship inheritance

Inheritance is a fundamental principle of the EER model where subclasses automatically inherit all attributes and relationships from their superclass(es). This promotes data consistency and reduces redundancy in the model.

Types of inheritance:

Single inheritance: A subclass inherits from exactly one superclass.

• Example: Student inherits from Person

Multiple inheritance: A subclass inherits from multiple superclasses, acquiring attributes and relationships from all parents.

• Example: TeachingAssistant might inherit from both Student and Employee

Inheritance properties:

• Transitive: If C is a subclass of B, and B is a subclass of A, then C inherits from both B and A
• Additive: Subclasses can define additional attributes and relationships beyond inherited ones
• Overriding: Generally not supported in EER (unlike object-oriented models)

Union types and categories

A union type (or category) represents a subclass that is a subset of the UNION of two or more distinct superclasses. Unlike regular subclasses that inherit from a single superclass, a category can have instances from multiple different entity types.

Characteristics:

• An instance of a category must belong to exactly one of the superclasses
• The category inherits attributes common to all superclasses
• Each instance maintains its identity in the originating superclass

Notation: Represented by a circle with the union symbol (∪) connected to all superclasses, with an arc and subset symbol (⊂) connecting to the category.

Example: In a vehicle registration database, an Owner category can be defined as a subset of PERSON ∪ BANK ∪ COMPANY. Each vehicle owner is either a person, a bank, or a company, but not multiple types simultaneously. The Owner category would have a relationship Owns with the Vehicle entity.

Formal definition: If category T is defined on superclasses S₁, S₂, ..., S_n, then:

• T ⊆ S₁ ∪ S₂ ∪ ... ∪ S_n
• For each instance t ∈ T, t ∈ S_i for exactly one i

Aggregation

Aggregation is an abstraction concept that treats a relationship as a higher-level entity, allowing relationships to participate in other relationships. This is useful when modeling complex scenarios where relationships themselves have attributes or participate in additional relationships.

Example: In a project management system:

• Employees work on Projects (relationship: WorksOn)
• For each work assignment, specific Machinery is required
• The aggregation treats the entire WorksOn relationship as an entity that can be related to Machinery

This allows modeling: "Employee E working on Project P requires Machinery M" without creating artificial intermediate entities.

Tools

Numerous tools support EER diagram creation, ranging from specialized database design software to general-purpose diagramming applications.

Database-specific tools

MySQL Workbench:

• Free, open-source tool for MySQL database design
• Full EER diagram support with reverse and forward engineering
• Reverse engineering: Generate EER diagrams from existing databases
• Forward engineering: Generate SQL scripts from EER diagrams
• Database synchronization: Update schemas to match diagram changes
• Visual query builder and administration features

Oracle SQL Developer Data Modeler:

• Enterprise-grade modeling tool for Oracle databases
• Supports conceptual, logical, and physical data models
• EER diagram creation with comprehensive constraint support
• Multi-database support (Oracle, SQL Server, DB2)
• Collaborative modeling features for team environments

Microsoft Visio:

• Professional diagramming tool with database modeling templates
• EER notation support through custom stencils
• Integration with Microsoft SQL Server
• Extensive formatting and presentation options

ERwin Data Modeler (by erwin, Inc.):

• Industry-leading enterprise data modeling tool
• Comprehensive EER and relational modeling
• Model management and version control
• Supports multiple database platforms
• Metadata management and data governance features

PowerDesigner (by SAP):

• Enterprise modeling tool for data architecture
• Supports EER, UML, and other modeling paradigms
• Business process modeling integration
• Metadata repository and impact analysis

General-purpose and online tools

Lucidchart:

• Web-based diagramming platform
• EER diagram templates and notation support
• Collaboration features for team modeling
• Integration with Google Workspace, Microsoft Office
• Export to various formats (PDF, PNG, SVG)

Draw.io (diagrams.net):

• Free, open-source web-based diagramming tool
• Customizable EER notation with entity and relationship shapes
• Offline desktop versions available
• Integration with cloud storage (Google Drive, OneDrive)
• Export to multiple formats

Creately:

• Online collaborative diagramming platform
• Pre-built EER diagram templates
• Real-time collaboration for distributed teams
• Visual database designer with relationship management

Visual Paradigm:

• Comprehensive UML and database modeling tool
• EER diagram support with ER-to-relational mapping
• Code and database generation
• Project management integration
• Academic and commercial licensing

DbSchema:

• Visual database design and management tool
• Support for multiple database systems
• Interactive EER diagrams with layout algorithms
• Database documentation generation
• Schema comparison and synchronization

Tool selection criteria

When choosing an EER modeling tool, consider:

• Target database platform: Ensure compatibility with your database system
• Reverse/forward engineering: Ability to generate diagrams from databases and vice versa
• Collaboration features: Team modeling and version control capabilities
• Constraint support: Full support for EER constraints (disjointness, completeness)
• Export options: Documentation, image export, SQL script generation
• Learning curve: Ease of use for team members
• Cost: Free/open-source vs. commercial licensing
• Integration: Compatibility with existing development tools

See also

• Object–relational database
• Slowly changing dimension
• Structured type

Further reading

Textbooks discussing EER and implementation using purely relational databases:

• Elmasri, Ramez; Navathe, Shamkant B. (2011). Fundamentals of Database Systems (6th ed.). Pearson/Addison Wesley. Chapters 8 and 9. ISBN 978-0-136-08620-8.
• Coronel, Carlos; Morris, Steven; Rob, Peter (2011). Database Systems: Design, Implementation, and Management (9th ed.). Cengage Learning. Chapter 5. ISBN 978-0-538-46968-5.
• Connolly, Thomas M.; Begg, Carolyn E. (2005). Database Systems: A Practical Approach to Design, Implementation, and Management (4th ed.). Addison-Wesley. Chapters 12 and 16. ISBN 978-0-321-21025-8.

Booklet discussing EER and implementation using object-oriented and object–relational databases:

• Dietrich, Suzanne W.; Urban, Susan D. (2011). Fundamentals of Object Databases: Object-Oriented and Object–Relational Design. Morgan & Claypool Publishers. ISBN 978-1-60845-476-1.

Textbook discussing implementation in relational and object–relational databases:

• Ricardo, Catherine (2011). Databases Illuminated (2nd ed.). Jones & Bartlett Publishers. Chapter 8. ISBN 978-1-4496-0600-8.

Shorter survey articles:

• Teorey, Toby J.; Yang, Dongqing; Fry, James P. (1986). "A logical design methodology for relational databases using the extended entity–relationship model". ACM Computing Surveys. 18 (2): 197–222. CiteSeerX 10.1.1.105.7211. doi:10.1145/7474.7475.
• Sikha Bagui (2006). "Extended Entity Relationship Modeling". In Laura C. Rivero; Jorge H. Doorn; Viviana E. Ferraggine (eds.). Encyclopedia of Database Technologies and Applications. Idea Group Inc (IGI). pp. 233–239. ISBN 978-1-59140-795-9.

External links

• [1] - Slides for chapter 8 from Fundamentals of Database Systems by Elmasri and Navathe (Pearson, 2011)
• [2] - Lecture notes from the University of Toronto
• [3] - The ER Conference