Database Homework 6- Stored Routines and Transactions Solution

For this homework we will use the bike_stores database you created in homework 5. If you removed it, dropped it, changed its data or lost it in any way, please follow the instructions in Problem 1 for homework 5 to recreate it.


Problem 1: Create a stored function (Points: 20)

Create a stored function called findStoreByProduct() that will take a string as input, and return a store_id. The store_id relates to the store with the largest stock the product identified in the input string.


1. Paste the full body of your stored function. (8 points)

delimiter //
create function findStoreByProduct(prod_name varchar(255)) returns int deterministic begin
declare storeid int; select s.store_id into storeid from stocks s join
(
select p.product_id, p.product_name, max(st.quantity) as max_quantity from products p
join stocks st on p.product_id=st.product_id where p.product_name=prod_name group by p.product_id, p.product_name ) t on s.product_id= t.product_id and
s.quantity=t.max_quantity; return storeid; end //

delimiter ;


2. Execute the following commands and provide screenshots of the results:

(4 points)

(4 points)

SELECT findStoreByProduct ("Trek Farley Alloy Frameset - 2017");




Problem 2: Create a stored procedure (Points: 30)

Create a stored procedure called placeOrder() that can be called to insert a new order in the database. It will receive a customerId as an INT, a productId as an INT and a qty as an INT and return (as an output parameter) the order_id of the new row created in table orders.


For the new record in the order_items table, set the product_id and quantity to the productId and qty passed into the stored procedure. The item_id shall be set to 1 (since this order will only have one item).The list price should be retrieved from the products table using the passed productId. The discount value should be set to 0.

We have provided a rough framework and code comments to help guide you. You do not need to follow this flow if you have other ideas on how to implement it:


DELIMITER //
CREATE PROCEDURE placeOrder(IN customerId INT, IN productId INT, IN qty INT, OUT createdOrderId INT) BEGIN
/* Declare your variables. */


/* Calculate the next order id, since this column is not auto-increment. */


/* Find the store to use for serving this order. */


/* Pick any staff member that works in the selected store. */
/* Create the order row. */

/* Find the price for the product */

/* Create the item row. */

END //
DELIMITER ;




1. Paste the full body of your stored procedure. (20 points)



DELIMITER //
CREATE PROCEDURE placeOrder(IN customerId INT, IN productId INT, IN qty INT, OUT createdOrderId INT)
BEGIN
/* Declare your variables. */ declare next_order_id int; declare storeId int; declare orderStatus int; declare staffId int; declare listPrice decimal(10,2); set orderStatus=1;

/* Calculate the next order id, since this column is not auto-increment. */ set next_order_id=(select max(order_id)+1 from orders); set createdOrderId=next_order_id;
/* Find the store to use for serving this order. */ set storeId=( select s.store_id from stocks s join ( select p.product_id, p.product_name, max(st.quantity) as max_quantity from products p join stocks st on p.product_id=st.product_id where p.product_id=productId group by p.product_id, p.product_name
) t on s.product_id= t.product_id and s.quantity=t.max_quantity
);

/* Pick any staff member that works in the selected store. */ set staffId=( select staff_id from staffs where store_id=storeId and active=1 limit 1 );
/* Create the order row. */
insert into orders(order_id, customer_id, order_status, order_date, required_date, shipped_date, store_id, staff_id)
values(next_order_id, customerId, orderStatus, curdate(), adddate(curdate(), interval 7 day), null, storeId, staffId);
/* Find the price for the product */ set listPrice=( select list_price from products where product_id=productId
);

/* Create the item row. */
insert into order_items(order_id, item_id, product_id, quantity, list_price, discount) values(next_order_id, 1, productId, qty, listPrice, 0);
END //
DELIMITER ;


2. Execute the following commands and provide screenshots of the results: (5 points)

CALL placeOrder( 20, 10, 1, @order_id);
SELECT * FROM orders WHERE order_id = @order_id;
SELECT * FROM order_items WHERE order_id = @order_id;




