CREATE TABLE "S_REGION" 
   (    "ID" NUMBER(7,0) CONSTRAINT "S_REGION_ID_NN" NOT NULL ENABLE, 
        "NAME" VARCHAR2(50 BYTE) CONSTRAINT "S_REGION_NAME_NN" NOT NULL ENABLE, 
         CONSTRAINT "S_REGION_ID_PK" PRIMARY KEY ("ID") ENABLE,
         CONSTRAINT "S_REGION_NAME_UK" UNIQUE ("NAME") ENABLE
   ) ;

CREATE TABLE "S_DEPT" 
   (    "ID" NUMBER(7,0) CONSTRAINT "S_DEPT_ID_NN" NOT NULL ENABLE, 
        "NAME" VARCHAR2(25 BYTE) CONSTRAINT "S_DEPT_NAME_NN" NOT NULL ENABLE, 
        "REGION_ID" NUMBER(7,0), 
         CONSTRAINT "S_DEPT_ID_PK" PRIMARY KEY ("ID"),
         CONSTRAINT "S_DEPT_NAME_REGION_ID_UK" UNIQUE ("NAME", "REGION_ID") ENABLE,
         CONSTRAINT "S_DEPT_REGION_ID_FK" FOREIGN KEY ("REGION_ID")
          REFERENCES "S_REGION" ("ID") ENABLE
   ) ;

CREATE TABLE "S_TITLE" 
   (    "TITLE" VARCHAR2(25 BYTE) CONSTRAINT "S_TITLE_TITLE_NN" NOT NULL ENABLE, 
         CONSTRAINT "S_TITLE_TITLE_PK" PRIMARY KEY ("TITLE") ENABLE
   ) ;


CREATE TABLE "S_EMP" 
   (    "ID" NUMBER(7,0) CONSTRAINT "S_EMP_ID_NN" NOT NULL ENABLE, 
        "LAST_NAME" VARCHAR2(25 BYTE) CONSTRAINT "S_EMP_LAST_NAME_NN" NOT NULL ENABLE, 
        "FIRST_NAME" VARCHAR2(25 BYTE), 
        "USERID" VARCHAR2(8 BYTE), 
        "START_DATE" DATE, 
        "COMMENTS" VARCHAR2(255 BYTE), 
        "MANAGER_ID" NUMBER(7,0), 
        "TITLE" VARCHAR2(25 BYTE), 
        "DEPT_ID" NUMBER(7,0), 
        "SALARY" NUMBER(11,2), 
        "COMMISSION_PCT" NUMBER(4,2), 
         CONSTRAINT "S_EMP_COMMISSION_PCT_CK" CHECK (commission_pct IN (10, 12.5, 15, 17.5, 20)) ENABLE, 
         CONSTRAINT "S_EMP_ID_PK" PRIMARY KEY ("ID") ENABLE,
         CONSTRAINT "S_EMP_USERID_UK" UNIQUE ("USERID") ENABLE,
         CONSTRAINT "S_EMP_DEPT_ID_FK" FOREIGN KEY ("DEPT_ID")
          REFERENCES "S_DEPT" ("ID") ENABLE, 
         CONSTRAINT "S_EMP_MANAGER_ID_FK" FOREIGN KEY ("MANAGER_ID")
          REFERENCES "S_EMP" ("ID") ENABLE, 
         CONSTRAINT "S_EMP_TITLE_FK" FOREIGN KEY ("TITLE")
          REFERENCES "S_TITLE" ("TITLE") ENABLE
   ) ;
 


CREATE TABLE "S_CUSTOMER" 
   (    "ID" NUMBER(7,0) CONSTRAINT "S_CUSTOMER_ID_NN" NOT NULL ENABLE, 
        "NAME" VARCHAR2(50 BYTE) CONSTRAINT "S_CUSTOMER_NAME_NN" NOT NULL ENABLE, 
        "PHONE" VARCHAR2(25 BYTE), 
        "ADDRESS" VARCHAR2(400 BYTE), 
        "CITY" VARCHAR2(30 BYTE), 
        "STATE" VARCHAR2(20 BYTE), 
        "COUNTRY" VARCHAR2(30 BYTE), 
        "ZIP_CODE" VARCHAR2(75 BYTE), 
        "CREDIT_RATING" VARCHAR2(9 BYTE), 
        "SALES_REP_ID" NUMBER(7,0), 
        "REGION_ID" NUMBER(7,0), 
        "COMMENTS" VARCHAR2(255 BYTE), 
         CONSTRAINT "S_CUSTOMER_CREDIT_RATING_CK" CHECK (credit_rating IN ('EXCELLENT', 'GOOD', 'POOR')) ENABLE, 
         CONSTRAINT "S_CUSTOMER_ID_PK" PRIMARY KEY ("ID") ENABLE,
         CONSTRAINT "S_CUSTOMER_REGION_ID_FK" FOREIGN KEY ("REGION_ID")
          REFERENCES "S_REGION" ("ID") ENABLE, 
         CONSTRAINT "S_SALES_REP_ID_FK" FOREIGN KEY ("SALES_REP_ID")
          REFERENCES "S_EMP" ("ID") ENABLE
   ) ;
 

 

  

