Tutorial: Entity Relationship Diagrams

1
TutorialEntity Relationship Diagrams

• MIS _at_ SFU

2
Data Modeling Context

• DFDs and Logic Modeling
• show how, where and when data is used
• Data Modeling, The Next Step
• shows data definition, structure relationships
• This is an important step
• crucial information for the design phase
• data is often more complex than processes
• data has reasonably permanent characteristics

3
Conceptual Data Modeling

• What is it?
• Captures the structure of organizational data
• Is independent of a Data Base Management System
  (DBMS) or physical implementation issues
• When is it used?
• Conceptual Data Modeling is during analysis
• Other data modeling is done throughout SDLC
• What form does it take?
• Entity Relationship Diagrams (ERDs) are currently
  the primary application for this, due primarily
  to the data model used in industry today.

4
ERDs

• Types
• ERD just for the current projects application
• ERD for the system that is being replaced
• ERD for the database used by the new application
• ERD for the database used by the old system
• Linkages to DFDs
• data flows should appear in the data model
• data stores should appear as data objects

5
Linking DFD with ERD
DataFlow Diagram
To link DFD and ERD, you must 1. Make sure that
each of the data elements included in the
dataflows (1-4) are included in the ER model. Any
raw data captured in a data flow must be
represented in the ERD.
Dataflow 1
Dataflow 4
Dataflow 2
Datastore 1
Dataflow 3
2. The datastore (1) must relate to one or more
entities in the ERD. That is, the datastore must
be represented somewhere in the ERD.
6
Basic ERD Symbols
Entity
Relationship
Attribute
Primary Key
7
Entities

• A person, place, object, event or concept that
  you want to maintain data about. It is often
  helpful to think of an entity as a Master File
• e.g., Customer, Product, Sale, Course, etc.
• entities are named with a noun
• represented by a rectangle

Place Entity Name Here
8
Figuring Out What Entities to Use

• Top Down Modeling
• Asking Questions
• Determining Business Rules
• What things do we care about, in that we need to
  store them in our data base to perform business
  operations?
• persons
• places
• objects
• events
• concepts

9
Figuring Out What Entities to Use

• Bottom-Up Modeling
• looking at forms and reports required in the
  system
• identifying entities from DFDs
• looking at old ERDs if they exist

10
Entity Types and Instances(aka distinctions
modelers make to keep you confused)

• Entity Type (or class) refers to a collection of
  entities that share common properties
• e.g. The entity student may have two entity
  types, graduate and undergraduate student
• Entity Instance refers to a single unique object
  within an entity type
• e.g., Employee is an entity, customer service
  representative (CSR) is an entity type, and the
  CSR named John is an entity instance.

11
Attributes

• A named characteristic of an entity that is of
  interest to the organization or application
• e.g., Customer , Customer Address, etc.
• Attributes are indicated by an ellipse
• attributes are named with nouns

Attribute name
12
Attributes, An Example

• If we wanted to store data about professors, we
  would want to have certain attributes. Some of
  them might be
• Last Name
• First Name
• Office Number
• Telephone Number
• Faculty
• Faculty ID Number

13
Attributes that are Keys

• A key is an attribute that uniquely identifies
  every instance of an entity type.
• Candidate keys are one or more attributes that
  uniquely identify every entity instance
• Primary key - the candidate key that is chosen as
  the unique identifier for every instance of the
  entity type.
• choose stable, non null, and simple attributes
• primary keys are underlined in the diagram
• For the professor example, the primary key would
  likely be the Faculty ID Number since we know
  it is unique to a particular instance of a
  professor. If we used telephone number, we could
  have a problem if two professors shared an
  office.

14
Candidate Keys and Identifiers

• Tape and Copy could uniquely identify a single
  physical tape.
• It would probably be better to choose a
  Tape_Serial_Number for each tape so we could keep
  the key to one attribute. That way, if we rented
  out three copies of Matrix and only two were
  returned, we could quickly find out who the evil
  tapenapper was and contact them.

15
Relationships

• The glue that combines entity types.
• More formally, a relationship is an association
  between instances of one or more entity types
• shaped like a diamond (generally)
• labeled with verb phases - one or two
• may be represented as a diamond or not shown at
  all on the diagram to avoid clutter
• It may also be helpful here to think of a
  relationship as a transaction file. This would
  represent a transaction among two or more
  entities.

16
Relationships

• Relationships typically represent a transaction
  of the organization

Rent
Members
Videos
1
0
0
17
Reading an ERD Relationship
Rent
Members
Videos
1
0
0
This relationship shows cardinality. This is
important for the design of a data base. This
diagram uses the crowsfeet notation, namely 0
1 or lt to represent the terms zero, one,
or many. We can read / write these cardinality
relationships by thinking through the nature of
the relation
18
Reading an ERD Relationship
Rent
Members
Videos
1
0
0
In this case If you have a specific instance of
a member, (s)he could, at any specific instant in
time, have rented Zero videos (i.e. they dont
currently have anything out) -OR- Many videos
(i.e. They could rent one or more. Many
includes one to keep the notation
simpler). Hence the notation at the end 0
lt Note the lt, called a crowsfoot, is sometimes
denoted with an N
19
Reading an ERD Relationship
Rent
Members
Videos
1
0
0
Going the other way, from Videos to
Members A specific instance of a video could
at any specific instant in time, be rented
by Nobody. (i.e. Its in inventory sitting on the
shelf) -OR- Exactly One Member. You cant rent
one instance of a video out to more than one
person at a time. Hence the notation 1 0
20
Relationship Types

• One to One
• One to Many
• Many to Many

