Transcript Data Modeling - Gonzaga University

Data Modeling
Professor Chen
School of Business Administration
Gonzaga University
Spokane, WA 99258
Dr. Chen, Business Database Systems
TM 1
Data Modeling
• The data model is a
cornerstone for every
information system,
because it describes
the entities that the
system will create and
maintain during its
lifetime.
Dr. Chen, Business Database Systems
TM 2
MODELING ENTERPERISE DATA
Enterprise View
Conceptual
Data Models
Congruent
Business
Needs
Coporate
strategies/
goals etc.
Logical Models
Maintenance
Physical Models
ProductionSystem
Data Modeling (Cont.)
• Building the data model is probably the
most important activity during requirements
definition, because in the process of
understanding how the data is organizaed
and identifying the relationships that exist
between entities, you can discover most of
the functionality that the system will satisfy.
Dr. Chen, Business Database Systems
TM 4
Business area of Data Model
• The data model of a business area tends to be
relatively stable, compared, for example, to the
set of operational procedures or organizational
structure, which changes frequently.
• Therefore, basing the implementation of the
future system upon a well-defined data model is
a good first step towards developing a system
that meets the real requirements of the users.
Dr. Chen, Business Database Systems
TM 5
Techniques and Approaches
• Two techniques are used interchangeably to
model the data of a system:
– Entity relationship model (E-R/M)
– Data normalization
Dr. Chen, Business Database Systems
TM 6
E-R/M and Normalization
• E-R/M (and EE-R/M) aims at identifying
the entities that are part of the system, the
attributes that make up these entities, and
the dependencies between entities.
• Normalization makes the data model
created using the E-R more robust and
extends the life of systems based on the
model.
Dr. Chen, Business Database Systems
TM 7
E-R and Normalization (Cont.)
• These two techniques
go hand in hand and
should be applied
conscientiously during
data modeling
activities.
Dr. Chen, Business Database Systems
TM 8
Approaches to Data Modeling
• Two approaches to
data modeling:
 Top-down
 Bottom-up
Dr. Chen, Business Database Systems
TM 9
Top-Down vs.
Bottom-Up Approach
Bottom-Up
TOP-DOWN
Identify Entities
Identify Entities
Discover Relationship
Discover Relationship
Define Attributes
Analyze/synthesize Data
Define Attributes
Collect Data
Dr. Chen, Business Database Systems
TM 10
Top-Down vs.
Bottom-Up Approach
Bottom-Up
TOP-DOWN
Identify Entities
Identify Entities
Discover Relationship
Discover Relationship
Define Attributes
Analyze/synthesize Data
Define Attributes
Collect Data
Dr. Chen, Business Database Systems
TM 11
Why Normalization?
• Remove the anomalies due to
– lack of flexibility
– ineffectiveness of data storage
– inefficiency of data manipulation operations
inserts (additions)
deletes (deletion)
updates (modification)
Dr. Chen, Business Database Systems
TM 12
Denormalization and Why?
• Denormalization is the process of
transforming normalized relations into
unnormalized physical record specifications.
• A normalized form (e.g., 3NF) presents
many important features, however, it may
required that queries join terms from
different entities more often than in a less
normalized data model.
Dr. Chen, Business Database Systems
TM 13
Another example of
denormalization
CUSTOMER
CustID
...
Normalization
ZIP CITY
Dr. Chen, Business Database Systems
CITY STATE
ZIP
? NO!
ZIP STATE
ZIP CITY STATE
TM 14
Denormalization : CUSTOMER
• In a denormalized version of the
CUSTOMER entity, ZIP, CITY, and STATE
may be defined as attributes of the entity
whose value is derived from entity ZIP and
stored redundantly with the other address
properties of each CUSTOMER.
Dr. Chen, Business Database Systems
TM 15
New Business Rules
• To maintain the data consistency, new
business rules should be implemented:
the values of attributes CITY and STATE will
be updated only in the entity ZIP. In all other
entities where these attributes are denormalized,
applications may insert and select but not
modify these attributes.
Dr. Chen, Business Database Systems
TM 16
New Business Rules (cont.)
 when the attributes CITY and STATE of
entity ZIP are modified, the changes will be
propagated in all the denormalized CITY and
STATE attributes of other entities.
Dr. Chen, Business Database Systems
TM 17
Un-normalization vs.
De-normalization
• Unnormalized data model is little or no
analysis is performed.
• Denormalized data model is derived from a
model in the 3NF, after the reasons and
impact of denormalizing some entities are
identified and weighed.
Dr. Chen, Business Database Systems
TM 18
Conclusion
• When parts of the data model are
denormalized, the rules for maintaining the
redundant data must be defined clearly and
implemented programmatically, in order to
avoid the anomalies discussed before.
Dr. Chen, Business Database Systems
TM 19
Are you still ...
Dr. Chen, Business Database Systems
TM 20