Note: This assignment is a modification of material developed by the Stanford Database Group.
For a brief introduction to MySQL scripting, check out this Script Programming Reference (especially Sections 1.4, 4.1, 4.2, 4.3, 4.4, and 4.5).
1 (10 points) Make sure you have foreign key constraints in your database. If you do not, add them. Show the create table statement that defines these foreign 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 points) 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 the results of the output. If your 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 (15 points) 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 databases with a larger dataset.
