Create at least three triggers and show that they work. One of your triggers should enforce an attribute constraint. You should also have a trigger that keeps track of some quantity such as the number of tuples in a relation, or an aggregate function of some attribute. Please, create a script triggers.sql that includes all of your trigger definitions. To show that your triggers work create a script scenario1.sql that shows, for each trigger, the effect of two database modifications. One modification should activate the trigger while the other should not. In the script, show queries that demonstrate that the trigger has an effect in the first case and not in the second one.
Create at least three stored routines, with at least one including a database modification, and show that they work. Please, create, a script routines.sql that includes all of your stored routine definitions. To show that your routines work create a script scenario2.sql that shows the effect of each routine. For routines including database modifications show the instance of an affected relation before and after the routine is called. For this assignment, there should be the four scripts: triggers.sql, scenario1.sql, routines.sql, scenario2.sql.
The instructions on how to submit your project can be found here.
Please make sure that all of the relevant files and no others are in that directory.
Note that you may need to change your database schema and instance by modifying create_db_large.sql and populate_db_large.sql scripts along with the data files referenced in populate_db_large.sql script from hw6. You can modify these scripts in order to ensure that your triggers and routines work as expected. You should commit those changes to your hw6 svn repository as well. We will not re-grade them but will use them to create and populate your database, so we can test your hw7 submission.
The name of the homework is MPCSDB-Aut13 HW7. There are 10 questions in this homework. 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 attempt the problem set as many times as you like; only your highest score will count. Note that you will probably get slightly different questions each time you attempt the problem set.
The due date for the Gradiance part of the homework is noon on Wednesday, November 20, 2013.