Term Project SQL

gistfile1.txt

/* Clean the slate */
DROP TABLE PAYROLL_ITEM CASCADE CONSTRAINTS;
DROP TABLE PAYROLL_PAYMENT CASCADE CONSTRAINTS;
DROP TABLE PAYROLL CASCADE CONSTRAINTS;
DROP TABLE EMPLOYEE_SCHEDULE CASCADE CONSTRAINTS;
DROP TABLE EMPLOYEE_TYPE CASCADE CONSTRAINTS;
DROP TABLE EMPLOYEE CASCADE CONSTRAINTS;
DROP TABLE VENDOR CASCADE CONSTRAINTS; 
DROP TABLE PURCHASE_ORDER CASCADE CONSTRAINTS; 
DROP TABLE PURCHASE_ORDER_ITEM CASCADE CONSTRAINTS; 
DROP TABLE HOURS_OF_OPERATION CASCADE CONSTRAINTS; 
DROP TABLE RECEIVING_ORDER CASCADE CONSTRAINTS; 
DROP TABLE CLOSED_HOLIDAY CASCADE CONSTRAINTS; 
DROP TABLE RECEIVED_ITEM CASCADE CONSTRAINTS; 
DROP TABLE STOCK CASCADE CONSTRAINTS; 
DROP TABLE ITEM CASCADE CONSTRAINTS; 
DROP TABLE FOOD_ITEM CASCADE CONSTRAINTS; 
DROP TABLE BOOK_ITEM CASCADE CONSTRAINTS; 
DROP TABLE MONO_ITEM CASCADE CONSTRAINTS; 
DROP TABLE ACCOMMODATION CASCADE CONSTRAINTS; 
DROP TABLE CUSTOMER_ORDER CASCADE CONSTRAINTS; 
DROP TABLE RESERVATION_TRANSACTION CASCADE CONSTRAINTS; 
DROP TABLE RESERVATION CASCADE CONSTRAINTS; 
DROP TABLE ORDER_TRANSACTION CASCADE CONSTRAINTS; 
DROP TABLE CUSTOMER CASCADE CONSTRAINTS; 
DROP TABLE BUSINESS CASCADE CONSTRAINTS;
DROP TABLE RESTAURANT_STORE CASCADE CONSTRAINTS;
DROP TABLE MONO_STORE CASCADE CONSTRAINTS;
DROP TABLE GIFT_STORE CASCADE CONSTRAINTS;
DROP TABLE BOOK_STORE CASCADE CONSTRAINTS;
DROP TABLE HOTEL CASCADE CONSTRAINTS;
DROP TABLE SHIFT CASCADE CONSTRAINTS;
DROP TABLE ADJUST_STOCK CASCADE CONSTRAINTS;
DROP TABLE ADJUSTED_STOCK_ITEM CASCADE CONSTRAINTS;
DROP TABLE SHIPMENT CASCADE CONSTRAINTS;
/* Create The Tables */
CREATE TABLE SHIPMENT(
    SHIPMENT_ID NUMBER PRIMARY KEY,
    FK_CUSTOMER_ORDER_ID NUMBER NOT NULL
);
CREATE TABLE SHIFT(
    SHIFT_ID NUMBER PRIMARY KEY,
    FK_EMPLOYEE_ID NUMBER NOT NULL
    CLOCK_IN DATE NOT NULL,
    CLOCK_OUT DATE
);
    ALTER TABLE SHIFT
    ADD CONSTRAINT fk_shift_to_employee
    FOREIGN KEY (FK_EMPLOYEE_ID) 
    REFERENCES EMPLOYEE(EMPLOYEE_ID);
    
CREATE TABLE PAYROLL_ITEM(
    PAYROLL_ITEM_ID NUMBER PRIMARY KEY,
    FK_PAYROLL_PAYMENT_ID NUMBER NOT NULL,
    PAYROLL_ITEM_DESCRIPTION VARCHAR2(60) NOT NULL,
    PAYROLL_ITEM_AMOUNT NUMBER (12,2) NOT NULL
);
    ALTER TABLE PAYROLL_ITEM
    ADD CONSTRAINT fk_payroll_item_to_payroll_payment
    FOREIGN KEY (FK_PAYROLL_PAYMENT_ID) 
    REFERENCES PAYROLL_PAYMENT(PAYROLL_PAYMENT_ID);


