• Preparation and Submission: All challenge problems must be submitted electronically. Please refer to Challenge Problems #1 for accepted formats, guidelines on typing solutions, and details on electronic submission. For this assignment call your solutions file challenge4.<ext>, and as usual submit your solutions using the script /afs/ir/class/cs145/bin/submit-challenge.

• The on-time deadline is 11:59 PM on the due date. See the Assigned Work page for the policy on late submissions.

• Honor Code reminder: You must indicate in your solution any assistance (human or otherwise) that you received. Any assistance received that is not given proper citation will be considered a violation of the Honor Code. In any event, you are responsible for understanding and being able to explain on your own all material that you submit.

The Problems

1. This problem explores the differences between row-level and statement-level triggers. For the entire problem you may assume that triggers are not permitted to introduce temporary variables or tables, and they cannot call stored procedures. Otherwise, use the SQL-99 trigger language as presented in class and the textbook. We suggest that your example triggers operate over a single table Employee(ID,dept,salary), where ID is a key. If you prefer to use a different table or wish to introduce additional tables you are welcome to do so as long as you state the schemas clearly.
   

   (a) Write a row-level trigger such that no statement-level trigger can be guaranteed to always achieve the same final state of the database. In addition to specifying the trigger in SQL, describe in English what your trigger does, and explain why an equivalent statement-level trigger cannot be written. (Recall that the equivalent trigger is not allowed to use temporary variables, temporary tables, or stored procedures.)
   

   (b) Write a statement-level trigger such that no row-level trigger can be guaranteed to always achieve the same final state of the database. In addition to specifying the trigger in SQL, describe in English what your trigger does, and explain why an equivalent row-level trigger cannot be written. (Recall that the equivalent trigger is not allowed to use temporary variables, temporary tables, or stored procedures.)

   Note: In the May 10 lecture it was suggested that there are statement-level triggers without equivalent row-level triggers, but not necessarily vice-versa. Actually both situations do exist; otherwise we would not be including part (a) of this problem.
   
   
   

2. Consider the following simple view over tables R(A,B) and S(B,C):

     Create View V as
       (Select A,C From R,S Where R.B=S.B)
   

   This problem explores when a modification posed against view V is guaranteed to have a single (unambiguous) view update translation. Suppose a user issues a modification M (inserts, deletes, or updates) to be performed on view V. Let v denote the logical contents of V before the modification, and let v' denote the new view contents as if modification M is performed directly on v. A view update translation is a set of one or more modifications M' on the base relations over which V is defined, such that if M' is performed on the original base relations producing v, then the updated base relations produce v'. Furthermore, M' should be a minimal set of modifications that correctly produce v'; that is, v' should not be produced if we drop any of the modifications in M'. You may find it helpful to draw a diagram of the relationships among the base data, v, v', M, and M'.

   For each of the following types of modifications to V, we are interested in whether there are conditions on the base data under which any view update of that type always has a single correct view update translation (as defined in the previous paragraph). The types of conditions you might consider are keys for base relations, referential integrity constraints on the base relations, or other constraints on join multiplicity. For each update type, list a set of such conditions that guarantee an unambiguous view update translation. If an unambiguous translation cannot be guaranteed regardless of conditions, say so.
   

   (a) Update the A value in a specific tuple t of V.

   (b) Update the C value in all tuples of V with A=k, for a given constant k.

   (c) Insert a single new tuple t into V.

   (d) Delete a specific tuple t from V.

   (e) Delete all tuples from V with A=k, for a given constant k.