SQL Script

CREATE TABLE  "DEPENDANT"
   (	"D_ID" CHAR(8) NOT NULL ENABLE,
	"DF_NAME" VARCHAR2(20) NOT NULL ENABLE,
	"DL_NAME" VARCHAR2(20) NOT NULL ENABLE,
	"D_DOB" DATE NOT NULL ENABLE,
	 CONSTRAINT "DEPENDANT_PK" PRIMARY KEY ("D_ID") ENABLE
   ) ;


CREATE OR REPLACE TRIGGER  "BI_DEPENDANT"
  before insert on "DEPENDANT"
  for each row
begin
  if :NEW."D_ID" is null then
    select "DEPESEQ".nextval into :NEW."D_ID" from dual;
  end if;
end;

/
ALTER TRIGGER  "BI_DEPENDANT" ENABLE;

CREATE TABLE  "EMPLOYEE"
   (	"E_ID" CHAR(8) NOT NULL ENABLE,
	"EF_NAME" VARCHAR2(20) NOT NULL ENABLE,
	"EL_NAME" VARCHAR2(20) NOT NULL ENABLE,
	"EST_ID" CHAR(4) NOT NULL ENABLE,
	 CONSTRAINT "EMPLOYEE_PK" PRIMARY KEY ("E_ID") ENABLE
   ) ;ALTER TABLE  "EMPLOYEE" ADD CONSTRAINT "EMPLOYEE_CON" FOREIGN KEY ("EST_ID")
	  REFERENCES  "STORE" ("ST_ID") ENABLE;


CREATE INDEX  "EMPLOYEE_IDX1" ON  "EMPLOYEE" ("EL_NAME")
  ;


CREATE OR REPLACE TRIGGER  "BI_EMPLOYEE"
  before insert on "EMPLOYEE"
  for each row
begin
  if :NEW."E_ID" is null then
    select "EMPLSEQ".nextval into :NEW."E_ID" from dual;
  end if;
end;

/
ALTER TRIGGER  "BI_EMPLOYEE" ENABLE;


CREATE TABLE  "MANAGER"
   (	"ME_ID" CHAR(8) NOT NULL ENABLE,
	"MF_NAME" VARCHAR2(20) NOT NULL ENABLE,
	"ML_NAME" VARCHAR2(20) NOT NULL ENABLE,
	"MST_ID" CHAR(8) NOT NULL ENABLE,
	 CONSTRAINT "MANAGER_PK" PRIMARY KEY ("ME_ID") ENABLE
   ) ;ALTER TABLE  "MANAGER" ADD CONSTRAINT "MANAGER_CON" FOREIGN KEY ("MST_ID")
	  REFERENCES  "STORE" ("ST_ID") ENABLE;


CREATE OR REPLACE TRIGGER  "BI_MANAGER"
  before insert on "MANAGER"
  for each row
begin
  if :NEW."ME_ID" is null then
    select "MANASEQ".nextval into :NEW."ME_ID" from dual;
  end if;
end;

/
ALTER TRIGGER  "BI_MANAGER" ENABLE;


CREATE TABLE  "ADDRESS"
   (	"ADDR_ID" CHAR(4) NOT NULL ENABLE,
	"STREET_NO" NUMBER(4,0) NOT NULL ENABLE,
	"STREET_BAR" VARCHAR2(3),
	"STREET_NME" VARCHAR2(20) NOT NULL ENABLE,
	"SUBURB" VARCHAR2(20) NOT NULL ENABLE,
	"CITY" VARCHAR2(20) NOT NULL ENABLE,
	"POSTAL_CODE" CHAR(4) NOT NULL ENABLE,
	 CONSTRAINT "ADDRESS_PK" PRIMARY KEY ("ADDR_ID") ENABLE
   ) ;


CREATE OR REPLACE TRIGGER  "BI_ADDRESS"
  before insert on "ADDRESS"
  for each row
begin
  if :NEW."ADDR_ID" is null then
    select "ADDRSEQ".nextval into :NEW."ADDR_ID" from dual;
  end if;
end;

/
ALTER TRIGGER  "BI_ADDRESS" ENABLE;


CREATE TABLE  "ORDER_FORM"
   (	"ORD_NO" VARCHAR2(8) NOT NULL ENABLE,
	"ORDE_ID" CHAR(6) NOT NULL ENABLE,
	"ORD_DATE" DATE NOT NULL ENABLE,
	 CONSTRAINT "ORDER_FORM_PK" PRIMARY KEY ("ORD_NO") ENABLE
   ) ;ALTER TABLE  "ORDER_FORM" ADD CONSTRAINT "ORDER_FORM_CON" FOREIGN KEY ("ORDE_ID")
	  REFERENCES  "EMPLOYEE" ("E_ID") ENABLE;


