CIS 3010: Assignment 2
Assignment 2 Weight 30% Marked out of 100 Due date: 11 October 2021
Please submit the CODE in readable format (NOT SCREEN CAPTURE) and OUTPUT for all programming questions. You will submit this assignment electronically in the ASSESSMENT area of the Study Desk.
For this assignment we will use the Brewers database Chapter 4 that comes with the textbook (script available under link to this file). The script to create the databases is included as a file in Assignment 1 section of the course website as well as Week 2 on the study desk. You will need to run the scripts to create the needed database tables. If you are using your own Oracle insulation the script should work as normal.
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 your own Oracle insulation the script should work as normal.
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 at the end of this document.
Please Read Carefully before starting.
There are four(4) questions valued a total of 100 marks. You need to provide a well-structured and documented solution to the problems.
With your code solution you also need to provide the source code in editable format copy and paste in the submission file (NOT SCREEN CAPTURE).
The resulting output of the running solution you can use the screen capture or text copy and paste into the document. 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. You can also use the default capture tool, search for ‘Snipping Tool’ in start menu. For Apple Mac use ‘Command (?)-Shift-4’ and select screen to capture.
Important Programming Requirements:
• You must NOT use any Implicit Cursors, or Cursors with table joins, subqueries, set operators, group functions or SQL aggregate functions (such as COUNT).
• Explicit Cursors should be declared as parametrized cursors when used in functions or procedures.
• 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.
Question 1 (30 marks)
a)
Write a function called calc_product_cost that returns the total cost of an item after the tax has been added to the product. The function has two IN parameters IDSHOPER and IDPRODUCT and returns a number as a result of the function execution. If IDSHOPER or IDPRODUCT or Customer STATE value does not exist in the relevant tables the function should return NULL as the result. You need to return the NULL result explicitly. The function calculates cost of an item based on the item cost and percentage tax charged for the state the customers address is in. (10 marks)
b)
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 2 by calling the calc_product_cost function. Rename the resulting filed from the function call TOTAL_COST. (5 marks)
c)
Write a function called sf_total_order_item_cost which takes in three IN parameters IDSHOPER, IDBASKET and IDPRODUCT. The function returns the total cost on the order for an item for the customer. IDBASKET may corresponds to either a place or unplaced order. The cost of the item is calculated by calling calc_product_cost and multiplying it by the quantity in the order. You cannot directly use the Total and Quantity information from bb_Basket table for this question.
(5 marks)
d)
Write a procedure (called sp_report_on_products) that takes a single IN parameter Product ID and creates a report to the screen as follows. For every customer in the table create a report using the DBMS_OUTPUT module to print the Customer Last Name, First Name first character with full stop, State, Product Description, total pre-tax product cost, total tax, and, by calling the sf_total_order_item_cost function, output the total cost of the products in all the orders (either placed and unplaced orders) by the customer. Please do not output customers who do not have any orders for the given product.
Format for the line output.
LASTNAME, FIRSTNAME. State, Product: ‘DESCRITION’ Cost: $9999.99, Tax: $9999.99, Total Cost: $9999.99.
Example dummy output
John, C. NC, Product: ‘Some product description here’ Cost: $13.75, Tax: $0.56, Total Cost: $14.31.
The total pre-tax product cost and total tax calculated are the sum of pre-tax cost and tax that each customer would have for all the orders (either placed or not placed) of that product. Students do NOT need to use sf_total_order_item_cost function to calculate the total pre-tax product cost and total tax.
After processing all the customers display the following information.
Total customers processed XXXX and total tax calculated for all orders is $9999.99.
Note:
• Total customers processed XXXX
Explanation:
This is the total number of consumers (not orders) who have been involved in orders (placed or not placed). For example, there could be 5 consumers who have been involved in orders but the number of orders could be 10.
• Total tax calculated for all orders is $9999.99.
Explanation:
Suppose there are a total of two customers involved in orders (placed or not placed), A and B:
A are involved in two orders, and paid a total of tax of $10 for the two orders;
B are involved in three orders, and paid a total of tax of $20 for the three orders;
So the total tax calculated is $10+$20=$30.
Call the procedure from an anonymous block and capture the results. (10 marks)
Question 2 (20 marks)
The Brewers Company is concerned about possible unauthorised changes to customer orders that may compromise the company's profit.
Your task is to create a database trigger to monitor such changes to some important columns in the BB_BASKET table.
Important Setup:
Create a copy of the BB_BASKET table in your own schema by using the following command:
CREATE TABLE CUSTOMER_COPY AS SELECT * FROM BB_BASKET;
Create a table to store change logs (call the table TMP_CHANGELOG) to hold the following fields IDBASKET, QUANTITY, SUBTOTAL, TOTAL and LOG_DATE. You will need to refer to the relevant table for field types and sizes. The LOG_DATE field will contain the system date.
1. Create a database trigger with the following specification:
• fires AFTER an UPDATE on CUSTOMER_COPY table.
• fires if there is any change to QUANTITY, SUBTOTAL or TOTAL columns of the CUSTOMER_COPY table.
• fires for each row changed.
• 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 TMP_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 TMP_CHANGELOG. (10 Marks)
2. Test the trigger with the following specification:
• Go ahead and make a change to CUSTOMER_COPY table by issuing the following command:
UPDATE CUSTOMER_COPY
SET QUANTITY = 99, TOTAL = 99.99
WHERE IDBASKET = 12;
(5 Marks)
3. For the second test write a DML statement that sets the SUBTOTAL value in CUSTOMER_COPY table for the IDBASKET 12 to 50% of the TOTAL value currently recorded for that row. (5 marks)
Provide copy of your code and capture of the execution output.
Question 3 (30 marks)
This question has a few sections (procedures and functions) described. You are free to write 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).
1) Create a PL/SQL procedure called sp_shopping_month which has IN parameter(s) for the year and the month (Students can choose to use one IN parameter for both year and month information or two separate IN parameters for year and month information respectively). The procedure will display to the screen the following information for each basket created (DTCREATED) for that year/month no matter whether the orders are placed or not. The information needs to be retrieved using an explicit cursor. Here BASKET_TOTAL=subtotal+tax. (5 marks)
Basket BASKEID has a total of BASKET_TOTAL plus SHIPPING total cost of order BASKET_TOATL_PLUS_SHIPING
2) Write a function called sf_total_shipping which takes in the date parameter and returns the calculated cost of shipping for all the baskets (no matter whether the orders are placed or not) which have been created in that year/month.
(5 marks)
3) Write a procedure called sp_uncommitted_order_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. (5 marks)
There are COUNT numbers of orders created but not finalized with a total of $AMOUNT.XX and total shipping cost of $SHIPPING.XX.
4) Write a procedure called sp_report_shopping_totals that will produce the following output in the format specified bellow. The orders can be either placed or not placed. The procedure needs to use explicit cursor/s for each year starting with the oldest year and produces a summary of the shopping based on the month(s) of the year.
The years and month output needs to be in ascending order.
Example dummy output only as a guideline. You will need to replace the uppercase fields with the necessary values
Year YYYY
January
Basket BASKEID has a total of BASKET_TOTAL plus SHIPPING total cost of order BASKET_TOATL_PLUS_SHIPING
Basket BASKEID has a total of BASKET_TOTAL plus SHIPPING total cost of order BASKET_TOATL_PLUS_SHIPING
Total Shipping for the month $SHIPPING.99
March
Basket BASKEID has a total of BASKET_TOTAL plus SHIPPING total cost of order BASKET_TOATL_PLUS_SHIPING
Total Shipping for the month $SHIPPING.99
Year YYYY
February
Basket BASKEID has a total of BASKET_TOTAL plus SHIPPING total cost of order BASKET_TOATL_PLUS_SHIPING
Total Shipping for the month $SHIPPING.99
Total number of baskets processed COUNT total income of all orders is $AMOUNT.99 total shipping cost $SHIPPING.99.
There are COUNT numbers of orders created but not finalized with a total of $AMOUNT.99 and shipping cost of $SHIPPING.99.
(10 marks)
Write an anonymous block should only have to call sp_report_shopping_totals which will contain the logic to display the requested output. Your solution for the final output needs to use the functions and procedures defined previously. 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. (5 marks)
Provide a copy of your code and capture of the execution output.
Question 4 (20 marks)
a) Write a stored function called is_number which takes one IN parameter and returns a number if the input can be converted to a number otherwise the function should return an appropriate result. The function needs to correctly identify if the parameter can be converted to a numeric value. The function should work in PL/SQL as well as with SQL statements. (5 marks)
b) Write an SQL statement to use is_number which passes in test values is_number(‘123’) and is_number(‘12a3’) and returns the result. Provide the SQL code and captured output. (make sure you change the quotes if copying from this document as Word uses the wrong characters for single quote characters) (5 marks)
c) Write a stored procedure called sp_number_conversion which has a single IN parameter and a single OUT parameter. The IN parameter takes in a string to convert and the out parameter will contain a message depending on the success or fail of the conversion operation.
On success
‘Converted ‘Input_Value’ to value XXXXX’.
On failure
‘Unable to convert ‘Input_Value’ to a number’.
where ‘Input_Value’ is the value passed to procedure as the IN parameter. (5 marks)
d) Write an anonymous block of code that will loop and break up the string below into sections and pass them to sp_number_conversion procedure. Print out the result from calling the procedure for each data passes into the procedure
String to process
‘12345, 3.14e3, a123, 123a, 12a3, SixtySix, -12.34, +12.56’
(5 Marks)
Table Structures with field types
BB_SHOPPER
BB_PRODUCT
BB_BASKET
BB_BASKETITEM
BB_TAX
BB_SHIPPING
MARKING CRITERIA
1. Specified procedures, functions with the requested parameters
2. The code executes without error messages.
3. The code produces the required output.
4. The code addresses the specification and provides a solution to every element in the specification.
5. The code is well structured and, where applicable, adopts an optimal and sophisticated approach to PL/SQL.
6. The programing units have been separated into necessary functions and procedures.
7. Exceptions need to be catered for appropriately in procedures and functions.
8. Cursors used as specified in the specifications.
9. The code is well commented and well structured.
Academic misconduct
Please ensure that you do not share any information about your assignment with anyone. Do not accidentally leave printouts or USB drives that contain your assignment details where someone else can access them. Make sure not to store your assignments on a computer where another student can access your assignment.
Academic misconduct is unacceptable and includes plagiarism, collusion and cheating. You will find further explanation if you follow the link on the USQ home page to -Current Students- or https://social.usq.edu.au/study-tips/slides/keep-clear-plagiarism.
• This assignment must be all your own work
• The source of all information must be correctly cited and referenced
• Do not leave printouts for other people to access your work
• Do not leave USB with assignments plugged in public computers
Assignment that do not adhere to these requirements have the potential to be deemed to be the result of academic misconduct. Please make sure to read the USQ policies.
Should you have any queries regarding this assignment, please do not hesitate to email the course examiner.
Assessment policy
Please ensure to check the USQ assessment procedure http://policy.usq.edu.au/documents/14749PL.
Late submission of Assignments
Students can apply for an extension of time, to submit an assignment, at any time up to the deadline. You will see a specific Assignment extension request submission has been set up for this purpose. Any requests for extension must be made there and follow the USQ policies (Assessment: http://policy.usq.edu.au/documents/14749PL, Compassionate and Compelling Circumstances: http://policy.usq.edu.au/documents/131150PL).
Please note the maximum number of days that can be applied for in http://policy.usq.edu.au/documents/14749PL.
Grade calculation
Please ensure that when you calculate your grade for an assignment to consider the weighting of the assessment as well.