Reference no: EM132385305

Question 1: CIS 3010 Advanced COBOL Programming, California State University

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.

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.

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. The cost of the item is calculated by calling calc_product_cost and multiplying it by the quantity in the order.

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, Product Cost, Tax, Total Cost, by calling the sf_total_order_item_cost function.

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

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;

3. For 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: This question has a few section (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 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.

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 which have been created in that year/month.

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.

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

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

Information related to above question is enclosed below:

Attachment:- Assignment.rar