CREATE OR REPLACE TRIGGER  "BI_ORDER_FORM"
  before insert on "ORDER_FORM"
  for each row
begin
  if :NEW."ORD_NO" is null then
    select "ORDFSEQ".nextval into :NEW."ORD_NO" from dual;
  end if;
end;

/
ALTER TRIGGER  "BI_ORDER_FORM" ENABLE;


CREATE TABLE  "ORDER_ITEM"
   (	"ITORD_ID" CHAR(8) NOT NULL ENABLE,
	"ITPRODUCT_ID" CHAR(8) NOT NULL ENABLE,
	"ITO_QUAN" NUMBER(3,0) NOT NULL ENABLE,
	"ITORD_NO" VARCHAR2(8) NOT NULL ENABLE,
	 CONSTRAINT "ORDER_ITEM_PK" PRIMARY KEY ("ITORD_ID") ENABLE
   ) ;ALTER TABLE  "ORDER_ITEM" ADD CONSTRAINT "ORDER_ITEM_CON" FOREIGN KEY ("ITPRODUCT_ID")
	  REFERENCES  "PRODUCT" ("PRODUCT_ID") ENABLE;ALTER TABLE  "ORDER_ITEM" ADD CONSTRAINT "ORDER_ITEM_CON1" FOREIGN KEY ("ITORD_NO")
	  REFERENCES  "ORDER_FORM" ("ORD_NO") ENABLE;


CREATE OR REPLACE TRIGGER  "BI_ORDER_ITEM"
  before insert on "ORDER_ITEM"
  for each row
begin
  if :NEW."ITORD_ID" is null then
    select "ORDISEQ".nextval into :NEW."ITORD_ID" from dual;
  end if;
end;

/
ALTER TRIGGER  "BI_ORDER_ITEM" ENABLE;


CREATE TABLE  "PRODUCT"
   (	"PRODUCT_ID" CHAR(8) NOT NULL ENABLE,
	"PR_DESC" VARCHAR2(80),
	"PR_NAME" VARCHAR2(40) NOT NULL ENABLE,
	"PRWH_ID" CHAR(3) NOT NULL ENABLE,
	 CONSTRAINT "PRODUCT_PK" PRIMARY KEY ("PRODUCT_ID") ENABLE
   ) ;ALTER TABLE  "PRODUCT" ADD CONSTRAINT "PRODUCT_CON" FOREIGN KEY ("PRWH_ID")
	  REFERENCES  "WAREHOUSE" ("WH_ID") ENABLE;


CREATE OR REPLACE TRIGGER  "BI_PRODUCT"
  before insert on "PRODUCT"
  for each row
begin
  if :NEW."PRODUCT_ID" is null then
    select "PDSEQ".nextval into :NEW."PRODUCT_ID" from dual;
  end if;
end;

/
ALTER TRIGGER  "BI_PRODUCT" ENABLE;


CREATE TABLE  "STORE"
   (	"ST_ID" CHAR(4) NOT NULL ENABLE,
	"STADDR_ID" CHAR(4) NOT NULL ENABLE,
	 CONSTRAINT "STORE_PK" PRIMARY KEY ("ST_ID") ENABLE
   ) ;ALTER TABLE  "STORE" ADD CONSTRAINT "STORE_CON" FOREIGN KEY ("STADDR_ID")
	  REFERENCES  "ADDRESS" ("ADDR_ID") ENABLE;


CREATE OR REPLACE TRIGGER  "BI_STORE"
  before insert on "STORE"
  for each row
begin
  if :NEW."ST_ID" is null then
    select "STORSEQ".nextval into :NEW."ST_ID" from dual;
  end if;
end;

/
ALTER TRIGGER  "BI_STORE" ENABLE;


CREATE TABLE  "STP_STOCK"
   (	"STP_ID" CHAR(8) NOT NULL ENABLE,
	"PRODUCT_ID" CHAR(8) NOT NULL ENABLE,
	"STP_QUANTITY" NUMBER(4,0) NOT NULL ENABLE,
	 CONSTRAINT "STP_STOCK_PK" PRIMARY KEY ("STP_ID") ENABLE
   ) ;ALTER TABLE  "STP_STOCK" ADD CONSTRAINT "STP_STOCK_CON" FOREIGN KEY ("PRODUCT_ID")
	  REFERENCES  "PRODUCT" ("PRODUCT_ID") ENABLE;


CREATE OR REPLACE TRIGGER  "BI_STP_STOCK"
  before insert on "STP_STOCK"
  for each row
begin
  if :NEW."STP_ID" is null then
    select "STOCSEQ".nextval into :NEW."STP_ID" from dual;
  end if;
end;

/
ALTER TRIGGER  "BI_STP_STOCK" ENABLE;