3. Execute the following commands and provide screenshots of the results: (5 points)

CALL placeOrder( 11, 12, 2, @order_id); SELECT * FROM orders WHERE order_id = @order_id;
SELECT * FROM order_items WHERE order_id = @order_id;





Problem 3: Stored procedure with transaction support (Points: 30)

Modify the placeOrder() stored procedure to receive a fourth parameter store_id and use transactions. Assign the received store_id to the created order. After inserting the order_items and orders rows, verify that the requested store has stock for the requested product and quantity and if it does not then rollback the transaction and return an order_id of -1.

1. Paste the full body of your stored procedure. (20 points)


drop procedure placeOrder;

DELIMITER //
CREATE PROCEDURE placeOrder(IN customerId INT, IN productId INT, IN qty INT, IN storeId int, OUT createdOrderId INT)
BEGIN
/* Declare your variables. */ declare next_order_id int; declare orderStatus int; declare staffId int;
declare listPrice decimal(10,2); declare _quantity int; set orderStatus=1;

/* Calculate the next order id, since this column is not auto-increment. */
set next_order_id=(select max(order_id)+1 from orders);
set createdOrderId=next_order_id;

START TRANSACTION;

set staffId=(
select staff_id from staffs where store_id=storeId and active=1 limit 1
);

insert into orders(order_id, customer_id, order_status, order_date, required_date, shipped_date, store_id, staff_id)
values(next_order_id, customerId, orderStatus, curdate(), adddate(curdate(), interval 7 day), null, storeId, staffId);

set listPrice=( select list_price
from products where product_id=productId
);

insert into order_items(order_id, item_id, product_id, quantity, list_price, discount)
values(next_order_id, 1, productId, qty, listPrice, 0);


/* verify that the requested store has stock for the requested product and quantity. */

set _quantity=(select quantity from stocks
where store_id=storeId and product_id=productId
);

if _quantity<qty then rollback;
set createdOrderId=-1; else
update stocks set quantity=quantity-qty where store_id=storeId and product_id=productId; commit; end if; END //
DELIMITER ;


2. Execute the following commands and provide screenshots of the results: (5 points)

CALL placeOrder( 11, 12, 10, 1, @order_id);
SELECT @order_id;
SELECT * FROM orders WHERE order_id = @order_id;
SELECT * FROM order_items WHERE order_id = @order_id;
SELECT max(order_id) FROM orders;





3. Execute the following commands and provide screenshots of the results: (5 points)

CALL placeOrder( 11, 12, 20, 1, @order_id);
SELECT @order_id;
SELECT * FROM orders WHERE order_id = @order_id;
SELECT * FROM order_items WHERE order_id = @order_id;
SELECT max(order_id) FROM orders;






Problem 4: Stored procedure with exception handling (Points: 20)

Modify the placeOrder() stored procedure from Problem 3 to handle issues with invalid customer_id, product_id and store_id using an EXIT HANDLER. The exit handler must rollback the transaction and return an order_id of -1

1. Paste the full body of the modified stored procedure. (10 points)


drop procedure placeOrder;

DELIMITER //
CREATE PROCEDURE placeOrder(IN customerId INT, IN productId INT, IN qty INT, IN storeId int, OUT createdOrderId INT)
BEGIN
/* Declare your variables. */ declare next_order_id int; declare orderStatus int; declare staffId int;
declare listPrice decimal(10,2); declare _quantity int;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
set createdOrderId=-1; END;
set orderStatus=1;
/* Calculate the next order id, since this column is not auto-increment. */
set next_order_id=(select max(order_id)+1 from orders);
set createdOrderId=next_order_id;

START TRANSACTION;

set staffId=(
select staff_id from staffs where store_id=storeId and active=1 limit 1
);

