Due: Dec 15th at 11:59:59pm
Dec 19th at 11:59:59pm
In this lab you will add more functionality to your database by implementing two views, adding several triggers and adding stored Procedures. Some of these will automate some of the functionality that you had to implement in Java in the last lab.
Add the following functionality to your database.
Add a trigger on the database to:
everytime a customer is removed from the database,
remove any of the customer's orders
remove the customer's address(es)
Whenever a new order is entered, the quantity from the order should be deducted from the total quantity on hand.
Whenever an order is deleted, the quantity from the order should be added to the total quantity on hand.
Create stored procedures to do the following:
Create a stored procedure to calculate the total cost to the customer for an order. The cost should be the item price times the quantity plus the sales tax on the order (calculated using the customer's shipping address.) plus the cost of the shipping method selected for the order.
Create a stored procedure to automate the placing of a customer order. You did this in Java last time, now you will do the same in the database itself. The procedure should take four parameters:
The quantity ordered
The uid of the customer doing the ordering
the uid of the product ordered
the uid of a shipping option.
This function should insert a new order into the table when it is called. If any of the above items are invalid for the current database, the function should return an error code. Document your error codes in comments and in the readme.
Create these views for the database:
Add a view called Month_stats. This view should show information about all of the orders from the month of November. The information that should be included is:
The first and last names of the customer.
The Name of the product ordered
The quantity ordered
The total billing cost of the order (hint: use your procedure from above)
Add a view called profits_by_product. In this view, you will include
The name of each product that has orders in the last month
The total profit generated from that product (just profit for the company, don't include tax, cost to the company or shipping cost of orders.)
The political climate if your simulated company has changed. The
higherups now believe that the products table should contain the
product cateory name for each product within the products table itself.
Create a view called Proeducts_new that simulates such a table.
turn in a file called lab4.sql with the commands that you used to create your views, stored procedures and triggers.
Once again you need to include a readme.txt file with your lab. Your file should contain:
Your name
A description of your implementation of the views, triggers and procedures. Tell me their names, what you did, how you did it an why. Do not skimp on this as it will be given a good bit of weight in the grading.
A description of how I should use each of your views, triggers and stored procedures. This is akin to the directions for running your programs from the last labs.
A list of anything that you didn't quite get working and a description of how far you did get.