cse 480: database systems
DESCRIPTION
CSE 480: Database Systems. Lecture 4: Enhanced Entity-Relationship Modeling. Reference: Read Chapter 8.1 – 8.5 of the textbook. Announcements. Class Project Each group must email the instructor by next Tuesday your group members - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: CSE 480: Database Systems](https://reader035.vdocuments.us/reader035/viewer/2022062723/56813eb0550346895da91792/html5/thumbnails/1.jpg)
1
CSE 480: Database Systems
Lecture 4: Enhanced Entity-Relationship Modeling
Reference:
Read Chapter 8.1 – 8.5 of the textbook
![Page 2: CSE 480: Database Systems](https://reader035.vdocuments.us/reader035/viewer/2022062723/56813eb0550346895da91792/html5/thumbnails/2.jpg)
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 you’re willing to
accept a third member
– If you don’t have a group yet, email me too. I’ll try to set up one for you.
![Page 3: CSE 480: Database Systems](https://reader035.vdocuments.us/reader035/viewer/2022062723/56813eb0550346895da91792/html5/thumbnails/3.jpg)
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)
![Page 4: CSE 480: Database Systems](https://reader035.vdocuments.us/reader035/viewer/2022062723/56813eb0550346895da91792/html5/thumbnails/4.jpg)
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
![Page 5: CSE 480: Database Systems](https://reader035.vdocuments.us/reader035/viewer/2022062723/56813eb0550346895da91792/html5/thumbnails/5.jpg)
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
SuperclassSubclass2
Subclass3
![Page 6: CSE 480: Database Systems](https://reader035.vdocuments.us/reader035/viewer/2022062723/56813eb0550346895da91792/html5/thumbnails/6.jpg)
6
Specialization
A superclass can have several specializations– EMPLOYEE has 3 specializations
Some specializations have only 1 subclass (e.g., Manager)
![Page 7: CSE 480: Database Systems](https://reader035.vdocuments.us/reader035/viewer/2022062723/56813eb0550346895da91792/html5/thumbnails/7.jpg)
7
Is-A vs 1-to-1 Relationship
EMPLOYEE DEPARTMENTMANAGES
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
![Page 8: CSE 480: Database Systems](https://reader035.vdocuments.us/reader035/viewer/2022062723/56813eb0550346895da91792/html5/thumbnails/8.jpg)
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
![Page 9: CSE 480: Database Systems](https://reader035.vdocuments.us/reader035/viewer/2022062723/56813eb0550346895da91792/html5/thumbnails/9.jpg)
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
![Page 10: CSE 480: Database Systems](https://reader035.vdocuments.us/reader035/viewer/2022062723/56813eb0550346895da91792/html5/thumbnails/10.jpg)
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)
![Page 11: CSE 480: Database Systems](https://reader035.vdocuments.us/reader035/viewer/2022062723/56813eb0550346895da91792/html5/thumbnails/11.jpg)
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
![Page 12: CSE 480: Database Systems](https://reader035.vdocuments.us/reader035/viewer/2022062723/56813eb0550346895da91792/html5/thumbnails/12.jpg)
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
![Page 13: CSE 480: Database Systems](https://reader035.vdocuments.us/reader035/viewer/2022062723/56813eb0550346895da91792/html5/thumbnails/13.jpg)
13
Constraints on Is-A Relationships
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)
Subclass1
SuperclassSubclass2
Subclass3
![Page 14: CSE 480: Database Systems](https://reader035.vdocuments.us/reader035/viewer/2022062723/56813eb0550346895da91792/html5/thumbnails/14.jpg)
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
d
o
superclass
superclass
![Page 15: CSE 480: Database Systems](https://reader035.vdocuments.us/reader035/viewer/2022062723/56813eb0550346895da91792/html5/thumbnails/15.jpg)
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
![Page 16: CSE 480: Database Systems](https://reader035.vdocuments.us/reader035/viewer/2022062723/56813eb0550346895da91792/html5/thumbnails/16.jpg)
16
Example: Disjoint Partial Specialization
Partial
Disjoint
![Page 17: CSE 480: Database Systems](https://reader035.vdocuments.us/reader035/viewer/2022062723/56813eb0550346895da91792/html5/thumbnails/17.jpg)
17
Example: Overlapping Total Specialization
Total
Overlapping
![Page 18: CSE 480: Database Systems](https://reader035.vdocuments.us/reader035/viewer/2022062723/56813eb0550346895da91792/html5/thumbnails/18.jpg)
18
Exercise
Can a technician be an engineer? Can a manager belong to a trade union?
![Page 19: CSE 480: Database Systems](https://reader035.vdocuments.us/reader035/viewer/2022062723/56813eb0550346895da91792/html5/thumbnails/19.jpg)
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)
![Page 20: CSE 480: Database Systems](https://reader035.vdocuments.us/reader035/viewer/2022062723/56813eb0550346895da91792/html5/thumbnails/20.jpg)
20
Exercise: Movie Database
ACTOR
DIRECTOR
MOVIE
Title
ID
Year
genres
length
DIRECTS
ACTS
Plot_Outline
role quotes
N
M
M
N
Person
NameID
DoB
o
![Page 21: CSE 480: Database Systems](https://reader035.vdocuments.us/reader035/viewer/2022062723/56813eb0550346895da91792/html5/thumbnails/21.jpg)
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
![Page 22: CSE 480: Database Systems](https://reader035.vdocuments.us/reader035/viewer/2022062723/56813eb0550346895da91792/html5/thumbnails/22.jpg)
22
Lattice (Multiple Inheritance)
SharedSubclass
![Page 23: CSE 480: Database Systems](https://reader035.vdocuments.us/reader035/viewer/2022062723/56813eb0550346895da91792/html5/thumbnails/23.jpg)
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
![Page 24: CSE 480: Database Systems](https://reader035.vdocuments.us/reader035/viewer/2022062723/56813eb0550346895da91792/html5/thumbnails/24.jpg)
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
![Page 25: CSE 480: Database Systems](https://reader035.vdocuments.us/reader035/viewer/2022062723/56813eb0550346895da91792/html5/thumbnails/25.jpg)
25
Categories (UNION types)
OWNER is the subclass of the union of 3 superclass entity types, PERSON,BANK, COMPANY (which have different key attributes)
![Page 26: CSE 480: Database Systems](https://reader035.vdocuments.us/reader035/viewer/2022062723/56813eb0550346895da91792/html5/thumbnails/26.jpg)
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)
![Page 27: CSE 480: Database Systems](https://reader035.vdocuments.us/reader035/viewer/2022062723/56813eb0550346895da91792/html5/thumbnails/27.jpg)
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
![Page 28: CSE 480: Database Systems](https://reader035.vdocuments.us/reader035/viewer/2022062723/56813eb0550346895da91792/html5/thumbnails/28.jpg)
28
Total vs Partial Category
Registered
User
u
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
Unregistered
![Page 29: CSE 480: Database Systems](https://reader035.vdocuments.us/reader035/viewer/2022062723/56813eb0550346895da91792/html5/thumbnails/29.jpg)
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