DATA INTEGRITY - Database Design and Development: An Essential Guide for IT Professionals - page 438

action updated the savings account database table; the other part meant to update
the checking account database table failed. The database became inconsistent as far
this customer's accounts were concerned.
Data integrity means absence of inconsistent data. The data in the database must
be correct, valid, and accurate at all times. None of the transactions that update the
database can leave the database in an inconsistent state. Transactions must preserve
consistency; they must not corrupt the correctness and validity of the database.
In a typical database environment, thousands of transactions interact with the
database and many of these update data. Concurrent transactions may attempt to
update the same data element simultaneously. A database is also vulnerable to
several types of failures. In such unsettling circumstances, the database system must
maintain data consistency, correctness, and validity.
First, we will explore the nature of a proper transaction and its requisite prop-
erties for data integrity. We will examine how improper executions of transactions
could cause problems, and we will review solution options. We will also discuss fail-
ures of the database caused by external elements and present methods to recover
from such failures.
TRANSACTION PROCESSING
Consider the database systems that support businesses like airlines, banking, finance,
and retail. These are large transaction processing systems. Transactions in the tens
of thousands interact with databases for airline reservations, banking operations,
supermarket sales, credit card processing, stock buying and selling, and so on. Such
critical database systems must be available at all times and must provide fast
response times for hundreds of concurrent users. Some of these transactions just
read the database and display data, but most of the transactions perform some type
of updating.
A database system is susceptible to two major types of problems. First, concur-
rent transactions attempting to update the same data item may affect one another
adversely and produce incorrect and inconsistent results. Second, hardware or soft-
ware malfunctions could cause failures of the database and ruin its content. Con-
currency control and recovery from failures form the foundation for maintaining
data integrity.
Transaction Concepts
Let us get back to the example of transfer of funds from a savings account to a
checking account. Figure 15-1 illustrates the execution of this transaction.
First, note that this transaction would be part of an application program usually
written in a high-level language. For a transaction, the terminal points are marked
with BEGIN and COMMIT statements. Consider a transaction as a unit of an appli-
cation program. A program consists of one or more transactions. When you execute
a program, you may be executing many transactions. Next, observe that in the
example shown of a transaction, although the transaction is considered as one unit,
it has two database updates—one to update the savings account record and
the other to update the checking account record. Before the execution of the