CS 235: Assignment 5

DatingBase: Part 5

1. Write five MySQL routines (See MySQL 5.0 Stored Procedures) to perform operations on your DatingBase. 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 DatingBase 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 DatingBase 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 name of the homework is CS 235 Homework 5. All questions in this problem set are multiple choice. However, to answer them correctly you will need to work out their long (general) answers. A correct answer is worth 3 points. You lose a point for each incorrect answer. You can take the assignment as many times as you like; only your last score will count. Note that you will probably get slightly different questions each time you take it.