CSE 480: Database Systems

1
CSE 480 Database Systems

• Lecture 4 Enhanced Entity-Relationship Modeling

Reference Read Chapter 8.1 8.5 of the textbook
2
Announcements

• Class Project
• Each group must email the instructor by next
  Tuesday your group members
• If your group has only 2 members, state whether
  youre willing to accept a third member
• If you dont have a group yet, email me too. Ill
  try to set up one for you.

3
Why EER?

• ER diagram has limitations in terms of accurately
  reflecting the data properties and constraints
• EER, which stands for Enhanced ER includes all
  the modeling concepts of basic ER with the
  following additional concepts
• subclasses/superclasses
• type inheritance
• shared subclasses and multiple inheritance
• categories (UNION types)

4
Why Subclasses and Superclasses?

• An entity type may have additional subgroupings
• EMPLOYEE may be further grouped into
• SECRETARY, ENGINEER, TECHNICIAN
• Based on job type
• SALARIED_EMPLOYEE, HOURLY_EMPLOYEE
• Based on method of pay
• EER diagram extends ER diagram to represent these
  additional subgroupings
• Each subgrouping is called a subclass of EMPLOYEE
• EMPLOYEE is the superclass of these subclasses

5
Is-A Relationships in EER

• Subclass-superclass relationships are also called
  IS-A relationships
• SECRETARY IS-A EMPLOYEE, TECHNICIAN IS-A
  EMPLOYEE, .

Subclass
Superclass
Subclass1
Superclass
Subclass2
Subclass3
6
Specialization

• A superclass can have several specializations
• EMPLOYEE has 3 specializations
• Some specializations have only 1 subclass (e.g.,
  Manager)

7
Is-A vs 1-to-1 Relationship
MANAGES
EMPLOYEE
DEPARTMENT
1
1
EMPLOYEE
TECHNICIAN

• In a 1-1 relationship, two distinct entities are
  related
• In a subclass-superclass relationship, the
  subclass member is the same entity as the
  superclass member but in a distinct specific role

8
Subclasses and Superclasses

• A subclass entity must be a member of the
  superclass
• But a superclass entity does not have to be a
  member of any of its subclasses

9
Why do we need Subclass/Superclass?

• Certain attributes may apply to some but not all
  entities of the superclass
• Ex Typing speed is an attribute of SECRETARY but
  not a TECHNICIAN
• Certain relationship types are applicable to
  some entities but not others
• HOURLY_EMPLOYEE has a relationship with
  TRADE_UNION SALARIED_EMPLOYEE does not have
  such a relationship

10
Attributes of a Subclass

• Local attributes TypingSpeed of SECRETARY,
  Tgrade of TECHNICIAN
• Type inheritance a subclass also inherits all
  attributes of its superclass
• SECRETARY inherits the attributes Name, SSN,
  Birth_date and Address from the EMPLOYEE entity
  type (its superclass)

11
Relationships of a Subclass

• A subclass can participate in specific
  relationship types
• Ex MANAGER subclass participates in the MANAGES
  relationship with PROJECT entity type

12
Relationships of a Subclass

• A subclass also inherits all the relationships in
  which the superclass participates
• If EMPLOYEE participates in WORKS_FOR
  relationship with DEPARTMENT then
• SECRETARY also participates in WORKS_FOR
  relationship with DEPARTMENT
• ENGINEER also participates in WORKS_FOR
  relationship with DEPARTMENT
• TECHNICIAN also participates in WORKS_FOR
  relationship with DEPARTMENT
• MANAGER also participates in WORKS_FOR
  relationship with DEPARTMENT
• SALARIED_EMPLOYEE also participates in WORKS_FOR
  relationship with DEPARTMENT
• HOURLY_EMPLOYEE also participates in WORKS_FOR
  relationship with DEPARTMENT

13
Constraints on Is-A Relationships
Subclass1
Superclass
Subclass2
Subclass3

• Constraint is defined on the subclass membership
  of the superclass entities
• Disjointness constraint
• How many subclasses a superclass entity can
  belong? (max)
• Completeness constraint
• Must a superclass entity belong to any of the
  subclasses? (min)

14
Disjointness Constraint

• Specifies whether subclasses are disjoint
• an entity can be a member of at most one of the
  subclasses of the specialization
