Extended ER features

Extended E-R Features: Specialization

• Top-down design process; we designate subgroupings within an entity set that are
distinctive from other entities in the set.

• The process of designating subgroupings within an entity set is called specialization.

• These subgroupings become lower-level entity sets that have attributes or participate in
relationships that do not apply to the higher-level entity set.

• Depicted by a triangle component labeled ISA (E.g., instructor “is a” person).

• Attribute inheritance – a lower-level entity set inherits all the attributes and
relationship participation of the higher-level entity set to which it is linked.
Aug 31, 2025 Dept. of I&CT 2
Extended E-R Features: Specialization
• In terms of an E-R diagram, specialization is depicted by a hollow arrow-head
pointing from the specialized entity to the other entity

• We refer to this relationship as the “ISA” relationship, which stands for “is a”
and represents, for example, that an instructor “is a” employee.

• For an overlapping specialization (as is the case for student and employee as
specializations of person), two separate arrows are used.

• For a disjoint specialization (as is the case for instructor and secretary as
specializations of employee not both at the same time), a single arrow is used.
Aug 31, 2025 Dept. of I&CT 3
Specialization/Generalization Example

Aug 31, 2025 Dept. of I&CT 4

Representing Specialization via Schemas
• Method 1:
• Form a schema for the higher-level entity
• Form a schema for each lower-level entity set, include primary key of higher-level entity set and local
attributes

schema attributes
person ID, name, street, city
student ID, tot_cred
employee ID, salary

• Drawback: getting information about, an employee requires accessing two relations, the one corresponding to
the low-level schema and the one corresponding to the high-level schema
Aug 31, 2025 Dept. of I&CT 5
Representing Specialization as Schemas
• Method 2:
• Form a schema for each entity set with all local and inherited attributes

schema attributes
person ID, name, street, city
student ID, name, street, city, tot_cred
employee ID, name, street, city, salary

• If specialization is total, the schema for the generalized entity set (person) not required to store information
• Can be defined as a “view” relation containing union of specialization relations

• Drawback: name, street and city may be stored redundantly for people who are both students and
employees

Aug 31, 2025 Dept. of I&CT 6

Extended ER Features: Generalization

• A bottom-up design process – combine a number of entity sets that share the
same features into a higher-level entity set.

• Specialization and generalization are simple inversions of each other; they are
represented in an E-R diagram in the same way.

• The terms specialization and generalization are used interchangeably.

Aug 31, 2025 Dept. of I&CT 7

Specialization and Generalization

• Can have multiple specializations of an entity set based on different features.

• E.g., permanent_employee vs. temporary_employee, in addition to instructor vs. secretary

• Each particular employee would be

• a member of one of permanent_employee or temporary_employee,

• and also, a member of one of instructor, secretary

• The “IS A” relationship also referred to as superclass - subclass relationship

Aug 31, 2025 Dept. of I&CT 8

Design Constraints on a Specialization/ Generalization

• Condition (predicate) defined: membership evaluated on the basis of, if the

entity has satisfied the condition

• User defined: designer assigns the entities

Aug 31, 2025 Dept. of I&CT 9

Design Constraints on a Specialization/ Generalization

• Constraint on whether or not entities may belong to more than one lower-level
entity set within a single generalization.
• Disjoint
• an entity can belong to only one lower-level entity set

• Noted in E-R diagram by having multiple lower-level entity sets link to the same triangle

• Overlapping
• an entity can belong to more than one lower-level entity set

Aug 31, 2025 Dept. of I&CT 10

Specialization/Generalization Example

Overlapping

Disjoint

Aug 31, 2025 Dept. of I&CT 11

Design Constraints on a Specialization/ Generalization
Completeness constraint -- specifies whether or not an entity in the higher-
level entity set must belong to at least one of the lower-level entity sets
within a generalization.
Total: an entity must belong to one of the lower-level entity sets

Partial: an entity need not belong to one of the lower-level entity sets

student must belong to either graduate or undergraduate

Employee’s Subclass are S/W Engineers and Quality Engineers

Aug 31, 2025 Dept. of I&CT 12

Aug 31, 2025 Dept. of I&CT 13
 Aggregation

 Consider the ternary relationship

proj_guide

 Recording evaluations of a student

