Fundamentals of Database Systems

1
Fundamentals of Database Systems

• Chapter 4
• Enhanced Entity-Relationship and Object Modeling

2
Chapter Outline

• EER stands for Enhanced ER or Extended ER
• EER Model Concepts
• Includes all modeling concepts of basic ER
• Additional concepts
• subclasses/superclasses
• specialization/generalization
• categories (UNION types)
• attribute and relationship inheritance
• These are fundamental to conceptual modeling
• The additional EER concepts are used to model
  applications more completely and more accurately
• EER includes some object-oriented concepts, such
  as inheritance

3
EER Model Concepts

• Extended Entity-Relationship (EER)Model
• An extension of ER model includes the concepts of
  
• subclass and superclass
• specialization and generalization
• category
• attribute and relationship inheritance

4
EER Model Concepts (cont.)

• Subclass and Superclass
• In many cases an entity type has numerous
  subgroupings of its entities
• e.g., EMPLOYEE may be grouped into SECRETARY,
  ENGINEER, MANAGER, TECHNICIAN
• Each subgroup form a subclass of the entity type
  (superclass)
• e.g.,

SECRETARY ENGINEER MANAGER TECHNICIAN
EMPLOYEE
superclass
subclass
5
EER Model Concepts (cont.)

• Class/subclass (IS-A/AN) relationship
• The relationship between a superclass and any one
  of its subclasses
• e.g., EMPLOYEE/MANAGER
• An entity being a member of a subclass must also
  be a member of the superclass
• e.g., a SECRETARY entity Joan Logano is also
  the EMPLOYEE Joan Logano
• A subclass entity is the same as that in the
  superclass, but in a distinct specific role

6
(No Transcript)
7
EER Model Concepts (cont.)

• Type inheritance
• A subclass entity inherits
• all the attributes of the entity as a member of
  the superclass
• all the relationships in which the superclass
  participates
• A subclass should have specific attributes and
  can participate in specific relationship types
• e.g., SECRETARY has attribute TypingSpeed

8
EER Model Concepts (cont.)

• Specialization
• The process of defining a set of subclasses of an
  entity type (superclass) based on some
  distinguishing characteristic, e.g.,
• SECRETARY, ENGINEER, TECHNICIAN based on job
  type
• SALARIED_EMPLOYEE, HOURLY_EMPLOYEE based on
  method of pay

9
EER Model Concepts (cont.)
10
EER Model Concepts (cont.)

• Reasons for using (specialization) 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

11
EER Model Concepts (cont.)

• Generalization
• The process of identifying the common features
  among several entity types, and generalizing them
  into a single superclass
• e.g., CAR and TRUCK can be generalized into
  VEHICLE
• Generalization can be viewed as the inverse of
  specialization

12
ER Model Concepts (cont.)
13
Constraints on Specialization/ Generalization

• Predicate-defined (or condition-defined)
  subclasses
• Placing a condition on the value the attribute
  that identify the subclasses
• e.g., SECRETARY subclass by the defining
  predicate JobType Secretary
• A constraint specifying that members of the
  subclass must satisfy the predicate

14
Constraints on Specialization/ Generalization
(cont.)
15
Constraints on Specialization/ Generalization
(cont.)

• Attribute-defined specialization
• Specify that all subclasses having the membership
  condition on the same attribute of the superclass
• The attribute is called the defining attribute of
  the specialization, e.g., Job type

16
Constraints on Specialization/ Generalization
(cont.)

• Disjointness constraint
• Specify that the subclasses of the specialization
  must be disjoint, i.e., an entity belongs to at
  most one subclass
• e.g., specialization HOURLY_EMPLOYEE,
  SALARIED_EMPLOYEE
• denoted as symbol
• If the subclasses are not disjoint, their sets of
  entities may overlap, denoted as symbol

d
o
17
Constraints on Specialization/ Generalization
(cont.)
18
Constraints on Specialization/ Generalization
(cont.)

• Completeness constraint
• Total specialization constraint
• Specify that every entity in the superclass must
  be a member of some subclass in the
  specialization
• Partial specialization
• Allow an entity not to belong to any of the
  subclasses

19
Characteristics on Specialization/ Generalization

• Specialization Hierarchies and Lattices
• A subclass itself may have further subclasses
  specified on it, forming a hierarchy or a lattice
  of specializations
• For a specialization hierarchy every subclass
  participates in only one class/subclass
• For a specialization lattice a subclass can be a
  subclass in more than one class/subclass

20
Characteristics on Specialization/ Generalization
(cont.)
21
Characteristics on Specialization/ Generalization
(cont.)

• A subclass with more than one superclass is
  called a shared subclass, leading to multiple
  inheritance
• e.g., ENGINEERING_MANAGER
• Any inherited attribute should be included only
  once in a shared subclass

22

23
Utilizing Specialization/Generalization in
Conceptual Modeling

• Top-down conceptual refinement
• Start with an entity type and then define
  subclasses by successive specialization
• e.g., A university database schema
• PERSON gt EMPLOYEE, ALUMNUS, STUDENT
• STAFF, FACULTY, STUDENT_ASSISTANT
• GRADUATE_STUDENT,UNDERGRADUATE_STUDENT

24
Utilizing Specialization/Generalization in
Conceptual Modeling (cont.)

• Bottom-up conceptual synthesis
• Start with entity types and then define
  superclasses by successive generalization
• e.g., STAFF, FACULTY, ALUMNUS
• EMPLOYEE
• Hybrid conceptual process
• A combination of the two processes

25
UNION Types Using Categories

• Union type (Category)
• Modeling a single superclass/subclass
  relationship with more than one superclass
• Superclasses can represent different entity types
  
• e.g., In a vehicle registration database, the
  vehicle owner can be a person, a bank, or a
  company
• gt OWNER a subclass of the UNION of COMPANY,
  BANK, and PERSON
• The union type (category) is specified via ?
  symbol, called set union operation

26
(No Transcript)
27
(No Transcript)
28
UNION Types Using Categories (cont.)

• A category can be total or partial
• e.g., Fig 4.9, where c1 and c2 are predicate
  conditions that specify which COMPANY and PERSON
  entities are members of ACCOUNT_HOLDER
• The superclasses of a category may
• have different key attributes
• e.g., OWNER category of Fig 4.8
• have the same key attribute
• e.g., REGISTERED_VEHICLE category

29
UNION Types Using Categories (cont.)
30
UNION Types Using Categories (cont.)

• Category vs shared subclass
• A category is a subset of the union of its
  superclasses
• An entity that is a member of a category must
  exist in only one of the superclasses
• e.g., an OWNER may be
• a COMPANY
• a BANK, or
• a PERSON

31
UNION Types Using Categories(cont.)

• A shared subclass is a subset of the intersection
  of the superclasses
• An entity that is a member of the shared subclass
  must exist in all superclasses
• e.g., An engineering manager must be
• an ENGINEER,
• a MANAGER, and
• a SALARIED_EMPLOYEE

32
UNION Types Using Categories(cont.)

• Category vs generalized superclass
• A total category can be represented as a
  specialization (or a generalization)
• e.g., Fig 4.9(b)
• A partial category cannot be represented as a
  partial specialization (or a generalization)
• e.g., category REGISTERED_VEHICLE (Fig 4.8) and
  generalized superclass VEHICLE (Fig 4.3(b))

33
UNION Types Using Categories (cont.)

34
An Example EER Schema

• A UNIVERSITY Database Example
• Requirements The database keeps track
• Students and their majors, transcripts, and
  registration
• The universitys course offerings
• The sponsored research projects of faculty and
  graduate students
• The advisor and thesis committee of each graduate
  student
• Colleges and their related departments

35
An Example EER Schema (cont.)

• Initial design
• Entity types
• STUDENT
• COURSE
• FACULTY
• GRADUATE_STUDENT
• GRANT (project)

36
An Example EER Schema (cont.)

