entity relationship (eer) model
TRANSCRIPT
ENHANCED ENTITY RELATIONSHIP (EER) MODEL
1
Instructor: Faisal Anwer and Mohammad Nadeem
SOURCE:Fundamentals of Database Systems (6th Edition) by Ramez Elmasi, Shamkant B. Navathe + Database System Concepts (English) 6th Edition by Abraham Silberschatz , S. Sudarshan , Henry F. Korth + Freely Accessible Web Resources
EER MODEL
ER modelling is usually sufficient for most of the database
applications.
However, new database requirements are emerging and so,
enhancements in existing ER modelling are required.
ER model, when incorporates new concepts, is called
Enhanced ER model.
The major enhancements are:
Superclass/Subclass relationship
Specialization
Generalization
Category
2
SUPERCLASS/SUBCLASS RELATIONSHIP
In many cases an entity type has numerous subgroupings or
subtypes of its entities that are meaningful.
They need to be represented explicitly because of their
significance to the database application.
Example:
EMPLOYEE could be PERMANENT or TEMPORARY.
STUDENT could be UNDERGRADUATE or
POSTGRADUATE.
ACCOUNT could be SAVING, CURRENT or DMAT.
3
EXAMPLES
4
CONCEPTS ABOUT SUPERCLASS/SUBCLASS
Reasons for using superclass/subclass:
certain attributes may apply to some but not all entities
of the superclass
some relationship types may be participated in only by
entities that are members of the subclass
Every instance of any subclass is also a member of its
superclass.
Relationship between a superclass and any one of its
subclasses is called class/subclass relationship (also called
IS-A relationship).
A subclass inherits all the attributes and relationships of
its superclass.
A subclass can have relationship types with other entities,
independently of the superclass.5
SPECIALIZATION
Specialization is the process of defining a set of subclasses
of an entity type; this entity type is called the superclass of
the specialization.
6
SPECIALIZATION
Usually, specialization is described by a defining attribute.
Example:
Job type (SECRETARY, ENGINEER, TECHNICIAN)
Method of pay (SALARIED, HOURLY)
One entity can have many specializations.
Attributes of subclass are called specific or local
attributes.
Relationship types of subclass are called specific
relationship types.
7
SUMMARY ABOUT SPECIALIZATION
Specialization process allows us to do the following:
Define a set of subclasses of an entity type
Establish additional specific attributes with each
subclass
Establish additional specific relationship types between
each subclass and other entity types or other subclasses
8
GENERALIZATION
Generalization process can be viewed as being functionally
the inverse of the specialization process.
We suppress the differences among several entity types,
identify their common features, and generalize them into a
single superclass of which the original entity types are
special subclasses.
9
GENERALIZATION
10
CONSTRAINTS ON SPECIALIZATION AND
GENERALIZATION
A specialization may result in one or more subclasses.
Predicate defined subclasses:
Subclasses are usually identified by a condition on the value
of some attribute of the superclass.
Such subclasses are called predicate-defined (or
condition-defined) subclasses.
Example:
(Job_type = ‘Secretary’)
This condition is a constraint specifying that exactly
those entities of the EMPLOYEE entity type whose
attribute value for Job_type is ‘Secretary’ belong to the
subclass.
11
CONSTRAINTS ON SPECIALIZATION AND
GENERALIZATION
If all subclasses are based on the same attribute of the
superclass, the specialization itself is called an attribute-
defined specialization.
such an attribute is called the defining attribute.
When we do not have a condition for determining membership
in a subclass, the subclass is called user-defined.
12
CONSTRAINTS ON SPECIALIZATION AND
GENERALIZATION
Disjointness constraint:
An attribute-defined specialization implies the disjointness
constraint.
When subclasses are disjoint, an entity can be a member of at
most one of the subclasses of the specialization.
If the subclasses are not constrained to be disjoint, their sets
of entities may be overlapping.
13
DISJOINTNESS AND OVERLAPPING CONSTRAINT
14
DisjointOverlapping
Completeness constraint:
It is a constraint on the belongingness of superclass
member in subclasses.
It could be total or partial:
Total: every entity in the superclass must be a
member of at least one subclass in the specialization.
Generalization process is usually total.
Partial: an entity may not belong to any subclass.
15
CONSTRAINTS ON SPECIALIZATION AND
GENERALIZATION
TOTAL AND PARTIAL CONSTRAINT
16
Disjoint & PartialOverlapping & Total
CONSTRAINTS ON SPECIALIZATION AND
GENERALIZATION
Disjointness and Completeness constraints are
independent.
Hence, four possible constraints on specialization:
Disjoint, total
Disjoint, partial
Overlapping, total
Overlapping, partial
These constraints create certain insertion/deletion rules.
Deleting from a superclass deletion from all the subclasses
Inserting in a superclass insertion in corresponding subclasses
Superclass of a total specialization implies that the entity is
mandatorily inserted in at least one of the subclasses. 17
SPECIALIZATION AND GENERALIZATION
HIERARCHIES
A specialization hierarchy (single inheritance) has the
constraint that every subclass can have only one superclass.
18
SPECIALIZATION AND GENERALIZATION
LATTICES
For a specialization lattice (multiple inheritance), a
subclass can belong to more than one superclasses.
Similar concepts are applicable on generalization as well. 19
UNION TYPES USING CATEGORIES
When a single subclass is modelled using more than one
superclasses, usually of different entity types, such a
subclass is called a union type or a category.
20
UNION TYPES USING CATEGORIES
A category is a subset of the union of its superclasses.
A cateogory inherits the attributes depending on the
superclass to which the entity belongs.
A category does not necessarily includes all instances of its
superclasses.
A category can be total or partial.
total category holds the union of all entities in its
superclasses.
a partial category can hold a subset of the union.
21
RELATIONAL DATABASE DESIGN BY EER
DIAGRAM
Step 8: Mapping of Specialization or Generalization:
For Ways:
8A: Multiple relations- Superclass and Subclasses:
Works for any specialization (total or partial, disjoint or
overlapping)
Useful when both common and specific attributes are many.
22
RELATIONAL DATABASE DESIGN BY EER
DIAGRAM
8B: Multiple relations- Superclass and Subclasses:
Works only when specialization is total.
If specialization is disjoint, the common entity would
result in redundant storage of attributes.
Useful when common attributes are few.
23
RELATIONAL DATABASE DESIGN BY EER
DIAGRAM
8C: Single relation with one type attribute:
Works only when specialization is disjoint.
Useful when specific attributes are few.
24
Job_type is the type attribute
RELATIONAL DATABASE DESIGN BY EER
DIAGRAM
8C: Single relation with multiple type attributes:
Used when specialization is overlapping (but also works for
disjoint scenario).
Includes m boolean type attribites, one for each subclass
Useful when specific attributes are few.
25
Mflag and Pflag are boolean type attributes
RELATIONAL DATABASE DESIGN BY EER
DIAGRAM
Mapping of Shared Subclasses (Lattice):
Depending upon the restrictions, any of the previous methods
(8A, 8B, 8C and 8D) could be applied.
8A can be applied anytime.
26
RELATIONAL DATABASE DESIGN BY EER
DIAGRAM
Step 9: Mapping of Categories (Union Types):
A new attribute, called surrogate key, is used to handle
different keys.
For a category whose superclasses have the same key, there is
no need for a surrogate key.
27
GATE-2005
28
GATE-2005
29
GATE-2011
30
GATE-2011
31