Developing Time-Oriented Database Applications in SQL
An enjoyable plunge into the how-to of time and data, with real-world situations and code.
TABLE OF CONTENTS Developing Time-Oriented Database Applications in SQL
Chapter 7: Modifying State Tables
OVERVIEW
A major thread of this exposition is the classification of statements (queries, integrity constraints) into current, sequenced, and nonsequenced variants. This taxonomy also benefits modifications.
SQL has three modification statements: INSERT, DELETE, and UPDATE. Interestingly, a current deletion is implemented as an UPDATE followed by a DELETE, and a sequenced deletion requires an INSERT, two UPDATEs, and a DELETE. Updates are more complex: a current update is implemented as three SQL statements; sequenced updates require five SQL statements. Mentioning other temporal tables makes things even more exciting. Finally, we consider the pros and cons of breaking up a valid-time table into a current portion and a historical portion, each a separate table.
We now turn to implementing modifications to a valid-time table. We consider separately current modifications (those on the current, and future, states), sequenced modifications (those evaluated, conceptually at least, at each point in time), and nonsequenced modifications (those explicitly mentioning the timestamp columns). We apply these modifications to the INCUMBENTS table.
We also examine how primary key and referential integrity constraints can be maintained across these modifications. One approach, exemplified in Chapter 5, is to specify an assertion. Any violating modification results in the rollback of the transaction (assuming the assertion is immediate; see Section 5.7), thereby guaranteeing that the database remains consistent with the integrity constraint. Of course, the problem is that this is an all-or-nothing proposition: one improper modification derails the entire transaction.
A second approach is to code the modification to ensure that...
Copyright Morgan Kaufmann Publishers, Inc. 2000 under license agreement with Books24x7
