Valacich J., George J., Hoffer J.A. Essentials of Systems Analysis and Design
Подождите немного. Документ загружается.
Chapter 9 Designing Databases 313
PART
Item_Number
Description
VENDOR
Vendor_ID
Address
PRICE QUOTE
Quote_Quantity
Price
PART RECEIPT
Order_Number
Date
Order_Quantity
Priced_at
FIGURE 9-21
E-R diagram for Problem
and Exercise 3.
assumptions you consider necessary to re-
solve any potential problems you identify in
the merging process.
b. Draw an E-R diagram for your answer to part a.
5. Consider the following 3NF relations about a
sorority or fraternity:
MEMBER(Member_ID
, Name, Address,
Dues_Owed)
OFFICE(Office_Name
, Officer_ID,
Term_Start_Date, Budget)
EXPENSE(Ledger_Number
,
Office_Name, _Expense_Date, Amt_Owed)
PAYMENT(Check_Number
, Expense_Ledger_
Number, Amt_Paid)
RECEIPT(Member_ID
, Receipt_Date,
Dues_Received)
COMMITTEE(Committee_ID
, Officer_
in_Charge)
WORKERS(Committee_ID
, Member_ID)
a. Foreign keys are not indicated in these rela-
tions. Decide which attributes are foreign keys
and justify your decisions.
b. Draw an E-R diagram for these relations, using
your answer to part a.
c. Explain the assumptions you made about car-
dinalities in your answer to part b. Explain
why it is said that the E-R data model is more
expressive or more semantically rich than the
relational data model.
6. Consider the following functional dependencies:
Applicant_ID
:
Applicant_Name
Applicant_ID
:
Applicant_Address
Position_ID
:
Position_Title
Position_ID
:
Date_Position_Opens
Position_ID
:
Department
Applicant_ID Position_ID
:
Date_Applied
Applicant_ID Position_ID
Date_Interviewed?
:
a. Represent these attributes with 3NF relations.
Provide meaningful relation names.
b. Represent these attributes using an E-R dia-
gram. Provide meaningful entity and relation-
ship names.
7. Suppose you were designing a file of student
records for your university’s placement office.
One of the fields that would likely be in this file is
the student’s major. Develop a coding scheme for
this field that achieves the objectives outlined in
this chapter for field coding.
8. In Problem and Exercise 3, you developed inte-
grated normalized relations. Choose primary
keys for the files that would hold the data for
these relations. Did you use attributes from the
relations for primary keys or did you design new
fields? Why or why not?
9. Suppose you created a file for each relation in your
answer to Problem and Exercise 3. If the following
queries represented the complete set of accesses
to this database, suggest and justify what primary
and secondary key indexes you would build.
a. For each PART, list all vendors and their asso-
ciated prices for that part.
b. List all PART RECEIPTs, including related
PART fields for all the parts received on a par-
ticular day.
c. For a particular VENDOR, list all the PARTs and
their associated prices that VENDOR can supply.
10. Suppose you were designing a default value for the
marriage status field in a student record at your
university. What possible values would you con-
sider and why? How would the default value
change depending on other factors, such as type of
student (undergraduate, graduate, professional)?
11. Consider Figure 9-19B. Explain a query that
would likely be processed more quickly using the
denormalized relations rather than the normal-
ized relations.
314 Part IV Systems Design
Case Problems
1. Pine Valley Furniture
Development work on Pine Valley Furniture’s
new Customer Tracking System is proceeding
according to plan and is on schedule. The project
team has been busy designing the human inter-
faces, and you have just completed the new track-
ing system’s Customer Profile Form, Products by
Demographics Summary Report, and Customer
Purchasing Frequency Report.
Because you are now ready for a new task,
Jim Woo asks you to prepare logical data models
for the form and two reports that you have just
designed and drop them by his office this after-
noon. At that time, the two of you will prepare a
consolidated database model, translate the Cus-
tomer Tracking System’s E-R data model into nor-
malized relations, and then integrate the logical
data models into a final logical data model for the
Customer Tracking System.
a. Develop logical data models for the form and
two reports mentioned in the case scenario.
b. Perform view integration on the logical mod-
els developed for part a.
c. What view integration problems, if any, exist?
How should you correct these problems?
d. Have a fellow classmate critique your logical
data model. Make any necessary corrections.
2. Hoosier Burger
As the lead analyst on the Hoosier Burger proj-
ect, you have had the opportunity to learn more
about the systems development process, work
with project team members, and interact with
the system’s end users, especially with Bob and
Discussion Questions
1. Many database management systems offer the
ability to enforce referential integrity. Why would
using such a feature be a good idea? Are there
any situations in which referential integrity
might not be important?
2. Assume you are part of the systems development
team at a medium-sized organization. You have
just completed the database design portion of the
systems design phase, and the project sponsor
would like a status update. Assuming the project
sponsor is a VP in the marketing department,
with only a high-level understanding of technical
subjects, how would you go about presenting the
database design you have just completed? How
would your presentation approach change if the
project sponsor were the manager of the data-
base team?
3. Discuss what additional information should be
collected during requirements analysis that is
needed for file and database design and that is
not especially useful for earlier phases of sys-
tems development.
4. Find out what database management systems are
available at your university for student use. Inves-
tigate which data types these DBMSs support.
Compare these DBMSs based upon data types
supported and suggest which types of applica-
tions each DBMS is best suited for based on this
comparison.
5. Find out what database management systems
are available at your university for student use.
Investigate what physical file and database
design decisions need to be made. Compare
this list of decisions to those discussed in this
chapter. For physical database and design deci-
sions (or options) not discussed in the chapter,
investigate what choices you have and how you
should choose among these choices. Submit a
report to your instructor with your findings.
12. Consider your answers to parts a and b of Prob-
lem and Exercise 11 in Chapter 7.
a. Transform the E-R diagram you developed in
part a into a set of 3NF relations. Clearly iden-
tify primary and foreign keys. Explain how
you determined the primary key for any many-
to-many relationships or associative entities.
b. Transform the E-R diagram you developed in
part b into a set of 3NF relations. Clearly identify
primary and foreign keys. Explain how you de-
termined the primary key for any many-to-many
relationships or associative entities.
13. Model a set of typical family relationships—
spouse, father, and mother—in a single 3NF
relation. Also include nonkey attributes name
and birth date. Assume that each person has only
one spouse, one father, and one mother. Show
foreign keys with dashed underlining.
Chapter 9 Designing Databases 315
Thelma. You have just completed the design work
for the various forms and reports that will be used
by Bob, Thelma, and their employees. Now it is
time to prepare logical and physical database
designs for the new Hoosier Burger system.
During a meeting with Hoosier Burger project
team members, you review the four steps in logi-
cal database modeling and design. It will be your
task to prepare the logical models for the Cus-
tomer Order Form, Customer Account Balance
Form, Daily Delivery Sales Report, and Inventory
Low-in-Stock Report. At the next meeting, the
E-R model will be translated and a final logical
model produced.
a. Develop logical models for each of the inter-
faces mentioned in the case scenario.
b. Integrate the logical models prepared for part
a into a consolidated logical model.
c. What types of problems can arise from view
integration? Did you encounter any of these
problems when preparing the consolidated
logical model?
d. Using your newly constructed logical model,
determine which fields should be indexed.
Which fields should be designated as calcu-
lated fields?
3. PlowMasters
PlowMasters is a locally owned and operated
snow removal business. PlowMasters provides
residential and commercial snow removal for
clients throughout a large metropolitan area. Typ-
ical services include driveway and walkway snow
removal, as well as parking lot snow maintenance
for larger commercial clients.
PlowMasters’ clientele has grown over the past
several snow seasons. Recent heavy snowfall,
coupled with a successful advertising campaign,
has increased current demand even more, and
this increase in demand is expected to continue.
In order to provide faster, more efficient service,
PlowMasters has hired your consulting company
to design, develop, and implement a computer-
based system. Your development team is cur-
rently preparing the logical and physical database
designs for PlowMasters.
a. What are the four steps in logical database
modeling and design?
b. Several relations have been identified for this
project, including removal technician, cus-
tomer, service provided, equipment inventory,
and services offered. What relationships exist
among these relations? How should these re-
lationships be represented?
c. Think of the attributes that would most likely
be associated with the relations identified in
the part b. For each data integrity control
method discussed in the chapter, provide a
specific example.
d. What are the guidelines for choosing indexes?
Identify several fields that should be indexed.
CASE: PETRIE’S ELECTRONICS
Designing Databases
Jim Watanabe, assistant director of IT for Petrie’s
Electronics and the manager of the “No Customer
Escapes” customer loyalty system project, was walk-
ing down the hall from his office to the cafeteria. It
was 4
P.M., but Jim was nowhere close to going home
yet. The deadlines he had imposed for the project
were fast approaching. His team was running behind,
and he had a lot of work to do over the next week to
try to get things back on track. He needed to get some
coffee for the start of what was going to be a late night.
As Jim approached the cafeteria, he saw Sanjay
Agarwal and Sam Waterston walking toward him.
Sanjay was in charge of systems integration for
Petrie’s, and Sam was one of the company’s top
interface designers. They were both on the customer
loyalty program team. They were having an intense
conversation as Jim approached.
“Hi guys,” Jim said.
“Oh, hi, Jim,” Sanjay replied. “Glad I ran into you—
we are moving ahead on the preliminary database de-
signs. We’re translating the earlier conceptual
designs into physical designs.”
“Who’s working on that? Stephanie?” Jim asked.
Stephanie Welch worked for Petrie’s database
administrator.
“Yes,” Sanjay replied. “But she is supervising a
couple of interns who have been assigned to her for
this task.”
“So how is that going? Has she approved their work?”
“Yeah, I guess so. It all seems to be under control.”
“I don’t want to second-guess Stephanie, but I’m
curious about what they’ve done.”
316 Part IV Systems Design
MEMO
To: Stephanie Welch
From: Xin Zhu & Anton Washington
Re: Preliminary physical database design for “No Customer Escapes”
Date: June 1, 2012
We were charged with converting the conceptual database designs for the customer loyalty
system to physical database designs. We started with one of the initial ERDs (see Figure 7-1),
designed at a very high level. The ERD identified six entities: Customer, Product, Service,
Promotion,Transaction, and Coupon.We discovered that all of these entities are already defined
in Petrie’s existing systems. The only entity not already defined is Coupon. Product and Service
are defined as part of the product database. Promotion is defined as part of the marketing data-
base. Customer and Transaction are defined as part of the core database.
However, after considerable consideration, we are not sure if some of these already identified
and defined entities are the same as those identified in the preliminary ERD we were given.
Specifically, we have questions about Customer, Transaction and Promotion.
Customer: The Customer entity is more complex than it appears. There are several ways to think
about the instances of this entity. For example, we can divide Customers into those who shop
online and those who shop in the Brick-and-Mortar stores. And there is of course some overlap.
The biggest distinction between these two groups is that we know the names of (and other
information about) the Customers who shop online, but we may have very little identifying
information about those who shop only in the stores. For example, if an individual shops only
at a store and pays only with cash, that individual meets the definition of Customer
(see Table 7-1), but we collect no data on that individual at all. We raise these issues to call
attention to the relationship between Customers and members of the customer loyalty program:
All members are Customers, but not all Customers are members. We suggest that the entity
called Customer in the preliminary ERD be renamed ‘Member,’ as we think that is a better name
for this entity. We are prepared to map out the table design when this change is approved.
Transaction: Petrie’s already has a relational table called Transaction, but that applies to all
transactions in all stores and online. The customer loyalty program focuses on the transactions
of its Members, so the program involves only a subset of Transactions. We suggest that the ERD
be redesigned to take this fact into account, and that what is now called Transaction be
renamed ‘Member Transaction.’ The relational tables should then be designed accordingly.
Promotion: Petrie’s already has a relational table called Promotion. Again, the customer loyalty
program, while having some interest in general promotions, focuses primarily on promotions
created specifically for Members of the program. What is called Promotion in the ERD is really
a subset of all of Petrie’s promotions. We recommend a name change to ‘Member Promotion’
with the associated relational table design.
Finally, for the Coupon entity, which is new, we note from the ERD that Coupon only has one
relationship, and that is with the Customer. As it is a one-to-many relationship, the PK from Cus-
tomer will be an FK in Coupon. We recommend the following table design:
COUPON (Coupon ID, Customer ID, Creation Date, Expiration Date, Value)
PE FIGURE 9-1
Memo on issues related to
physical database design for
Petrie’s Electronic’s customer
loyalty program.
“Do you really have time to review interns’ work?”
Sanjay asked. “OK, let me send you the memo
Stephanie sent me [PE Figure 9-1].”
“You’re right, I don’t have time,” Jim said, “but
I’m curious. It won’t take long to read the memo,
right?”
“OK, I’ll send it as soon as I get back to my desk.”
“OK, thanks.” Jim walked on to the cafeteria, and
he poured himself a big cup of coffee.
Case Questions
1. In the questions associated with the Petrie’s Elec-
tronics case at the end of Chapter 7, you were asked
to modify the E-R diagram given in PE Figure 7-1 to
include any other entities and the attributes you
identified from the Petrie’s case. Review your an-
swers to these questions, and add any additional
needed relations to the document in PE Figure 9-1.
Chapter 9 Designing Databases 317
2. Study your answer to Question 1. Verify that the
relations you say represent the Petrie’s Electron-
ics database are in third normal form. If they are,
explain why. If they are not, change them so that
they are.
3. The E-R diagram you developed in questions in
the Petrie’s Electronics case at the end of
Chapter 7 should have shown minimum cardinal-
ities on both ends of each relationship. Are mini-
mum cardinalities represented in some way in
the relations in your answer to Question 2? If not,
how are minimum cardinalities enforced in the
database?
4. Using your answer to Question 2, select data
types, formats, and lengths for each attribute of
each relation. Use the data types and formats sup-
ported by Microsoft Access. What data type
should be used for nonintelligent primary keys?
5. Complete all table and field definitions for
the Petrie’s Electronics case database using
Microsoft Access. Besides the decisions you have
made in answers to the preceding questions, fill in
all other field definition parameters for each field
of each table.
6. The one decision for a relational database that
usually influences efficiency the most is index
definition. What indexes do you recommend for
this database? Justify your selection of each index.
7. Using Microsoft Visio, develop an E-R diagram
with all the supporting database properties for
decisions you made in Questions 1–6. Can all the
database design decisions you made be docu-
mented in Visio? Finally, use Visio to generate
Microsoft Access table definitions. Did the table
generation create the table definitions you would
create manually?
318
Systems Implementation
and Operation
쐍 Describe the process of coding, testing, and
installing an organizational information system
and outline the deliverables and outcomes of
the process.
쐍 Apply four installation strategies: direct, parallel,
single location, and phased installation.
쐍 List the deliverables for documenting the
system and for training and supporting users.
쐍 Compare the many modes available for
organizational information system training.
쐍 Discuss the issues of providing support for
end users.
쐍 Explain why systems implementation
sometimes fails.
쐍 Explain and contrast four types of
maintenance.
쐍 Describe several factors that influence the cost
of maintaining an information system.
After studying this chapter, you should be able to:
ten
Chapter Objectives
age fotostock/SuperStock
Chapter Preview . . .
The implementation and operation phase of
the systems development life cycle is the most
expensive and time-consuming phase of the en-
tire life cycle. This phase is expensive because so
many people are involved in the process. It is
time consuming because of all the work that has
to be completed through the entire life of the
system. During implementation and operation,
physical design specifications must be turned
into working computer code. Then the code is
tested until most of the errors have been de-
tected and corrected, the system is installed,
user sites are prepared for the new system, and
users must come to rely on the new system
rather than the existing one to get their work
done. Even once the system is installed, new
features are added to the system, new business
requirements and regulations demand system
improvements, and corrections are made as
flaws are identified from use of the system in
new circumstances. These changes will have rip-
ple effects, causing rework in many systems de-
velopment phases. The seven major activities we
are concerned with in this chapter are coding,
testing, installation, documentation, training,
support, and maintenance. These and other
activities are highlighted in Figure 10-1. Our
intent is not to explain how to program and test
systems—most of you have already learned
about writing and testing programs in other
courses. Rather, this chapter shows you where
coding and testing fit in the overall scheme of
319
Systems
Planning and
Selection
Systems
Analysis
Systems
Design
Systems
Implementation
and Operation
SDLC
Coding
Testing
Installation
Documentation
Training
Support
Maintenance
✓
✓
✓
✓
✓
✓
✓
FIGURE 10-1
The activities of the systems
implementation and operation
phase of the SDLC.
Systems Implementation and Operation
Systems implementation and operation is made up of seven major activities:
쐍 Coding
쐍 Testing
쐍 Installation
쐍 Documentation
쐍 Training
쐍 Support
쐍 Maintenance
The purpose of these steps is to convert the final physical system specifications
into working and reliable software and hardware, document the work that has
been done, and provide help for current and future users and caretakers of the
system. Usage of the system leads to changes, so during maintenance, users and
others submit maintenance requests; requests are transformed into specific
changes to the system; the system is redesigned to accept the changes; and the
changes are implemented.
These steps are often done by other project team members besides analysts,
although analysts may do some programming and other steps. Often a separate an-
alyst and developer team from those who developed the original system is re-
sponsible for testing, documenting, training, and maintenance activities. In any
case, analysts are responsible for ensuring that all of these various activities are
properly planned and executed. We briefly discuss these activities in three groups:
1. Activities that lead to the system going into operation—coding, testing,
and installation
2. Activities that are necessary for successful system operation—
documenting the system and training and supporting users
3. Activities that are ongoing and needed to keep the system working and up
to date—maintenance
320 Part V Systems Implementation and Operation
implementation and stresses the view of imple-
mentation as an organizational change process
that is not always successful.
In addition, you will learn about providing doc-
umentation about the new system for the informa-
tion systems personnel who will maintain the
system; likewise, you will learn about providing
documentation and conducting training for the
system’s users. Once training has ended and the
system is accepted and used, you must provide a
means for users to get answers to their additional
questions and to identify needs for further training.
Your first job after graduation may well be as a
maintenance programmer/analyst. Maintenance
can begin soon after the system is installed.
A question many people have about maintenance
relates to how long organizations should main-
tain a system. Five years? Ten years? Longer?
This question has no simple answer, but it is most
often an issue of economics. In other words, at
what point does it make financial sense to dis-
continue updating an older system and build or
purchase a new one? Upper IS management gives
significant attention to assessing the trade-offs
between maintenance and new development.
In this chapter, we describe the maintenance
process and the issues that must be considered
when maintaining systems. At the end of the
chapter, we describe the process of resolving a
maintenance request at Pine Valley Furniture.
Chapter 10 Systems Implementation and Operation 321
The Processes of Coding,Testing, and Installation
Coding, as we mentioned before, is the process through which the physical
design specifications created by the design team are turned into working com-
puter code by the programming team. Depending on the size and complexity of
the system, coding can be an involved, intensive activity. Once coding has be-
gun, the testing process can begin and proceed in parallel. As each program
module is produced, it can be tested individually, then as part of a larger pro-
gram, and then as part of a larger system. You learn about the different strate-
gies for testing later in the chapter. We should emphasize that although testing
is done during implementation, you must begin planning for testing earlier in the
project. Planning involves determining what needs to be tested and collecting
test data. These activities are often done during the analysis phase, because
testing requirements are related to system requirements.
Installation is the process during which the current system is replaced by the
new system. It includes conversion of existing data, software, documentation,
and work procedures to those consistent with the new system. Users must give
up the old ways of doing their jobs, whether manual or automated, and adjust
to accomplishing the same tasks with the new system. Users will sometimes re-
sist these changes, and you must help them adjust. However, you cannot control
all the dynamics of user-system interaction involved in the installation process.
Deliverables and Outcomes from Coding,Testing,
and Installation
Table 10-1 shows the deliverables from the coding, testing, and installation
processes. The most obvious outcome is the code itself, but just as important as
the code is documentation of the code. Modern programming languages, such
as Visual Basic, are said to be largely self-documenting. When standard naming
and program design conventions are used, the code itself spells out much about
the program’s logic, the meaning of data and variables, and the locations where
data are accessed and output. But even well-documented codes can be myste-
rious to maintenance programmers who must maintain the system for years af-
ter the original system was written and the original programmers have moved
on to other jobs. Therefore, clear, complete documentation for all individual
modules and programs is crucial to the system’s continued smooth operation.
TABLE 10-1: Deliverables from Coding,Testing, and Installation
Action Deliverable
Coding Code
Program documentation
Testing Test scenarios (test plan) and test data
Results of program and system testing
Installation User guides
User training plan
Installation and conversion plan
Hardware and software installation schedule
Data conversion plan
Site and facility remodeling plan
322 Part V Systems Implementation and Operation
Increasingly, automated tools are used to maintain the documentation needed
by systems professionals.
The results of program and system testing are important deliverables from
the testing process because they document the tests, as well as the test
results. For example, what type of test was conducted? What test data were
used? How did the system handle the test? The answers to these questions
can provide important information for system maintenance as changes
require retesting, and similar testing procedures will be used during the main-
tenance process.
The next two deliverables, user guides and the user training plan, result from
the installation process. User guides provide information on how to use the
new system, and the training plan is a strategy for training users so they can
quickly learn the new system. The development of the training plan probably
began earlier in the project, and some training on the concepts behind the new
system may have already taken place. During the early stages of implementa-
tion, the training plans are finalized and training on the use of the system
begins. Similarly, the installation plan lays out a strategy for moving from the
old system to the new. Installation includes installing the system (hardware and
software) at central and user sites. The installation plan answers such ques-
tions as when and where the new system will be installed, what people and
resources are required, which data will be converted and cleansed, and how
long the installation process will take. It is not enough that the system is
installed; users must actually use it.
As an analyst, your job is to ensure that all of these deliverables are produced
and done well, whether by you or by others. Coding, testing, and installation
work may be done by IS professionals in your organization, by contractors,
hardware designers, and, increasingly, by users. The extent of your responsibil-
ities will vary according to the size and standards of the organization you work
for, but your ultimate role includes ensuring that all the coding, testing, and in-
stallation work leads to a system that meets the specifications developed in
earlier project phases.
The Processes of Documenting the System,Training Users,
and Supporting Users
Although the process of documentation proceeds throughout the life cycle, it
receives formal attention now, because once the system is installed, the analy-
sis team’s involvement in system development usually ceases. As the team is
getting ready to move on to new projects, you and the other analysts need to
prepare documents that reveal all of the important information you have
learned about this system during its development and implementation. The two
audiences for this final documentation are (1) the information systems person-
nel who will maintain the system throughout its productive life, and (2) the peo-
ple who will use the system as part of their daily lives.
Larger organizations also tend to provide training and support to computer
users throughout the organization, sometimes as part of a corporate univer-
sity. Some of the training and support is directed to off-the-shelf software
packages. For example, it is common to find courses on Microsoft Windows
and Office in organization-wide training facilities. Analysts typically work
with corporate trainers to provide training and support tailored to particular
computer applications they have helped develop. Centralized information sys-
tem training facilities tend to have specialized staff who can help with train-
ing and support issues. In smaller organizations that cannot afford to have
well-staffed centralized training and support facilities, fellow users are the
best source of training and support that users have, whether the software is
customized or off the shelf.