Conceptual Data Modeling: More EntityRelationship Diagramming

1
Conceptual Data Modeling More Entity-Relationship
Diagramming

• Prof. Irwin
• CIS355
• Based on HPM, Chapter 3

2
Road Map for CIS355
3
Learning Objectives

• Explain the Entity Relationship (ER) model and
  its constructs
• Create a conceptual ER diagram
• Translate plain English business rules into an
  ER diagram, and vice versa.
• Differentiate business rules that can be
  represented in an ER diagram from those that
  cannot.

See the syllabus, course objectives 4-7
4
The ER Model Advanced Concepts

• Weak Entity Types
• Unary Relationships
• Ternary Relationships
• Time-Dependent Data

5
Weak Entity Type

• What is it? How is it different from a strong
  entity type?

6
Weak Entity Types
Note Visio doesnt have a built-in tool for
specifying a weak entity type, so I used shading
Your text uses a double-lined rectangle.
7
Weak Entity Types
Is Order Line weak, associative, or both?
back
8
Unary Relationships

• What is a unary relationship?
• This is an entity that has a relationship with
  itself.
• This is not a rare occurance.
• How do we show a unary relationship in an ERD?

9
Unary Relationships
Org chart Employees manage other employees.
Draw the ERD.
10
Unary Relationships
Bill-of-materials specification for a bike
manufacturer Parts are composed of other parts.
Draw the ERD.
11
Unary Relationships
back
12
Ternary Relationships

• What is a ternary relationship?
• How do we show a ternary relationship in an ERD?

13
Ternary Relationship
14
Ternary Relationship
15
Ternary Relationship
16
Ternary Relationship
back
17
Time-Dependent Data

• Values of attributes may change over time
• This is no problem if we only care about the
  current value of an attribute.
• What if we need to know a value at a prior point
  in time?
• What if we need to know all changes when they
  occurred (and by whom, and for what reason)?

18
Time-Dependent Data
Assume an items unit price may change over time.
How do we know what price an item was sold at on
a particular sales order line?
19
Time-Dependent Data
What if we want to track the history of price
changes to each item?