CREATE TABLE PAYROLL_PAYMENT(
    PAYROLL_PAYMENT_ID NUMBER PRIMARY KEY,
    FK_PAYROLL_ID NUMBER NOT NULL,
    FK_EMPLOYEE_ID NUMBER NOT NULL
);
    ALTER TABLE PAYROLL_PAYMENT
    ADD CONSTRAINT fk_payroll_payment_to_payroll
    FOREIGN KEY (FK_PAYROLL_ID) 
    REFERENCES PAYROLL(PAYROLL_ID);
    
    ALTER TABLE PAYROLL_PAYMENT
    ADD CONSTRAINT fk_payroll_payment_to_employee
    FOREIGN KEY (FK_EMPLOYEE_ID) 
    REFERENCES EMPLOYEE(EMPLOYEE_ID);
    
CREATE TABLE PAYROLL (
    PAYROLL_ID NUMBER PRIMARY KEY,
    FK_BUSINESS_ID NUMBER NOT NULL
);
    ALTER TABLE PAYROLL
    ADD CONSTRAINT fk_payroll_to_business
    FOREIGN KEY (FK_BUSINESS_ID) 
    REFERENCES BUSINESS(BUSINESS_ID);

CREATE TABLE EMPLOYEE_TYPE(
    EMPLOYEE_TYPE_ID NUMBER PRIMARY KEY,
    FK_EMPLOYEE_ID NUMBER NOT NULL
);

CREATE TABLE EMPLOYEE(
    EMPLOYEE_ID NUMBER PRIMARY KEY,
    FK_BUSINESS_ID NUMBER NOT NULL,
    FK_EMPLOYEE_TYPE_ID NUMBER NOT NULL
);
    ALTER TABLE EMPLOYEE
    ADD CONSTRAINT fk_employee_to_business
    FOREIGN KEY (FK_BUSINESS_ID) 
    REFERENCES BUSINESS(BUSINESS_ID);

    ALTER TABLE EMPLOYEE
    ADD CONSTRAINT fk_employee_to_employee_type
    FOREIGN KEY (FK_EMPLOYEE_TYPE_ID) 
    REFERENCES EMPLOYEE_TYPE(EMPLOYEE_TYPE_ID);

CREATE TABLE EMPLOYEE_SCHEDULE(
    EMPLOYEE_SCHEDULE_ID NUMBER PRIMARY KEY,
    FK_EMPLOYEE_ID NUMBER,
    FK_HOURS_OF_OPERATION_ID NUMBER
);
CREATE TABLE VENDOR(
    VENDOR_ID NUMBER PRIMARY KEY,
    VENDOR_NAME VARCHAR2(60)
);

CREATE TABLE PURCHASE_ORDER(
    PURCHASE_ORDER_ID NUMBER PRIMARY KEY,
    FK_VENDOR_ID NUMBER,
    FK_EMPLOYEE_ID NUMBER NOT NULL,
    FK_BUSINESS_ID NUMBER NOT NULL
);
    ALTER TABLE PURCHASE_ORDER
    ADD CONSTRAINT fk_purchase_order_to_employee
    FOREIGN KEY (FK_EMPLOYEE_ID) 
    REFERENCES EMPLOYEE(EMPLOYEE_ID);
    
    ALTER TABLE PURCHASE_ORDER
    ADD CONSTRAINT fk_purchase_order_to_business
    FOREIGN KEY (FK_BUSINESS_ID) 
    REFERENCES BUSINESS(BUSINESS_ID);
    
CREATE TABLE PURCHASE_ORDER_ITEM(
    PURCHASE_ORDER_ITEM_ID NUMBER PRIMARY KEY,
    FK_PURCHASE_ORDER_ID NUMBER NOT NULL,
    ADJUST_STOCK_QTY NUMBER NOT NULL,
    FK_ITEM_ID NUMBER NOT NULL
);
    ALTER TABLE PURCHASE_ORDER_ITEM
    ADD CONSTRAINT fk_purchase_order_item_to_purchase_order
    FOREIGN KEY (FK_PURCHASE_ORDER_ID) 
    REFERENCES PURCHASE_ORDER(PURCHASE_ORDER_ID);

    ALTER TABLE PURCHASE_ORDER_ITEM
    ADD CONSTRAINT fk_purchase_order_item_to_item
    FOREIGN KEY (FK_ITEM_ID) 
    REFERENCES ITEM(ITEM_ID);

