Unit 2 – Enhanced Entity Relational Model
(EER)
1. Why Do We Need an Enhanced ER Model?
The traditional ER model is powerful but limited in handling advanced real-world
scenarios. Many systems require features like inheritance, generalization, union
types, and aggregation to model complex data more accurately. The EER model
extends ER with these concepts, making it closer to object-oriented design.
Key Features of EER:
Specialization – Breaking a higher-level entity into sub-entities.
Generalization – Combining lower-level entities into a higher-level entity.
Union Types (Categories) – Representing an entity that can belong to
more than one type.
Aggregation – Treating a relationship as an entity for higher-level
abstraction.
2. From EER to Relational Model (Mapping Process)
Database systems cannot store diagrams directly, so EER diagrams must be
converted into relational schemas.
Mapping Steps:
1. Entities → Tables: Each entity becomes a table, with attributes as columns.
2. Relationships → Foreign Keys or Tables:
o 1:1 or 1:N mapped using foreign keys.
o M:N mapped using separate tables.
3. Specialization/Generalization:
o Option 1: Single table with NULLs for irrelevant attributes.
o Option 2: One table per subclass.
o Option 3: Hybrid (mix of both).
4. Aggregation: Convert relationship into an entity and then map normally.
3. SQL and Advanced Features
SQL is the standard language for interacting with relational databases.
Core SQL Components:
DDL (Data Definition Language): CREATE, ALTER, DROP.
DML (Data Manipulation Language): INSERT, UPDATE, DELETE.
DQL (Data Query Language): SELECT.
DCL (Data Control Language): GRANT, REVOKE.
Advanced SQL Features:
Views – Virtual tables based on queries.
Joins – Combine rows from multiple tables.
Constraints – Ensure data integrity (PK, FK, UNIQUE, CHECK).
Triggers – Automatic execution of code on data changes.
Stored Procedures – Precompiled sets of SQL statements.
4. File Structures (How DBMS Stores Data)
Efficient file structures improve performance.
Heap Files – Records stored randomly.
Sequential Files – Records stored in sorted order.
Hashing – Records placed in buckets using hash functions.
Indexing – Data structures (like B-trees) for fast retrieval.
5. Indexing in Databases
Indexes improve retrieval speed but may slow down inserts/updates.
Types: - Primary Index – Built on primary key. - Secondary Index – On non-key
attributes. - Clustered Index – Determines physical order of rows. - Non-
clustered Index – Separate structure pointing to data.
6. Hashing in Databases
Hashing helps with near-instant lookups. - Static Hashing: Fixed number of
buckets. - Dynamic Hashing: Buckets grow/shrink with data size.
7. Classroom Activity Ideas
Activity 1: Whiteboard exercise – Convert EER model of “University” into
relational tables.
Activity 2: SQL Lab – Implement joins, views, triggers.
Activity 3: Indexing Demo – Compare queries with and without indexes.
Activity 4: Hashing Game – Assign student IDs to buckets.
8. Example: University Database (EER → Relational)
EER Entities:
STUDENT (SID, Name, Dept)
UG_STUDENT (Year, CGPA)
PG_STUDENT (ResearchArea)
COURSE (CID, Title, Credits)
ENROLLS (SID, CID, Grade)
Relational Mapping:
STUDENT(SID [PK], Name, Dept)
UG_STUDENT(SID [PK, FK], Year, CGPA)
PG_STUDENT(SID [PK, FK], ResearchArea)
COURSE(CID [PK], Title, Credits)
ENROLLS(SID [FK], CID [FK], Grade)
9. Key Takeaways
1. EER models extend ER with specialization, generalization, union types, and
aggregation.
2. Conversion to relational schema follows systematic mapping rules.
3. SQL provides both core and advanced features for data manipulation.
4. File structures, indexing, and hashing are crucial for database efficiency.
5. Practical exercises help solidify theoretical understanding.