University of Helsinki, Depaertment of Computer Science
Database Management, Practice Session 5, Spring 2003

  1. Consider withdrawing 140 euros on your account using an ATM. What database operations are involved in this activity? What operations together form a transaction? When must this transaction be cancelled. Is it possible that the withdrawal remains committed in the dtabase but you don't get the money?

  2. It's on the responcibility of an application programmer to take care of the consistency of the transactions: each committed transaction should preserve the consistency of the database. If some consistency constraint (typically key- anf foreign key constraints) are defined in the schema, the database managemnet system must guarantee that the constraints are not violated. What control activities are needed and how should they be positioned as copared to the actual operations of the transaction. Consider key contraint and foreign key constraint. Should these be included in the same transaction as the actual operations or should there be a separate system transaction. What should be done if the transaction violates some constraint?

    Consider table works_on works_on(personID->person,projectID->project, hours) and the transaction 

       begin;
      insert into tyoskentely values (s1,p1,h1);
      insert into tyoskentely values (s2, p2, h2);
      insert into tyoskentely values (sn, pn, hn);
    commit;
    as an example.
  3. When the system crashes the log has the following contents: 
    
             99: [checkpoint] 
            100: [start, T1] 
            101: [start, T2] 
            102: [write, T1, A, 10, 20] 
            103: [write, T2, B, 11, 22] 
            104: [commit, T1] 
            105: [start, T3] 
            106: [write, T3, A, 20, 30] 
            107: [abort, T2] 
            108: [checkpoint] 
            109: [start, T4] 
            110: [write, T3, B, 11, 33] 
            111: [write, T3, A, 30, 40] 
            112: [write, T4, C, 15, 25] 
            113: [commit, T3] 
            114: [write, T4, C, 25, 35]
    1. What operations must be done to recover the database when page of data element A has PageLSN = 106, and the page of data element B has PageLSN = 110 and the page of data element C has PageLSN = 112.
    2. What operations are needed if line 113 (T3-commit) is omitted?

  4. What kind of commitment protocol should be used, if we want to avoid redoing any operations during recovery. What are the deficiences of this technique?

  5. Transaction processing creates a lot of log entries. On which point it is possible to cut the log and delete the entries not needed anymore?