DROP TABLE OR_INVOICE;
DROP TABLE OR_LINE_ITEM;
DROP TABLE OR_PLACES;
DROP TABLE OR_REQ;
DROP TABLE OR_ORDER;
DROP TABLE OR_CUST;
DROP TABLE OR_SUPBY;
DROP TABLE OR_SUPP;
DROP TABLE OR_PROD;
DROP TABLE OR_SHIPPERS;

CREATE TABLE OR_CUST
(C_CUST_ID    VARCHAR2(5)  PRIMARY KEY,
 C_COMPANY    VARCHAR2(40) NOT NULL ,
 C_CONTACT    VARCHAR2(30),
 C_CONT_TITLE VARCHAR2(30),
 C_ADDR       VARCHAR2(60),
 C_CITY       VARCHAR2(15),
 C_STATE      VARCHAR2(2),
 C_ZIP        VARCHAR2(10),
 C_PHONE#     NUMBER(12),
 C_FAX#       NUMBER(12));

CREATE TABLE OR_SUPP
(SP_SUPP_ID    VARCHAR2(5)  PRIMARY KEY,
 SP_COMPANY    VARCHAR2(40) NOT NULL,
 SP_CONTACT    VARCHAR2(30),
 SP_CON_TITLE  VARCHAR2(30),
 SP_ADDR       VARCHAR2(60),
 SP_CITY       VARCHAR2(15),
 SP_STATE      VARCHAR2(2),
 SP_ZIP        VARCHAR2(10),
 SP_PHONE#     NUMBER(12),
 SP_FAX#       NUMBER(12));

CREATE TABLE OR_PROD 
(P_PROD_ID          VARCHAR2(5) PRIMARY KEY, 
 P_SUPPLIER_ID      VARCHAR2(5),
 P_CATEGORY_ID      VARCHAR2(5),
 P_NAME             VARCHAR2(40) NOT NULL,
 P_QTY_PER_UNIT     VARCHAR2(20),
 P_UNIT_PRICE       NUMBER(11) 
 CHECK(P_UNIT_PRICE > 0),
 P_UNITS_ONHAND     NUMBER(20)
 CHECK(P_UNITS_ONHAND > 0),
 P_UNITS_ON_ORDER   NUMBER(3)
 CHECK(P_UNITS_ON_ORDER BETWEEN 1 AND 999),
 P_REORDER_LEVEL    NUMBER(3)
 CHECK(P_REORDER_LEVEL BETWEEN 1 AND 999),
 P_DISCONTINUED     VARCHAR2(3)
 CHECK(P_DISCONTINUED IN('YES','NO','yes','no')));

CREATE TABLE OR_ORDER
(OR_ORDER_ID          VARCHAR2(5)  PRIMARY KEY, 
 OR_CUST_ID           VARCHAR2(5) NOT NULL,
 OR_SALES_CONTACT_ID  VARCHAR2(5),
 OR_ORDER_DATE        DATE       NOT NULL,
 OR_SHIP_BY_DATE      DATE,
 FOREIGN KEY(OR_CUST_ID)   
 REFERENCES OR_CUST(C_CUST_ID));

CREATE TABLE OR_LINE_ITEM
(LI_LINE_ITEM   VARCHAR2(5)   NOT NULL,
 LI_ORDER_ID    VARCHAR2(5)   NOT NULL,
 LI_INVOICE_ID  VARCHAR2(5), 
 LI_PRODUCT_ID  VARCHAR2(5)   NOT NULL,
 LI_UNIT_PRICE  NUMBER(11)  NOT NULL
 CHECK(LI_UNIT_PRICE > 0),
 LI_QTY         NUMBER(4)   NOT NULL
 CHECK (LI_QTY BETWEEN 1 AND 999),
 LI_DISCOUNT    NUMBER(3)
 CHECK (LI_DISCOUNT BETWEEN 1 AND 75),
 PRIMARY KEY(LI_LINE_ITEM,LI_ORDER_ID),
 FOREIGN KEY(LI_ORDER_ID)
 REFERENCES OR_ORDER(OR_ORDER_ID),
 FOREIGN KEY(LI_PRODUCT_ID)
 REFERENCES OR_PROD(P_PROD_ID));

CREATE TABLE OR_SHIPPERS
(SH_SHIP_ID  VARCHAR2(5)    PRIMARY KEY,
 SH_COMPANY  VARCHAR2(40)  NOT NULL);

CREATE TABLE OR_INVOICE
(IN_INVOICE_ID    VARCHAR2(5)   NOT NULL,
 IN_ORDER_ID      VARCHAR2(5)  NOT NULL,
 IN_SHIPNAME      VARCHAR2(40),
 IN_SHIPADDR      VARCHAR2(60),
 IN_SHIPCITY      VARCHAR2(15),
 IN_SHIPSTATE     VARCHAR2(2),
 IN_SHIPZIP       VARCHAR2(10),
 IN_SHIPPED_DATE  DATE,
 IN_FREIGHT_CHG   NUMBER(8)
 CHECK(IN_FREIGHT_CHG >= 0),
 IN_SHIP_ID       VARCHAR(5),
 PRIMARY KEY(IN_INVOICE_ID,IN_ORDER_ID),
 FOREIGN KEY(IN_ORDER_ID)
 REFERENCES OR_ORDER(OR_ORDER_ID),
 FOREIGN KEY(IN_SHIP_ID)
 REFERENCES OR_SHIPPERS(SH_SHIP_ID));

CREATE TABLE OR_PLACES
(PL_CUST_ID     VARCHAR2(5)  NOT NULL,
 PL_ORDER_ID    VARCHAR2(5)    NOT NULL,
 PRIMARY KEY(PL_CUST_ID,PL_ORDER_ID),
 FOREIGN KEY(PL_CUST_ID)
 REFERENCES OR_CUST(C_CUST_ID),
 FOREIGN KEY(PL_ORDER_ID)
 REFERENCES OR_ORDER(OR_ORDER_ID));

CREATE TABLE OR_SUPBY
(BY_SUPP_ID  VARCHAR2(5)  NOT NULL,
 BY_PROD_ID  VARCHAR2(5)  NOT NULL,
 PRIMARY KEY(BY_SUPP_ID,BY_PROD_ID),
 FOREIGN KEY(BY_SUPP_ID)
 REFERENCES OR_SUPP(SP_SUPP_ID),
 FOREIGN KEY(BY_PROD_ID)
 REFERENCES OR_PROD(P_PROD_ID));

CREATE TABLE OR_REQ
(R_REQUEST_ID   VARCHAR2(5)   NOT NULL,
 R_ORDER_ID     VARCHAR2(5)    NOT NULL,
 R_PROD_DISCR   VARCHAR2(256),
 PRIMARY KEY(R_REQUEST_ID,R_ORDER_ID),
 FOREIGN KEY(R_ORDER_ID)
 REFERENCES OR_ORDER(OR_ORDER_ID));