Systems Analysis and Design

5th Edition

Chapter 11. Data Storage Design

Alan Dennis, Barbara Haley Wixom, and Roberta Roth

© Copyright 2011 John Wiley & Sons, Inc. 11-0

 Chapter 11 Outline

▪Data storage formats.

- Files.
- Databases.
▪Moving from logical to physical data
models.
▪Optimizing data storage.
© Copyright 2011 John Wiley & Sons, Inc. 11-1
 INTRODUCTION
▪ The data storage function is concerned with how data
is stored and handled by programs that run the system.
▪ Data storage design is to
- select the data storage format;
- convert the logical data model created during analysis
into a physical data model to reflect the
implementation decision;
- ensure that DFDs and ERDs balance; and
- design the selected data storage format to optimize its
processing efficiency.
© Copyright 2011 John Wiley & Sons, Inc. 11-2
Data Storage Formats
• Two main types of data storage formats:
o Files: electronic lists of data, optimized to perform a particular
transaction
o Database: a collection of groupings of information that are
related to each other in some way
• Database Management System (DBMS): software that
creates and manipulates the databases

Copyright ©2022 John Wiley & Sons, Inc. 10-3

 Files

▪ A data file contains an electronic list of information

that is formatted for a particular transaction.
▪ Typically, files are organized sequentially.
▪ Records can be associated with other records by
pointers.
▪ Sometimes files are called linked Lists because of
the way the records are linked together using
pointers.
© Copyright 2011 John Wiley & Sons, Inc. 11-4
Types of Files
• Master files – store core information that is important
to the application
• Look-up files – contain static values
• Transaction files – store information that can be used
to update a master file
• Audit files – record “before” and “after” images of data
as the data is altered
• History files (or archive files) – store past transactions

Copyright ©2022 John Wiley &

10-5
Sons, Inc.
File based DB: appointment record

© Copyright 2011 John Wiley & Sons, Inc. 11-6

Example of
database:

© Copyright 2011 John Wiley & Sons, Inc. 11-7

 Databases

▪There are many types of databases:

▪ Relational database
▪ Object database
▪ Multidimensional database
▪ NoSQL database

© Copyright 2011 John Wiley & Sons, Inc. 11-8

 Relational Databases
▪ The relational database is the most popular kind of
database for application development today.
▪ A relational database is based on collections of tables,
each of which has a primary key.
▪ The tables are related to each other by the placement
of the primary key from one table into the related table
as a foreign key.
▪ Most relational database management systems (RDBMS)
support referential integrity, or the idea of ensuring that
values linking the tables together are valid and correctly
synchronized.
▪ Structured Query Language (SQL) is the standard language
for accessing the data in the tables.
© Copyright 2011 John Wiley & Sons, Inc. 11-9
 (cont’d)
Relational
database
example

© Copyright 2011 John Wiley & Sons, Inc. 11-10

 Object Databases

▪ The object database, or object-oriented

database, is based on the premise of object
orientation that all things should be treated as
objects that have both data (attributes) and
processes (behaviors).
▪ Changes to one object have no effect on other
objects because the attributes and behaviors
self-contained, or encapsulated, within each one.
▪ This encapsulation allows objects to be reused.
© Copyright 2011 John Wiley & Sons, Inc. 11-11
(cont’d)

▪Object Database Example

© Copyright 2011 John Wiley & Sons, Inc. 11-12

 Multidimensional Databases
▪ A multidimensional database is a type of relational
database that is used extensively in data
warehousing.
▪ Data warehousing is the practice of taking and
storing data in a data warehouse (i.e., a large
database) that supports decision support systems
(DSS).
▪ Data marts are smaller databases based on data
warehouse data, and support DSS for specific
departments or ©functional areas of the organization.
Copyright 2011 John Wiley & Sons, Inc. 11-13
NoSQL Databases
• Newest database approach; not based on the relational
model or SQL
• Rapid processing on replicated database servers in the
cloud
• Various types include:
o Document-oriented databases: manage collection of
documents of varying forms and structures (example: Mongo
DB)
o Wide column databases: store data in records holding very
large numbers of dynamic columns (potentially billions of
columns). Example: Bigtable, Cassandra, Dynamo
o Graph databases: a collection of nodes and edges using
graph theory to store, map, and query relationships

Copyright ©2022 John Wiley &

10-14
Sons, Inc.
 Selecting a Storage Format
Comparing Data Storage Formats
Files Legacy
databases
Relational
databases
Object
databases
Multidimensional
databases
NoSQL
databases
Major Files can be Very mature Leader in the Able to handle Configured to Designed for huge,
strengths designed for fast products database market; complex data answer business varied data sets
performance; can handle fast intelligence
good for updating and questions quickly
short-term data querying needs
storage.
Major Redundant data; Not able to Cannot handle Limited market Highly specialized New in the market,
weaknesses data must be store data as complex data acceptance; use; skills are highly specialized
updated using efficiently; skills are hard to hard to find use; skills are hard
programs. limited future find. to find
Data types Simple Not Simple Complex (e.g., Aggregated Mixed data sets
supported recommended video, audio, with structured and
for new systems images) unstructured
components
Application Transaction Not Transaction Transaction Business Business
system types processing recommended processing and processing intelligence intelligence; finding
supported for new systems decision making patterns and
relationships in
mixed data
Existing data Organization Organization Organization Organization Organization Organization
formats dependent dependent dependent dependent dependent dependent

