Chapter 13

Designing Databases

Systems Analysis and Design

Kendall & Kendall
Sixth Edition
 Major Topics
• Files
• Databases
• Normalization
• Key design
• Using the database
• Data warehouses
• Data mining
Kendall & Kendall © 2005 Pearson Prentice Hall 13-2
 Data Storage Design
Objectives
The objectives in the design of data
storage organization are:
• The data must be available when the user
wants to use it.
• The data must have integrity.
• It must be accurate and consistent.
• Efficient storage of data as well as efficient
updating and retrieval.

Kendall & Kendall © 2005 Pearson Prentice Hall 13-3

 Data Storage Design
Objectives
• The objectives in the design of data storage
organization are (continued):
• The information retrieval be purposeful.
• The information obtained from the stored data
must be in an integrated form to be useful for:
• Managing.
• Planning.
• Controlling.
• Decision making.

Kendall & Kendall © 2005 Pearson Prentice Hall 13-4

 Approaches to Data Storage
There are two approaches to the
storage of data in a computer system:
• Store the data in individual files each
unique to a particular application.
• Storage of data in a computer-based
system involves building a database.
• A database is a formally defined and centrally
controlled store of data intended for use in
many different applications.

Kendall & Kendall © 2005 Pearson Prentice Hall 13-5

 Files
• A file can be designed and built quite
rapidly, and the concerns for data
availability and security are minimized.
• Analysts can choose an appropriate file
structure according to the required
processing speed of the particular
application system.

Kendall & Kendall © 2005 Pearson Prentice Hall 13-6

 Objectives of Effective
Databases
The effectiveness objectives of the
database include:
• Ensuring that data can be shared among
users for a variety of applications.
• Maintaining data that are both accurate
and consistent.
• Ensuring all data required for current and
future applications will be readily available.

Kendall & Kendall © 2005 Pearson Prentice Hall 13-7

 Objectives of Effective
Databases
• The effectiveness objectives of the
database include (continued):
• Allowing the database to evolve and the
needs of the users to grow.
• Allowing users to construct their personal
view of the data without concern for the
way the data are physically stored.

Kendall & Kendall © 2005 Pearson Prentice Hall 13-8

 Metadata
Metadata is the information that
describes data in the file or database.
• Used to help users understand the form
and structure of the data

Kendall & Kendall © 2005 Pearson Prentice Hall 13-9

 Reality, Data, and Metadata

Kendall & Kendall © 2005 Pearson Prentice Hall 13-10

 Entity-Relationship Concepts
• Entities are objects or events for which
data is collected and stored.
• An entity subtype represents data about
an entity that may not be found on
every record.
• Relationships are associations between
entities.

Kendall & Kendall © 2005 Pearson Prentice Hall 13-11

 Entities
A distinct collection of data for one
person, place, thing, or event.

Customer

Kendall & Kendall © 2005 Pearson Prentice Hall 13-12

 Student

Entity Subtype
Internship

• An entity subtype is a special one-to-one

relationship used to represent additional
attributes, which may not be present on
every record of the first entity.
• This eliminates null fields on the primary
database.
• For example, a company that has preferred
customers, or student interns may have
special field.
Kendall & Kendall © 2005 Pearson Prentice Hall 13-13
 Associative Entity
• Associative Entity - links two entities
• An associative entity can only exist
between two entities

Order
Item

Kendall & Kendall © 2005 Pearson Prentice Hall 13-14

 Attributive Entity
• An attributive Entity - describes
attributes, especially repeating
elements.
Book
Subject

Kendall & Kendall © 2005 Pearson Prentice Hall 13-15

 Entity-Relationship
Diagram Symbols

Kendall & Kendall © 2005 Pearson Prentice Hall 13-16

 Relationships
• Relationships may be:
• One-to-one.
• One-to-many.
• Many-to-many.
• A single vertical line represents one.
• A circle represents zero or none.
• A crows foot represents many.
Kendall & Kendall © 2005 Pearson Prentice Hall 13-17
 Relationships

Many One

Many NoneO

Kendall & Kendall © 2005 Pearson Prentice Hall 13-18

 Self-Join

A self-join is when a record has a

relationship with another record on the
same file.

Kendall & Kendall © 2005 Pearson Prentice Hall 13-19

 Entity-Relationship Diagram
Example

Kendall & Kendall © 2005 Pearson Prentice Hall 13-20

 Attributes, Records, and Keys
• Attributes are a characteristic of an
entity, sometimes called a data item.
• Records are a collection of data items
that have something in common.
• Keys are data items in a record used to
identify the record.

Kendall & Kendall © 2005 Pearson Prentice Hall 13-21

 Key Types
• Key types are:
• Primary key, unique for the record.
• Secondary key, a key which may not be
unique, used to select a group of records.
• Concatenated key, a combination of two or
more data items for the key.
• Foreign key, a data item in one record that
is the key of another record.

Kendall & Kendall © 2005 Pearson Prentice Hall 13-22

 Files
• A file contains groups of records used
to provide information for operations,
planning, management, and decision
making.
• Files can be used for storing data for an
indefinite period of time, or they can be
used to store data temporarily for a
specific purpose.
Kendall & Kendall © 2005 Pearson Prentice Hall 13-23
 File Types
Types of files available are:
• Master file.
• Table file.
• Transaction file.
• Work file.
• Report file.

Kendall & Kendall © 2005 Pearson Prentice Hall 13-24

 Master and Transaction Files
• Master files
• Have large records
• Contain all pertinent information about an
entity
• Transaction records
• Are short records
• Contain information used to update master
files

Kendall & Kendall © 2005 Pearson Prentice Hall 13-25

 File Organization
• The different organizational structures
for file design are:
• Sequential organization.
• Linked lists.
• Hashed file organization.

Kendall & Kendall © 2005 Pearson Prentice Hall 13-26

 Databases
• A database is intended to be shared by
many users.
• There are three structures for storing
database files:
• Relational database structures.
• Hierarchical database structures (older).
• Network database structures (older).

Kendall & Kendall © 2005 Pearson Prentice Hall 13-27

 Logical and Physical Database
Design

Kendall & Kendall © 2005 Pearson Prentice Hall 13-28