CREATE TABLE HOURS_OF_OPERATION (
    HOURS_OF_OPERATION_ID NUMBER PRIMARY KEY,
    FK_BUSINESS_ID NUMBER NOT NULL,
    DAY_OF_WEEK NUMBER NOT NULL,
    START_TIME DATE NOT NULL,
    STOP_TIME DATE NOT NULL
);
CREATE TABLE RECEIVING_ORDER(
    RECEIVING_ORDER_ID NUMBER PRIMARY KEY,
    FK_PURCHASE_ORDER_ID NUMBER NOT NULL,
    FK_EMPLOYEE_ID NUMBER NOT NULL,
    FK_BUSINESS_ID NUMBER NOT NULL
);
    ALTER TABLE RECEIVING_ORDER
    ADD CONSTRAINT fk_receiving_order_to_purchase_order
    FOREIGN KEY (FK_PURCHASE_ORDER_ID) 
    REFERENCES PURCHASE_ORDER(PURCHASE_ORDER_ID);
    
    ALTER TABLE RECEIVING_ORDER
    ADD CONSTRAINT fk_receiving_order_to_employee
    FOREIGN KEY (FK_EMPLOYEE_ID) 
    REFERENCES EMPLOYEE(EMPLOYEE_ID);    
    
    ALTER TABLE RECEIVING_ORDER
    ADD CONSTRAINT fk_receiving_order_to_business
    FOREIGN KEY (FK_BUSINESS_ID) 
    REFERENCES BUSINESS(BUSINESS_ID);        

CREATE TABLE CLOSED_HOLIDAY (
    CLOSED_HOLIDAY_ID NUMBER PRIMARY KEY,
    FK_BUSINESS_ID NUMBER NOT NULL
);
CREATE TABLE RECEIVED_ITEM(
    RECEIVED_ITEM NUMBER PRIMARY KEY,
    FK_RECEIVING_ORDER_ID NUMBER NOT NULL,
    FK_ITEM_ID NUMBER NOT NULL,
    FK_STOCK_ID NUMBER NOT NULL
);

    ALTER TABLE RECEIVED_ITEM
    ADD CONSTRAINT fk_received_item_to_receiving_order
    FOREIGN KEY (FK_RECEIVING_ORDER_ID) 
    REFERENCES RECEIVING_ORDER(RECEIVING_ORDER_ID); 
    
    ALTER TABLE RECEIVED_ITEM
    ADD CONSTRAINT fk_received_item_to_item_id
    FOREIGN KEY (FK_ITEM_ID) 
    REFERENCES ITEM(ITEM_ID); 

    ALTER TABLE RECEIVED_ITEM
    ADD CONSTRAINT fk_received_item_to_stock
    FOREIGN KEY (FK_STOCK_ID) 
    REFERENCES STOCK(STOCK_ID); 
    
    
CREATE TABLE STOCK(
    STOCK_ID NUMBER PRIMARY KEY,
    FK_BUSINESS_ID NUMBER NOT NULL,
    FK_ITEM_ID NUMBER NOT NULL
);

    ALTER TABLE STOCK
    ADD CONSTRAINT fk_stock_to_business
    FOREIGN KEY (FK_BUSINESS_ID) 
    REFERENCES BUSINESS(BUSINESS_ID); 

    ALTER TABLE STOCK
    ADD CONSTRAINT fk_stock_to_item
    FOREIGN KEY (FK_ITEM_ID) 
    REFERENCES ITEM(ITEM_ID); 

