Enhanced Entity-Relationship (EER) Modeling is an extension of the basic Entity-
Relationship (ER) model used in database design. It adds more powerful concepts
to represent complex real-world scenarios more accurately.
Below is a comprehensive, structured explanation suitable for lecture notes or a 3-
hour course.
1. What is EER Modeling?
EER (Enhanced Entity-Relationship) Model extends the traditional ER model by
including:
• Inheritance (Superclass–Subclass)
• Specialization & Generalization
• Categories (Union Types)
• Aggregation (Advanced abstraction)
It is mainly used in conceptual database design before implementing in systems
like MySQL.
2. Basic ER Concepts (Quick Review)
Before EER, recall ER basics:
✔ Entity
• Real-world object (Student, Course)
✔ Attribute
• Properties of entity (Name, ID)
✔ Relationship
• Association between entities (Enrolls, WorksFor)
✔ Keys
• Primary Key (PK)
• Foreign Key (FK)
3. Why EER Model?
The ER model is not sufficient when:
• Entities have hierarchies
• Some entities share common attributes
• There are complex constraints
Example:
• Person → Student, Employee
• A Car can be owned by a Person OR a Company
4. Superclass and Subclass (Inheritance)
✔ Definition
• Superclass: General entity
• Subclass: Specialized entity
✔ Example:
• Person (Superclass)
o Student (Subclass)
o Employee (Subclass)
✔ Characteristics:
• Subclass inherits attributes from superclass
• Subclass may have additional attributes
Example Structure
Person(PersonID, Name, Address)
|
|--- Student(StudentID, Major)
|--- Employee(EmployeeID, Salary)
5. Specialization vs Generalization
✔ Specialization (Top-Down)
• Start with a general entity → divide into subclasses
Person → Student, Employee
✔ Generalization (Bottom-Up)
• Combine multiple entities → one superclass
Car, Truck → Vehicle
Key Difference
Concept Direction
Specialization Top-down
Generalization Bottom-up
6. Constraints in Specialization
✔ 1. Disjoint Constraint
• An entity can belong to only ONE subclass
Example:
• A person is either Student OR Employee
✔ 2. Overlapping Constraint
• An entity can belong to multiple subclasses
Example:
• A person can be BOTH Student AND Employee
✔ 3. Total Participation
• Every entity MUST belong to a subclass
✔ 4. Partial Participation
• Some entities may NOT belong to any subclass
Representation
• d → Disjoint
• o → Overlapping
• Double line → Total
• Single line → Partial
7. Attribute Inheritance
Subclass inherits:
• Attributes
• Relationships
Example:
• Student inherits Name from Person
8. Categories (Union Types)
✔ Definition:
A subclass that comes from multiple superclasses
Example:
• Owner = Person OR Company
Representation:
Person Company
\ /
\ /
Owner
Owner is a category (union type)
9. Aggregation
✔ Definition:
Treat a relationship as an entity
Used when:
• A relationship needs to participate in another relationship
✔ Example:
• Employee works_on Project
• Manager supervises this work
"works_on" becomes an entity
10. EER Diagram Symbols
Symbol Meaning
Rectangle Entity
Oval Attribute
Diamond Relationship
Triangle ISA (Inheritance)
Double line Total participation
Circle (d/o) Constraints
11. Mapping EER to Relational Model
✔ 1. Superclass Table
Person(PersonID PK, Name, Address)
✔ 2. Subclass Table
Student(StudentID PK, Major,
FK → Person(PersonID))
✔ 3. Options for Mapping
Approach Description
Single Table All attributes in one table
Multiple Tables Separate tables
Hybrid Combination
12. Practical Example (University Database)
✔ Entities:
• Person
• Student
• Instructor
• Course
✔ Relationships:
• Student enrolls in Course
• Instructor teaches Course
✔ EER Features:
• Person → Student, Instructor (Inheritance)
• Student ↔ Course (M:N)
• Instructor → Course (1:N)
13. Advantages of EER Model
✔ More expressive
✔ Handles complex systems
✔ Reduces redundancy
✔ Better conceptual clarity
14. Disadvantages
More complex diagrams
Harder to implement
Requires experience
15. Real-Life Applications
• University systems
• Banking systems
• Hospital databases
• E-commerce platforms
16. Common Mistakes
⚠ Mixing specialization and generalization
⚠ Incorrect constraints (d/o)
⚠ Missing keys
⚠ Overcomplicating design
17. Summary
EER Modeling extends ER by adding:
• Inheritance (Superclass/Subclass)
• Specialization / Generalization
• Constraints (Disjoint, Overlapping)
• Categories
• Aggregation
It is essential for complex database design.
Below is a clear, step-by-step guide to create an EER Diagram in MySQL Workbench,
tailored for teaching or hands-on practice.
1. Start MySQL Workbench & Create Model
7
Steps:
1. Open MySQL Workbench
2. Click Database → New Model
3. Click Add Diagram (or “+” icon)
You are now in the EER Diagram canvas
2. Add Tables (Entities)
Here’s a step-by-step mini guide on how to set PK, NN, UQ, AI, BIN, and ZF in MySQL
Workbench. I’ll describe it clearly so you can follow along even without
screenshots, and I’ll explain what each step does.
Step 1: Open Your Table in MySQL Workbench
1. Open MySQL Workbench and connect to your database.
2. Go to Schemas → choose your database → right-click Tables → Create Table…
3. The table editor will open with a grid listing columns and their properties.
Step 2: Add Columns
1. Click “+” to add a new column.
2. Enter the column name (e.g., StudentID) and data type (e.g., INT or
VARCHAR(50)).
Step 3: Set PK (Primary Key)
1. In the PK column checkbox for your chosen column, tick it.
2. This column will now uniquely identify each row and cannot be NULL.
Step 4: Set NN (Not Null)
1. Tick the NN checkbox for columns that must have a value.
2. Example: Name or Email should usually be NN.
Step 5: Set UQ (Unique)
1. Tick the UQ checkbox to make a column unique.
2. Example: Email should not be repeated across rows.
3. Note: PK columns are automatically Unique.
Step 6: Set AI (Auto Increment)
1. Tick the AI checkbox for numeric PK columns if you want them to auto-
increment.
2. Example: StudentID → first row = 1, second = 2, etc.
Step 7: Set BIN (Binary / Case Sensitive)
1. For string columns (CHAR, VARCHAR, TEXT), click the Datatype → choose
Binary collation or append BINARY to type.
o Example: VARCHAR(50) BINARY
2. This ensures case-sensitive comparisons (abc != ABC).
Step 8: Set ZF (Zero Fill)
1. For numeric columns (INT, BIGINT, etc.), tick ZF.
2. Enter a display width in the type field (e.g., INT(5)), then enable ZF.
3. Numbers will be displayed with leading zeros: 42 → 00042.
Step 9: Apply Changes
1. Click Apply → Workbench will generate the CREATE TABLE SQL script.
2. Review the SQL → click Apply → your table is created with all the chosen flags.
Tip:
Here’s how a final table might look in Workbench:
Column Type PK NN UQ AI BIN ZF
StudentID INT(5) ✓ ✓ ✓ ✓ ✓
Email VARCHAR(50) ✓ ✓
Code VARCHAR(10) BINARY ✓ ✓
Name VARCHAR(50) ✓
Steps:
1. Click “Place a New Table” tool
2. Click anywhere on canvas
3. Double-click the table
Example: Create Person
Column Name Type Key
PersonID INT PK
Name VARCHAR(45)
Address VARCHAR(100)
Set:
• ✔ Primary Key (PK)
• ✔ Auto Increment (optional)
3. Create Subclass Tables (EER Concept)
6
Create:
✔ Student Table
• StudentID (PK, FK → [Link])
• EnrollmentDate
• Major
✔ Employee Table
• EmployeeID (PK, FK → [Link])
• Salary
Important:
• Subclass PK = Superclass PK
• Also acts as Foreign Key
4. Create Relationships
5
Steps:
1. Select 1:N Relationship Tool
2. Click Parent table (Person)
3. Click Child table (Student)
This automatically:
• Creates FK
• Connects tables visually
5. Define Foreign Keys Manually (If Needed)
Steps:
1. Double-click Student table
2. Go to Foreign Keys tab
3. Add:
o Column: StudentID
o Referenced Table: Person
o Referenced Column: PersonID
6. Create M:N Relationship (Important Example)
4
Example:
Student ↔ Course
Steps:
1. Create Course table
2. Use N:M relationship tool
3. Click Student → Course
Workbench automatically creates:
Student_has_Course
Modify it:
• Rename to Enrollment
• Add attributes:
o Grade
o Semester
7. Add Attributes to Relationships
Open Enrollment table:
• Add:
o Grade (VARCHAR)
o Date (DATE)
8. Use EER Features (Conceptual Design Tips)
⚠ MySQL Workbench does NOT directly show:
• ISA triangle
• Disjoint/Overlapping symbols
You simulate EER using:
• Tables + FK constraints
✔ How to Represent EER Concepts
EER Concept MySQL Implementation
Inheritance PK = FK
Disjoint Application logic
Total participation NOT NULL FK
Category Multiple FK options
9. Arrange Diagram (Important for Teaching)
7
Tips:
• Put Person at top
• Subclasses below
• Relationships in middle
• Avoid crossing lines
10. Forward Engineer (Generate SQL)
Steps:
1. Click Database → Forward Engineer
2. Select options
3. Execute
This generates SQL like:
CREATE TABLE Person (
PersonID INT PRIMARY KEY,
Name VARCHAR(45),
Address VARCHAR(100)
);
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
EnrollmentDate DATE,
Major VARCHAR(45),
FOREIGN KEY (StudentID) REFERENCES Person(PersonID)
);
11. Common Problems (VERY IMPORTANT)
FK not appearing
✔ Use relationship tool (not manual typing)
N:M not visible
✔ Use correct many-to-many tool
Cannot add table
✔ Ensure you are in EER Diagram, not SQL editor
Toolbar missing
✔ Enable via:
• View → Panels → Model Overview
12. Mini Example Structure (Final Diagram)
Person
├── Student
├── Employee
Student ──< Enrollment >── Course
Instructor ── teaches ── Course
Full University DB example
Here is a complete University Database (EER → MySQL implementation) that you
can directly use for teaching, demos, or MySQL Workbench.
1. Conceptual EER Design (Overview)
7
✔ Main Concepts
Entities:
• Person (Superclass)
• Student (Subclass)
• Instructor (Subclass)
• Course
• Department
• Enrollment (Associative Entity)
2. EER Structure (Important)
✔ Inheritance (EER)
Person
├── Student
└── Instructor
✔ Relationships
• Student ↔ Course → Enrollment (M:N)
• Instructor → Course → Teaches (1:N)
• Department → Course (1:N)
3. Relational Schema Mapping
✔ Tables:
Table Description
Person Base entity
Student Subclass
Instructor Subclass
Department Academic units
Table Description
Course Courses
Enrollment M:N relation
4. FULL SQL IMPLEMENTATION
You can paste this directly into MySQL Workbench
CREATE DATABASE UniversityDB;
USE UniversityDB;
-- 1. Superclass
CREATE TABLE Person (
PersonID INT PRIMARY KEY,
Name VARCHAR(50),
Email VARCHAR(100),
Address VARCHAR(100)
);
-- 2. Student (Subclass)
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
EnrollmentDate DATE,
Major VARCHAR(50),
FOREIGN KEY (StudentID) REFERENCES Person(PersonID)
);
-- 3. Instructor (Subclass)
CREATE TABLE Instructor (
InstructorID INT PRIMARY KEY,
HireDate DATE,
Salary DECIMAL(10,2),
FOREIGN KEY (InstructorID) REFERENCES Person(PersonID)
);
-- 4. Department
CREATE TABLE Department (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(50)
);
-- 5. Course
CREATE TABLE Course (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(50),
Credits INT,
DeptID INT,
InstructorID INT,
FOREIGN KEY (DeptID) REFERENCES Department(DeptID),
FOREIGN KEY (InstructorID) REFERENCES Instructor(InstructorID)
);
-- 6. Enrollment (M:N relationship)
CREATE TABLE Enrollment (
StudentID INT,
CourseID INT,
Semester VARCHAR(20),
Grade VARCHAR(2),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);
5. SAMPLE DATA (For Demo)
-- Persons
INSERT INTO Person VALUES
(1,'Ali Yılmaz','ali@[Link]','Istanbul'),
(2,'Ayşe Demir','ayse@[Link]','Ankara'),
(3,'Dr. Smith','smith@[Link]','Izmir');
-- Students
INSERT INTO Student VALUES
(1,'2023-09-01','Computer Engineering'),
(2,'2022-09-01','Mathematics');
-- Instructor
INSERT INTO Instructor VALUES
(3,'2020-01-15',15000);
-- Department
INSERT INTO Department VALUES
(10,'Engineering'),
(20,'Science');
-- Course
INSERT INTO Course VALUES
(100,'Database Systems',4,10,3),
(200,'Calculus',3,20,3);
-- Enrollment
INSERT INTO Enrollment VALUES
(1,100,'Fall 2024','AA'),
(2,200,'Fall 2024','BB');
6. Example Queries (VERY IMPORTANT)
✔ 1. List all students with their courses
SELECT [Link], [Link]
FROM Student s
JOIN Person p ON [Link] = [Link]
JOIN Enrollment e ON [Link] = [Link]
JOIN Course c ON [Link] = [Link];
✔ 2. Instructor teaching courses
SELECT [Link], [Link]
FROM Instructor i
JOIN Person p ON [Link] = [Link]
JOIN Course c ON [Link] = [Link];
✔ 3. Students with grades
SELECT [Link], [Link], [Link]
FROM Enrollment e
JOIN Student s ON [Link] = [Link]
JOIN Person p ON [Link] = [Link]
JOIN Course c ON [Link] = [Link];
7. How to Draw This in MySQL Workbench
Steps Summary:
1. Create all tables
2. Define PKs
3. Add FKs:
o Student → Person
o Instructor → Person
o Course → Department, Instructor
o Enrollment → Student, Course
4. Use:
o 1:N tool (Department → Course)
o N:M tool (Student ↔ Course)
8. EER Concepts Used (Mapping)
EER Concept Implementation
Inheritance Student & Instructor use PK=FK
M:N Relationship Enrollment table
1:N Relationship Department → Course
Attribute in Relationship Grade, Semester
9. Extensions (Advanced Topics)
You can extend this model with:
✔ Classroom
✔ Schedule (Time, Room)
✔ Prerequisites (recursive relationship)
✔ Advisor (Instructor → Student)
Scenario: University Management System
We want to model a university system that keeps track of students, faculty,
courses, and departments. Some additional complexity is added using EER
concepts.
1. Entities and Attributes
Entity Attributes
Person PersonID (PK), Name, DateOfBirth, Address
Student StudentID (PK, FK from Person), EnrollmentDate, Major
Faculty FacultyID (PK, FK from Person), HireDate, Rank
Course CourseID (PK), CourseName, Credits
Department DeptID (PK), DeptName, Office
Note: Here Student and Faculty are subclasses of Person (specialization).
2. Relationships
Relationship Entities Involved Type Attributes
EnrollsIn Student ↔ Course Many-to-Many Grade
Teaches Faculty ↔ Course One-to-Many Semester
BelongsTo Person ↔ Department Many-to-One None
Advises Faculty ↔ Student One-to-Many None
3. Specialization / Generalization
• General Entity: Person
• Special Entities: Student, Faculty
• Constraint: Each person must be either a student or faculty (total
specialization)
• Disjointness: A person cannot be both student and faculty (disjoint)
Diagram Representation:
Person
/ \
Student Faculty
4. Category (Union Type Example)
Suppose we have a Staff entity that can be either Faculty or AdminStaff.
• Category Entity: Staff
• Subclasses: Faculty, AdminStaff
• Constraint: Each staff member is either faculty or admin staff.
Staff (Category)
/ \
Faculty AdminStaff
5. Sample EER Diagram
Here’s a textual representation:
[Person]
|
----------------
| |
[Student] [Faculty]
| |
|EnrolledIn |Teaches
| |
[Course]---------<BelongsTo>----[Department]
• EnrollsIn is M:N with attribute Grade
• Teaches is 1:N
• BelongsTo is N:1
6. MySQL Implementation
CREATE TABLE Person (
PersonID INT PRIMARY KEY,
Name VARCHAR(100),
DateOfBirth DATE,
Address VARCHAR(255)
);
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
EnrollmentDate DATE,
Major VARCHAR(50),
FOREIGN KEY (StudentID) REFERENCES Person(PersonID)
);
CREATE TABLE Faculty (
FacultyID INT PRIMARY KEY,
HireDate DATE,
Rank VARCHAR(50),
FOREIGN KEY (FacultyID) REFERENCES Person(PersonID)
);
CREATE TABLE Department (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(50),
Office VARCHAR(50)
);
CREATE TABLE Course (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(100),
Credits INT,
DeptID INT,
FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);
CREATE TABLE EnrollsIn (
StudentID INT,
CourseID INT,
Grade CHAR(2),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);
CREATE TABLE Teaches (
FacultyID INT,
CourseID INT,
Semester VARCHAR(10),
PRIMARY KEY (FacultyID, CourseID),
FOREIGN KEY (FacultyID) REFERENCES Faculty(FacultyID),
FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);