Department of Computer Science & Information Technology

NAME – Aryan Malkani

PRN – 23070122502
Class – SYCS A3
DBMS Lab Assignment -2
Conversion of EER Model into Relational Model
The Extended Entity-Relationship Model is a more complex and high-level model that extends
an E-R diagram to include more types of abstraction, and to more clearly express constraints.
All of the concepts contained within an E-R diagram are included in the EE-R model, along
with additional concepts that cover more semantic information. These additional concepts
include generalization, specialization, and aggregation.

Generalization is a bottom-up approach in which two lower level entities combine to form a
higher level entity. In generalization, the higher level entity can also combine with other lower

level entity to make further higher level entity.

Specialization Generalization

Specialization is opposite to Generalization. It is a top-down approach in which one higher

level entity can be broken down into two lower level entities. In specialization, some higher
level entities may not have lower-level entity sets at all.

1
 Department of Computer Science & Information Technology

Conversion process for Generalization/ Specialization into Relational Schema:

• Create tables for all higher-level entities.

• Create tables for lower-level entities.

• Add primary keys of higher-level entities in the table of lower-level entities.

• In lower-level tables, add all other attributes of lower-level entities.

• Declare primary key of higher-level table and the primary key for lower-level table.

• Declare foreign key constraints.

2
Department of Computer Science & Information Technology

3
 Department of Computer Science & Information Technology

One limitation on ER model is that it cannot express relationships among relationships.

Aggregation provides best solution for this purpose. Aggregation is a process when relation
between two entities is treated as a single entity. To represent relationship set involving
aggregation of R, treat the aggregation like an entity set whose primary key is the primary key
of the table for R.

4
Department of Computer Science & Information Technology

5
 Department of Computer Science & Information Technology

Assignment 2 Lab Questions

Theory Questions:-
Q.1 Explain the need of EER diagrams

Ans- EER (Enhanced Entity-Relationship) diagrams are an extension of the traditional Entity-
Relationship (ER) diagrams used in database design. They are particularly useful in modeling
complex database structures and relationships. Here are some reasons why EER diagrams are
needed:

1. Enhanced Modeling Capabilities: EER diagrams provide additional modeling constructs beyond
those offered by basic ER diagrams. These enhancements include concepts like subclasses,
superclasses, specialization, and generalization. This allows for more accurate and comprehensive
representation of real-world scenarios in the database design.
2. Inheritance and Generalization: EER diagrams support the concepts of inheritance and
generalization, allowing entities to be organized into hierarchies. This is especially beneficial
when dealing with entities that share common attributes but also have unique characteristics. For
example, a "Person" entity can be a superclass with subclasses like "Student" and "Employee."
3. Subtypes and Supertypes: EER diagrams allow the modeling of subtypes and supertypes,
enabling the representation of different categories of entities within a single entity set. This is
helpful when there are entities that share some common attributes but also have additional
attributes specific to their subtype.
4. Attribute Inheritance: In EER diagrams, attributes can be inherited from superclasses to
subclasses, reducing redundancy and promoting a more modular and maintainable database
design.
5. Relationship Types: EER diagrams support more advanced relationship types, such as
identifying relationships and participation constraints. Identifying relationships are used to
represent relationships where the child entity's primary key includes the primary key of the
parent entity. Participation constraints specify whether an entity is required or optional in a
relationship.
6. Complex Relationships: EER diagrams allow for the representation of complex relationships,
including recursive relationships where an entity is related to itself. This capability is essential
when modeling intricate connections within a database.
7. Enhanced Constraints: EER diagrams support additional constraints, such as the disjoint and
overlapping constraints for representing whether subtypes are mutually exclusive or can overlap.
8. Visual Clarity: The enhanced notation and symbols used in EER diagrams provide a clearer and
more expressive way to communicate complex database designs, making it easier for
stakeholders to understand the relationships and constraints involved.

6
 Department of Computer Science & Information Technology

Q.2 Explain in detail Generalization, specialization and Aggregation and how do we convert
them into relational model.
Ans - Generalization is the process of extracting common properties from a set of entities and
creating a generalized entity from it. It is a bottom-up approach in which two or more entities
can be generalized to a higher-level entity if they have some attributes in common.
In generalization, the higher level entity can also combine with other lower level entities to make
further higher level entity.

It's more like Superclass and Subclass system, but the only difference is the approach, which is
bottom-up. Hence, entities are combined to form a more generalised entity, in other words, sub-
classes are combined to form a super-class.

Specialization is opposite to Generalization. It is a top-down approach in which one higher level

entity can be broken down into two lower level entity. In specialization, a higher level entity may
not have any lower-level entity sets, it's possible. In specialization, an entity is divided into sub-
entities based on its characteristics. It is a top-down approach where the higher-level entity is
specialized into two or more lower-level entities.

Aggregation is when an ER diagram is not capable of representing the relationship between an

entity and a relationship which may be required in some scenarios. In those cases, a relationship
with its corresponding entities is aggregated into a higher-level entity. Aggregation is an
abstraction through which we can represent relationships as higher-level entity sets.

Conversion process for Generalization/ Specialization into Relational Schema:

• Create tables for all higher-level entities.

• Create tables for lower-level entities.

• Add primary keys of higher-level entities in the table of lower-level entities.

• In lower-level tables, add all other attributes of lower-level entities.

• Declare primary key of higher-level table and the primary key for lower-level table.

• Declare foreign key constraints.

7
 Department of Computer Science & Information Technology

Implementation Question:-
Q.1 Draw the EER for SIU library management and convert into relational model.

Student
lid Stud id Name Email Phone no Year Dept

Fresher
lid Prn Stud id

Senior
lid Prn Stud id memid

Faculty
lid Name Email Phone no Dept

Staff
lid e Salary Emp id

Chairperson
lid Specialization

8
 Department of Computer Science & Information Technology

Community
lid Mem id

Books
Book id Title Category Price Copies

Specialization
Spec id Spec name Book id

Q.2 Convert following EER to relational model

Student
SSN Name D.O.B GPA StartDate

Senior
SSN Major Advisor GPA

Freshman
SSN GPA

Junior
SSN Major GPA

Sophomore
SSN Major GPA
9
 Department of Computer Science & Information Technology

Employee
SSN Name D.O.B Salary Department

Secretary
SSN Salary Department

Technician
SSN Specialization Department

10