Consider the following relational database schema:
CREATE TABLE ACCOUNTS
CONSTRAINT PK_ACCOUNTS PRIMARY KEY(account_no)
CREATE TABLE PENDING_TRANSFERS
CONSTRAINT PK_ PENDING_TRANSFERS PRIMARY KEY(from_account_no,
FOREIGN KEY(from_account_no) REFERENCES ACCOUNTS(account_no),
FOREIGN KEY(to_account_no) REFERENCES ACCOUNTS(account_no),
(a) Write three SQL statements: the first statement should add the pending amounts to the appropriate accounts, the second statement should subtract the pending amounts from the appropriate accounts, and the third statement should delete the pending balance transfer information.
(b) Explain the dangers of the sequence of statements in part (a) above, in terms of what would happen in case the database server or SQL interface were to crash in between two of these three statements.
(c) Show how to remove the dangers you described in part (b).
Please find below the requested queries.
1) add the pending amounts to the appropriate accounts
SET ACCOUNTS.amount = ACCOUNTS.amount + PENDING_TRANSFERS.amount
WHERE PENDING_TRANSFERS.to_account_no = ACCOUNTS.account_no
2) subtract the pending amounts from the appropriate accounts
This solution provides assistance writing SQL statements.