Chapter 6
Data Modeling
SYSTEMS ANALYSIS AND DESIGN
SEVENTH EDITION
DENNIS, WIXOM, AND ROTH
Learning Objectives
• Explain the rules and style guidelines for creating entity
relationship diagrams (ERDs).
• Create an ERD.
• Describe the use of a data dictionary and metadata.
• Explain how to balance ERDs and data flow diagrams.
• Describe the process of normalization.
Copyright ©2019 John Wiley & Sons, Inc. 2
Key Definitions (1 of 2)
• Data model
• A formal way of representing the data that are used and
created by a business system
• Shows the people, places and things about which data is
captured and the relationships among them.
• Logical data model shows the organization of data without
indicating how it is stored, created, or manipulated
• Physical data model shows how the data will actually be
stored in databases or files.
Copyright ©2019 John Wiley & Sons, Inc. 3
Key Definitions (2 of 2)
• Entity Relationship Diagram (ERD)
• A popular way to depict the data model
• Normalization is the process analysts use to validate
data models.
• Data models should balance with process models
Copyright ©2019 John Wiley & Sons, Inc. 4
Why Is Data Modeling Crucial?
• Data is a resource to be shared by as many processes
as possible.
• Data organization must be flexible and adaptable to
unanticipated business requirements – and that is the
purpose of data modeling.
Copyright ©2019 John Wiley & Sons, Inc. 5
Other Data Modeling Issues…
• Data structures and properties are reasonably
permanent –more stable than the processes that use
the data.
• Typically very similar to the existing system.
• Data models are much smaller than process models
and are constructed more rapidly.
• Constructing the data model helps analysts and users
quickly reach consensus on business terminology and
rules.
Copyright ©2019 John Wiley & Sons, Inc. 6
Entity Relationship Diagrams
WHAT DO ERDS TELL US?
Copyright ©2019 John Wiley & Sons, Inc. 7
Reading an ERD
Copyright ©2019 John Wiley & Sons, Inc. 8
Using the ERD to Show Business Rules (1 of 3)
• Business rules are constraints that are followed when
the system is in operation.
• ERD symbols can show when one instance of an entity
must exist for an instance of another to exist
• The customer instance must exist before custom drone order
instances can be created for that customer
• The drone component instance must exist before ordered
drone components instances can be created for that drone
component
Copyright ©2019 John Wiley & Sons, Inc. 9
Using the ERD to Show Business Rules (2 of 3)
• ERD symbols can show when one instance of an entity
can be related to only one or to many instances of
another entity
• One customer instance can create many custom drone order
instances; each custom drone order instance is created by
only one customer instance
• A drone can be included on many chemical requests; a
chemical request is for only one chemical
Copyright ©2019 John Wiley & Sons, Inc. 10
Using the ERD to Show Business Rules (3 of 3)
• ERD symbols show when the existence of an entity
instance is optional for a related entity instance
• A customer instance may exist without being included on any
custom drone order instances
Copyright ©2019 John Wiley & Sons, Inc. 11
An ERD Example
Copyright ©2019 John Wiley & Sons, Inc. 12
Entity
• A person, place, event, or thing about which data is
collected
• Must be multiple occurrences to be an entity
Person Place Object Event Concept
STUDENT DORM BOOK LIBRARY CHECK COURSE
OUT
Copyright ©2019 John Wiley & Sons, Inc. 13
CASE Entry for Entity
Copyright ©2019 John Wiley & Sons, Inc. 14
Attributes
• Information captured about an entity
• Only those used by the organization should be included
in the model
• Attribute names are nouns
• Sometimes entity name is added at the beginning of
the attribute name for clarity
Copyright ©2019 John Wiley & Sons, Inc. 15
CASE Entry for Attribute
Copyright ©2019 John Wiley & Sons, Inc. 16
Attributes – Understand Them!
Copyright ©2019 John Wiley & Sons, Inc. 17
Identifier Types (1 of 2)
• One or more attributes can serve as the entity
identifier, uniquely identifying each entity instance
• Concatenated identifier consists of several attributes
• An identifier may be ‘artificial,’ such as creating an ID
number
• Final decision on identifiers may postponed to the
Design Phase
Copyright ©2019 John Wiley & Sons, Inc. 18
Identifier Types (2 of 2)
Copyright ©2019 John Wiley & Sons, Inc. 19
Relationships
• Associations between entities
• The first entity in the relationship is the parent entity;
the second entity in the relationship is the child entity
• Relationships should have active verb names
• Relationships go in both directions
Copyright ©2019 John Wiley & Sons, Inc. 20
Cardinality
• Refers to the number of times instances in one entity
can be related to instances in another entity
• One instance in an entity refers to one and only one instance
in the related entity (1:1)
• One instance in an entity refers to one or more instances in
the related entity (1:N)
• One or more instances in an entity refer to one or more
instances in the related entity (M:N)
Copyright ©2019 John Wiley & Sons, Inc. 21
Modality
• Refers to whether or not an instance of a child entity
can exist without a related instance in the parent entity
• Not Null - an instance in the related entity must exist for an
instance in another entity to be valid
• Null - no instance in the related entity is necessary for an
instance in another entity to be valid
Copyright ©2019 John Wiley & Sons, Inc. 22
CASE Entry for Relationship
Copyright ©2019 John Wiley & Sons, Inc. 23
Binary Relationships
Copyright ©2019 John Wiley & Sons, Inc. 24
Foreign Keys (1 of 2)
• A relationship implies that instances of one entity are
related to instances of another entity
• The primary key of one entity is migrated into the
other entity as a foreign key.
• A foreign key is a primary key of one entity that is
contributed to (duplicated in) another entity for the
purpose of identifying instances of a relationship. A
foreign key (always in a child entity) always matches
the primary key (in a parent entity).
Copyright ©2019 John Wiley & Sons, Inc. 25
Foreign Keys (2 of 2)
Copyright ©2019 John Wiley & Sons, Inc. 26
Creating an ERD
HOW ERDS ARE DEVELOPED
Copyright ©2019 John Wiley & Sons, Inc. 27
Overview
• Drawing the ERD is an iterative process of trial and
revision
• ERDs can become quite complex
• Steps in building ERDs…
• Identify the entities
• Add appropriate attributes for each entity
• Draw the relationships that connect associated entities
Copyright ©2019 John Wiley & Sons, Inc. 28
Identify the Entities
• Identify major categories of information
• If available, check the process models for data stores, external
entities, and data flows
• Check the major inputs and outputs from the use cases
• Verify that there is more than one instance of the
entity that occurs in the system
Copyright ©2019 John Wiley & Sons, Inc. 29
Add Appropriate Attributes
• Identify attributes of the entity that are relevant to the
system under development
• Check the process model repository entries for details on data
flows and data stores
• Check the data requirements of the requirements definition
• Interview knowledgeable users
• Perform document analysis on existing forms and reports
• Select the entity’s candidate identifier (final decision
may be postponed until Design phase)
Copyright ©2019 John Wiley & Sons, Inc. 30
Draw the Relationships
• Start with an entity and identify all entities with which
it shares relationships
• Describe the relationship with the appropriate verb
phrase
• Determine the cardinality and modality by discussing
the business rules with knowledgeable users
Copyright ©2019 John Wiley & Sons, Inc. 31
ERD Building Tips
• Data stores of the DFD generally correspond to entities
• Only include entities with more than one instance
• Don’t include entities associated with implementation
of the system (e.g., archive files of older data). They
will be added later.
Copyright ©2019 John Wiley & Sons, Inc. 32
Advanced Syntax - Intersection Entities
• A new entity is created to store information about two
entities sharing an M:N relationship
• Remove the M:N relationship between two entities and insert
new entity between them
• Create two 1:N relationships: original entities are parents to
the new child intersection entity
• Name the intersection entity
• Migrate parent entity primary keys to new entity as foreign
keys (possibly also concatenated primary key)
Copyright ©2019 John Wiley & Sons, Inc. 33
Resolving M:N with an Intersection Entity
Copyright ©2019 John Wiley & Sons, Inc. 34
Resolving M:N Relationship
Additional Example
Copyright ©2019 John Wiley & Sons, Inc. 35
Validating an ERD
ENSURING A QUALITY DATA MODEL
Copyright ©2019 John Wiley & Sons, Inc. 36
Design Guidelines
• Best practices rather than rigid rules
• Entities should have many occurrences
• Avoid unnecessary attributes
• Clearly label all components
• Apply correct cardinality and modality
• Break attributes into lowest level needed
• Labels should reflect common business terms
• Assumptions should be clearly stated
Copyright ©2019 John Wiley & Sons, Inc. 37
Balancing ERDs with DFDs
• All analysis activities are interrelated
• Process models contain two data components
• Data flows and data stores
• The DFD data components need to balance the ERD’s data
stores (entities) and data elements (attributes)
• Many CASE tools provide features to check for imbalance
• Check that all data stores and elements correspond
between models
• Data that is not used is unnecessary
• Data that has been omitted results in an incomplete system
• Do not follow thoughtlessly -- check that the models make
sense!
Copyright ©2019 John Wiley & Sons, Inc. 38
Use of a CRUD Matrix
Copyright ©2019 John Wiley & Sons, Inc. 39
Normalization
• Technique used to validate data models
• Series of rules applied to logical data model to improve
its organization
• Three normalization rules are common
Copyright ©2019 John Wiley & Sons, Inc. 40
Example 1: Unnormalized Entity
Begin with an entity from the logical data model
Do any attributes (or groups
of attributes) occur more than
once for a single occurrence
of the entity?
Copyright ©2019 John Wiley & Sons, Inc. 41
Example 1: 1st Normal Form
Do any attributes (or groups of attributes) occur more than once for a single
occurrence of the entity?
If yes, remove the attributes (or groups) into separate entities.
ORDER ORDERED ITEM
OrderNumber Item Number
OrderNumber
includes
OrderDate
is included in
CustomerName Item Name
CustomerAddress consisting of: Quantity Ordered
Street Item Unit
City Quantity Shipped
State Item Price
ZipCode
CustomerType
Initials
District Number
Region Number
Copyright ©2019 John Wiley & Sons, Inc. 42
Potential Anomalies with 1st Normal Form
ORDER ORDERED ITEM
Insert anomaly:
OrderNumber Item Number
includes
OrderNumber Insert a new Item?
OrderDate
is included in
CustomerName Item Name
CustomerAddress consisting of: Quantity Ordered Can’t do without Order Number
Street Item Unit
City Quantity Shipped
State
ZipCode
Item Price
Deletion anomaly:
CustomerType
Initials Assume only one order has been made
District Number
Region Number
for Item #456. What happens if that
order is cancelled?
Will lose all information about Item # 456
Update anomaly:
The price of Item # 789 should be changed from $7.99 to $8.95. What problem
occurs?
Need to search entire database for all occurrences of Item # 789
Copyright ©2019 John Wiley & Sons, Inc. 43
Potential Anomalies with 1st Normal Form
ORDER ORDERED ITEM Reason anomalies exist?
OrderNumber Item Number
includes
OrderNumber Several non-key attributes depend
OrderDate
CustomerName
is included in
Item Name only on ItemNumber and NOT on
CustomerAddress consisting of:
Street
Quantity Ordered
Item Unit
the full primary key (ItemNumber +
City Quantity Shipped OrderNumber)
State Item Price
ZipCode
CustomerType
Initials
District Number
Region Number
Copyright ©2019 John Wiley & Sons, Inc. 44
Example 1: 2nd Normal Form
For entities with concatenated keys…
Do any attributes depend on just part of the key rather than the entire key?
If yes, move partially-dependent attributes to a new entity…
ORDER ORDERED ITEM ITEM
OrderNumber includes/ Item Number Is included in/ Item Number
is included in OrderNumber includes
OrderDate Item Name
CustomerName Quantity Ordered Item Unit
CustomerAddress consisting of: Quantity Shipped Item Price
Street
City
State
ZipCode
CustomerType
Initials
District Number
Region Number
Copyright ©2019 John Wiley & Sons, Inc. 45
Potential Anomalies with 2nd Normal Form
ORDER ORDERED ITEM ITEM
Reason Anomalies
Exist:
OrderNumber includes/ Item Number Is included in/ Item Number
is included in OrderNumber includes
OrderDate Item Name
CustomerName
CustomerAddress consisting of:
Quantity Ordered
Quantity Shipped
Item Unit
Item Price The ORDER entity
Street
City contains transitive
State
ZipCode dependencies
CustomerType
Initials
District Number
Region Number
This means that several non-key attributes depend on another non-key
attribute, and NOT on the Primary Key
CustomerAddress, CustomerType, DistrictNumber, and RegionNumber depend
on the CustomerName, NOT the OrderNumber
Solution: remove these attributes to a separate entity
Copyright ©2019 John Wiley & Sons, Inc. 46
Example 1: 3rd Normal Form
Do any attribute values depend on an attribute that is not the entity’s key?
If yes, move these attributes to a new entity.
CUSTOMER ORDER ORDERED ITEM ITEM
CustomerNumber places/ OrderNumber includes/ Item Number is included in/ Item Number
is placed by is included in OrderNumber includes
CustomerName OrderDate Item Name
CustomerAddress consisting of: CustomerNumber Quantity Ordered Item Unit
Street Initials Quantity Shipped Item Price
City
State
ZipCode
CustomerType
District Number
Region Number
Copyright ©2019 John Wiley & Sons, Inc. 47
Summary of Normalization Steps
Copyright ©2019 John Wiley & Sons, Inc. 48
After reading and studying this chapter,
you should be able to: (1 of 2)
• Define the meaning and purpose of the entity and
relationship shown on an entity relationship diagram
(ERD).
• Explain the meaning and purpose of attributes
included in a data model.
• Explain what is meant by an entity’s identifier.
• Explain the meaning of the cardinality and modality of
a relationship.
Copyright ©2019 John Wiley & Sons, Inc. 49
After reading and studying this chapter,
you should be able to: (2 of 2)
• Explain the concept of metadata and how it is
compiled in the project repository.
• Discuss the process used to create a data model.
• Describe how to ensure that the process model and
data model are balanced through the use of the CRUD
matrix.
• Discuss how the normalization process is performed
and how it contributes to the quality of the data
model (from chapter appendix).
Copyright ©2019 John Wiley & Sons, Inc. 50
Copyright
Copyright © 2019 John Wiley & Sons, Inc.
All rights reserved. Reproduction or translation of this work beyond that permitted in
Section 117 of the 1976 United States Act without the express written permission of the
copyright owner is unlawful. Request for further information should be addressed to the
Permissions Department, John Wiley & Sons, Inc. The purchaser may make back-up
copies for his/her own use only and not for distribution or resale. The Publisher assumes
no responsibility for errors, omissions, or damages, caused by the use of these programs
or from the use of the information contained herein.
Copyright ©2019 John Wiley & Sons, Inc. 51
