------------------------------------------------
-- DDL Statements for table "CIS3311 "."ORDERS"
------------------------------------------------
CREATE TABLE "CIS3311 "."ORDERS" (
"ORD_ID" INTEGER NOT NULL WITH DEFAULT 0,
"ORD_DATE" DATE ,
"ORD_REQ_NUM" VARCHAR(6) ,
"ORD_PO_NUM" VARCHAR(6) ,
"VEN_CODE" VARCHAR(50) ,
"ORD_INV_NUM" VARCHAR(10) ,
"ORD_DESCRIP" VARCHAR(60) ,
"ORD_SHIP_COST" DECIMAL(15,2) ,
"ORD_TOT_COST" DECIMAL(15,2) ,
"USER_ID" INTEGER ,
"ORD_DATE_REC" DATE ,
"ORD_STATUS" VARCHAR(10) ,
"ORD_FUND_TYPE" VARCHAR(5) ) ;
-- DDL Statements for primary keys on Table "CIS3311 "."ORDERS"
ALTER TABLE "CIS3311 "."ORDERS"
ADD PRIMARY KEY
("ORD_ID");
------------------------------------------------
-- DDL Statements for table "CIS3311 "."DEPARTMENT"
------------------------------------------------
CREATE TABLE "CIS3311 "."DEPARTMENT" (
"DEPT_ID" VARCHAR(50) NOT NULL ,
"DEPT_DESC" VARCHAR(50) NOT NULL ,
"USR_ID" INTEGER WITH DEFAULT 0) ;
-- DDL Statements for indexes on Table "CIS3311 "."DEPARTMENT"
CREATE INDEX "CIS3311 "."S_DEPARTMENT_USR_I" ON "CIS3311 "."DEPARTMENT"
("USR_ID" ASC);
-- DDL Statements for primary keys on Table "CIS3311 "."DEPARTMENT"
ALTER TABLE "CIS3311 "."DEPARTMENT"
ADD PRIMARY KEY ("DEPT_ID");
------------------------------------------------
-- DDL Statements for table "CIS3311 "."INVTYPE"
------------------------------------------------
CREATE TABLE "CIS3311 "."INVTYPE" (
"TY_GROUP" VARCHAR(50) NOT NULL ,
"TY_CLASS" VARCHAR(2) ,
"TY_CATEG" VARCHAR(2) ,
"TY_TYPE" VARCHAR(2) ,
"TY_SUBTY" VARCHAR(2) ,
"TY_DESCR" VARCHAR(50) ,
"TY_UNIT" VARCHAR(5) ,
"TY_STATUS" VARCHAR(50) ) ;
-- DDL Statements for primary keys on Table "CIS3311 "."INVTYPE"
ALTER TABLE "CIS3311 "."INVTYPE"
ADD PRIMARY KEY ("TY_GROUP");
------------------------------------------------
-- DDL Statements for table "CIS3311 "."USER"
------------------------------------------------
CREATE TABLE "CIS3311 "."USER" (
"USR_ID" INTEGER NOT NULL WITH DEFAULT 0,
"USR_FNAME" VARCHAR(40) ,
"USR_MNAME" VARCHAR(3) ,
"USR_LNAME" VARCHAR(40) ,
"USR_SSN" VARCHAR(50) ,
"DEPT_ID" VARCHAR(20) ,
"USR_JOBTITLE" VARCHAR(30) ,
"USR_BSNS_STREET" VARCHAR(150) ,
"USR_BSNS_CITY" VARCHAR(50) ,
"USR_BSNS_STATE" VARCHAR(2) ,
"USR_BSNS_ZIP" VARCHAR(15) ,
"USR_BSNS_FAX" VARCHAR(20) ,
"USR_BSNS_PH" VARCHAR(20) ,
"USR_HOME_STREET" VARCHAR(150) ,
"USR_HOME_CITY" VARCHAR(50) ,
"USR_HOME_STATE" VARCHAR(2) ,
"USR_HOME_ZIP" VARCHAR(15) ,
"USR_HOME_PH" VARCHAR(20) ,
"USR_PAGER" VARCHAR(20) ,
"USR_EMAIL" VARCHAR(25) ,
"USR_GENDER" VARCHAR(1) ,
"USR_OFFICE" VARCHAR(25) ,
"USR_POBOX" VARCHAR(25) ,
"USR_WEB_PAGE" VARCHAR(35) ) ;
-- DDL Statements for indexes on Table "CIS3311 "."USER"
CREATE INDEX "CIS3311 "."S_USER_BUSINESSPOS" ON "CIS3311 "."USER"
("USR_BSNS_ZIP" ASC);
-- DDL Statements for indexes on Table "CIS3311 "."USER"
CREATE INDEX "CIS3311 "."S_USER_DEPT_ID" ON "CIS3311 "."USER"
("DEPT_ID" ASC);
-- DDL Statements for indexes on Table "CIS3311 "."USER"
CREATE INDEX "CIS3311 "."S_USER_POSTAL_CODE" ON "CIS3311 "."USER"
("USR_HOME_ZIP" ASC);
-- DDL Statements for primary keys on Table "CIS3311 "."USER"
ALTER TABLE "CIS3311 "."USER"
PRIMARY KEY ("USR_ID");
------------------------------------------------
-- DDL Statements for table "CIS3311 "."VENDOR"
------------------------------------------------
CREATE TABLE "CIS3311 "."VENDOR" (
"VEN_CODE" VARCHAR(50) NOT NULL ,
"VEN_NAME" VARCHAR(50) ,
"VEN_CONTACT_NAME" VARCHAR(50) ,
"VEN_ADDRESS" VARCHAR(100) ,
"VEN_CITY" VARCHAR(50) ,
"VEN_STATE" VARCHAR(2) ,
"VEN_ZIP" VARCHAR(50) ,
"VEN_PH" VARCHAR(15) ,
"VEN_FAX" VARCHAR(15) ,
"VEN_EMAIL" VARCHAR(50) ,
"VEN_CUS_ID" VARCHAR(75) ,
"VEN_SUPPORT_ID" INTEGER ,
"VEN_SUPPORT_PH" VARCHAR(15) ,
"VEN_WEB_PAGE" VARCHAR(50) ) ;
-- DDL Statements for indexes on Table "CIS3311 "."VENDOR"
CREATE INDEX "CIS3311 "."S_VENDOR_CUSTOMERI" ON "CIS3311 "."VENDOR"
("VEN_CUS_ID" ASC);
-- DDL Statements for primary keys on Table "CIS3311 "."VENDOR"
ALTER TABLE "CIS3311 "."VENDOR"
ADD PRIMARY KEY ("VEN_CODE");
------------------------------------------------
-- DDL Statements for table "CIS3311 "."ORDER_LINE"
------------------------------------------------
CREATE TABLE "CIS3311 "."ORDER_LINE" (
"ORD_ID" INTEGER NOT NULL ,
"ORD_LINE_ID" INTEGER NOT NULL WITH DEFAULT 0,
"TY_GROUP" VARCHAR(50) ,
"ORD_LINE_DESC" VARCHAR(150) ,
"ORD_LINE_QTY" INTEGER WITH DEFAULT 0,
"ORD_LINE_UNIT_COST" DECIMAL(15,2) WITH DEFAULT 0,
"ORD_LINE_TOT" DECIMAL(15,2) WITH DEFAULT 0) ;
-- DDL Statements for indexes on Table "CIS3311 "."ORDER_LINE"
CREATE INDEX "CIS3311 "."S_ORDER_LINE_ORD_I" ON "CIS3311 "."ORDER_LINE"
("ORD_ID" ASC);
-- DDL Statements for indexes on Table "CIS3311 "."ORDER_LINE"
CREATE INDEX "CIS3311 "."S_ORDER_LINE_POLIN" ON "CIS3311 "."ORDER_LINE"
("ORD_LINE_ID" ASC);
-- DDL Statements for indexes on Table "CIS3311 "."ORDER_LINE"
CREATE INDEX "CIS3311 "."S_ORDER_LINE_TY_GR" ON "CIS3311 "."ORDER_LINE"
("TY_GROUP" ASC);
-- DDL Statements for primary keys on Table "CIS3311 "."ORDER_LINE"
ALTER TABLE "CIS3311 "."ORDER_LINE"
ADD PRIMARY KEY ("ORD_ID",
"ORD_LINE_ID");
-- DDL Statements for foreign keys on Table "CIS3311 "."ORDERS"
ALTER TABLE "CIS3311 "."ORDERS"
ADD CONSTRAINT "VENDORORDERS" FOREIGN KEY
("VEN_CODE")
REFERENCES "CIS3311 "."VENDOR"
("VEN_CODE")
ON DELETE NO ACTION
ON UPDATE NO ACTION;
-- DDL Statements for foreign keys on Table "CIS3311 "."ORDER_LINE"
ALTER TABLE "CIS3311 "."ORDER_LINE"
ADD CONSTRAINT "INVTYPEORDER_LINE" FOREIGN KEY
("TY_GROUP")
REFERENCES "CIS3311 "."INVTYPE"
("TY_GROUP")
ON DELETE NO ACTION
ON UPDATE NO ACTION;
ALTER TABLE "CIS3311 "."ORDER_LINE"
ADD CONSTRAINT "ORDERSORDER_LINE" FOREIGN KEY
("ORD_ID")
REFERENCES "CIS3311 "."ORDERS"
("ORD_ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION;
-------------------------------
-- DDL Statements for Triggers
-------------------------------
CREATE TRIGGER CIS3311.AN_I_ORDER_LINE NO CASCADE BEFORE INSERT ON CIS3311.ORDER_LINE
REFERENCING NEW AS newrow FOR EACH ROW MODE DB2SQL SET newrow.ORD_LINE_ID
= COALESCE((SELECT MAX(ORD_LINE_ID) FROM CIS3311.ORDER_LINE) + 1, 1);
CREATE TRIGGER CIS3311.AN_I_ORDERS NO CASCADE BEFORE INSERT ON CIS3311.ORDERS
REFERENCING NEW AS newrow FOR EACH ROW MODE DB2SQL SET newrow.ORD_ID
= COALESCE((SELECT MAX(ORD_ID) FROM CIS3311.ORDERS) + 1, 1);
CREATE TRIGGER CIS3311.AN_I_USER NO CASCADE BEFORE INSERT ON CIS3311.USER
REFERENCING NEW AS newrow FOR EACH ROW MODE DB2SQL SET newrow.USR_ID =
COALESCE((SELECT MAX(USR_ID) FROM CIS3311.USER) + 1, 1);
CREATE TRIGGER CIS3311.AN_U_ORDER_LINE NO CASCADE BEFORE UPDATE OF ORD_LINE_ID
ON CIS3311.ORDER_LINE REFERENCING NEW AS newrow OLD as oldrow FOR EACH
ROW MODE DB2SQL SET newrow.ORD_LINE_ID = oldrow.ORD_LINE_ID;
CREATE TRIGGER CIS3311.AN_U_ORDERS NO CASCADE BEFORE UPDATE OF ORD_ID
ON CIS3311.ORDERS REFERENCING NEW AS newrow OLD as oldrow FOR EACH
ROW MODE DB2SQL SET newrow.ORD_ID = oldrow.ORD_ID;
CREATE TRIGGER CIS3311.AN_U_USER NO CASCADE BEFORE UPDATE OF USR_ID ON
CIS3311.USER REFERENCING NEW AS newrow OLD as oldrow FOR EACH ROW MODE
DB2SQL SET newrow.USR_ID = oldrow.USR_ID;
CREATE TRIGGER CIS3311.DR_I_USER NO CASCADE BEFORE INSERT ON CIS3311.USER
REFERENCING NEW AS newrow FOR EACH ROW MODE DB2SQL WHEN ( (DEPT_ID IS
NOT NULL) AND NOT EXISTS ( SELECT DEPT_ID FROM CIS3311.DEPARTMENT WHERE
(DEPT_ID = newrow.DEPT_ID) ) ) SIGNAL SQLSTATE '75002' ('Inputted foreign-key
does not match primary key of table: CIS3311.DEPARTMENT.');
CREATE TRIGGER CIS3311.DR_U_USER NO CASCADE BEFORE UPDATE OF DEPT_ID ON
CIS3311.USER REFERENCING NEW AS newrow FOR EACH ROW MODE DB2SQL WHEN (
( DEPT_ID IS NOT NULL) AND NOT EXISTS ( SELECT DEPT_ID FROM CIS3311.DEPARTMENT
WHERE (DEPT_ID = newrow.DEPT_ID) ) ) SIGNAL SQLSTATE '75002' ('Inputted
foreign-key does not match primary key of table: CIS3311.DEPARTMENT.');
CREATE TRIGGER CIS3311.PR_D_DEPARTMENT NO CASCADE BEFORE DELETE ON CIS3311.DEPARTMENT
REFERENCING OLD as oldrow FOR EACH ROW MODE DB2SQL WHEN ( EXISTS ( SELECT
DEPT_ID FROM CIS3311.USER WHERE DEPT_ID = oldrow.DEPT_ID ) ) SIGNAL SQLSTATE
'75001' ('Delete denied. Related rows exist in dependent table: CIS3311.USER.');
CREATE TRIGGER CIS3311.PR_U_DEPARTMENT AFTER UPDATE OF DEPT_ID ON CIS3311.DEPARTMENT
REFERENCING NEW AS newrow OLD AS oldrow FOR EACH ROW MODE DB2SQL UPDATE
CIS3311.USER SET DEPT_ID = newrow.DEPT_ID WHERE DEPT_ID = oldrow.DEPT_ID;