CREATE TABLE ITEM(
    ITEM_ID NUMBER PRIMARY KEY,
    ITEM_NAME VARCHAR(60) NOT NULL,
    ITEM_PRICE NUMBER(13,2) NOT NULL,
    ITEM_TYPE VARCHAR(1) NOT NULL
);
CREATE TABLE FOOD_ITEM(
    ITEM_ID NUMBER PRIMARY KEY
);
CREATE TABLE BOOK_ITEM(
    ITEM_ID NUMBER PRIMARY KEY
);
CREATE TABLE MONO_ITEM(
    ITEM_ID NUMBER PRIMARY KEY
);
CREATE TABLE ACCOMMODATION (
    ACCOMMODATION_ID NUMBER PRIMARY KEY,
    BUSINESS_ID NUMBER NOT NULL,
    ACCOMMODATION_PRICE NUMBER(13,2) NOT NULL,
    ACCOMMODATION_DESCRIPTION VARCHAR(60) NOT NULL
);
CREATE TABLE CUSTOMER_ORDER(
    CUSTOMER_ORDER_ID NUMBER PRIMARY KEY,
    FK_SHIPMENT_ID NUMBER,
    FK_CUSTOMER_ID NUMBER
);
CREATE TABLE RESERVATION_TRANSACTION (
    RESERVATION_TRANSACTION_ID NUMBER PRIMARY KEY,
    FK_RESERVATION_ID NUMBER NOT NULL
);
CREATE TABLE RESERVATION (
    RESERVATION_ID NUMBER PRIMARY KEY,
    FK_CUSTOMER_ID NUMBER NOT NULL,
    FK_ACCOMMODATION_ID NUMBER NOT NULL
);
CREATE TABLE ORDER_TRANSACTION(
    ORDER_TRANSACTION_ID NUMBER PRIMARY KEY,
    FK_CUSTOMER_ORDER_ID NUMBER NOT NULL
);
CREATE TABLE CUSTOMER (
    CUSTOMER_ID NUMBER PRIMARY KEY
);
CREATE TABLE BUSINESS(
    BUSINESS_ID NUMBER PRIMARY KEY,
    BUSINESS_TYPE VARCHAR(1) NOT NULL
);
CREATE TABLE RESTAURANT_STORE(
    BUSINESS_ID NUMBER PRIMARY KEY
);
CREATE TABLE MONO_STORE(
    BUSINESS_ID NUMBER PRIMARY KEY
);
CREATE TABLE GIFT_STORE(
    BUSINESS_ID NUMBER PRIMARY KEY
);
CREATE TABLE BOOK_STORE(
    BUSINESS_ID NUMBER PRIMARY KEY
);
CREATE TABLE HOTEL(
    BUSINESS_ID NUMBER PRIMARY KEY
);

CREATE TABLE ADJUST_STOCK(
    ADJUST_STOCK_ID NUMBER PRIMARY KEY,
    FK_EMPLOYEE_ID NUMBER NOT NULL,
    FK_CUSTOMER_ORDER_ID NUMBER NOT NULL,
    FK_BUSINESS_ID NUMBER NOT NULL,
    ADJUSTED_STOCK_DESCRIPTION VARCHAR(60)
);
    ALTER TABLE ADJUST_STOCK
    ADD CONSTRAINT fk_adjust_stock_to_employee
    FOREIGN KEY (FK_EMPLOYEE_ID) 
    REFERENCES EMPLOYEE(EMPLOYEE_ID);
    
    ALTER TABLE ADJUST_STOCK
    ADD CONSTRAINT fk_adjust_stock_to_business
    FOREIGN KEY (FK_BUSINESS_ID) 
    REFERENCES BUSINESS(BUSINESS_ID);
    
/*
THIS IS A LEDGER 
MATH_OPERATION 1 is ADDITION
MATH_OPERATION 2 is SUBTRACTION

*/
CREATE TABLE ADJUSTED_STOCK_ITEM(
    ADJUSTED_STOCK_ITEM_ID NUMBER PRIMARY KEY,
    FK_ADJUST_STOCK_ID NUMBER NOT NULL,
    FK_ITEM_ID NUMBER NOT NULL,
    MATH_OPERATION NUMBER(1) NOT NULL
);

    ALTER TABLE ADJUSTED_STOCK_ITEM
    ADD CONSTRAINT fk_adjusted_stock_item_to_
    FOREIGN KEY (FK_BUSINESS_ID) 
    REFERENCES BUSINESS(BUSINESS_ID);