Tutorial 5

Solution Notes

• Before the Tutorial
• Questions
  • Transactions
  • True/False

Before the Tutorial

Revise the material from Lecture 23.

[Top]

Questions

True/False

State whether each of the following statements/propositions is True or False.

1. The concept of a transaction provides a mechanism for describing logical units of database processing.

   True

   [EN5:609]

   
   

2. Transactions should possess several properties, often called the ACID properties.

   True

   [EN5:619]

   
   

3. "A" in "ACID" stands for "Authentic".

   False

   The above False statement is from an answer given by a student in a COMP2400 exam.

   "A" in "ACID" stands for "Atomicity".

   [EN5:620]

   
   

4. "C" in "ACID" stands for "Control".

   False

   The above False statement is from an answer given by a student in a COMP2400 exam.

   "C" in "ACID" stands for "Consistency".

   [EN5:620]

   
   

5. "I" in "ACID" stands for "Integrity".

   False

   The above False statement is from an answer given by a student in a COMP2400 exam.

   "I" in "ACID" stands for "Isolation".

   [EN5:620]

   
   

6. "D" in "ACID" stands for "Divergence".

   False

   The above False statement is from an answer given by a student in a COMP2400 exam.

   "D" in "ACID" stands for "Durability".

   [EN5:620]

   
   

7. The changes applied to a database by an uncommitted transaction must persist in the database.

   False

   The changes applied to a database by a committed transaction must persist in the database.

   [EN5:620]

   
   

8. The ACID properties of a transaction should be enforced by the concurrency control and recovery methods of the DBMS.

   True

   [EN5:619-20]

   
   

9. A single application program cannot contain more than one transaction.

   False

   A single application may contain more than one transaction.

   [EN5:611]

   
   

10. Several problems can occur when concurrent transactions execute in an uncontrolled manner.

    True

    [EN5:613]

[Top]

Transactions

Simplified Relational Schema for Piggy's Bank

T1: Withdrawal from an ATM

1. Validate use of card - Input is :CardNo, :Pin

   SELECT * FROM Card
    WHERE CardNo = :CardNo
      AND Pin = :Pin;

   If fail terminate with appropriate message

2. Compare Balance and Amount - Input is :AcctNo, :Amount

   SELECT * FROM Account
    WHERE AcctNo = :AcctNo
      AND Balance >= :Amount;

   If fail terminate with appropriate message

3. Update balance

   UPDATE Account
   SET Balance = Balance - :Amount
   WHERE AcctNo = :AcctNo;

4. Spit out cash and print appropriate message

T2: Transfer Funds at ATM

1. Validate use of card - Input is :CardNo, :Pin

   SELECT * FROM Card
   WHERE CardNo = :CardNo
   AND Pin = :Pin;

   If fail terminate with appropriate message

2. Transfer funds from one account to another
   - Input is :AcctNo_From, :AcctNo_To, :Amount

   Compare Balance and Amount 

   SELECT * FROM Account
    WHERE AcctNo = :AcctNo_From
      AND Balance >= :Amount;

   If fail terminate with appropriate message

3. Update balances

   UPDATE Account
   SET Balance = Balance - :Amount
   WHERE AcctNo = :AcctNo_From;

   UPDATE Account
   SET Balance = Balance + :Amount
   WHERE AcctNo = :AcctNo_To;

4. Print appropriate message

The Scenario

Fred Nurk and Jo Bloggs have a joint account at Piggy's Bank. One lunchtime, they both access the joint account from different ATMs. Fred withdraws $200 from the joint account, and Jo transfers $100 from the joint account to her personal account.

0. If the balance in the joint account is $500 at the beginning of the lunchtime, what should its balance be after both transactions have completed?

   $200

   
   

1. If the transactions are processed as shown in Schedule A, what will the balance of the joint account be after both transactions have completed?

   If the balance after both transactions have completed is not the same as the balance determined at 0., what sort of problem has occurred? And which ACID property does not hold?

2. If the transactions are processed as shown in Schedule B, what will the balance of the joint account be after both transactions have completed?

   If the balance after both transactions have completed is not the same as the balance determined at 0., what sort of problem has occurred? And which ACID property does not hold?