CREATE TABLE "S_IMAGE" 
   (    "ID" NUMBER(7,0) CONSTRAINT "S_IMAGE_ID_NN" NOT NULL ENABLE, 
        "FORMAT" VARCHAR2(25 BYTE), 
        "USE_FILENAME" VARCHAR2(1 BYTE), 
        "FILENAME" VARCHAR2(255 BYTE), 
        "IMAGE" LONG RAW, 
         CONSTRAINT "S_IMAGE_FORMAT_CK" CHECK (format in ('JFIFF', 'JTIFF')) ENABLE, 
         CONSTRAINT "S_IMAGE_USE_FILENAME_CK" CHECK (use_filename in ('Y', 'N')) ENABLE, 
         CONSTRAINT "S_IMAGE_ID_PK" PRIMARY KEY ("ID") ENABLE
   ) ;
   
CREATE TABLE "S_LONGTEXT" 
   (    "ID" NUMBER(7,0) CONSTRAINT "S_LONGTEXT_ID_NN" NOT NULL ENABLE, 
        "USE_FILENAME" VARCHAR2(1 BYTE), 
        "FILENAME" VARCHAR2(255 BYTE), 
        "TEXT" VARCHAR2(2000 BYTE), 
         CONSTRAINT "S_LONGTEXT_USE_FILENAME_CK" CHECK (use_filename in ('Y', 'N')) ENABLE, 
         CONSTRAINT "S_LONGTEXT_ID_PK" PRIMARY KEY ("ID") ENABLE
   ) ;
   
 
 CREATE TABLE "S_PRODUCT" 
   (    "ID" NUMBER(7,0) CONSTRAINT "S_PRODUCT_ID_NN" NOT NULL ENABLE, 
        "NAME" VARCHAR2(50 BYTE) CONSTRAINT "S_PRODUCT_NAME_NN" NOT NULL ENABLE, 
        "SHORT_DESC" VARCHAR2(255 BYTE), 
        "LONGTEXT_ID" NUMBER(7,0), 
        "IMAGE_ID" NUMBER(7,0), 
        "SUGGESTED_WHLSL_PRICE" NUMBER(11,2), 
        "WHLSL_UNITS" VARCHAR2(25 BYTE), 
         CONSTRAINT "S_PRODUCT_ID_PK" PRIMARY KEY ("ID") ENABLE,
         CONSTRAINT "S_PRODUCT_NAME_UK" UNIQUE ("NAME") ENABLE,
         CONSTRAINT "S_PRODUCT_IMAGE_ID_FK" FOREIGN KEY ("IMAGE_ID")
          REFERENCES "S_IMAGE" ("ID") DISABLE, 
         CONSTRAINT "S_PRODUCT_LONGTEXT_ID_FK" FOREIGN KEY ("LONGTEXT_ID")
          REFERENCES "S_LONGTEXT" ("ID") ENABLE
   ) ;
 

CREATE TABLE "S_ORD" 
   (    "ID" NUMBER(7,0) CONSTRAINT "S_ORD_ID_NN" NOT NULL ENABLE, 
        "CUSTOMER_ID" NUMBER(7,0) CONSTRAINT "S_ORD_CUSTOMER_ID_NN" NOT NULL ENABLE, 
        "DATE_ORDERED" DATE, 
        "DATE_SHIPPED" DATE, 
        "SALES_REP_ID" NUMBER(7,0), 
        "TOTAL" NUMBER(11,2), 
        "PAYMENT_TYPE" VARCHAR2(6 BYTE), 
        "ORDER_FILLED" VARCHAR2(1 BYTE), 
         CONSTRAINT "S_ORD_PAYMENT_TYPE_CK" CHECK (payment_type in ('CASH', 'CREDIT', 'CHECK')) ENABLE, 
         CONSTRAINT "S_ORD_ORDER_FILLED_CK" CHECK (order_filled in ('Y', 'N')) ENABLE, 
         CONSTRAINT "S_ORD_ID_PK" PRIMARY KEY ("ID") ENABLE,
         CONSTRAINT "S_ORD_CUSTOMER_ID_FK" FOREIGN KEY ("CUSTOMER_ID")
          REFERENCES "S_CUSTOMER" ("ID") ENABLE, 
         CONSTRAINT "S_ORD_SALES_REP_ID_FK" FOREIGN KEY ("SALES_REP_ID")
          REFERENCES "S_EMP" ("ID") ENABLE
   ) ;
 


