Homework 3: Create A PL/SQL Program That Handles The Order P

Homework 3create A Plsql Program That Handles The Order Placement Fo

Create a PL/SQL program that handles the order placement for the application described in Project 1. When customer Newton decides to buy, all the items in his shopping cart that are not in the wish list will be included in a purchase order. The following actions take place: 1. If there are items in the shopping cart for Newton, a new row is inserted in the ORDERS table. Increment the order ID of the new row accordingly. 2. For each item in the SHOPPING_CART_ITEMS that is in Newton’s buying list (not in the wish list) do the following: a. A corresponding item is created in the ORDER_ITEMS table. All these newly added order items refer to the order created in step 1. b. The quantity in INVENTORY_ITEMS table is updated accordingly 3. All the items in the SHOPPING_CART_ITEMS table that were just ordered are removed. Add a few output lines using DBMS_OUTPUT.PUT_LINE function to indicate how your program progresses through the list of actions. Submit the PL/ SQL program as a text file (.txt) following the document naming convention FirstnameLastnameHW3.txt. reference: project 1: Consider the multi-table third normal form (3NF) database specified in the file “Project 1 DDL.txt” attached. The database is used by an e-commerce website that sells a variety of products. Customers go to the web site, select products based on product categories and sets of product features, and fill a shopping cart with products that they want to buy. Later on, they place an order for one or more products that exist in the shopping cart. Once an order is placed, all the items that were bought are removed from the shopping cart and added to the ordered items table. The shopping cart has also the ability to hold products as part of a wish list, which the customer is interested in but hasn’t decided yet to buy. Use the file “Project 1 DDL.txt” to understand the structure of this database. Use it also to create and populate the database on your personal Oracle account. Then generate SQL code to answer the following questions: Show the shopping cart content for the customers with word ‘David’ in their full name, sorted with the wish list at the end List of ALL customers and the total price of their shopping carts, excluding the wish list List ALL shopping carts in descending order of the number of items that they hold, excluding the wish list List ALL products and the number of shopping carts they are in (if any) List the products with a 'Brand' feature in ‘Outdoors’ and ‘Electronics’ categories Create a view of unshipped goods, with individual quantities and prices, for each customer, and select all the rows from it. An order item is unshipped if the DateShipped attribute in set to NULL Using the view created before, display all customers who have unshipped orders, together with the total value of the unshipped orders per customer List the first three most sold products of category 'Books' (don't count unshipped orders) Display the number of ‘HP’ brand products sold during the last month (from current date) Use a correlated query to list the names of the customers who have more than 2 copies of the same item in their shopping cart List of shopping carts, together with the cheapest and the most expensive product in each. The second file attached (“Project 1 Results.txt”) contains the expected results for each of the queries. Try to make your queries match the content provided in this second file. Use your Oracle account to run these SQL statements and verify correctness.

Paper For Above instruction

The process of handling order placement in an e-commerce database system embodies several key operations that ensure data integrity and efficient transaction processing. The objective is to automate the creation of purchase orders based on customer shopping carts, update inventory levels accordingly, and maintain accurate records of customer orders and shopping behavior. Implementing such a system in PL/SQL involves careful consideration of database interactions, transaction control, and user feedback via output messages.

The initial step in the PL/SQL program involves identifying whether the customer (in this case Newton) has items in their shopping cart that are not part of their wish list. This is crucial because only these items should be processed for order placement. The program begins by querying the SHOPPING_CART_ITEMS table for Newton’s customer ID, filtering items that are not flagged as wish list items. If such items are present, the program proceeds to insert a new order record into the ORDERS table, ensuring correct incrementation of the order ID. Typically, this involves retrieving the current maximum order ID and incrementing it to generate a new, unique order identifier.

Once the order header is recorded, the program iterates over each relevant shopping cart item. For each item, a corresponding entry is created in the ORDER_ITEMS table, linking it to the newly created order record. During this process, the program updates the INVENTORY_ITEMS table by subtracting the ordered quantity from the existing stock—thus reflecting the sale. The use of PL/SQL’s FOR LOOP constructs facilitates iteration over multiple items, while DBMS_OUTPUT.PUT_LINE statements offer real-time feedback about the progress of the transaction, enhancing transparency and debugging.

After successfully processing each item, the program deletes these items from the shopping cart to prevent duplicate processing and to keep the cart current. This removal finalizes the order placement process, maintaining consistent state across the database tables. Throughout this workflow, exception handling may be integrated to manage errors such as insufficient inventory or database constraints, though such details are beyond the scope of this illustration.

The implementation of this PL/SQL procedure ensures atomicity: either all steps complete successfully, or none do, preserving data consistency. It can be encapsulated within a stored procedure or an anonymous block, depending on organizational practices. Moreover, the program can be expanded to include additional features, such as updating customer loyalty points, sending notifications, or logging transactions for audit purposes.

To conclude, automating order placement through PL/SQL enhances operational efficiency for e-commerce applications. Properly designed, it maintains accurate inventory and order records, improves response times for customers, and minimizes manual intervention. The outlined approach demonstrates a practical application of PL/SQL scripting that aligns with database normalization principles, transaction management, and real-world e-commerce requirements.

References

• Beauchemin, H. (2014). Oracle PL/SQL Programming. O'Reilly Media.