by a guide on a project

Aug 31, 2025 Dept. of I&CT 14

Aggregation

• Without introducing redundancy, the

following diagram represents:
• A student is guided by a particular instructor on
a particular project
• A student, instructor, project combination may
have an associated evaluation
• Allows relationships between relationships

Abstract entity

Aug 31, 2025 Dept. of I&CT 15

Aggregation
• Aggregation is an abstraction through which relationships are treated as
higher-level entities. Thus, the relationship between entities and is treated as if
it were an entity .

Aug 31, 2025 Dept. of I&CT 16

Schemas Corresponding to Aggregation
• To represent aggregation, create a schema
containing

• primary key of the aggregated relationship,

• the primary key of the associated entity set

• any descriptive attributes

eval_for (s_ID, project_id, i_ID, evaluation_id)

Aug 31, 2025 Dept. of I&CT 17

• Employees work for projects.

• An employee working for a

particular project uses various
machinery.

Aug 31, 2025 Dept. of I&CT 18

• Manufacturers have tie-ups with
distributors to distribute products.

• Each tie-up has specified for it the

set of products which are to be
distributed.

Aug 31, 2025 Dept. of I&CT 19

 Design Issues

Aug 31, 2025 Dept. of I&CT 20

Use of entity sets vs. attributes

Fig A Fig B

• Use of phone as an entity allows extra information about phone numbers (plus multiple
phone numbers)
• What constitutes an attribute, and what constitutes an entity set? Unfortunately, there are
no simple answers. The distinctions mainly depend on the structure of the real-world
enterprise being modeled, and on the semantics associated with the attribute in question.
Aug 31, 2025 Dept. of I&CT 21
Use of entity sets vs. relationship sets

• One possible guideline in determining whether to use an entity set or a relationship set is to
designate a relationship set to describe an action that occurs between entities.

• This approach can also be useful in deciding whether certain attributes may be more appropriately
expressed as relationships.
Aug 31, 2025 Dept. of I&CT 22
Design Issues

• Binary versus n-ary relationship sets

• Although it is possible to replace any nonbinary (n-ary, for n > 2) relationship set by a
number of distinct binary relationship sets
• a n-ary relationship set shows more clearly that several entities participate in a single
relationship.

Aug 31, 2025 Dept. of I&CT 23

Binary Vs. Non-Binary Relationships

• Some relationships that appear to be non-binary may be better represented using binary
relationships
• E.g., A ternary relationship parents, relating a child to his/her father and mother, is best
replaced by two binary relationships, father and mother
• Using two binary relationships allows partial information (e.g., only mother being know)

• But there are some relationships that are naturally non-binary

• Example: proj_guide

Aug 31, 2025 Dept. of I&CT 24

Converting Non-Binary Relationships to Binary Form
• By creating an artificial entity set.

• Replace R between entity sets A, B and C by an entity set E, and three relationship sets:
1. RA, relating E and A
2. RB, relating E and B
3. RC, relating E and C

• Create a special identifying attribute for E

• Add any attributes of R to E

• For each relationship (ai , bi , ci) in R, create

1. a new entity ei in the entity set E
2. add (ei , ai ) to RA
3. add (ei , bi ) to RB
4. add (ei , ci ) to RC
Aug 31, 2025 Dept. of I&CT 25
Converting Non-Binary Relationships
• Also need to translate constraints
• Translating all constraints may not be possible

• There may be instances in the translated schema that cannot correspond to any instance
of R
• Exercise: add constraints to the relationships RA, RB and RC to ensure that a newly created entity
corresponds to exactly one entity in each of entity sets A, B and C

• We can avoid creating an identifying attribute by making E a weak entity set identified
by the three relationship sets

Aug 31, 2025 Dept. of I&CT 26

Design issue
• Placement of relationship attributes

e.g., attribute date as attribute of advisor or as attribute of student

• The design decision of where to place descriptive attributes in such cases —as a relationship
or entity attribute—should reflect the characteristics of the enterprise being modeled.

• The designer may choose to retain date as an attribute of advisor to express explicitly that
the date refers to the advising relationship and not some other aspect of the student’s
university status

Aug 31, 2025 Dept. of I&CT 27

Aug 31, 2025 Dept. of I&CT 28