CREATE TABLE  "WAREHOUSE"
   (	"WH_ID" CHAR(3) NOT NULL ENABLE,
	"WHADDRESS_ID" CHAR(4) NOT NULL ENABLE,
	 CONSTRAINT "WAREHOUSE_PK" PRIMARY KEY ("WH_ID") ENABLE
   ) ;ALTER TABLE  "WAREHOUSE" ADD CONSTRAINT "WAREHOUSE_CON" FOREIGN KEY ("WHADDRESS_ID")
	  REFERENCES  "ADDRESS" ("ADDR_ID") ENABLE;


CREATE OR REPLACE TRIGGER  "BI_WAREHOUSE"
  before insert on "WAREHOUSE"
  for each row
begin
  if :NEW."WH_ID" is null then
    select "WHSEQ".nextval into :NEW."WH_ID" from dual;
  end if;
end;

/
ALTER TRIGGER  "BI_WAREHOUSE" ENABLE;


CREATE TABLE  "WHPR_STK"
   (	"WHSTOCK_ID" CHAR(8) NOT NULL ENABLE,
	"QUANTITY" NUMBER(8,0) NOT NULL ENABLE,
	 CONSTRAINT "WHPR_STK_PK" PRIMARY KEY ("WHSTOCK_ID") ENABLE
   ) ;


CREATE OR REPLACE TRIGGER  "BI_WHPR_STK"
  before insert on "WHPR_STK"
  for each row
begin
  if :NEW."WHSTOCK_ID" is null then
    select "WHPRSEQ".nextval into :NEW."WHSTOCK_ID" from dual;
  end if;
end;

/
ALTER TRIGGER  "BI_WHPR_STK" ENABLE;


CREATE UNIQUE INDEX  "ADDRESS_PK" ON  "ADDRESS" ("ADDR_ID")
  ;

CREATE UNIQUE INDEX  "DEPENDANT_PK" ON  "DEPENDANT" ("D_ID")
  ;

CREATE INDEX  "EMPLOYEE_IDX1" ON  "EMPLOYEE" ("EL_NAME")
  ;

CREATE UNIQUE INDEX  "EMPLOYEE_PK" ON  "EMPLOYEE" ("E_ID")
  ;

CREATE UNIQUE INDEX  "MANAGER_PK" ON  "MANAGER" ("ME_ID")
  ;

CREATE UNIQUE INDEX  "ORDER_FORM_PK" ON  "ORDER_FORM" ("ORD_NO")
  ;

CREATE UNIQUE INDEX  "ORDER_ITEM_PK" ON  "ORDER_ITEM" ("ITORD_ID")
  ;

CREATE UNIQUE INDEX  "PRODUCT_PK" ON  "PRODUCT" ("PRODUCT_ID")
  ;

CREATE UNIQUE INDEX  "STORE_PK" ON  "STORE" ("ST_ID")
  ;

CREATE UNIQUE INDEX  "STP_STOCK_PK" ON  "STP_STOCK" ("STP_ID")
  ;

CREATE UNIQUE INDEX  "WAREHOUSE_PK" ON  "WAREHOUSE" ("WH_ID")
  ;

CREATE UNIQUE INDEX  "WHPR_STK_PK" ON  "WHPR_STK" ("WHSTOCK_ID")
  ;

CREATE SEQUENCE   "ADDRSEQ"  MINVALUE 1000 MAXVALUE 9999 INCREMENT BY 1 START WITH 1000 NOCACHE  NOORDER  NOCYCLE ;

CREATE SEQUENCE   "DEPESEQ"  MINVALUE 10000000 MAXVALUE 99999999 INCREMENT BY 1 START WITH 10000000 NOCACHE  NOORDER  NOCYCLE ;

CREATE SEQUENCE   "EMPLSEQ"  MINVALUE 10000000 MAXVALUE 99999999 INCREMENT BY 1 START WITH 10000000 NOCACHE  NOORDER  NOCYCLE ;

CREATE SEQUENCE   "MANASEQ"  MINVALUE 10000000 MAXVALUE 99999999 INCREMENT BY 1 START WITH 10000000 NOCACHE  NOORDER  NOCYCLE ;

CREATE SEQUENCE   "ORDFSEQ"  MINVALUE 1000 MAXVALUE 99999999 INCREMENT BY 1 START WITH 1000 NOCACHE  NOORDER  NOCYCLE ;

CREATE SEQUENCE   "ORDISEQ"  MINVALUE 10000000 MAXVALUE 99999999 INCREMENT BY 1 START WITH 10000000 NOCACHE  NOORDER  NOCYCLE ;

CREATE SEQUENCE   "PDSEQ"  MINVALUE 10000000 MAXVALUE 99999999 INCREMENT BY 1 START WITH 10000000 NOCACHE  NOORDER  NOCYCLE ;

