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";