COMP 3421 PDA #5

(50 Points)

Due noon, Feb 23, 2016

Step 5 of Your PDA

Note: this assignment is a modification of material developed by the Stanford Database Group

For a brief intro to mySQL scripting, check out this Script Programming Reference

Also helpful will be this List Of Example procedures, functions, and triggers.

1. (10 pts.) Make sure you have foreign key constraints in your database. 1If you do not, add them. Show the create table statement that defines these forign key constraints. Create an update that violates the foreign key constraint. List the update command and then show the output of running this update.

2. (15 pts.) Write a mySQL procedure or function (your choice) and script that calls this procedure/function. This procedure/function should be logical for your database and should include one or more inputs and an output. After calling the procedure you should print out the results of the output. If you procedure/function makes multiple database state changes document the changes with before/after select commands showing a (small) set of data that is changed. Include prose (english) that describes what the procedure/function does.

3. (10 pts.) Create a Logging trigger and demonstrate how it works with your database.

4. (15 pts.) Create an index and show how it speeds up two types of queries: 1) a selection on a single relation; and 2) a selection that involves a join. Note, to get times that will be measurably different you will likely need to populate your databses with a larger data set.