Step 1: Add date of delivery and dates of sales to QDEL and QSALE tables and populate (update them) with dates in 2010 and 2011 respectively.
Step 2: Add QtyOnHand field for the level of inventory in QITEM table and set up initial values to 100.
Step 3: Update QITEM inventory as follows:
a) Using QDEL transaction records subtract if delivery was to consuming administrative or management departments (make a view of those first), and add to the inventory if delivery was to other (sales) departments.
b) Using QSALE records diminish the inventory.
c) Provide Inventory status report indicating QtyOnHand and for all values under a 100 initiate one delivery in 2012 of random values (say between 100 and 200) to sales departments only! Take care if necessary of items that had no sales or attempting to sell more than available quantity.
d) Test all changes
Step 4: Reconstruct QEMP table so that BOSS information from employees table will became MANAGER information in new QORG table derived from QDEPT. Start QORG by inserting SUNRISE (company) as organizational unit managed by the top BOSS (and use a query on QEMP to determine the top BOSS). As a result QEMP will also need to change, not to have BOSS information, and QDEPT is to be deleted once all information migrates to QORG.
Step 5: Hire five salespersons as employees working in department involved with sales in 2012 (insert five records).
Step 6: Create QCUSTOMER table with 20 prospects and generate one sale to each in 2013, keeping inventory information updated (automatically). Note need to expand QSALE to include FK to customer (an optional field as they still be some sales to unidentified customers and for backward compatibility).
Step 7: In January 2014 SUNRISE started accepting ORDERS for multiple ITEMS (one per ORDERLINE) and started fulfilling sales by mail. To keep old system intact a new MSALE table is to be created to handle possibly multiline order fulfillment. Show DDL code (for new tables ORDER, ORDERLINE, and MSALE) and illustrate with a few transactions.
Step 8: SUNRISE also started in January 2014 to accept payments by CC for all mail orders if inventory is available.
Step 9: SUNRISE is adding its own automatic orders SORDERS to suppliers to initiate delivery of all ITEMS when their quantity at hand reaches a certain minimal level (for10% of the average for the past year, note that a running average needs to be calculated during the current year and kept for the past year). So a trigger needs to be made that will use a function for calculating running avg. How would you prepare, in 2015, for this new functionality in order to be ready for 2016.
Step 10: SUNRISE is considering expanding the DB with INVOICES to old customers (if inventory on hand cannot completely fulfill an order), but will still accept new customers for orders that can be completely fulfilled from on hand inventory.
Step 11: To demonstrate final DB, create 3-4 intermediate level SQL queries, not possible in the old QSALE DB, illustrating interests of various stakeholders (managers, customers, suppliers, employees).
Use cover page and include lessons learned (reflection of the problem and your efforts, what is worth remembering…)
This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.drop table msale cascade constraints PURGE;
drop table orderline cascade constraints PURGE;
drop table orders cascade constraints PURGE;
drop table qcustomer cascade constraints PURGE;
drop table qorg cascade constraints PURGE;
-- Step 1: Add date of delivery and dates of sales to QDEL and QSALE tables and populate (update them) with dates in 2010 and 2011 respectively.
ALTER TABLE QDEL
update QDEL set DELDATE = DATE '2010-11-20';
update QDEL set DELDATE = DELDATE + ROWNUM;
ALTER TABLE QSALE
update QSALE set SALEDATE = DATE '2011-2-13';
update QSALE set SALEDATE = SALEDATE + ROWNUM;
-- Step 2: Add QtyOnHand field for the level of inventory in QITEM table and set up initial values to 100.
ALTER TABLE QITEM
update QITEM set QTYONHAND = 100;...