Comp 580 Database Systems

Lab4 Triggers, Views and Stored Procedures


Due: Dec 15th at 11:59:59pm Dec 19th at 11:59:59pm

Summary:

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.

Details:

Add the following functionality to your database.

Triggers:

Add a trigger on the database to:

  1. everytime a customer is removed from the database,

      1. remove any of the customer's orders

      2. remove the customer's address(es)

    1. Whenever a new order is entered, the quantity from the order should be deducted from the total quantity on hand.

    2. Whenever an order is deleted, the quantity from the order should be added to the total quantity on hand.

Stored Procedures

Create stored procedures to do the following:

  1. 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.

  2. 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:

      1. The quantity ordered

      2. The uid of the customer doing the ordering

      3. the uid of the product ordered

      4. 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.

Views:

Create these views for the database:

  1. 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:

      1. The first and last names of the customer.

      2. The Name of the product ordered

      3. The quantity ordered

      4. The total billing cost of the order (hint: use your procedure from above)

  2. Add a view called profits_by_product. In this view, you will include

      1. The name of each product that has orders in the last month

      2. 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.)

  3. 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.



What to turn in.



A listing of your sql commands

turn in a file called lab4.sql with the commands that you used to create your views, stored procedures and triggers.

Readme.txt

Once again you need to include a readme.txt file with your lab. Your file should contain: