IT360 Lab 9: Views, Stored Procedures, Triggers
DUE: April 9, 2010, BEFORE start of lab
This lab should get you familiarized with creating and using views, triggers, and stored procedures. We will use MySQL for this lab. Use your notes, the slides, and online documentation to solve the exercises in the lab.
· For this lab you will be using your MySQL database on schooner.cs.usna.edu. Your user account and the database name are mxxxxxx, where xxxxxx is your alpha. You can connect to schooner.cs.usna.edu server by using the user name and password. If you do not remember your password, contact your instructor.
· Using MySQL interactively:
· Establish a connection to the MySQL server: From “Start”àAll ProgramsàMySQLàMySQL Query Browser. Fill out the information requested:
o Server Host: schooner.cs.usna.edu
o Username: mxxxxxx
o Password: yourpassword
o Default Schema: mxxxxxx (xxxxxx is you alpha).
· Write and execute SQL queries: Write the SQL query in the SQL Query Area Response Set tab. Click “Execute” button, or select QueryàExecute from the menu, to execute the current query (the one with the cursor on it). Note that you need to terminate each SQL statement with a semi-colon (;), if you have more that one SQL statement in the window. If you want to execute multiple SQL statement, you can open a script tab, and write your queries there. When you execute the script, all statements in the script execute.
· Write and execute stored procedures and triggers: You need to open a script tab and write your code there, then execute the script. Do not forget the DELIMITER statements to change/ restore the default delimiter (;).
a)Copy all your files from Lab8 directory to Lab9 directory on your W drive.
b) Connect to the schooner.cs.usna.edu MySQL server by using MySQL Query Browser.
c)If the Product table in your database does not have a “QuantityInStock” column, 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)
PART 1: Queries and Views
Write SQL queries to accomplish the following tasks. Save all answers for this lab in a file called yourlastname_lab9.sql. Make sure you save the file on your X drive and not on the local machine. Write a comment “/* Exercise xx */”, where xx is the exercise number, 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 3 again.
Did the results changed? Why?
Run the following query (or the similar query based on your data):
SET PName = 'French Bread'
WHERE PName = 'Bread'
Does the query succeed? Why?
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?
PART 2: STORED PROCEDURES
Write SQL code/ queries to accomplish the following tasks. Add all answers to the yourlastname_lab9.sql file. If the answer is not an SQL statement, place the answer between /* … */.
a) Write and execute the code to create the stored procedure. (Open a script tab in MySQL Query Browser, write the code to change the delimiter, create the stored procedure, then change the delimiter back, and execute it) You should see the spInsertProduct stored procedure in your database if you right-click and “refresh”.
b) Try to insert a product with price < 0 into the database by invoking the stored procedure from the MySQL Query Browser. Was the product inserted? Try now to insert a product with price > 0. Was this product inserted?
c) Extra credit:
Modify your add_product method in the Product class file that you created in Lab 5 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)”.
Add a quantityInStock input field in your form in product_page.php created in Lab 5 and then modify the product_page.php and product.inc.php to process this field. Now you should be able to just insert a product using the web interface (the “insert” button in the form should invoke the modified add_product method in the Product class)
PART 3: TRIGGERS
Write SQL code/queries to accomplish the following tasks. Add all answers to the yourlastname_lab9.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 the previous lab to buy some product, or manually insert a row in the SaleItem table for an existing sale. Was the quantity in the Product table updated? Why?
Turn in (due before start of lab on April 9, 2010):