• DEPARTMENT (students majors)
• SECTION (course offerings, students
  registration)
• COLLEGE
• Superclass/subclass
• STUDENT/GRAD_STUDENT
• SECTION/CURRENT_SECTION (for quarter system
  universities)

37
An Example EER Schema (cont.)

• Generalization
• PERSON FACULTY, STUDENT
• Category (Union type)
• INSTRUCTOR_RESEARCHER FACULTY, GRAD_STUDENT

38
An Example EER Schema (cont.)

• Iterative refinement
• Relationship types
• STUDENT ltREGISTEREDgt CURRENT_SECTION
• STUDENT ltMAJORgt DEPARTMENT
• STUDENT ltTRANSCRIPTgt SECTION
• FACULTY ltBELONGSgt DEPARTMENT
• FACULTY ltADVISORgt GRAD_STUDENT
• FACULTY ltCOMMITTEEgt GRAD_STUDENT
• FACULTY ltPIgt GRANT

39
An Example EER Schema (cont.)

• INSTRUCTOR_RESEARCHER ltTEACHgt SECTION
• GRANT ltSUPPORTgt INSTRUCTOR_RESEARCHER
• COURSE ltCSgt SECTION
• DEPARTMENT ltDCgt COURSE
• COLLEGE ltCDgt DEPARTMENT
• Structure constraints

40
ER Conceptual Design (cont.)
41
Alternative Notation
42
Conceptual Object Modeling

• UML (Universal Modeling Language)
• Defines several diagrams that can be used in the
  process of object oriented software design
• Among the UML diagrams, class diagrams are
  similar to EER diagrams in many ways

43
Conceptual Object Modeling (cont.)

• UML class diagram vs EER diagram
• Entity vs Object.
• Entity type vs Class
• Each class comprises three parts
• class name
• attributes
• operations
• A composite attribute is modeled as a structured
  domain
• A multivalued attribute is modeled as a separate
  class

44
Conceptual Object Modeling
45
Conceptual Object Modeling (cont.)
46
Conceptual Object Modeling (cont.)

• Relationship type vs Association
• UML has two types of relationships association
  and aggregation
• Aggregation is meant to represent a relationship
  between a whole object and its component parts
• Relationship constraints, called multiplicities
  in UML, are specified in the form min..max,
  corresponding to (min, max) notation in ERD
• The placement of multiplicity is opposite when
  compared to ERD
• e.g., cp. WORKS_FOR in Figs 3.15 and 4.11

47
Conceptual Object Modeling (cont.)

• An association/aggregation may or may not have a
  name
• It is not clear that when to choose aggregation
  or association
• Weak entities can be modeled as a qualified
  association (or qualified aggregation)
• The partial key is placed in a box attach to the
  owner class, e.g., Dependent Name
• Relationship vs link
• In UML, a relationship attribute is called a link
  attribute

48
Conceptual Object Modeling (cont.)

• UML notation for generalization/specialization
• A blank triangle indicates a disjoint
  specialization/generalization
• A filled triangle indicates overlapping
• cp., Figs 4.7 and 4.12

49
Conceptual Object Modeling (cont.)
50
Conceptual Object Modeling (cont.)
51
Chapter Summary

• Introduced the EER model concepts
• Class/subclass relationships
• Specialization and generalization
• Inheritance
• These augment the basic ER model concepts
  introduced in Chapter 3
• EER diagrams and alternative notations were
  presented

52
In-Class Exercise

• Consider the BANK ER schema of Figure 3.21, and
  suppose that is is necessary to keep track of
  different types of ACCOUNTS (SAVINGS_ACCTS,
  CHECKING_ACCTS,...) and LOANS (CAR_LOANS,
  HOME_LOANS, ...).
• Suppose that is is also desirable to keep track
  of each account's TRANSACTIONs (deposits,
  withdrawals, checks, ...) and each loan's
  PAYMENTs both of these include the amount, date,
  time, ... .
• Modify the BANK schema, using ER and EER concepts
  of specialization and generalization.