DROP TABLE CUSTOMER CASCADE CONSTRAINTS;
DROP TABLE ORDER_SOURCE CASCADE CONSTRAINTS;
DROP TABLE ORDERS CASCADE CONSTRAINTS;
DROP TABLE CATEGORY CASCADE CONSTRAINTS;
DROP TABLE ITEM CASCADE CONSTRAINTS;
DROP TABLE INVENTORY CASCADE CONSTRAINTS;
DROP TABLE ORDER_LINE CASCADE CONSTRAINTS;
CREATE TABLE CUSTOMER (
    C_ID        NUMBER(5),
    C_LAST      VARCHAR2(30),
    C_FIRST     VARCHAR2(30),
    C_MI        VARCHAR2(1),
    C_BIRTHDATE DATE,
    C_ADDRESS   VARCHAR2(30),
    C_CITY      VARCHAR2(30),
    C_STATE     VARCHAR2(2),
    C_ZIP       VARCHAR2(10),
    C_DPHONE    VARCHAR2(10),
    C_EPHONE    VARCHAR2(10),
    C_USERID    VARCHAR2(15),
    C_PASSWORD  VARCHAR2(15),
    CONSTRAINT CUSTOMER_C_ID_PK PRIMARY KEY REFERENCES (C_ID)
);
CREATE TABLE ORDER_SOURCE (
    OS_ID   NUMBER(5),
    OS_DESC VARCHAR2(30),
    CONSTRAINT ORDER_SOURCE_OS_ID_PK PRIMARY KEY REFERENCES (OS_ID)
);
CREATE TABLE ORDERS (
    OD_ID     NUMBER(5),
    O_DATE    DATE,
    O_METHPMT VARCHAR2(5),
    C_ID      NUMBER(5),
    OS_ID     NUMBER(5),
    CONSTRAINT ORDERS_OD_ID_PK PRIMARY KEY (OD_ID),
    CONSTRAINT ORDERS_C_ID_FK FOREIGN KEY REFERENCES CUSTOMER(C_ID),
    CONSTRAINT ORDERS_OS_ID_FK FOREIGN KEY REFERENCES ORDER_SOURCE(OS_ID)
);
CREATE TABLE CATEGORY (
    CAT_ID   NUMBER(5),
    CAT_DESC VARCHAR2(32),
    CONSTRAINT CATEGORY_CAT_ID_PK PRIMARY KEY REFERENCES (CAT_ID)
);
CREATE TABLE ITEM (
    ITEM_ID    NUMBER(5),
    ITEM_DESC  VARCHAR2(32),
    CAT_ID     NUMBER(5),
    ITEM_IMAGE VARCHAR(15),
    CONSTRAINT ITEM_ITEM_ID_PK PRIMARY KEY REFERENCES (ITEM_ID),
    CONSTRAINT ITEM_CAT_ID_FK FOREIGN KEY REFERENCES CATEGORY(CAT_ID)
);
CREATE TABLE INVENTORY (
    INV_ID NUMBER(5),
    ITEM_ID NUMBER(3),
    COLOR VARCHAR2(15),
    INV_SIZE VARCHAR2(2),
    INV_PRICE FLOAT(9),
    INV_QOH NUMBER(3),
    CONSTRAINT INVENTORY_INV_ID_PK PRIMARY KEY REFERENCES (INV_ID),
    CONSTRAINT INVCENTORY_ITEM_ID_FK PRIMARY KEY REFERENCES ITEM(ITEM_ID)
);
CREATE TABLE ORDER_LINE (
    O_ID        NUMBER(5),
    INV_ID      NUMBER(5),
    OL_QUANTITY NUMBER(3),
    CONSTRAINT ORDER_LINE_O_ID_PK PRIMARY KEY REFERENCES (O_ID),
    CONSTRAINT ORDER_LINE_INV_ID_FK FOREIGN KEY REFERENCES ITEM(INV_ID)

);