• Specified by d in EER diagram
• Ex STUDENT is either FRESHMAN, SOPHOMORE,
  JUNIOR, SENIOR, GRADUATE
• If not disjoint, specialization is overlapping
• an entity may be a member of more than one
  subclasses
• Specified by o in EER diagram
• Ex PERSON can be STUDENT, EMPLOYEE, or both

superclass
superclass
15
Completeness Constraint

• Total specialization
• Every entity in superclass must be a member of at
  least one subclass in the specialization
• Ex every EMPLOYEE must be either an
  HOURLY_EMPLOYEE or SALARIED_EMPLOYEE
• Shown in EER diagrams by a double line
• Partial specialization
• Allows a superclass entity not to belong to any
  subclasses
• Ex some EMPLOYEE entities do not belong to any
  of the subclasses SECRETARY, TECHNICIAN, or
  ENGINEER
• Shown in EER diagrams by a single line

superclass
superclass
16
Example Disjoint Partial Specialization
17
Example Overlapping Total Specialization
18
Exercise

• Can a technician be an engineer?
• Can a manager belong to a trade union?

19
Exercise MOVIE database

• Each movie is identified by title and year of
  release. Each movie has a length in minutes and
  is classified under one or more genres (horror,
  action, drama, etc)
• Each movie has one or more directors and one or
  more actors appear in it. Each movie also has a
  plot outline and zero or more quotable quotes,
  each of which is spoken by a particular actor
  appearing in the movie
• Actors are identified by name and date of birth
  and appear in one or more movies. Each actor has
  a role in the movie
• Directors are also identified by name and date of
  birth and direct one or more movies. It is
  possible for a director to act in a movie
  (including one he or she may also direct)

20
Exercise Movie Database
role
quotes
M
ACTOR
ACTS
Name
DoB
genres
N
ID
Title
Person
MOVIE
ID
o
Year
length
Plot_Outline
N
M
DIRECTOR
DIRECTS
21
Hierarchies Lattices

• Hierarchy (Single Inheritance)
• every subclass has only one immediate superclass
• Lattice (Multiple inheritance)
• a subclass can be subclass of more than one
  immediate superclass
• A subclass with multiple immediate superclasses
  is called a shared subclass
• In a lattice or hierarchy, a subclass inherits
  attributes not only of its immediate superclass,
  but also of all its predecessor superclasses

22
Lattice (Multiple Inheritance)
23
Another Shared Subclass Example

• ENGINEERING_MANAGER is a shared subclass
• ENGINEERING_MANAGER Is-a ENGINEER
• ENGINEERING_MANAGER Is-a MANAGER
• ENGINEERING_MANAGER Is-a SALARIED_EMPLOYEE
• It will inherit all the attributes of its
  superclasses, including EMPLOYEE, ENGINEER,
  MANAGER, and SALARIED_EMPLOYEE

24
Categories (UNION types)

• A shared subclass is a subclass participating in
  multiple superclass/subclass (is-a) relationships
• Each distinct relationship has a single
  superclass
• In some cases, we need to model a single
  superclass/subclass relationship with more than
  one superclass
• Each superclass can represent a different entity
  type
• Such a subclass is called a category or UNION
  TYPE
• Difference between shared subclass and union type
• An entity of a shared subclass exists in all of
  its superclasses
• An entity of a category exists in only one of its
  superclasses
• It inherits only attributes of the superclass in
  which it belongs

25
Categories (UNION types)
OWNER is the subclass of the union of 3
superclass entity types, PERSON,BANK, COMPANY
(which have different key attributes)
26
Example of UNION types
OWNER is the subclass of the union of 3 entity
types, PERSON,BANK, COMPANY (which have different
key attributes) REGISTERED_VEHICLE is the
subclass of the union of CAR and TRUCK (which
have same key attribute)
27
Constraints on UNION types

• Total vs partial category (see example on next
  slide)
• Total category holds the union of all entities in
  its superclasses
• Represented by a double line
• Partial category holds a subset of the union
• Represented by a single line

28
Total vs Partial Category
Registered
Unregistered
u
User
User is the union of all registered and
unregistered user entity types
Owner is the union of all banks, but not all
persons and not all companies
29
Summary

• Introduced the EER model concepts
• Class/subclass relationships
• Type inheritance
• Shared subclass and categories (union types)
• These augment the basic ER model concepts
  introduced in lectures 2-3