Transcript Document 7545729

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.
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
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
street
SSN
name
city
CUSTOMER
owner
type
Has
account number
ACCOUNT
balance
Basic ER Diagram
Last
Maiden
Middle
Title
First
Suffix
Name
CUSTOMER
Composite Attribute
Last
Maiden
Middle
Title
First
Suffix
Name
Deductions
EMPLOYEE
Multivalued Attribute
Effective
Date
EMPLOYEE
Fills
POSITION
Relationship with an Attribute
Effective
Date
EMPLOYEE
Employment
History
POSITION
Associative Entity
child
parent
person
parent
Unary Relationship
type
Talent
training
name
Performer
address
Performs
Show
dates
location
Ternary Relationship
type
Talent
name
training
Has
Used in
Performer
Show
address
Performs in
dates
location
Three Binary Relationships
Effective
Date
EMPLOYEE
Fills
POSITION
Cardinality Constraints
account#
balance
account
savings account
checking account
max overdraft
interest rate
A supertype and subtypes
model number
model number
model number
make
make
price
make
price
price
LENS
fits
uses
CAMERA
film type
FLASH
requires
requires
quantity
quantity
BATTERY
size
type
model number
price
PHOTO EQUIPMENT
make
LENS
fits
CAMERA
uses
FLASH
film type
requires
requires
quantity
quantity
BATTERY
size
type
SSN
DateEnrolled
SSN
DateEnrolled
Degree
STUDENT
GRADUATE
Graduation
Date
Grade
Grade
TRANSCRIPT
TRANSCRIPT
Date
Date
COURSE
SSN
DateEnrolled
Grade
TRANSCRIPT
STUDENT
Date
COURSE
Degree
CURRENT
FORMER
Generalization
Graduation
Date
SUPPLIER
Wastage
SuppliedBy
Has
Ingredients
ITEM
Quantity
Part of a grocery store database
Quantity
Has
Ingredients
ITEM
BAKERY ITEM
SUPPLIED ITEM
SuppliedBy
PERISHABLE
ITEM
Specialization
SUPPLIER
Wastage
DOB
DOB
PRIMATE
Origin
LivesIn
BIRD
HABITAT
LivesIn
Food
LivesIn
Food
LivesIn
4-LEGGED
ANIMAL
REPTILE
Origin
DOB
DOB
Origin
Food
Food
Part of a Zoo database
Origin
Origin
DOB
Food
HABITAT
ANIMAL
LivesIn
PRIMATE
BIRD
REPTILE
4-LEGGED
ANIMAL
Specialization
Origin
DOB
Food
HABITAT
ANIMAL
LivesIn
Class
Replacing a set of subtypes with an attribute
model number
price
PHOTO EQUIPMENT
make
type =
type
d
“lens”
“flash”
“camera”
LENS
fits
uses
CAMERA
FLASH
film type
requires
requires
Disjoint specialization
with discriminator
quantity
quantity
BATTERY
size
type
MOTHER
Has
OFFSPRING
d
BAD
CHILDREN
GOOD
CHILDREN
Total, disjoint specialization
MOTHER
Has
SentCard
OFFSPRING
SentCard =
“Yes”
GOOD
CHILDREN
d
“No”
BAD
CHILDREN
Total, disjoint specialization with discriminator
DateEnrolled
SSN
Grade
Enrolled?
TRANSCRIPT
STUDENT
Status
Date
Status:
Graduated?
COURSE
o
Enrolled=“Y”
Graduated=“Y”
Degree
CURRENT
FORMER
Graduation
Date
Overlapping specialization with discriminator
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.
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
Example DFD and ERD
Customer
DFD
1.0
Places
Order
Cook
Name
Customer
Name
Hours
Address
Take
Order
Inventory
2.0
Convert Order
to Cooking
Instructions
3.0
Processed
Order
Cooking
Instructions
Convert Order
to Ingredient
List
Item
Quantity
Ingredients
D1 Order Log
Cook
Inventory
Processing
Incorrect ERD
Example DFD and ERD
OrderId
Customer
Order
DFD
Date
Contains
1.0
Take
Order
2.0
Convert Order
to Cooking
Instructions
Cooking
Instructions
ItemQuantity
Includes
Convert Order
to Ingredient
List
Ingredients
Requires
ItemId
Ingredient
Ingredient
Quantity
Item
3.0
Processed
Order
Time
ItemName
Index
Description
Cooking
Instructions
StepId
D1 Order Log
Cook
Inventory
Processing
Correct ERD
Description