CREATE TABLE "S_ITEM" 
   (    "ORD_ID" NUMBER(7,0) CONSTRAINT "S_ITEM_ORD_ID_NN" NOT NULL ENABLE, 
        "ITEM_ID" NUMBER(7,0) CONSTRAINT "S_ITEM_ITEM_ID_NN" NOT NULL ENABLE, 
        "PRODUCT_ID" NUMBER(7,0) CONSTRAINT "S_ITEM_PRODUCT_ID_NN" NOT NULL ENABLE, 
        "PRICE" NUMBER(11,2), 
        "QUANTITY" NUMBER(9,0), 
        "QUANTITY_SHIPPED" NUMBER(9,0), 
         CONSTRAINT "S_ITEM_ORDID_ITEMID_PK" PRIMARY KEY ("ORD_ID", "ITEM_ID") ENABLE,
         CONSTRAINT "S_ITEM_ORDID_PRODID_UK" UNIQUE ("ORD_ID", "PRODUCT_ID") ENABLE,
         CONSTRAINT "S_ITEM_ORD_ID_FK" FOREIGN KEY ("ORD_ID")
          REFERENCES "S_ORD" ("ID") ENABLE, 
         CONSTRAINT "S_ITEM_PRODUCT_ID_FK" FOREIGN KEY ("PRODUCT_ID")
          REFERENCES "S_PRODUCT" ("ID") ENABLE
   ) ;
 

CREATE TABLE "S_ITEM_AUDIT" 
 (      "A" NUMBER
 );


CREATE OR REPLACE TRIGGER "AUDIT_S_ITEM" AFTER INSERT ON s_item 
FOR EACH ROW 
BEGIN 
  INSERT INTO s_item_audit VALUES (1); 
END; 
/
ALTER TRIGGER "AUDIT_S_ITEM" ENABLE;
 

 
 

 



 

 

CREATE TABLE "S_WAREHOUSE" 
   (    "ID" NUMBER(7,0) CONSTRAINT "S_WAREHOUSE_ID_NN" NOT NULL ENABLE, 
        "REGION_ID" NUMBER(7,0) CONSTRAINT "S_WAREHOUSE_REGION_ID_NN" NOT NULL ENABLE, 
        "ADDRESS" LONG, 
        "CITY" VARCHAR2(30 BYTE), 
        "STATE" VARCHAR2(20 BYTE), 
        "COUNTRY" VARCHAR2(30 BYTE), 
        "ZIP_CODE" VARCHAR2(75 BYTE), 
        "PHONE" VARCHAR2(25 BYTE), 
        "MANAGER_ID" NUMBER(7,0), 
         CONSTRAINT "S_WAREHOUSE_ID_PK" PRIMARY KEY ("ID") ENABLE,
         CONSTRAINT "S_WAREHOUSE_MANAGER_ID_FK" FOREIGN KEY ("MANAGER_ID")
          REFERENCES "S_EMP" ("ID") ENABLE, 
         CONSTRAINT "S_WAREHOUSE_REGION_ID_FK" FOREIGN KEY ("REGION_ID")
          REFERENCES "S_REGION" ("ID") ENABLE
   ) ;
   
CREATE TABLE "S_INVENTORY" 
   (    "PRODUCT_ID" NUMBER(7,0) CONSTRAINT "S_INVENTORY_PRODUCT_ID_NN" NOT NULL ENABLE, 
        "WAREHOUSE_ID" NUMBER(7,0) CONSTRAINT "S_INVENTORY_WAREHOUSE_ID_NN" NOT NULL ENABLE, 
        "AMOUNT_IN_STOCK" NUMBER(9,0), 
        "REORDER_POINT" NUMBER(9,0), 
        "MAX_IN_STOCK" NUMBER(9,0), 
        "OUT_OF_STOCK_EXPLANATION" VARCHAR2(255 BYTE), 
        "RESTOCK_DATE" DATE, 
         CONSTRAINT "S_INVENTORY_PRODID_WARID_PK" PRIMARY KEY ("PRODUCT_ID", "WAREHOUSE_ID") ENABLE,
         CONSTRAINT "S_INVENTORY_PRODUCT_ID_FK" FOREIGN KEY ("PRODUCT_ID")
          REFERENCES "S_PRODUCT" ("ID") ENABLE, 
         CONSTRAINT "S_INVENTORY_WAREHOUSE_ID_FK" FOREIGN KEY ("WAREHOUSE_ID")
          REFERENCES "S_WAREHOUSE" ("ID") ENABLE
   ) ;
 
   
 

 CREATE SEQUENCE  "S_CUSTOMER_SEQ";
 

 CREATE SEQUENCE  "S_DEPT_SEQ";
 

 CREATE SEQUENCE  "S_EMP_SEQ";
 

 CREATE SEQUENCE  "S_LONGTEXT_SEQ";
 

 CREATE SEQUENCE  "S_ORD_SEQ";
 

 CREATE SEQUENCE  "S_PRODUCT_SEQ";
 

 CREATE SEQUENCE  "S_WAREHOUSE_SEQ";