Future needs Limited future Poor future Good future Uncertain Uncertain future New, uncertain
prospects prospects prospects future prospects future prospects
prospects
© Copyright 2011 John Wiley & Sons, Inc. 11-15
MOVING FROM LOGICAL TO PHYSICAL

© Copyright 2011 John Wiley & Sons, Inc. 11-16

 Conceptual, Logical and Physical
▪ Conceptual, logical and physical model or ERD are
three different ways of modeling data in a domain.

▪ Business analyst uses conceptual and logical model

for modeling the data required and produced by
system from a business angle

▪ while database designer refines the early design to

produce the physical model for presenting physical
database structure ready for database construction.11-17
© Copyright 2011 John Wiley & Sons, Inc.
 Conceptual Model

▪ Conceptual ERD models

information gathered
from business
requirements.
▪ Entities and relationships
modeled in such ERD are
defined around the
business's need.
▪ The need of satisfying the
database design is not
considered yet.
© Copyright 2011 John Wiley & Sons, Inc. 11-18
 Logical Model

▪ Logical ERD also models information gathered from business requirements. It is

more complex than conceptual model in that column types are set. Note that
the setting of column types is optional and if you do that, you should be doing
that to aid business analysis. It has nothing to do with database creation yet.
 Physical Model
▪ Actual design blueprint of a relational database.
▪ It represents how data should be structured and related
in a specific DBMS so it is important to consider the
convention and restriction of the DBMS you use when
you are designing a physical ERD.
▪ This means that an accurate use of data type is needed
for entity columns and the use of reserved words has to
be avoided in naming entities and columns.
▪ Besides, database designers may also add primary keys,
foreign keys and constraints to the design.
© Copyright 2011 John Wiley & Sons, Inc. 11-20
Physical Model

11-21
Meta
data

© Copyright 2011 John Wiley & Sons, Inc. 11-22

 OPTIMIZING DATA STORAGE

▪The data storage format is now optimized

for processing efficiency.
▪There are two primary dimensions in
which to optimize a relational database:
for storage efficiency and for speed of
access.

© Copyright 2011 John Wiley & Sons, Inc. 11-23

 Optimizing Storage Efficiency

▪The most efficient tables in a relational

database in terms of storage space have
no redundant data and very few null
values.
▪Normalization is the best way to optimize
data storage for efficiency.

© Copyright 2011 John Wiley & Sons, Inc. 11-24

(cont’d)
Optimizing data storage

© Copyright 2011 John Wiley & Sons, Inc. 11-25

 Optimizing Access Speed
▪ After having optimized the data model design for data
storage efficiency, the end result is that data are spread out
across a number of tables.
▪ For a large relational database, it is necessary to optimize
access speed.
▪ There are several techniques of optimizing access speed:
– Denormalization
– Clustering
– Indexing
– Estimating the size of data for hardware planning
© Copyright 2011 John Wiley & Sons, Inc. 11-26
11-27
(cont’d)
There are four
reasons for
de-normalization.

© Copyright 2011 John Wiley & Sons, Inc. 11-28

 Clustering
▪ Clustering – placing records together
physically so that like records are stored
close together.
▪ Intrafile clustering – Similar records in the
table are stored together.
▪ Interfile clustering – Combining records
from more that one table that typically are
retrieved together.
© Copyright 2011 John Wiley & Sons, Inc. 11-29
 Indexing
▪An index in data storage is a minitable
(similar to an index of a book) that contains
values from one or more columns in a table
and the location of the values within the
table.
▪Indexes require overhead in that they take
up space on the storage.
© Copyright 2011 John Wiley & Sons, Inc. 11-30
(cont’d)
Example of indexing

© Copyright 2011 John Wiley & Sons, Inc. 11-31

(cont’d)

▪Guidelines for creating indexes

© Copyright 2011 John Wiley & Sons, Inc. 11-32

 Estimating Storage Size

▪ Volumetrics – technique of estimating the

amount of data that the hardware will need to
support.
1. Calculate the amount of raw data - all the data
that are stored within the tables of the database.
2. Calculate the overhead requirements based on
the DBMS vendor’s recommendations.
3. Record the number of initial records that will be
loaded into the table, as well as the expected
growth per month.
© Copyright 2011 John Wiley & Sons, Inc. 11-33
(cont’d)

▪Example of calculating volumetrics

© Copyright 2011 John Wiley & Sons, Inc. 11-34

 SUMMARY
▪ File data storage formats
- Files are electronic lists of data.
- Five types of files: master, look-up, transaction, audit, and
history.
▪ Database storage formats
- A database is a collection of groupings of information
- A DBMS is software that creates and manipulates these
databases.
▪ Selecting a data storage format
- Relational databases support simple data types very
effectively, whereas object databases are best for complex
data.
© Copyright 2011 John Wiley & Sons, Inc. 11-35
(cont’d)
▪ Physical entity relationship diagrams
- Physical ERDs contain references to how data will
be stored in a file or database table, and
metadata are included.
▪ Optimizing data storage
- There are two primary dimensions in which to
optimize a relational database: for storage
efficiency and for speed of access.
- There are a number of techniques of optimizing
data storage. © Copyright 2011 John Wiley & Sons, Inc. 11-36