Homework: SQL
Start with the solution schema for Homework.
I. 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?
II. Pre-populate 'seller', 'item', 'item_category', and 'inventory' with data.
III. 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
IV. 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
Format your homework according to the give formatting requirements:
• The answer must be using Times New Roman font (size 12), double spaced, typed, with one-inch margins on all sides.
• The response also includes a cover page containing the student's name, the title of the homework, the course title, and the date. The cover page is not included in the required page length.
• Also include a reference page. The references and Citations should follow APA format. The reference page is not included in the required page length.