employee_ib6.sql

jvcl driver development envionment

INSERT INTO SALARY_HISTORY (EMP_NO, CHANGE_DATE, UPDATER_ID, OLD_SALARY, PERCENT_CHANGE) VALUES (72, '1993-12-20', 'elaine', 91740, 10);
INSERT INTO SALARY_HISTORY (EMP_NO, CHANGE_DATE, UPDATER_ID, OLD_SALARY, PERCENT_CHANGE) VALUES (118, '1993-12-20', 'elaine', 6800000, 10);
INSERT INTO SALARY_HISTORY (EMP_NO, CHANGE_DATE, UPDATER_ID, OLD_SALARY, PERCENT_CHANGE) VALUES (121, '1993-12-20', 'elaine', 90000000, 10);
INSERT INTO SALARY_HISTORY (EMP_NO, CHANGE_DATE, UPDATER_ID, OLD_SALARY, PERCENT_CHANGE) VALUES (127, '1993-12-20', 'elaine', 40000, 10);
INSERT INTO SALARY_HISTORY (EMP_NO, CHANGE_DATE, UPDATER_ID, OLD_SALARY, PERCENT_CHANGE) VALUES (134, '1993-12-20', 'elaine', 355000, 10);

COMMIT WORK;

INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V91E0210', 1004, 11, 'shipped', '1991-03-04', '1991-03-05', NULL, 'y', 10, 5000, 0.100000001490116, 'hardware');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V92E0340', 1004, 11, 'shipped', '1992-10-15', '1992-10-16', '1992-10-17', 'y', 7, 70000, 0, 'hardware');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V92J1003', 1010, 61, 'shipped', '1992-07-26', '1992-08-04', '1992-09-15', 'y', 15, 2985, 0, 'software');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V93J2004', 1010, 118, 'shipped', '1993-10-30', '1993-12-02', '1993-11-15', 'y', 3, 210, 0, 'software');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V93J3100', 1010, 118, 'shipped', '1993-08-20', '1993-08-20', NULL, 'y', 16, 18000.4, 0.100000001490116, 'software');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V92F3004', 1012, 11, 'shipped', '1992-10-15', '1993-01-16', '1993-01-16', 'y', 3, 2000, 0, 'software');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V93F3088', 1012, 134, 'shipped', '1993-08-27', '1993-09-08', NULL, 'n', 10, 10000, 0, 'software');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V93F2030', 1012, 134, 'open', '1993-12-12', NULL, NULL, 'y', 15, 450000.49, 0, 'hardware');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V93F2051', 1012, 134, 'waiting', '1993-12-18', NULL, '1994-03-01', 'n', 1, 999.98, 0, 'software');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V93H0030', 1005, 118, 'open', '1993-12-12', NULL, '1994-01-01', 'y', 20, 5980, 0.200000002980232, 'software');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V94H0079', 1005, 61, 'open', '1994-02-13', NULL, '1994-04-20', 'n', 10, 9000, 0.0500000007450581, 'software');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V9324200', 1001, 72, 'shipped', '1993-08-09', '1993-08-09', '1993-08-17', 'y', 1000, 560000, 0.200000002980232, 'hardware');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V9324320', 1001, 127, 'shipped', '1993-08-16', '1993-08-16', '1993-09-01', 'y', 1, 0, 1, 'software');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V9320630', 1001, 127, 'open', '1993-12-12', NULL, '1993-12-15', 'n', 3, 60000, 0.200000002980232, 'hardware');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V9420099', 1001, 127, 'open', '1994-01-17', NULL, '1994-06-01', 'n', 100, 3399.15, 0.150000005960464, 'software');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V9427029', 1001, 127, 'shipped', '1994-02-07', '1994-02-10', '1994-02-10', 'n', 17, 422210.97, 0, 'hardware');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V9333005', 1002, 11, 'shipped', '1993-02-03', '1993-03-03', NULL, 'y', 2, 600.5, 0, 'software');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V9333006', 1002, 11, 'shipped', '1993-04-27', '1993-05-02', '1993-05-02', 'n', 5, 20000, 0, 'other');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V9336100', 1002, 11, 'waiting', '1993-12-27', '1994-01-01', '1994-01-01', 'n', 150, 14850, 0.0500000007450581, 'software');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V9346200', 1003, 11, 'waiting', '1993-12-31', NULL, '1994-01-24', 'n', 3, 0, 1, 'software');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V9345200', 1003, 11, 'shipped', '1993-11-11', '1993-12-02', '1993-12-01', 'y', 900, 27000, 0.300000011920929, 'software');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V9345139', 1003, 127, 'shipped', '1993-09-09', '1993-09-20', '1993-10-01', 'y', 20, 12582.12, 0.100000001490116, 'software');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V93C0120', 1006, 72, 'shipped', '1993-03-22', '1993-05-31', '1993-04-17', 'y', 1, 47.5, 0, 'other');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V93C0990', 1006, 72, 'shipped', '1993-08-09', '1993-09-02', NULL, 'y', 40, 399960.5, 0.100000001490116, 'hardware');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V9456220', 1007, 127, 'open', '1994-01-04', NULL, '1994-01-30', 'y', 1, 3999.99, 0, 'hardware');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V93S4702', 1011, 121, 'shipped', '1993-10-27', '1993-10-28', '1993-12-15', 'y', 4, 120000, 0, 'hardware');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V94S6400', 1011, 141, 'waiting', '1994-01-06', NULL, '1994-02-15', 'y', 20, 1980.72, 0.400000005960464, 'software');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V93H3009', 1008, 61, 'shipped', '1993-08-01', '1993-12-02', '1993-12-01', 'n', 3, 9000, 0.0500000007450581, 'software');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V93H0500', 1008, 61, 'open', '1993-12-12', NULL, '1993-12-15', 'n', 3, 16000, 0.200000002980232, 'hardware');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V93F0020', 1009, 61, 'shipped', '1993-10-10', '1993-11-11', '1993-11-11', 'n', 1, 490.69, 0, 'software');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V93I4700', 1013, 121, 'open', '1993-10-27', NULL, '1993-12-15', 'n', 5, 2693, 0, 'hardware');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V93B1002', 1014, 134, 'shipped', '1993-09-20', '1993-09-21', '1993-09-25', 'y', 1, 100.02, 0, 'software');
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE) VALUES ('V93N5822', 1015, 134, 'shipped', '1993-12-18', '1994-01-14', NULL, 'n', 2, 1500, 0, 'software');

