Information Technology Reference
In-Depth Information
1.4
A banking database contains the relations
account
(
number
,
balance
),
holder
(
card number
,
account number
),
card
(
number
,
holder name
,
holder address
,
crypted password
),
transaction
(
site
,
date time
,
type
,
amount
,
account number
,
card number
),
where the relation
transaction
stores information about every completed or
attempted withdrawal and deposit and about every balance lookup.
Give an embedded
SQL
program for a transaction for a withdrawal of s euros
using card c with password p, where the withdrawal is allowed only if no overdraft
will occur. The transaction also shows the balance that remains in the account after
completing the withdrawal. We assume that the program includes the following
statement:
exec sql whenever sqlerror goto
L,
where L is a program address to which the program control is transferred whenever
an error status is returned by the execution of some
SQL
statement.
1.5
Consider extending the read-write transaction model with an action CŒx,
which
declares
the updates on x as
committed
. This action can appear in the
forward-rolling phase of the transaction, and it has the effect that even if the
transaction eventually aborts and rolls back, the updates on x done before CŒx
will not be undone. Accordingly, complete the transaction
BR
Œx;
u
W Œx;
u
;
u
0
RŒy;
v
W Œy;
v
;
v
0
CŒxRŒ
z
;
w
W Œ
z
;
w
;
w
0
W Œy;
v
0
;
v
00
CŒyWŒx;
u
0
;
u
00
A
to a rolled back transaction. Consider situations in which this feature might be
useful.
1.6
Our key-range transaction model assumes that the tuples .x;
v
/ are only
referenced via the unique primary keys x. Extend the model to include relations
r.
X
;Y;V/, where tuples .x; y;
v
/ can be referenced by either the primary key x or
by the (non-unique) secondary key y.
1.7
We extend our key-range transaction model by a
cursor mechanism
that
works as follows. When a transaction starts, it allocates a main-memory array
(the
cursor
), private to the transaction, to store the tuples returned by all the read
actions performed by the transaction, in the order of the actions. Each read action
RŒx;
v
appends the tuple .x;
v
/ to the next available entry in the cursor. In a more
general setting, we would have several cursors, with special actions to open and
close a cursor.
Now if the transaction performs a partial rollback to a savepoint, the contents of
the cursor should be restored to the state that existed at the time the savepoint was
set. Obviously, to that effect, we should define an undo action, R
1
Œx, for a read
action RŒx. Elaborate this extension of our transaction model.