insert into orders(order_id, customer_id, order_status, order_date, required_date, shipped_date, store_id, staff_id)
values(next_order_id, customerId, orderStatus, curdate(), adddate(curdate(), interval 7 day), null, storeId, staffId);

set listPrice=( select list_price
from products where product_id=productId
);

insert into order_items(order_id, item_id, product_id, quantity, list_price, discount)
values(next_order_id, 1, productId, qty, listPrice, 0);


/* verify that the requested store has stock for the requested product and quantity. */

set _quantity=(select quantity
from stocks
where store_id=storeId and product_id=productId
);

if _quantity<qty then rollback;
set createdOrderId=-1; else
update stocks set quantity=quantity-qty where store_id=storeId and product_id=productId; commit; end if; END //
DELIMITER ;


2. Execute the following commands and provide screenshots of the results: (5 points)

CALL placeOrder( 11, 12, 10, 1, @order_id);
SELECT @order_id;
SELECT * FROM orders WHERE order_id = @order_id;
SELECT * FROM order_items WHERE order_id = @order_id;
SELECT max(order_id) from orders;





3. Execute the following commands and provide screenshots of the results: (5 points)

SELECT max(order_id) from orders;
CALL placeOrder( 1500, 12, 10, 1, @order_id);
SELECT @order_id;
CALL placeOrder( 11, 500, 10, 1, @order_id);
SELECT @order_id;
CALL placeOrder( 11, 12, 10, 8, @order_id);
SELECT @order_id;
SELECT max(order_id) from orders;







Bonus: Triggers (Points: 10)

Create a trigger object that will assign the store to serve a given order AFTER the order is created. For this to work we will need to modify the orders table so that the store_id and staff_id columns can be left null. Here is the command to do that:

ALTER TABLE orders MODIFY store_id INT NULL, MODIFY staff_id INT NULL;

Now, take the stored procedure you created in Problem 2 and modify it so it does not assign the store_id or the staff_id. Invoke it and verify the store_id and the staff_id are filled by the trigger action on INSERT in the order_items table by invoking the modified placeOrder() stored procedure.

1. Provide the full body of your trigger. (5 points)



DELIMITER //
CREATE TRIGGER orders_update AFTER insert
ON order_items FOR EACH ROW
BEGIN
DECLARE storeId INT; declare staffId int;

select s.store_id into storeId from stocks s join
(
select p.product_id, p.product_name, max(st.quantity) as max_quantity from products p
join stocks st on p.product_id=st.product_id where p.product_id=new.product_id group by p.product_id, p.product_name ) t on s.product_id= t.product_id and
s.quantity=t.max_quantity
;

select staff_id into staffId from staffs where store_id=storeId and active=1 limit 1;
update orders set store_id=storeId, staff_id=staffId where order_id=NEW.order_id;

END //
DELIMITER ;




drop procedure placeOrder;
DELIMITER //
CREATE PROCEDURE placeOrder(IN customerId INT, IN productId
INT, IN qty INT, OUT createdOrderId INT)
BEGIN
/* Declare your variables. */
declare next_order_id int; declare orderStatus int; declare listPrice decimal(10,2); set orderStatus=1;

/* Calculate the next order id, since this column is not auto-increment. */
set next_order_id=(select max(order_id)+1 from orders);
set createdOrderId=next_order_id;

/* Create the order row. */
insert into orders(order_id, customer_id, order_status, order_date, required_date, shipped_date)
values(next_order_id, customerId, orderStatus, curdate(), adddate(curdate(), interval 7 day), null);

/* Find the price for the product */ set listPrice=( select list_price
from products where product_id=productId
);

/* Create the item row. */
insert into order_items(order_id, item_id, product_id, quantity, list_price, discount)
values(next_order_id, 1, productId, qty, listPrice, 0);
END //
DELIMITER ;


2. Copy and paste the output from invoking your stored procedure and then executing the following commands (5 points):

SHOW columns FROM orders;
SELECT * FROM orders WHERE order_id = @order_id;