Database Redesign - Database Processing: Fundamentals, Design, and Implementation - page 375

procedures? In which relationships? Because of the need to know all of the dependencies,
many database redesign projects begin by making a dependency graph .
The term graph arises from the mathematical topic of graph theory. Dependency graphs
are not graphical displays like bar charts; rather, they are diagrams that consist of nodes and
arcs (or lines) that connect those nodes.
Figure 8-3 shows a partial dependency graph that was drawn using the results of the RE
model, but manually interpreting views and triggers we developed in Chapter 7. For simplicity,
this graph does not show the views and triggers of CUSTOMER, nor does it show CUSTOMER_
ARTIST_INT and related structures. Also, the stored procedure WORK_AddWorkTransaction
is not shown, nor are the constraints.
Even this partial diagram reveals the complexity of dependencies among database con-
structs. You can see that it would be wise to tread lightly, for example, when changing anything
in the TRANS table. The consequences of such a change need to be assessed against two rela-
tionships, two triggers, and two views. Again, measure twice and cut once!

Database Backup and Test Databases
Because of the potential damage that can be done to a database during redesign, a complete
backup of the operational database should be made prior to making any changes. Equally
important, it is essential that any proposed changes be thoroughly tested. Not only must
structural changes proceed successfully, but all triggers, stored procedures, and applications
must also run correctly on the revised database.
Typically, at least three different copies of the database schema are used in the redesign
process. One is a small test database that can be used for initial testing. The second is a large
test database, which may even be a full copy of the operational database. Sometimes, there are
several large test databases. Finally, there is the operational database.
A means must be created to restore all test databases to their original state during the
testing process. In that way, the test can be rerun as necessary against the same starting
point. Depending on the facilities of the DBMS, backup and recovery or other means are used
to restore the database after a test run.
Obviously, for enterprises with very large databases, it is not possible to have a test data-
base that is a copy of the operational database. Instead, smaller test databases need to be cre-
ated, but those test databases must have all the important data characteristics of the opera-
tional database; otherwise, they will not provide a realistic test environment. The construction
of such test databases is in itself a difficult and challenging job. In fact, many interesting career
opportunities are available for developing test databases and database test suites.

Figure 8-3
Example Dependency Graph
(Partial)

TRANS_CheckSalesPrice

ARTIST

CUSTOMER

WORK

TRANS

TRANS_AskingPriceInitialValue

ArtistWorkNetView

Table
View
Trigger

ArtistWorkTotalNetView