3. If the transactions are processed as shown in Schedule C, what will the balance of the joint account be after both transactions have completed?

   If the balance after both transactions have completed is not the same as the balance determined at 0., what sort of problem has occurred? And which ACID property does not hold?

4. If the transactions are processed as shown in Schedule D, what will the balance of the joint account be after both transactions have completed?

   If the balance after both transactions have completed is not the same as the balance determined at 0., what sort of problem has occurred? And which ACID property does not hold?

   As discussed at Lecture 24, my use of SQL in the pseudo-code made these questions more interesting than I intended them to be.

   There will not be any questions like these 1.-4. on the Final Exam.

   
   

5. Add appropriate BEGIN and END/COMMIT commands to T1 and T2.

   I am placing BEGIN and END around read-only transactions, and BEGIN and COMMIT around transactions which modify the database.

   T1: Withdrawal from an ATM

   BEGIN
   
   1. Validate use of card - Input is :CardNo, :Pin
   
      SELECT * FROM Card
       WHERE CardNo = :CardNo
         AND Pin = :Pin;
   
      If fail terminate with appropriate message
   
   END
         
   BEGIN
         
   2. Compare Balance and Amount - Input is :AcctNo, :Amount
   
      SELECT * FROM Account
       WHERE AcctNo = :AcctNo
         AND Balance >= :Amount;
   
      If fail terminate with appropriate message
   
   3. Update balance
   
      UPDATE Account
      SET Balance = Balance - :Amount
      WHERE AcctNo = :AcctNo;
   
   4. Spit out cash and print appropriate message
   
   COMMIT
   

   T2: Transfer Funds at ATM

   BEGIN
   1. Validate use of card - Input is :CardNo, :Pin
   
      SELECT * FROM Card
      WHERE CardNo = :CardNo
      AND Pin = :Pin;
   
      If fail terminate with appropriate message
   
   END
   
   BEGIN
         
   2. Transfer funds from one account to another
      - Input is :AcctNo_From, :AcctNo_To, :Amount
   
      Compare Balance and Amount 
   
      SELECT * FROM Account
       WHERE AcctNo = :AcctNo_From
         AND Balance >= :Amount;
   
      If fail terminate with appropriate message
   
   3. Update balances
   
      UPDATE Account
      SET Balance = Balance - :Amount
      WHERE AcctNo = :AcctNo_From;
   
      UPDATE Account
      SET Balance = Balance + :Amount
      WHERE AcctNo = :AcctNo_To;
   
   4. Print appropriate message
         
   COMMIT
   

   Because we have three logical units of work in this scenario, I suggest that we should have three transactions:

   T0: Login to ATM

   BEGIN
   
   1. Validate use of card - Input is :CardNo, :Pin
   
      SELECT * FROM Card
       WHERE CardNo = :CardNo
         AND Pin = :Pin;
   
      If fail terminate with appropriate message
         
   END
   
   T1: Withdrawal from an ATM
         
   BEGIN
         
   1. Compare Balance and Amount - Input is :AcctNo, :Amount
   
      SELECT * FROM Account
       WHERE AcctNo = :AcctNo
         AND Balance >= :Amount;
   
      If fail terminate with appropriate message
   
   2. Update balance
   
      UPDATE Account
      SET Balance = Balance - :Amount
      WHERE AcctNo = :AcctNo;
   
   3. Spit out cash and print appropriate message
   
   COMMIT
   

   T2: Transfer Funds at ATM

   BEGIN
         
   1. Transfer funds from one account to another
      - Input is :AcctNo_From, :AcctNo_To, :Amount
   
      Compare Balance and Amount 
   
      SELECT * FROM Account
       WHERE AcctNo = :AcctNo_From
         AND Balance >= :Amount;
   
      If fail terminate with appropriate message
   
   2. Update balances
   
      UPDATE Account
      SET Balance = Balance - :Amount
      WHERE AcctNo = :AcctNo_From;
   
      UPDATE Account
      SET Balance = Balance + :Amount
      WHERE AcctNo = :AcctNo_To;
   
   3. Print appropriate message
         
   COMMIT
   

[Top]