Start with the solution schema for Assignment 1 (see solution files under Assignment 1).
- Create an ‘inventory’ table that stores the number of items available for each seller. How would you modify the ‘item’ and ‘seller’ tables to store this information?
- Pre-populate ‘seller’, ‘item’, ‘item_category’, and ‘inventory’ with data.
- Write a SQL script to create a new order. This script should do the following:
- Create the customer if they don’t exist
- Place an order for an item or items
- Store the shipping address
- Reduce the count of the number of items left in the inventory based on this order, e.g. if there are 10 Levi’s Jeans from seller Levi’s, and an order is placed for 2 of them, the inventory count goes down to 8
- The order should have ‘Open’ status
- Write a SQL script that cancels an open order. This script should do the following:
- Uses an input order_id representing an order that is ‘Open’
- Sets the order status to ‘Canceled’.
- Update the inventory of reset the number of items to its original amount prior to making the order
Make up your own data for this assignment, or use the existing data from Assignment 1 as a start.