COMMIT WORK;



/* Check constraints definition */

ALTER TABLE JOB ADD CHECK (min_salary < max_salary);
ALTER TABLE EMPLOYEE ADD CHECK ( salary >= (SELECT min_salary FROM job WHERE
                        job.job_code = employee.job_code AND
                        job.job_grade = employee.job_grade AND
                        job.job_country = employee.job_country) AND
            salary <= (SELECT max_salary FROM job WHERE
                        job.job_code = employee.job_code AND
                        job.job_grade = employee.job_grade AND
                        job.job_country = employee.job_country));
ALTER TABLE PROJ_DEPT_BUDGET ADD CHECK (FISCAL_YEAR >= 1993);
ALTER TABLE SALARY_HISTORY ADD CHECK (percent_change between -50 and 50);
ALTER TABLE CUSTOMER ADD CHECK (on_hold IS NULL OR on_hold = '*');
ALTER TABLE SALES ADD CHECK (order_status in
                            ('new', 'open', 'shipped', 'waiting'));
ALTER TABLE SALES ADD CHECK (ship_date >= order_date OR ship_date IS NULL);
ALTER TABLE SALES ADD CHECK (date_needed > order_date OR date_needed IS NULL);
ALTER TABLE SALES ADD CHECK (paid in ('y', 'n'));
ALTER TABLE SALES ADD CHECK (qty_ordered >= 1);
ALTER TABLE SALES ADD CHECK (total_value >= 0);
ALTER TABLE SALES ADD CHECK (discount >= 0 AND discount <= 1);
ALTER TABLE SALES ADD CHECK (NOT (order_status = 'shipped' AND ship_date IS NULL));
ALTER TABLE SALES ADD CHECK (NOT (order_status = 'shipped' AND
            EXISTS (SELECT on_hold FROM customer
                    WHERE customer.cust_no = sales.cust_no
                    AND customer.on_hold = '*')));


/******************************************************************************/
/***                           Unique Constraints                           ***/
/******************************************************************************/

ALTER TABLE DEPARTMENT ADD UNIQUE (DEPARTMENT);
ALTER TABLE PROJECT ADD UNIQUE (PROJ_NAME);


/******************************************************************************/
/***                              Primary Keys                              ***/
/******************************************************************************/

ALTER TABLE COUNTRY ADD PRIMARY KEY (COUNTRY);
ALTER TABLE CUSTOMER ADD PRIMARY KEY (CUST_NO);
ALTER TABLE DEPARTMENT ADD PRIMARY KEY (DEPT_NO);
ALTER TABLE EMPLOYEE ADD PRIMARY KEY (EMP_NO);
ALTER TABLE EMPLOYEE_PROJECT ADD PRIMARY KEY (EMP_NO, PROJ_ID);
ALTER TABLE JOB ADD PRIMARY KEY (JOB_CODE, JOB_GRADE, JOB_COUNTRY);
ALTER TABLE PROJECT ADD PRIMARY KEY (PROJ_ID);
ALTER TABLE PROJ_DEPT_BUDGET ADD PRIMARY KEY (FISCAL_YEAR, PROJ_ID, DEPT_NO);
ALTER TABLE SALARY_HISTORY ADD PRIMARY KEY (EMP_NO, CHANGE_DATE, UPDATER_ID);
ALTER TABLE SALES ADD PRIMARY KEY (PO_NUMBER);


/******************************************************************************/
/***                              Foreign Keys                              ***/
/******************************************************************************/

