/* The organization would like to expand its database so that it can also keep track of the orders it sent to vendors. With this expansion, it will be able to implement the segregation of duties principal to reconcile requests, orders and invoices. In other words, it would be able to better keep track of how requests turn into orders and how orders turn into invoices.

The organization has gone through the systems analysis and design stages and updated its database schema, which is the SP19 class diagram of M:\Courses\MIS480-01\Sandbox\Oracle\ap_vendors_sp19.simp

Now it is time to implement the expansion.

The first task is to create the ap_orders table based on the ap_orders class. The underlying business rules are: 1. An order must know when it was placed (order date), who placed it (the id of a manager) and who it was sent to (the id of the vendor). 2. An order record cannot be created without knowing the when, the 2 whos. 3. An order can be placed only by a manager, while a manager can place many orders. 4. An order can be sent to only a vendor, while a vendor can receive many orders. 5. If a manager record is to be deleted, corresponding order records cannot be deleted. 6. If a vendor record is to be deleted, corresponding order record cannot be deleted.

The code has been written, please execute the following SQL statement. */ CREATE TABLE ap_orders ( order_id NUMBER, order_date DATE NOT NULL, manager_id NUMBER(6) NOT NULL, vendor_id NUMBER(6) NOT NULL, CONSTRAINT ap_orders_pk PRIMARY KEY (order_id), CONSTRAINT ap_orders_managers_fk FOREIGN KEY (manager_id) REFERENCES ap_managers(employee_id), CONSTRAINT ap_orders_vendors_fk FOREIGN KEY (vendor_id) REFERENCES ap_vendors(vendor_id) );

/* The second task is to create the ap_order_items table based on the ap_order_items class.

The underlying business rules are: 1. An order include many order items, while each order item can only be about an order. 2. An item can be ordered many times as order items, while each order item can only be about an item. 3. Each order item also needs to be know order quantity (maximum 3 digits with no decimal places), item price (maximum 9 digits, including 2 decimal places), and a note (maximum 100 characters). 4. When an order item record is created, it must know order quantity, item price, but not necessarily the note. 5. The systems analysis has determined that it is the most optimal to use a composite PK in this case. 6. If an order record is to be deleted, corresponding order items records SHOULD be deleted. 7. If an item record is to be deleted, corresponding order items records cannot be deleted. 8. The item price needs to be checked to ensure that it is not greater than 99999 but greater than 0.

Q1: Based on the ap_order_items class and the business rules above, write a SQL statement to create the ap_order_items table.