CREATE SEQUENCE   "STOCSEQ"  MINVALUE 10000000 MAXVALUE 99999999 INCREMENT BY 1 START WITH 10000000 NOCACHE  NOORDER  NOCYCLE ;

CREATE SEQUENCE   "STORSEQ"  MINVALUE 1000 MAXVALUE 9999 INCREMENT BY 1 START WITH 1000 NOCACHE  NOORDER  NOCYCLE ;

CREATE SEQUENCE   "WHPRSEQ"  MINVALUE 10000000 MAXVALUE 99999999 INCREMENT BY 1 START WITH 10000000 NOCACHE  NOORDER  NOCYCLE ;

CREATE SEQUENCE   "WHSEQ"  MINVALUE 100 MAXVALUE 999 INCREMENT BY 1 START WITH 100 NOCACHE  NOORDER  NOCYCLE ;

CREATE OR REPLACE TRIGGER  "BI_ADDRESS"
  before insert on "ADDRESS"
  for each row
begin
  if :NEW."ADDR_ID" is null then
    select "ADDRSEQ".nextval into :NEW."ADDR_ID" from dual;
  end if;
end;

/
ALTER TRIGGER  "BI_ADDRESS" ENABLE;

CREATE OR REPLACE TRIGGER  "BI_DEPENDANT"
  before insert on "DEPENDANT"
  for each row
begin
  if :NEW."D_ID" is null then
    select "DEPESEQ".nextval into :NEW."D_ID" from dual;
  end if;
end;

/
ALTER TRIGGER  "BI_DEPENDANT" ENABLE;


CREATE OR REPLACE TRIGGER  "BI_EMPLOYEE"
  before insert on "EMPLOYEE"
  for each row
begin
  if :NEW."E_ID" is null then
    select "EMPLSEQ".nextval into :NEW."E_ID" from dual;
  end if;
end;

/
ALTER TRIGGER  "BI_EMPLOYEE" ENABLE;

CREATE OR REPLACE TRIGGER  "BI_MANAGER"
  before insert on "MANAGER"
  for each row
begin
  if :NEW."ME_ID" is null then
    select "MANASEQ".nextval into :NEW."ME_ID" from dual;
  end if;
end;

/
ALTER TRIGGER  "BI_MANAGER" ENABLE;

CREATE OR REPLACE TRIGGER  "BI_ORDER_FORM"
  before insert on "ORDER_FORM"
  for each row
begin
  if :NEW."ORD_NO" is null then
    select "ORDFSEQ".nextval into :NEW."ORD_NO" from dual;
  end if;
end;

/
ALTER TRIGGER  "BI_ORDER_FORM" ENABLE;

CREATE OR REPLACE TRIGGER  "BI_ORDER_ITEM"
  before insert on "ORDER_ITEM"
  for each row
begin
  if :NEW."ITORD_ID" is null then
    select "ORDISEQ".nextval into :NEW."ITORD_ID" from dual;
  end if;
end;

/
ALTER TRIGGER  "BI_ORDER_ITEM" ENABLE;

CREATE OR REPLACE TRIGGER  "BI_PRODUCT"
  before insert on "PRODUCT"
  for each row
begin
  if :NEW."PRODUCT_ID" is null then
    select "PDSEQ".nextval into :NEW."PRODUCT_ID" from dual;
  end if;
end;

/
ALTER TRIGGER  "BI_PRODUCT" ENABLE;

CREATE OR REPLACE TRIGGER  "BI_STORE"
  before insert on "STORE"
  for each row
begin
  if :NEW."ST_ID" is null then
    select "STORSEQ".nextval into :NEW."ST_ID" from dual;
  end if;
end;

/
ALTER TRIGGER  "BI_STORE" ENABLE;

CREATE OR REPLACE TRIGGER  "BI_STP_STOCK"
  before insert on "STP_STOCK"
  for each row
begin
  if :NEW."STP_ID" is null then
    select "STOCSEQ".nextval into :NEW."STP_ID" from dual;
  end if;
end;

/
ALTER TRIGGER  "BI_STP_STOCK" ENABLE;

CREATE OR REPLACE TRIGGER  "BI_WAREHOUSE"
  before insert on "WAREHOUSE"
  for each row
begin
  if :NEW."WH_ID" is null then
    select "WHSEQ".nextval into :NEW."WH_ID" from dual;
  end if;
end;

/
ALTER TRIGGER  "BI_WAREHOUSE" ENABLE;

CREATE OR REPLACE TRIGGER  "BI_WHPR_STK"
  before insert on "WHPR_STK"
  for each row
begin
  if :NEW."WHSTOCK_ID" is null then
    select "WHPRSEQ".nextval into :NEW."WHSTOCK_ID" from dual;
  end if;
end;

/
ALTER TRIGGER  "BI_WHPR_STK" ENABLE;