Entity Relationship Diagrams

1
Entity Relationship Diagrams

• Used for conceptual data modeling - part of
  database design
• Represents
• the data that is important
• the structure of the data
• the relationships between different pieces of
  data
• Does NOT show process, roles, users, etc.

2
Basic Elements

• Entities
• people, places, things
• entity type is a set of things all of which have
  the same data properties
• entity instance is one concrete example of the
  entity type
• things that the system keeps track of, NOT things
  that are part of the system

3
Basic Elements (cont.)

• Relationships
• show how instances of one entity are related to
  instances of another entity
• have cardinality and degree
• Attributes
• single pieces of data that describe an instance
  of an entity
• like data elements

4
SSN
street
name
city
CUSTOMER
owner type
Has
account number
ACCOUNT
balance
Basic ER Diagram
5
Maiden
Last
Title
Middle
First
Suffix
Name
CUSTOMER
Composite Attribute
6
Maiden
Last
Title
Middle
First
Suffix
Name
Deductions
EMPLOYEE
Multivalued Attribute
7
Effective Date
Fills
POSITION
EMPLOYEE
Relationship with an Attribute
8
Effective Date
Employment History
POSITION
EMPLOYEE
Associative Entity
9
child
parent
person
parent
Unary Relationship
10
type
training
Talent
name
Performer
Performs
Show
address
dates
location
Ternary Relationship
11
type
training
Talent
name
Has
Used in
Performer
Show
address
dates
Performs in
location
Three Binary Relationships
12
Effective Date
EMPLOYEE
Fills
POSITION
Cardinality Constraints
13
account
balance
U
U
max overdraft
A supertype and subtypes
14
model number
model number
model number
make
price
make
price
make
price
LENS
CAMERA
FLASH
film type
requires
requires
BATTERY
size
type
15
model number
price
PHOTO EQUIPMENT
make
U
U
U
LENS
CAMERA
FLASH
film type
requires
requires
BATTERY
size
type
16
DateEnrolled
DateEnrolled
SSN
SSN
Degree
STUDENT
Graduation Date
GRADUATE
Grade
TRANSCRIPT
TRANSCRIPT
Grade
Date
Date
COURSE
17
DateEnrolled
SSN
Grade
TRANSCRIPT
STUDENT
Date
COURSE
U
U
Degree
Graduation Date
FORMER
CURRENT
Generalization
18
SUPPLIER
Wastage
SuppliedBy
Has Ingredients
ITEM
Quantity
Part of a grocery store database
19
Quantity
Has Ingredients
ITEM
U
U
U
BAKERY ITEM
SUPPLIED ITEM
SuppliedBy
PERISHABLE ITEM
SUPPLIER
Specialization
Wastage
20
DOB
DOB
PRIMATE
BIRD
HABITAT
Origin
Food
4-LEGGED ANIMAL
REPTILE
DOB
DOB
Origin
Food
Part of a Zoo database
21
Origin
Food
DOB
HABITAT
ANIMAL
U
U
U
U
PRIMATE
BIRD
4-LEGGED ANIMAL
REPTILE
Specialization
22
Origin
Food
DOB
HABITAT
ANIMAL
Class
Replacing a set of subtypes with an attribute
23
model number
price
PHOTO EQUIPMENT
make
type
type
lens
flash
U
U
camera
U
LENS
CAMERA
FLASH
film type
requires
requires
Disjoint specialization with discriminator
BATTERY
size
type
24
MOTHER
Has
OFFSPRING
U
U
BAD CHILDREN
GOOD CHILDREN
Total, disjoint specialization
25
MOTHER
Has
SentCard
OFFSPRING
SentCard
No
Yes
U
U
BAD CHILDREN
GOOD CHILDREN
Total, disjoint specialization with discriminator
26
DateEnrolled
SSN
Grade
TRANSCRIPT
STUDENT
Status
Date
Enrolled?
Status
Graduated?
COURSE
GraduatedY
EnrolledY
U
U
Degree
Graduation Date
FORMER
CURRENT
Overlapping specialization with discriminator
27
DFDs and ERDs

• DFDs and ERDs are both used to model systems, but
  they show two very different perspectives on the
  system
• A DFD shows what the system does as well as the
  data that the system manipulates
• An ERD shows only the data that the system
  manipulates.

28
DFDs and ERDs (cont.)

• Entities on an ERD often (but not always)
  correspond to data stores on a DFD
• Attributes on an ERD usually correspond to data
  elements (listed in the data dictionary) that
  make up the data store and data flows on a DFD
• Relationships on an ERD do not correspond to
  processes on a DFD.
• Sources and sinks on a DFD usually do not show up
  as entities on an ERD

29
Example DFD and ERD
DFD
Incorrect ERD
30
Example DFD and ERD
DFD
Correct ERD