ALTER TABLE CUSTOMER ADD FOREIGN KEY (COUNTRY) REFERENCES COUNTRY (COUNTRY);
ALTER TABLE DEPARTMENT ADD FOREIGN KEY (HEAD_DEPT) REFERENCES DEPARTMENT (DEPT_NO);
ALTER TABLE DEPARTMENT ADD FOREIGN KEY (MNGR_NO) REFERENCES EMPLOYEE (EMP_NO);
ALTER TABLE EMPLOYEE ADD FOREIGN KEY (DEPT_NO) REFERENCES DEPARTMENT (DEPT_NO);
ALTER TABLE EMPLOYEE ADD FOREIGN KEY (JOB_CODE, JOB_GRADE, JOB_COUNTRY) REFERENCES JOB (JOB_CODE, JOB_GRADE, JOB_COUNTRY);
ALTER TABLE EMPLOYEE_PROJECT ADD FOREIGN KEY (EMP_NO) REFERENCES EMPLOYEE (EMP_NO);
ALTER TABLE EMPLOYEE_PROJECT ADD FOREIGN KEY (PROJ_ID) REFERENCES PROJECT (PROJ_ID);
ALTER TABLE JOB ADD FOREIGN KEY (JOB_COUNTRY) REFERENCES COUNTRY (COUNTRY);
ALTER TABLE PROJECT ADD FOREIGN KEY (TEAM_LEADER) REFERENCES EMPLOYEE (EMP_NO);
ALTER TABLE PROJ_DEPT_BUDGET ADD FOREIGN KEY (DEPT_NO) REFERENCES DEPARTMENT (DEPT_NO);
ALTER TABLE PROJ_DEPT_BUDGET ADD FOREIGN KEY (PROJ_ID) REFERENCES PROJECT (PROJ_ID);
ALTER TABLE SALARY_HISTORY ADD FOREIGN KEY (EMP_NO) REFERENCES EMPLOYEE (EMP_NO);
ALTER TABLE SALES ADD FOREIGN KEY (CUST_NO) REFERENCES CUSTOMER (CUST_NO);
ALTER TABLE SALES ADD FOREIGN KEY (SALES_REP) REFERENCES EMPLOYEE (EMP_NO);


/******************************************************************************/
/***                                Indices                                 ***/
/******************************************************************************/

CREATE INDEX CUSTNAMEX ON CUSTOMER (CUSTOMER);
CREATE INDEX CUSTREGION ON CUSTOMER (COUNTRY, CITY);
CREATE DESCENDING INDEX BUDGETX ON DEPARTMENT (BUDGET);
CREATE INDEX NAMEX ON EMPLOYEE (LAST_NAME, FIRST_NAME);
CREATE DESCENDING INDEX MAXSALX ON JOB (JOB_COUNTRY, MAX_SALARY);
CREATE INDEX MINSALX ON JOB (JOB_COUNTRY, MIN_SALARY);
CREATE UNIQUE INDEX PRODTYPEX ON PROJECT (PRODUCT, PROJ_NAME);
CREATE DESCENDING INDEX CHANGEX ON SALARY_HISTORY (CHANGE_DATE);
CREATE INDEX UPDATERX ON SALARY_HISTORY (UPDATER_ID);
CREATE INDEX NEEDX ON SALES (DATE_NEEDED);
CREATE DESCENDING INDEX QTYX ON SALES (ITEM_TYPE, QTY_ORDERED);
CREATE INDEX SALESTATX ON SALES (ORDER_STATUS, PAID);


/******************************************************************************/
/***                                Triggers                                ***/
/******************************************************************************/


SET TERM ^ ;




/* Trigger: POST_NEW_ORDER */
CREATE TRIGGER POST_NEW_ORDER FOR SALES
ACTIVE AFTER INSERT POSITION 0
AS
BEGIN
    POST_EVENT 'new_order';
END
^

/* Trigger: SAVE_SALARY_CHANGE */
CREATE TRIGGER SAVE_SALARY_CHANGE FOR EMPLOYEE
ACTIVE AFTER UPDATE POSITION 0
AS
BEGIN
    IF (old.salary <> new.salary) THEN
        INSERT INTO salary_history
            (emp_no, change_date, updater_id, old_salary, percent_change)
        VALUES (
            old.emp_no,
            'NOW',
            user,
            old.salary,
            (new.salary - old.salary) * 100 / old.salary);
END
^

/* Trigger: SET_CUST_NO */
CREATE TRIGGER SET_CUST_NO FOR CUSTOMER
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    new.cust_no = gen_id(cust_no_gen, 1);
END
^

/* Trigger: SET_EMP_NO */
CREATE TRIGGER SET_EMP_NO FOR EMPLOYEE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    new.emp_no = gen_id(emp_no_gen, 1);
END
^


SET TERM ; ^


/******************************************************************************/
/***                           Stored Procedures                            ***/
/******************************************************************************/


SET TERM ^ ;

ALTER PROCEDURE ADD_EMP_PROJ (
    EMP_NO SMALLINT,
    PROJ_ID CHAR(5) CHARACTER SET NONE)
AS