Chapter 4
Chapter 4
Chapter 4
1
database applications
• traditional
– database processing applications in business and industry
• newer applications
– CAD/CAM
– telecommunications
– images and graphics
– multimedia
– data mining
– data warehousing
– GIS
– databases for indexing the World Wide Web
– ...
2
Enhanced ER (EER) model
3
4.1 subclass attributes
• entity type (a type of entity)
relationships
– e.g., EMPLOYEE
• entity set (collection of entities of that type)
– e.g., current set of EMPLOYEE entities
• subclass (vs superclass)
– e.g., SECRETARY, ENGINEER, TECHNICIAN, MANAGER,
SALARIED_EMPLOYEE, HOURLY_EMPLOYEE
• class/subclass relationship
‧An entity must also be a member
– e.g., EMPLOYEE/SECRETARY
of the superclass
• An entity type has numerous subgroupings of ‧An entity can be a member of any
its entities that are meaningful and need to be number of subclasses
represented explicitly because of their ‧It is not necessary that every
significance to the database application. entity in a superclass be a member
of some subclasses.
•We call this subgroupings a subclass of the
given entity type. The entity type is called the
4
superclass of the subclass
subclass (continued)
• Implementation
– A member entity of the subclass represents the same real‐world
entity as some member of the superclass
• the same entity but in specific role
– A distinct record that is related via the key attribute to its
superclass entity
5
4.2 Specialization and Generalization
• 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. The set of subclasses that form a specialization is
defined on the basis of some distinguishing characteristic of the
entities in the superclass.
6
Specialization (continued)
• Fig 4.1 represents the EER diagram of
specialization. The subclasses that define a
specialization are attached by lines to a
circle, which is connected to the superclass.
7
Figure 4.1 EER diagram for representing specialization an d subclasses.
EMPLOYEE
completeness constraint: completeness constraint :
partial specialization total specialization
subset
disjointness symbol disjointness
constraint constraint specific
specific subset d disjoint
(local) symbol d attributes
attributes subset
Typing Speed TGrade EngType
symbol Pay Scale
SALARIED_EMPLOYEE
.e
.e 1
EMPLOYEE .e 4
e1 .
e2 .
e3 . ENGINEER
e4 . .e
e5 . .e 2
e6 . 7
e7 .
e8 .
TECHNICIAN
.e
Figure 4.2 Some instances of the spcialization of EMPLOYEE
into the {SECRERARY, ENGINEER, TECHNICIAN}
set of subclasses.
.e 3
10
Specialization process
11
Generalization
• Define a generalized entity type from
the given entity types
NoOfPassengers NoOfAxles
Price Price
MaxSpeed Tonnage
CAR TRUCK
Vehicleld Vehicleld
LicensePlateNO LicensePlateNo
v1
VEHICLE
v1 v2
v2 v3
v3 v4
v4 d v5
NoOfPassengers v5 v6 NoOfAxles
MaxSpeed v7
Tonnage
CAR v3 v3 TRUCK
v1 v1 v4 v4
v2 v2 v5 v5
Figure 4.3 Examples of generalization. (a) Two entity types CAR and TRUCK.
(b) Generalizing CAR and TRUCK into VEHICLE. 13
4.3 Constraints on
4.3 Specialization and Generalization
• single subclass only EMPLOYEE
– {MANAGER} specification
• predicate‐defined subclasses MANAGER
(condition-defined)
– determined by a condition
– (JobType = ‘Secretary’) <‐‐‐ defining predicate
– attribute‐defined specialization (see Figure 4.4 at 4‐14)
• membership condition on the same attribute of the superclass (defining
attribute)
• user‐defined subclass
– determined by the database users
– {HOURLY_EMPLOYEE, SALARIED_EMPLOYEE}
Membership is specified
14
individually for each entity.
• In some specializations we can determine exactly the
entities that will become members of each subclass by
placing a condition on the value of some attribute of the
superclass. Such subclasses are called predicate‐defined (or
condition‐defined) subclasses.
• We display a predicate‐defined subclass by writing the
predicate condition next to the line that connects the
subclass to the specialization circle.
• If all subclasses in a specialization have the membership
condition on the same attribute of the superclass, the
specialization itself is called an attribute‐defined
specialization, and the attribute is called the defining
attribute of the specialization
• We displayed by placing the defining attribute name next to
the arc from the circle to the superclass.
• When we do not have a condition for determining
membership in a subclass, the subclass is called user‐
defined. 15
FName Minit LName
EMPLOYEE
d
“Secretary” “Engineer” predicate condition
16
Constraints (continued)
• disjointness constraint
– specifies that the subclasses of the specialization must be
disjoint.
– an entity can be a member of at most one of the subclasses of
the specialization
– attribute‐defined specialization ‐‐>
defining attribute is singled‐valued
• d : disjoint for attribute/user‐defined subclass
• Overlapping constraint
– : an entity may be a member of more than one subclass of a
specialization
– An entity can be a member of at least one of the subclasses of
the specialization.
17
PartNo Description
PART
o
overlap
ManufactureDate
SupplierName
MANUFACTURED_PART PURCHASED_PART
18
Constraints (continued)
• completeness constraint (4‐7)
– total specialization constraint
• every entity in the superclass must be a member of some
subclass in the specialization
• e.g., {HOURLY_EMPLOYEE, SALARIED_EMPLOYEE}
• notation: superclass
19
Constraints (continued)
20
Some insertion/deletion rules for
specialization/generalization
• Deleting an entity from a superclass
– it is automatically deleted from all the subclasses to which it
belongs
• Inserting an entity in a superclass
– it is mandatorily inserted in all predicate‐defined (or attribute‐
defined) subclasses for which it satisfies the defining predicate
• Inserting an entity in a superclass of a total specialization
– it is mandatorily inserted in at lease one of subclasses
21
Specialization/Generalization
Hierarchies and Lattices
• A subclass itself may have further subclasses
specified on it.
– Specialization hierarchy
• every subclass participates as a subclass
in only one class/subclass relationship
– Specialization lattice
• a subclass can be a subclass in more than
one class/subclass relationship
22
Figure 4.6 Specialization lattice with the shared subclass EMGINEERING_MANAGER..
EMPLOYEE
e1
e2
e3
e4
e5
e6
d e7
e8 d
e9
e10
SECRETARY TECHNICIAN ENGINEER MANAGER
HOURLY_EMPLOYEE
e1 e3 e5 e8 SALARIED_EMPLOYEE
e2 e4 e6 e9 e1
e6 e2
e7
e7 e3
e8
ENGINEERING_MANAGER lattice e8 e4
e8 shared subclass e9 e5
e10
multiple inheritance
23
Figure 4.7 Specialization lattice for a Name Sex Address
UNIVERSITY database.
SSN PERSON BirthDate
P1 P6
An entity may exist in several
P2 P7 leaf nodes of the hierarchy
P3 P8
P4 P9
e.g. GRADUATE_STUDENT
P5 P10
o RESEARCH ASSISTANT
Degrees
STUDENT_
STAFF FACULTY ASSISTANT GRADUATE_ UNDERGRADUATE_
STUDENT STUDENT
shared subclass
Position Rank multiple inheritance DegreeProgram
d Class
(inherited only once)
leaf node
Project Course
RESEARCH_ASSISTANT TEACHING_ASSISTANT
25
Specialization/Generalization
in Conceptual Data Modeling
• top‐down conceptual refinement process
– a specialization process
• bottom‐up conceptual synthesis
– a generalization process
• combination
26
Categories (UNION TYPES)
• All of the superclass/subclass relationships we have seen thus far
have a single superclass
• A shared subclass is subclass in more than one distinct
superclass/subclass relationships, where each relationships has a
single superclass (multiple inheritance)
• In some cases, need to model a single superclass/subclass
relationship with more than one superclass
• Superclasses represent different entity types
• Such a subclass is called a category or UNION TYPE
• Example: Database for vehicle registration, vehicle owner can be a
person, a bank (holding a lien on a vehicle) or a company.
– Category (subclass) OWNER is a subset of the union of the three
superclasses COMPANY, BANK, and PERSON
– A category member must exist in at least one of its superclasses
• Note: The difference from shared subclass, which is subset of the
intersection of its superclasses (shared subclass member must exist
in all of its superclasses).
BName BAddress
BANK
SSN Name Address CName CAddress
b1
DriverLicenseNo
PERSON b2 COMPANY c1
P1 b3 c2
P2 u
P3 set union operation
P4 P1
Figure 4.8 Two categories: OWNER P2 OWNER
and REGISTERED_VEHICLE.
b1
LienOrRegular
c1
M PurchaseDate
OWNS
N
LicensePlateNo
REGISTERED_VEHICLE
c1
c2
CYear
CModel
u t1 TYear
CMake TMake
set TModel
CStyle
c1 union Tonnage
CAR TRUCK
Vehicleld c2 operation Vehicleld
t1 28
c3
t2
Only cars & trucks can be Partial:
Category vs. shared subclass members of VEHICLE may contain
REGISTERED_VEHICLE other types of entities
• intersection Category VS. generalized superclass
REGISTERED_VEHICLE VEHICLE
– shared subclass (Fig. 4.6, 4‐21) (Fig. 4.8, 4.25) (Fig. 4.3, 4-12)
– ENGINEERING_MANAGER is a subset of the intersection of ENGINEER,
MANAGER, and SALARIED_EMPLOYEE
– an engineering manager must be an ENGINEER, a MANAGER, and a
SALARIED_EMPLOYEE
– ENGINEERING_MANAGER inherits all the attributes of its superclasses
• union
– category (Fig. 4.8, 4‐25)
– OWNER is a subset of the union of COMPANY, a BANK, or a person
– an OWNER may be a COMPANY, a BANK, or a PERSON
– an OWNER entity inherits the attributes depending on the superclass to
which the entity belongs
29
A category can be total or partial.
Figure 4.9 Categories. (a) Partial category
ACCOUNT_HOLDER that is a
(a) subset of the union of two entity
COMPANY PERSON
types COMPANY and PERSON.
(b) Total category PROPERTY
c1 P1 and a similar generalization.
c2 C1 C2 P2
c3 predicate conditions
c4 u
partial
c1 ACCOUNT_ HAS_ BANK
HOLDER ACCT
c2
P1
(b) specialization/
BUILDING LOT PROPERTY
generalization
total category b1 l1 total
b2 l2
d
b3 u
total
b1 l1 PROPERTY BUILDING LOT
b2 l2 30
b3
An Example UNIVERSITY
EER Schema
31
FName Minit LName Ssn BDate Sex No Street AptNo City State Zip
FPhone FACULTY
Class
1 ADVISOR N Degree Year
College
STUDENT
M N Degrees
COMMITTEE
1
P1 GRAD_STUDENT
N Title No
GRANT Agency
M StDate
BELONGS Start u M
1
N
Time
SUPPORT N
CHAIRS REGISTERED
End MINOR
N
1 N
INSTRUCTOR_RESEARCHER 1 MAJOR
1 Grade M
1 TRANSCRIPT
TEACH N CURRENT_SECTION N
Sec#
DEPARTMENT SECTION Year
N
Qtr
DName DPhone Office CS
1
1
N
COLLEGE 1 CD DC COURSE
N
COffice Dean C# CName Cdesc
CName
Figure 4.10 ERR conceptual schema for a UNIVERSITY database
Formal Definitions
• class
– a set or collection of entities, including any of the EER schema
constructs that group entities such as entity types, subclasses,
superclasses, and categories
• subclass S
– a class whose entities must always be a subset of the entities in
another class C (superclass) of the superclass/subclass
relationship C/S
– SC
33
Formal Definitions of EER Model (1)
• Class C: A set of entities; could be entity type, subclass, superclass,
category.
• Subclass S: A class whose entities must always be subset of the
entities in another class, called the superclass C of the
superclass/subclass (or IS‐A) relationship S/C:
S⊆C
• Specialization Z: Z = {S1, S2,…, Sn} a set of subclasses with same
superclass G; hence, G/Si a superclass relationship for i = 1, …., n.
– G is called a generalization of the subclasses {S1, S2,…, Sn}
– Z is total if we always have:
S1 ∪ S2 ∪ … ∪ Sn = G;
Otherwise, Z is partial.
– Z is disjoint if we always have:
Si ∩ S2 empty‐set for i ≠ j;
Otherwise, Z is overlapping.
Formal Definitions of EER Model (2)
• Subclass S of C is predicate defined if predicate p on attributes of C is used to
specify membership in S; that is, S = C[p], where C[p] is the set of entities in C
that satisfy p
• A subclass not defined by a predicate is called user‐defined
• Attribute‐defined specialization: if a predicate A = ci (where A is an attribute of
G and ci is a constant value from the domain of A) is used to specify
membership in each subclass Si in Z
• Note: If ci ≠ cj for i ≠ j, and A is single‐valued, then the attribute‐defined
specialization will be disjoint.
• Category or UNION type T
– A class that is a subset of the union of n defining superclasses
D1, D2,…Dn, n>1:
T ⊆ (D1 ∪ D2 ∪ … ∪ Dn)
A predicate pi on the attributes of T.
– If a predicate pi on the attributes of Di can specify entities of Di that are
members of T.
– If a predicate is specified on every Di: T = (D1[p1] ∪ D2[p2] ∪…∪ Dn[pn]
– Note: The definition of relationship type should have 'entity type' replaced
with 'class'.
Conceptual Object Modeling Using
UML Class Diagrams
• UML ‐ Universal Modeling Language
• OMT ‐ Object Modeling Technique
36
Class Diagrams
• The entity types are modeled as classes. An
entity in ER corresponds to an object in UML.
• In UML class diagrams, a class is displayed as
a box (see Figure 04.11) that includes three
sections:
– the top section gives the class name;
– the middle section includes the attributes for
individual objects of the class;
– and the last section includes operations that can
be applied to these objects.
37
Class Diagram Contd.
• Operations are not specified in EER diagrams.
• Relationship types are called associations in
UML terminology, and relationship instances
are called links.
• A binary association (binary relationship type)
is represented as a line connecting the
participating classes (entity types), and may
(optional) have a name.
38
Class diagram contd.
• A relationship attribute, called a link attribute, is
placed in a box that is connected to the association’s
line by a dashed line.
• In UML, there are two types of relationships:
association and aggregation. Aggregation is meant to
represent a relationship between a whole object and
its component parts, and it has a distinct
diagrammatic notation. In the Figure below, we
modeled the locations of a department and the single
location of a project as aggregations .
39
contd.
• Weak entities can be modeled using the
construct called qualified association (or
qualified aggregation) in UML; this can
represent both the identifying relationship
and the partial key, which is placed in a box
attached to the owner class.
40
Multipicities min..max (* : no maximum limit)
(relationship constraints)
class association
(relationship types)
name
attributes
domain
composite
attributes
link attribute
operations (relationship attribute)
multivalued
role attribute
is modeled
a class
qualified role
aggregation
reflexive
(identifying association
relationship) (recursive
relationship)
a relationship between a
whole object and its
component parts
4-33.1 41
generalization / specialization
overlapping
disjoint
4-33.2 42
Relationships of Higher Degree
4-34 43
Ternary Relationship Types
(a)
SName Quantity ProjName
IName Sem_Year
CAN_TEACH OFFERED_DURING
CNumber
COURSE