Reflexive Rela1tionships

1
Reflexive Rela1tionships

• An entity may be related to another entity of the
  same type.
• Example A Customer could have Children, who are
  also Customers. Joe Doe is Jane Does father.
  Bob Doe is Jane Does son.

Child
Joe Doe
Jane Doe
Child
Sue San
Bob Doe
Customer
2
Reflexive Relationship Types - Diagram

• Relationship type connects to self in an ER
  diagram.

Children
Customer
3
Kinds of Relationship Types

• How many of each entity type participate in a
  relationship type?
• 1-1
• 1-many
• many-1
• many-many
• Total vs. Partial participation
• Total participation means that every entity
  participates in the relationship.
• Partial means that some entities might not
  participate.
• Bounds (min-max) on participation
• (At least) three different diamond notations
• Arrow vs. straight line with or without
  participation bounds
• DO NOT MIX NOTATIONS!

4
An Example One-to-One Relationship Type

• A Customer might be an Employee.
• An Employee IsA Customer.
• Participation is total on the Employee side in
  this example.

Customer
IsA
Employee
5
Diagramming One-To-One Rel. Types
B
(0,1)
(0,1)
A
C
B
1
1
A
C
B
A
C

• An element in A is associated with at most one
  element in C via the relationship B. An element
  in C is associated with at most one element in A
  via B.

6
DVD Store Database Example

• An employee can also be a customer.
• Each Customer entity can be matched with at most
  one Employee entity, and each Employee entity can
  be matched with at most one Customer entity (both
  entities represent the same person).
• Often this can be better represented using
  additional attributes, or by using subclasses
  (wait a few slides!)

7
Total vs. Partial Participation

• Use a double line to indicate total
  participation.
• Example Every A is in a B relationship with
  exactly one C, but some Cs may be unrelated to
  an A.
• Using participation constraints, total
  participation is a 1 on the minimum bound.

B
1
1
A
C
B
(1,1)
(0,1)
A
C
8
An Example One-to-Many Relationship Type

• A Customer Rents zero to several DVD copies.
• A DVD copy can be Rented by at most one Customer.
• Participation is partial on both sides in this
  example.

Customer
Rents
DVD copy
9
One-To-Many Relationship

• An element in A is associated with several
  (including 0) elements in C via B. An element in
  C is associated with at most one element in A via
  B.

B
(0,m)
(0,1)
A
C
B
1
m
A
C
B
A
C
10
DVD Store Database Example

• A Customer might not be related to a DVD copy
  entity.
• Each DVD copy entity is associated with at most
  one Customer entity.

11
An Example Many-to-Many Rel. Type

• A Performer Stars In one or many Films.
• A Film can Star zero to many Performers.
• Participation is total on the Performer in this
  example.

Performer
Stars In
Film
12
Many-To-Many Relationship
B
(0,m)
(0,n)
A
C
B
n
m
A
C
B
A
C

• An element in A is associated with several
  elements in C via B.
• An element in C is associated with several
  elements in A via B.

13
DVD Store Database Example
14
Roles

• Definition A role is a label on a relationship
  type edge.
• Example labels "parent" and "child" are roles.
• Roles are optional, and are used to clarify
  semantics of a relationship type.

15
Weak Entity Types

• Definition A weak entity type borrows key
  attributes from another entity type (called the
  owning or strong entity type) to uniquely
  identify entities.
• Example A DVD copy has a Copy , relative to a
  Film title (e.g., Babe copy 1, Babe copy 2,
  Finding Nemo copy 1. Copy is not a key, but
  combined with Title it is (for DVD copy).
• ER diagram - double box represents weak entity
  type.
• The existence of a DVD copy entity depends on the
  existence of a Film entity.

16
Weak Entity Type Partial Keys

• A weak entity type has a partial key (key is
  completed by borrowing key attributes from owning
  entity type(s)).
• Example Key for Video Tape is (Title, Copy).
• ER diagram - partial key represented with dashed
  line.

Copy
Status
Title
Copies
1
m
Film
DVD copy
17
Weak Entity Type, cont.

• Semantics
• Deletion of a Film entity requires deletion of
  that film's DVD copy entities.
• A weak entity is related to precisely one entity
  in the owning entity type, via a 1-1 or 1-many
  relationship.
• It is possible to introduce more attributes to
  the DVD copy entity type, so that a primary key
  will exist, but they may not be needed for
  database processing.

18
Cascaded Weak Entity Types

• Weak entity types can be cascaded
• Semantics
• To delete an employee, the family's dependents
  and their medical records must also be deleted.

Dependent Name
Gender
Phone
Name
emp fam
1
m
Employee
Dependent
1
Fam med
SSN
Visit Num
m
Medical Record
Date
19
A Video Store ER Schema
NumCheckOuts
AmountPaid
CustomerID
TotalRes
CopyNum
Status
Status
ReturnDate
parent (0,n)
Rents
Children
1
m
Customer
DVD
m
child (0,2)
m
Name
Copies
Reserves
Address
Street
n
1
City
State
Title
FilmID
StarsIn
n
m
Kind
Film
Performer
RentalPrice
Date
Name
Role
Distributor
Director