Advanced Entity Relationship Concepts

1
Advanced Entity Relationship Concepts
2
Advanced Concepts

• UIDs
• Intersection Entities
• Recursive Relationships
• Roles

• Subtypes
• Exclusivity
• Historical
• Fan Traps
• Non transferability

3
Simple Unique Identifier
CUSTOMER
customer num
Single attribute

Tag the UID with
4
Compound UID - Attributes
Members get a new membership each year but keep
their membership number.
MEMBERSHIP
num
start date
Multiple attributes
5
Compound UID - Composite
What would you need to know to identify a
specific instance of ACCOUNT?
ACCOUNT
BANK
num
num

Use a UID bar to indicate that a relationship is
part of the entitys UID
Use to indicate that the attribute is part of
the entitys UID
6
Compound UID - Relationships
RENTAL ITEM
What do you need to know to identify a specific
instance of RENTAL ITEM?
Rental item requires the rental transaction num
and the inventory num
rental period return date
o
RENTAL
COPY
transaction num transaction date
inventory num purchase cost
7
Multi-Level Relationship UIDs
PERFORMANCE
PLAY
VENUE
date time
name
title
CUSTOMER
name
What would you need to know to identify a
specific instance of TICKET?
8
Multi-Level Relationship UIDs
PERFORMANCE
PLAY
VENUE
date time
name
title
Venue name Play title Performance date
Performance time Customer name
CUSTOMER
name
9
Multiple UIDs
badge num
EMPLOYEE
(1)
badge num
payroll num
(2)

first name
(2)

last name
(3)
payroll num
first and last name
10
Composition of a UID
11
Primary Unique Identifier

• An entity can have several unique identifiers.
• One may be appointed as the primary unique
  identifier.
• Only the Primary UID is shown in the entity
  relationship diagram.

12
Intersection Entity
Note how Crows Feet Change!
13
Intersection EntitiesIdentifying the Problem
supplier of
TITLE
SUPPLIER
prod code name
supplier no name
supplied by
In which entity would you store the attribute
purchase price?
14
Intersection Entities
CATALOG ITEM
purchase price
for
for
available as
supplier of
SUPPLIER
TITLE
TITLE
prod code name
supplier no name
15
Unique Identifiers
CATALOG ITEM
CATALOG ITEM
purchase price
item num purchase price
OR
for
for
for
for
available as
supplier of
available as
supplier of
SUPPLIER
SUPPLIER
TITLE
TITLE
prod code name
prod code name
supplier no name
supplier no name
TITLE
TITLE
16
Modeling Recursive Relationships
...but Im HIS manager!
...and mine
...hes my manager
17
Modeling Hierarchical Data
Company
Division
Department
Team
18
Hierarchies as Recursive Relationships
TEAM
name
made up of
DEPARTMENT
name
ORGANIZATIONELEMENT
name type
DIVISION
within
name
COMPANY
name
19
Network Structures
a part of
COMPONENT
identifier
made up of
20
Network Structures
a part of
COMPONENT
identifier
made up of
made up of
COMPONENT
COMPONENT
identifier
identifier
a part of
21
Network Structures
ASSEMBLY RULE
o quantity
for
for
made up of
a part of
COMPONENT
COMPONENT
identifier
identifier
made up of
COMPONENT
COMPONENT
identifier
identifier
a part of
22
Network Structures
ASSEMBLYRULE
o quantity
for
for
made up of
a part of
COMPONENT
identifier
23
Identifying Roles
ENROLLMENT
date enrolled fee
for
for
taken by
included in
INSTRUCTOR
taught by
COURSE SECTION
id name salary
STUDENT
the teacher of
id name
location start date
for
COURSE
code name
the subject of
24
Modeling Roles
enrolled on
PERSON
ENROLLMENT
id name o salary
date enrolled
fee
for
the teacher of
for
taught by
taken by
COURSE
for
COURSE SECTION
code name
location start date
included in
25
Subtypes
TITLE


product code title description


MOVIE
GAME
category duration O audio
category medium minimum memory
All titles are either movies or games
26
Creating a Subtype

• In ERD
• Drag and drop.
• or
• Draw new entity fully within or around the other.

27
Exclusive Entities
COPY
COMPANY
acquired from
inventory num o condition
the source of
id name telephone num supplier num sales
contact




o
held by
o

the holder of
o
Company in two roles
28
Splitting Entities
COMPANY

id name telephone num



held by

the holder of
o
Could teacher-student-person be so modeled?
29
Nesting Entities
Business Rule only sales reps can drive company
cars
CAR
30
Nesting Entities
AIRCRAFT
AIRPLANE
POWERED AIRPLANE
PROP PLANE
GLIDER
JET PLANE
OTHER AIRCRAFT
HELICOPTER
HOVERCRAFT
31
Recursive Subtypes
ORGANIZATION ELEMENT
of
ORGANIZATION
the classification for
within
SUBDIVISION
made up of
32
Modeling Exclusive Relationships
COMPANY
name postal area 0 contact name
held by
the holder of
o
MEMBERSHIP
num start date expiry date o termination
CUSTOMER
the holder of
o
num first name last name
held by
33
Creating an Arc
PUBLICATION
CATALOG
REVIEW
34
Modeling Exclusivity
We offer membership to individual customers and
companies
MEMBERSHIP
MEMBERSHIP
INDIVIDUAL
ORGANIZATION
MEMBERSHIP
CUSTOMER
COMPANY
CUSTOMER
COMPANY
MEMBER
CUSTOMER
COMPANY
35
Modeling Data over Time
What if you need to hold an apartments rental
history?
PERSON
APARTMENT
rented by
id last name first name
code address
the renter of
36
Modeling Data over Time
PERSON
for
RENTAL HISTORY
id last name first name
from date to date
the renter of
o
for
rented by
APARTMENT
code address
37
Modeling Data over Time
employed by
COMPANY
the employer of
code name
38
Modeling Data over Time
EMPLOYMENTHISTORY ENTRY
from date to date
o
for
for
the employer of
employed by
39
Fan Traps
PERSON
POSITION
the holder of
id last name first name
job title job description
o
held by
employed by
included in
COMPANY
code name
the employer of
the employer of
40
Fan Traps
POSITION HISTORY
POSITION
employed as
PERSON
for
job title job description
id last name first name
o
held by
for
the subject of
employed at
for
for
ORGANIZATIONHISTORY
COMPANY HISTORY
for
for
COMPANY
the employer for
code name
the employer for
41
Resolving Fan Traps
COMPANY
EMPLOYMENT HISTORY
at
code name
the employer for

as
for
a party to
POSITION
PERSON
included in
job title job description
o
id last name first name
42
Transferable Relationships
PERSON
works in
DEPARTMENT
id last name first name
code
employs
Head office
Personnel
Finance
Sales
43
Non-Transferable Relationships
COMPANY

id name telephone num



acquired from
COPY
SUPPLIER
inventory num o condition

supplier num sales contact

the source of
OTHER