Databases Reference
In-Depth Information
×
I/O time = 240 block accesses
2.2 ms
= .5 second.
Common to both strategies is the addition of an order record to the tables order-
ItemSales and orderDeptSales. For the sake of simplicity, we will assume these tables to
be unsorted, so the addition of a new order will require only one record access at the
end of the table and, thus, negligible I/O time.
The basic performance and normalization data for these two schemas and the two
transactions given previously are summarized in Table 15.2. The refined schema dra-
matically reduces the I/O time for the query transaction and the update, but the cost is
storage space and significant reduction in the degree of normalization. The normaliza-
tion is reduced because we now have a transitive FD: orderNum -> custNum -> jobTitle
in table orderCust. The implication of this, of course, is that there is a delete anomaly
for jobTitle when a customer deletes an order or the order is filled (in particular, when
the jobTitle value deleted is the last instance of that jobTitle in the database).
The significance of these performance and data integrity differences depends on the
overall objectives as well as the computing environment for the database, and it must be
analyzed in that context. For instance, the performance differences must be evaluated
for all relevant transactions, present and projected. Storage space differences may or may
not be significant in the computing environment. Integrity problems with the deletion
commands need to be evaluated on a case-by-case basis to determine whether the side
effects of certain record deletions are destructive to the objectives of the database. In
summary, the database designer now has the ability to evaluate the tradeoffs among
query and update requirements, storage space, and integrity associated with normaliza-
tion. This knowledge can be applied to a variety of database design problems.
Table 15.2
Comparison of Performance and Integrity of Original Tables and Join Table
