Project 3
docx
keyboard_arrow_up
School
Austin Community College District *
*We aren’t endorsed by this school
Course
2309
Subject
Industrial Engineering
Date
Dec 6, 2023
Type
docx
Pages
3
Uploaded by DeaconMusic9356
Project 3
Case 6-1: Updating Basket Data at Order Completion
A number of functions created in this chapter assume that the basket amounts, including shipping,
tax, and total, are already posted to the BB_BASKET table. However, the program units for updating
these columns when a shopper checks out haven’t been developed yet. A procedure is needed to
update the following columns in the BB_BASKET table when an order is completed: ORDERPLACED,
SUBTOTAL, SHIPPING, TAX, and TOTAL.
Construct three functions to perform the following tasks: calculating the subtotal by using the
BB_BASKETITEM table based on basket ID as input, calculating shipping costs based on basket ID as
input, and calculating the tax based on basket ID and subtotal as input. Use these functions in a
procedure.
A value of 1 entered in the ORDERPLACED column indicates that the shopper has completed the order.
The subtotal is determined by totaling the item lines of the BB_BASKETITEM table for the applicable
basket number. The shipping cost is based on the number of items in the basket: 1 to 4 = $5, 5 to 9 =
$8, and more than 10 = $11.
The tax is based on the rate applied by referencing the SHIPSTATE column of the BB_BASKET table with
the STATE column of the BB_TAX table. This rate should be multiplied by the basket subtotal, which
should be an INPUT parameter to the tax calculation because the subtotal is being calculated in this
same procedure. The total tallies all these amounts.
The only INPUT parameter for the procedure is a basket ID. The procedure needs to update the
correct row in the BB_BASKET table with all these amounts. To test, first set all column values to NULL
for basket 3 with the following UPDATE statement. Then call the procedure for basket 3 and check the
INSERT results.
UPDATE bb_basket
SET orderplaced = NULL,
Subtotal = NULL,
Tax = NULL,
Shipping = NULL,
Total = NULL
WHERE idBasket = 3;
COMMIT;
Solution:
Function to calculate subtotal
Function to calculate Tax
Function to calculate shipping cost
Procedure to update bb_basket
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help