IT360 Lab 12: Triggers, Stored Procedures, and Views
DUE: April 18, 2013, 2359 (paper copy BEFORE start of lab next day)
This lab should get you familiarized with creating and using stored procedures, triggers, and views. We will use MySQL for this lab. Use your notes, the slides, and online documentation to solve the exercises in the lab.
a) For this lab you will be using your MySQL database on cardhu.cs.usna.edu. Connect to your database on cardhu using MySQL Workbench
b) If the Product table in your database does not have a “QuantityInStock” column or something similar, modify the table to add the column:
ALTER TABLE Product add QuantityInStock integer default 0;
You should have now some tables similar with these:
Product (BarCode, PName, Price, QuantityInStock)
SaleItem (SaleID, BarCode, Quantity)
c) Create a file called yourLastName_yourfirstName_Lab12.sql to contain all the questions as answers for this lab. Place the question as a comment before each answer. Make sure you save the file on your X drive and not on the local machine. If the answer is not an SQL statement, place the answer between /* … */.
Write SQL code queries to accomplish the following tasks. All answers should be saved in yourLastName_yourFirstName_Lab12.sql file.
PART 1: TRIGGERS
Write SQL code/queries to accomplish the following tasks. Add all answers to the yourLastName_yourFirstName_Lab12.sql file. If the answer is not an SQL statement, place the answer between /* … */.
a) Write and execute the code to create the trigger.
b) Either use your application created in lab 10 to buy some products, or manually insert a row in the SaleItem table for an existing sale. Was the quantity in the Product table updated? Why? Write your answers in the yourLastName_yourFirstName_Lab12.sql file.
PART 2: STORED PROCEDURES
a) Write and execute the code to create the stored procedure. Do not forget to change the default delimiter before and after the procedure. You should see the spInsertProduct stored procedure in your database under “routines” if you right-click on your database name and select “refresh”.
b) Try to insert a product with price < 0 into the database by invoking the stored procedure. Write your invocation code to yourLastName_yourFirstName_Lab12.sql file. Was the product inserted? Try now to insert a product with price > 0. Was this product inserted? Write your answers in the yourLastName_yourFirstName_Lab12.sql file.
c) Extra credit:
c1) Modify your addProduct method in the Product class file that you created in Lab 9 to invoke the spInsertProduct procedure instead of directly submitting an INSERT query to the database. Hint: The query you submit to the database should be something like “call spInsertProduct(xx,yy,zz)”.
c2) Add a quantityInStock input field in your form in productPage.php created in Lab 9 and then modify the productPage.php and product.inc.php to process this field. Now you should be able to insert a product using the web interface (the “insert” button in the form should invoke the modified addProduct method in the Product class)
PART 3: Queries and Views
Write SQL queries to accomplish the following tasks. Add all answers to the yourLastName_yourFirstName_lab11.sql file. Write the question as a comment /*…*/ before each answer. If the answer is not an SQL statement, place the answer between /* … */.
Run the SQL statement.
Check in the database (right-clickàRefresh) that the view was created.
To demonstrate this, do the following:
a) Insert a row with value for PName = ‘Bread’ in Product table (use INSERT INTO … VALUES …). (You can change the product name to fit your store)
b) Execute the query from exercise 5 again.
Did the results changed? Why? Write your answers in the yourLastName_yourFirstName_Lab12.sql file.
Run the following query (or the similar query based on your data):
SET PName = 'French Bread'
WHERE PName = 'Bread'
Does the query succeed? Why? Write your answers in the yourLastName_yourFirstName_lab11.sql file.
Execute “SELECT * FROM Product” and check whether the name of ‘Bread’ product was changed to ‘French Bread’.
One reason to use the views: Hide complexity and simplify queries when frequently using the results of a complex query.
Example: In C++, would you rather write the code to compute a square root instead of using sqrt()? Of course not, and the same idea applies to SQL. Hide complexity and present a simple “replacement” of the complex query.
Hint1: use GROUP BY
Hint2: you can do calculations in the SELECT statement. For example, the following query will display the barcode and the total inventory value for each product:
SELECT BarCode, Price*QuantityInStock AS TotalValue
Write the SQL query on the ProductProfit view to show the top 5 products based on total profit in order of highest profit to lowest profit.
Does the query succeed? Why? Write your answers in the yourLastName_yourFirstName_Lab12.sql file.
Turn in (due before start of lab on April 12, 2012) BOTH electronic and paper submissions are required: