Modern Systems Analysis
and Design
Sixth Edition
Jeffrey A. Hoffer
Joey F. George
Joseph S. Valacich
Chapter 9
Designing Databases
Learning Objectives
Concisely define
each of the following key database design terms: relation,
primary key, normalization, functional dependency, field, data
type, null value, and secondary key.
Explain
the role of designing databases in the analysis and design of an
information system.
Transform
an entity-relationship (E-R) diagram into an equivalent set of
well-structured (normalized) relations.
© 2011 Pearson Education, Inc. Publishing as Prentice
Chapter 9 Hall 2
Introduction
FIGURE 9-1
Systems development
life cycle with design
phase highlighted
© 2011 Pearson Education, Inc. Publishing as Prentice
Chapter 9 Hall 3
Database Design
File and database design occurs in two steps.
1. Develop a logical database model, which describes data using
notation that corresponds to a data organization used by a
database management system.
Relational database model
2. Prescribe the technical specifications for computer files and
databases in which to store the data.
Physical database design provides specifications
Logical and physical database design in parallel with
other system design steps
© 2011 Pearson Education, Inc. Publishing as Prentice
Chapter 9 Hall 4
FIGURE 9-2
Relationship between data modeling and the systems development life cycle
© 2011 Pearson Education, Inc. Publishing as Prentice
Chapter 9 Hall 5
The Process of Database Design
(Cont.)
Four key steps in logical database modeling
and design:
1. Develop a logical data model for each known user interface for
the application using normalization principles.
1. Combine normalized data requirements from all user interfaces
into one consolidated logical database model (view integration).
© 2011 Pearson Education, Inc. Publishing as Prentice
Chapter 9 Hall 6
The Process of Database Design
(Cont.)
Four key steps in logical database modeling
and design:
3. Translate the conceptual E-R data model for the application into
normalized data requirements.
4. Compare the consolidated logical database design with the
translated E-R model and produce one final logical database
model for the application.
© 2011 Pearson Education, Inc. Publishing as Prentice
Chapter 9 Hall 7
Physical Database Design
Key physical database design decisions include:
Choosing a storage format for each attribute from the
logical database model.
Arranging related records in secondary memory (hard
disks) so that records can be stored, retrieved and
updated rapidly.
Selecting media and structures for storing data to make
access more efficient.
© 2011 Pearson Education, Inc. Publishing as Prentice
Chapter 9 Hall 8
Deliverables and Outcomes
Logical database design
Must account for every data element on a system
input or output.
Normalized relations are the primary deliverable.
Physical database design
Converts relations into database tables.
Programmers and database analysts code the definitions
of the database.
Written in Structured Query Language (SQL).
© 2011 Pearson Education, Inc. Publishing as Prentice
Chapter 9 Hall 9
FIGURE 9-3 (d)
Conceptual data
model and
transformed relations
© 2011 Pearson Education, Inc. Publishing as Prentice
Chapter 9 Hall 10
Relational Database Model
Relational database model:
data represented as a set of related tables
or relations
Relation:
a named, two-dimensional table of data;
each relation consists of a set of named
columns and an arbitrary number of
unnamed rows
© 2011 Pearson Education, Inc. Publishing as Prentice
Chapter 9 Hall 11
Well-Structured Relation and
Primary Keys
Well-Structured Relation (or table)
A relation that contains a minimum amount of redundancy
Allows users to insert, modify, and delete the rows without
errors or inconsistencies
Primary Key
An attribute whose value is unique across all occurrences of
a relation
All relations have a primary key.
This is how rows are ensured to be unique.
A primary key may involve a single attribute or be composed
of multiple attributes.
© 2011 Pearson Education, Inc. Publishing as Prentice
Chapter 9 Hall 12
Normalization and Rules of
Normalization
Normalization:
the process of converting complex data
structures into simple, stable data structures
The result of normalization is that every
nonprimary key attribute depends upon the
whole primary key.
© 2011 Pearson Education, Inc. Publishing as Prentice
Chapter 9 Hall 13
Functional Dependencies and
Primary Keys
Functional Dependency: a particular
relationship between two attributes
For a given relation, attribute B is functionally
dependent on attribute A if, for every valid
value of A, that value of A uniquely
determines the value of B.
The functional dependence of B on A is
represented by A→B.
© 2011 Pearson Education, Inc. Publishing as Prentice
Chapter 9 Hall 14
Functional Dependencies and
Primary Keys (Cont.)
Functional dependency is not a mathematical
dependency.
Knowledge of problem domain is most
reliable method for identifying functional
dependency.
© 2011 Pearson Education, Inc. Publishing as Prentice
Chapter 9 Hall 15
Transforming E-R Diagrams into
Relations
It is useful to transform the conceptual
data model into a set of normalized
relations.
Steps
Represent entities.
Represent relationships.
Normalize the relations.
Merge the relations.
© 2011 Pearson Education, Inc. Publishing as Prentice
Chapter 9 Hall 16
Representing Entities
Each regular entity is transformed into a
relation.
The identifier of the entity type becomes
the primary key of the corresponding
relation.
© 2011 Pearson Education, Inc. Publishing as Prentice
Chapter 9 Hall 17
Representing Entities
The primary key must satisfy the following
two conditions.
The value of the key must uniquely identify
every row in the relation.
The key should be nonredundant.
The entity type label is translated into a
relation name.
© 2011 Pearson Education, Inc. Publishing as Prentice
Chapter 9 Hall 18
© 2011 Pearson Education, Inc. Publishing as Prentice
Chapter 9 Hall 19
© 2011 Pearson Education, Inc. Publishing as Prentice
Chapter 9 Hall 20
FIGURE 9-16
Class diagram
corresponding to
normalized relations of
Hoosier Burger‘s inventory
control system
© 2011 Pearson Education, Inc. Publishing as Prentice
Chapter 9 Hall 21
Relations for Hoosier Burger
© 2011 Pearson Education, Inc. Publishing as Prentice
Chapter 9 Hall 22
Designing Fields
Field: the smallest unit of named
application data recognized by system
software
Attributes from relations will be represented as
fields
Data Type: a coding scheme recognized
by system software for representing
organizational data
© 2011 Pearson Education, Inc. Publishing as Prentice
Chapter 9 Hall 23
Choosing Data Types
Selecting a data type balances four
objectives:
Minimize storage space.
Represent all possible values of the field.
Improve data integrity of the field.
Support all data manipulations desired on the
field.
© 2011 Pearson Education, Inc. Publishing as Prentice
Chapter 9 Hall 24
Calculated Fields
Calculated (or computed or derived)
field: a field that can be derived from other
database fields
It is common for an attribute to be
mathematically related to other data.
The calculate value is either stored or
computed when it is requested.
© 2011 Pearson Education, Inc. Publishing as Prentice
Chapter 9 Hall 25
Controlling Data Integrity
Default Value: a value a field will assume unless an
explicit value is entered for that field
Range Control: limits range of values that can be
entered into field
Both numeric and alphanumeric data
Referential Integrity: an integrity constraint specifying
that the value (or existence) of an attribute in one
relation depends on the value (or existence) of the
same attribute in another relation
Null Value: a special field value, distinct from zero,
blank, or any other value, that indicates that the value
for the field is missing or otherwise unknown
© 2011 Pearson Education, Inc. Publishing as Prentice
Chapter 9 Hall 26
Electronic Commerce Application:
Designing Databases
Designing databases for Pine Valley
Furniture’s WebStore
Review the conceptual model (E-R diagram).
Examine the lists of attributes for each entity.
Complete the database design.
Share all design information with project team to
be turned into a working database during
implementation.
© 2011 Pearson Education, Inc. Publishing as Prentice
Chapter 9 Hall 27
Summary
In this chapter you learned how to:
Concisely define
each of the following key database design terms: relation,
primary key, normalization, functional dependency, foreign key,
field, data type, null value and secondary key.
Explain
the role of designing databases in the analysis and design of
an information system.
Transform
an entity-relationship (E-R) diagram into an equivalent set of
well-structured (normalized) relations.
© 2011 Pearson Education, Inc. Publishing as Prentice
Chapter 9 Hall 28
All rights reserved. No part of this publication may be reproduced, stored in a
retrieval system, or transmitted, in any form or by any means, electronic,
mechanical, photocopying, recording, or otherwise, without the prior written
permission of the publisher. Printed in the United States of America.
Copyright © 2011 Pearson Education, Inc.
Publishing as Prentice Hall