CS 235: Assignment 5

Due in class, Wednesday, May 16, 2007


PDA: Part 5

The fifth part of your PDA project is to incorporate MySQL Routines (stored procedures and functions) in your database.
  1. Write five MySQL routines (See MySQL 5.0 Stored Procedures) to perform operations on your PDA. Each routine should be nontrivial, illustrating a feature or features such as local variables, multiple SQL statements, loops, and branches. In addition, at least one should involve a cursor. We encourage you to be imaginative. However, here are some sorts of things you might try if you can't think of something more interesting:

    1. Compute some aggregate value from a relation and use that value to modify values in that or another relation.
    2. Create a new relation and load it with values computed from one or more existing relations.
    3. Enforce a constraint by searching your database for violations and fixing them in some way.

    Submit a listing of your routines and scripts showing them working. You should demonstrate that the routines had their intended effect by querying (before and after) some relation of your PDA that was changed by the routines. These queries may be included in the file that holds your MySQL routines for convenience.

  2. Choose two triggers that you declared in the fourth part of your PDA and show that they work. You need to turn in a script that shows, for each trigger, the effect of two database modifications. One modification should trigger the trigger, and the other not. Show in the script queries that demonstrate that the trigger has an effect in the first case and not in the second.

Problem Set

The relation R(x) consists of a set of integers --- that is, one-component tuples with an integer component. Angelina's transaction is a query: 

SELECT SUM(x) FROM R;
COMMIT;
Brad's transaction is a sequence of inserts: 

INSERT INTO R VALUES(10);
INSERT INTO R VALUES(20);
INSERT INTO R VALUES(30);
COMMIT;
Connie's transaction is a sequence of deletes: 

DELETE FROM R WHERE x=30;
DELETE FROM R WHERE x=20;
COMMIT;
Before any of these transactions execute, the sum of the integers in R is 1000, and none of these integers are 10, 20, or 30. Angelina's, Brad's, and Connie's transactions run at about the same time.
  1. If all three transactions run under isolation level READ COMMITTED, which sums could be produced by Angelina's transaction?
  2. Which sums could be returned by Angelina's transaction if all three transactions run under isolation level READ UNCOMMITTED, but not if they run under isolation level SERIALIZABLE?