CIS 3010: Assignment 2
Due date: 14 October 2016
Value: 30%
Please submit the CODE and OUTPUT for all programming questions. You will submit this assignment electronically in the ASSESSMENT area of the Study Desk.
For the output, a screen dump/capture is required as well as the code.
IMPORTANT NOTES – PLEASE READ BEFORE YOU BEGIN WORK
1. This assignment prepares you for the examination and you should consider each hour devoted to the assignment as an hour devoted to exam preparation.
2. I urge you not to give up if you are unable to do all the questions. Do as many as you can and submit what you have done.
For this assignment we will use the Brewers database that comes with the textbook. The script to create the databases is included as a file in the assignment 1 section of the Moodle course web site. You will need to run the scripts to create the needed database tables. If you are using you own Oracle insulation the script should work as normal.
Please Read Carefully.
There are 3 question valued a total of 100 marks. You need to provide a well-structured and commented solution to the problems. With your code solution you also need to provide a screen shot of the solution output on your screen. You can use the screen capture features of the OS you are using. For Windows you can use the ‘print current window capture’ key stroke ‘Alt-Shift-Print Screen’. Depending on your keyboard you may have to use the function key as well. For Apple Mac use ‘Command (?)-Shift-4’ and select screen to capture.
Important Notes:
• You must NOT use any implicit cursors, table joins, subqueries, set operators, group functions or SQL functions (such as COUNT) in the PL/SQL function or the PL/SQL anonymous block unless explicitly stated that they can be used.
• Explicit Cursors should be declared as parametrized cursors as needed.
• The PL/SQL anonymous block must be ONE block only to call the relevant procedures or functions. Do NOT write a block to perform each task of the specifications.
MARKING CRITERIA
1. The code executes without error messages.
2. The code produces the required output.
3. The code addresses the specification and provides a solution to every element in the specification.
4. The code is well structured and, where applicable, adopts an optimal and sophisticated approach to PL/SQL.
5. The programing units have been separated into necessary functions and procedures
6. Exceptions need to be catered for appropriately in procedures and functions.
7. The code is well commented and well structured
The structures and information about the tables is located in Appendix A (page 423) of the Casteel textbook. The structure of the tables used in this assignment questions is reproduced below.
BB_TAX
BB_SHOPER
BB_PRODUCT
BB_SHIPPING
BB_BASKETITEM
BB_BASKET
Question 1 (40 marks)
a) Write a function (called total_cost) that returns the total cost of an item after the tax has been added to the product. The function has two IN parameters Customer ID and Item ID and returns a number as a result of the function execution. If a Customer ID, Product ID or STATE does not exist in the relevant tables the function should return NULL as the result. You need to return the NULL result explicitly and not as a part of the cost calculation. The function calculates cost of an item based on the percentage charged for the state the product is shipped to. (10 marks)
b) Write a stored function called calculated_tax (which take in take a single IN parameter Product ID. The function returns the sum of tax that all customer had to pay for that product. (5 marks)
c) Write a stored procedure called total_product_customers which takes in a single IN OUT parameter. The procedure finds the total number customer who have purchased that item. (5 marks)
d) Write a stored procedure (called products_report) that takes a single IN parameter Product ID and creates a report to the screen as follows. For every customer in the table that purchased that Product ID create a report using the DBMS_OUTPUT module to print the Customer Last Name, First Name, State, Product Description, Product Cost, Tax, total cost by making use of relevant procedures and functions defined previously.
After processing all the customers display the following information.
Total number of customers who purchased product XX is YY and total tax calculated for sales is $XX.YY. (10 marks)
Call the procedure products_report from an anonymous block and capture the results. (5 marks)
e) Write an SQL statement that displays customer details (ID, First Name, Last, Name, State) and total to be paid for each customer for product in the BB_PRODUCT table whose ID is 4 by calling the total_cost function. Execute the SQl statement and capture the output os the SQL statement and paste the result in the assignment. (5 marks)
Question 2 (20 marks)
The Brewers Company is concerned about possible unauthorised changes to customer orders that may compromise the company's profit. You will create a database trigger to monitor such changes to some important columns in the BB_BASKET table.
1. Create a copy of the BB_BASKET table in your own schema by using the following command:
CREATE TABLE MY_CUSTOMER AS SELECT * FROM BB_BASKET;
2. Also create a table to store change logs (call the table BB_CHANGELOG) to hold the following fields
IDBASKET, QUANTITY, SUBTOTAL, TOTAL and LOG_DATE DATE.
You will need to refer to the relevant table for field types and sizes. The LOG_DATE field will contain the system date.
3. Create a database trigger with the following specification:
• fires AFTER an UPDATE on MY_CUSTOMER table.
• fires if there is any change to QUANTITY, SUBTOTAL or TOTAL columns of the MY_CUSTOMER table.
• fires for each row.
• inserts a first row: the affected IDBASKET column, the OLD values of the QUANTITY, SUBTOTAL and TOTAL columns and the current date into the table BB_CHANGELOG.
• inserts a second row: the affected IDBASKET column, the NEW values of the QUANTITY, SUBTOTAL and TOTAL columns and the current date into the table BB_CHANGELOG.
4. Test the trigger with the following specification:
• Change to MY_CUSTOMER table by issuing the following command:
UPDATE MY_CUSTOMER
SET QUANTITY = 333, TOTAL = 25000
WHERE IDBASKET = 13;
5. Now, select all rows from the BB_CHANGELOG table. This should display the OLD and NEW values of the changes you made due to firing of the trigger.
Output all the rows of CUSTOMER_CHANGELOG table using a select statement and capture the output and paste it in the assignment submission.
Question 3 (40 marks)
This question has a few section (procedures and functions) described. You are free to write other helper procedures or functions but they will have to have high cohesion and low coupling (do one thing only and not be reliant on external values).
a) Create a PL/SQL procedure called shopping_month which has an in parameters for the year and the month. The procedure will display to the screen the following information for each basket created (DTCREATED) for that year/month. The information needs to be retrieved using an explicit cursor. (10 marks)
BASKEID basket has a total of BASKET_TOTAL plus SHIPPING total cost of order BASKET_TOATL_PLUS_SHIPING
b) Write a function called total_shipping which takes in the date parameter and calculates the cost of shipping for all the baskets which have been created in that year/month. (5 marks)
c) Write a stored procedure called shipping_brakedown which takes no parameters but produces a breakdown of the shipping costs for all orders grouped by the shipping category.
Shipping cost 1 – 5 total orders XX total cost $YY.YY
Shipping cost 6 - 10 total order XX total cost $YY.YY
d) Write a procedure called uncommitted_month which reports on all the baskets created but where the order has not been placed (ORDERPLACED) for all the baskets in the table. The output should be as follows. (10 marks)
Currently there are COUNT numbers of orders created but not finalized with a total of $AMOUNT.XX and shipping cost of $SHIPPING.XX.
e) Write a procedure called report_shopping_totals that will produce the following output in the format specified bellow. The procedure needs to use explicit cursor/s for each year starting with the oldest year and produces a summary of the shopping for that month.
The years and month output needs to be in ascending order.
Example output only as a guideline. You will need to replace the uppercase fields with the necessary values
Year: YYYY
Month: January
BASKEID basket has a total of BASKET_TOTAL plus SHIPPING total cost of order BASKET_TOATL_PLUS_SHIPING
Total Shipping for the month of January is $SHIPPING.XX
March
Basket BASKEID has a total of BASKET_TOTAL plus SHIPPING total cost of order BASKET_TOATL_PLUS_SHIPING
Total Shipping for the month of March is $SHIPPING.XX
Year: YYYY
Month: February
Basket BASKEID has a total of BASKET_TOTAL plus SHIPPING total cost of order BASKET_TOATL_PLUS_SHIPING
Total Shipping for the month of Ferbuary is $SHIPPING.XX
Total number of baskets processed NUMBER_OF_BASKETS total income of all orders is $AMOUNT.XX total shipping cost $SHIPPING.XX.
There are NUMBER_OF_ORDERS numbers of orders created but not finalized with a total of $AMOUNT.XX and shipping cost of $SHIPPING.XX
Shipping Breakdown
Shipping cost 1 – 5 total orders XX total cost $YY.YY
Shipping cost 6 - 10 total order XX total cost $YY.YY
The anonymous block should only have to call report_shopping_totals which will contain the logic to display the requested output using loops and calling stored procedures and functions as needed. Your solution for the final output needs to use the functions and procedures defined previously and any other helper function or procedures you have added. Due to the data in the tables you will only have one year on the report.
Your code needs to work when more data is added. You may add extra functions and procedures as your design needs but you must make use of the procedure and functions specified in this question. (15 marks)
GET ANSWERS / LIVE CHAT