Assigned
Student
Parking Space
1
1
Owns
Student
Textbooks
1
N
Takes
Student
Classes
N
N
21
More ERD Concepts

• Degrees of Relationships
• Cardinality
• Many to many relationships

22
Relationships - Degrees

• Unary (or recursive)
• between instances of one entity type
• e.g., Employee manages Employee
• Binary
• between instances of two entity types
• the most common and what to know for BUS362
• Ternary
• a simultaneous relationship among three entities
• e.g., a vendor ships a part to a warehouse

23
Degrees - Examples
Unary
Binary
Ternary
24
Relationships - Cardinalities

• The number of instances of entity A that can be
  associated with instances of entity B
• For example

Owns
Student
Textbooks
1
N
Cardinalities
Takes
Student
Classes
N
N
25
Min and Max Cardinality

• Often, cardinalities are represented as ranges
  (minimum and maximum)
• Minimum Maximum
• if minimum is zero than relationship is optional
• if minimum is one than it is mandatory
• the maximum can be a fixed number (like 2 or 3)
  or the maximum could be unlimited (anything gt 1).
  
• Like you noted in the DFD tutorial, notation
  standards vary a bit here.

26
Cardinality - Mandatory
One
Many
Example Patient has Patient History
Patient
Patient History
has
27
Cardinality- Optional
One
Many
Example Movie is available on videotape
Movie
Video Tape
Stored as
28
Many-to-Many Relationships

• Generally implies that there are attributes
  associated with the relationship
• i.e., attributes that do not belong with one of
  the entities alone but with the combination
• Many to Many relationships are very difficult to
  deal with in databases
• For BUS 362, well create a 3rd entity when we
  are faced with a many to many relation
• the primary key for the new entity will be the
  keys of the entities that it connects

29
Many-to-Many Example
Title
0
rents
Member
Can be transformed into
Tape
0
Title
Rents
has
Member
This transformation makes the relationship easier
to handle. In general, we seek to eliminate many
to many relationships whenever possible.
30
Figure 16-14a Representing an MN Relationship
Note here that the attributes are included. They
can be in bubbles like this, or listed with
linking lines. Format isnt really as important
here as listing a key attribute and the other
major elements.
31
And now a practice quiz ...

• Now that you have seen the presentation, we would
  like you to answer some questions. Note that your
  answer to these questions will not affect your
  grades for the course. Drew will not see your
  answers. We are only interested in measuring the
  effectiveness of the presentation.

32
Instructions

• There are two parts to the test. Use the diagram
  provided to answer the questions. Please
  complete Part A before moving to Part B. Your
  answers should be placed in an e-mail message
  and sent to gemino_at_sfu.ca
• Before beginning part A, please note the time you
  started the quiz. When completing Part A, note
  that there are only three possible choices (True,
  False, or Uncertain).
• In Part B, more than one answer can be provided
  for each question. When you have finished Part B,
  please note the time you finished so that we can
  measure the time taken to complete the quiz.
• Thank you for your time.

33
Please write down your start time......Use the
diagram provided on the next page to answer
questions in Part A and Part B.
34
Skill
Mechanic Name Labour Rate Yrs. Experience
Skilled Mechanic
Fax
Local Customer
Customer
Mechanic
1 0
1, N
1, N
1, N
assign
owns
assign
1, 1
1, 1
1, N
1, N
Equipment
Repair Number
Total Cost
1 0
1 0
1 0
1 0
Repair Details Requiring Parts
Diesel Engine
Reciprocating Pump
Centrifugal Pump
1,N
1,N
1,1
1,N
Parts
1,1
1,N
Far East Engine Repair
1,1
1,N
Bin
Manufacture
35
Part A True/False/ Uncertain Questions Answer
the following twelve questions with either Yes,
No, or Uncertain.
1. Do all repairs require parts? 2. Can a repair
be worked on by more than one mechanic? 3. Are
all repairs assigned to at least one mechanic? 4.
Are there parts stored in the warehouse that are
not used for repairs? 5. Does Far Eastern collect
different information for different machine
types? 6. Does Far Eastern differentiate their
local customers in any way? 7. Can a mechanic who
does not have a special skill be assigned to more
than one repair.? 8. Do all the mechanics related
to the same repair, pool their hours to create a
single entry for hours worked? 9. Can a piece of
equipment undergo more than one repair? 10. Can
more than one part be listed in a single repair
detail? 11. Is the cylinder volume recorded for
all pumps that are repaired? 12. Can a part be
supplied by more than one manufacturer?
36
Part B Problem Solving Questions Provide as many
possible answers as you can think of for each of
the four questions below. Please use point form
for your answers, and make sure to indicate what
question your answers are related to.
1. A customer of Far Eastern has called to
complain that the machine they sent for repair
has not been repaired yet. What possible reasons
can you provide for what might have gone wrong.
Provide as many possibilities as you can think
of 2. Far Eastern is experiencing a very large
increase in the number of machines that they
should repair. What problems might Far Eastern
experience because of this increase in repairs?
Provide as many possibilities as you can think
of. 3. Two customers of Far Eastern were
talking to each other. One customer found that he
was charged more for his repair than the other
person, even though the machines were very
similar. What reasons can you suggest for the
difference in prices between the two repairs?
Provide as many possibilities as you can think
of. 4. Customers of Far Eastern are not happy
when the actual repair price is higher than the
estimated repair price. The sales person says
that it is not his fault because the estimation
is so difficult. Provide as many possibilities as
you can think of that make the accurate
estimation of the total cost of a repair
difficult.
37
Please write down your end time......
38
Thank you for your time...

• Please e-mail your answers, the time taken to
  complete the practice quiz, and whether you used
  the narrated or non-narrated version to
• gemino_at_sfu.ca