Database Reference
In-Depth Information
You may be wondering, “Why do we have to redesign a database? If we build it correctly the
first time, why would we ever need to redesign it?” This question has two answers. First, it
is not easy to build a database correctly the first time, especially databases that arise from
the development of new systems. Even if we obtain all of the users' requirements and build a
correct data model, the transformation of that data model into a correct database design is
difficult. For large databases, the tasks are daunting and may require several stages of devel-
opment. During those stages, some aspects of the database will need to be redesigned. Also,
inevitably, mistakes will be made that must be corrected.
The second answer to this question is the more important one. Reflect for a moment
on the relationship between information systems and the organizations that use them. It is
tempting to say that they influence each other; that is, that information systems influence
organizations and that organizations influence information systems.
In truth, however, the relationship is much stronger than that. Information systems and
organizations do not just influence each other; they
create
each other. When a new informa-
tion system is installed, the users can behave in new ways. As the users behave in those new
ways, they will want changes to the information system to accommodate their new behaviors.
As those changes are made, the users will have more new behaviors, they will request more
changes to the information system, and so forth, in a never-ending cycle.
We are now in the
system maintenance
step of the
systems development life cycle
(SDLC)
in the
systems analysis and design
process. This is the SDLC step where we face
the fact that revising an information system is a natural step in using and maintaining that
information system. (For an introduction to systems analysis and design, and to the SDLC, see
Appendix B.) The system maintenance step may therefore result in the need for a redesigned
and reimplemented system, and thus start an new iteration of the SDLC. This circular process
means that changes to an information system are not the sad consequence of a poor implemen-
tation, but rather a natural outcome of information system use. Therefore, the need for change
to information systems never goes away; it neither can nor should be removed by better require-
ments definition, better initial design, better implementation, or anything else. Instead, change
is part and parcel of information systems use. Thus, we need to plan for it. In the context of data-
base processing, this means we need to know how to perform database redesign.
Database redesign is not terribly difficult if the database has no data. The serious difficulties
arise when we have to change a database that has data and when we want to make changes
with minimum impact on existing data. Telling the users that the system now works the way
they want but that all of their data were lost while making the change is not acceptable.
Often, we need to know whether certain conditions or assumptions are valid in the data
before we can proceed with a change. For example, we may know from user requirements that
Department functionally determines DeptPhone, but we may not know whether that func-
tional dependency is correctly represented in all of the data.
Recall from Chapter 3 that if Department determines DeptPhone, every value of
Department must be paired with the same value of DeptPhone. If, for example, Accounting has
a DeptPhone value of 834-1100 in one row, it should have that value in every row in which it
appears. Similarly, if Finance has a DeptPhone of 834-2100 in one row, it should have that value
in all rows in which it appears. Figure 8-1 shows data that violate this assumption. In the third
row, the DeptPhone for Finance is different than for the other rows; it has too many zeroes. Most
likely, someone made a keying mistake when entering DeptPhone. Such errors are typical.
Now, before we make a database change, we need to find all such violations and correct
them. For the small table shown in Figure 8-1, we can just look at the data, but what if the
EMPLOYEE table has 4,000 rows? Two SQL statements are particularly helpful in this regard:
correlated subqueries and their cousins, the SQL EXISTS and NOT EXISTS keywords. We will
consider each of these in turn.
Search WWH ::
Custom Search