This section contains the SQL DDL that creates the demonstration database provided with the installation of CA IDMS.
********************************************************************* * Create schema for the following tables. Then set session qualifier * for that schema ********************************************************************* CREATE SCHEMA DEMOEMPL; SET SESSION CURRENT SCHEMA DEMOEMPL; ********************************************************************* * Create the tables that belong to the schema DEMOEMPL. Each * table is associated with an area in the segment DEMOEMPL. *********************************************************************
CREATE TABLE BENEFITS (FISCAL_YEAR UNSIGNED NUMERIC(4,0) NOT NULL, EMP_ID UNSIGNED NUMERIC(4,0) NOT NULL, VAC_ACCRUED UNSIGNED DECIMAL(6,2) NOT NULL WITH DEFAULT, VAC_TAKEN UNSIGNED DECIMAL(6,2) NOT NULL WITH DEFAULT, SICK_ACCRUED UNSIGNED DECIMAL(6,2) NOT NULL WITH DEFAULT, SICK_TAKEN UNSIGNED DECIMAL(6,2) NOT NULL WITH DEFAULT, STOCK_PERCENT UNSIGNED DECIMAL(6,3) NOT NULL WITH DEFAULT, STOCK_AMOUNT UNSIGNED DECIMAL(10,2) NOT NULL WITH DEFAULT,
LAST_REVIEW_DATE DATE , REVIEW_PERCENT UNSIGNED DECIMAL(6,3) , PROMO_DATE DATE , RETIRE_PLAN CHAR(6) , RETIRE_PERCENT UNSIGNED DECIMAL(6,3) , BONUS_AMOUNT UNSIGNED DECIMAL(10,2) , COMP_ACCRUED UNSIGNED DECIMAL(6,2) NOT NULL WITH DEFAULT, COMP_TAKEN UNSIGNED DECIMAL(6,2) NOT NULL WITH DEFAULT,
EDUC_LEVEL CHAR(06) ,
UNION_ID CHAR(10) ,
UNION_DUES UNSIGNED DECIMAL(10,2) ,
CHECK ( (RETIRE_PLAN IN ('STOCK', 'BONDS', '401K') ) AND
(EDUC_LEVEL IN ('GED', 'HSDIP', 'JRCOLL', 'COLL',
'MAS', 'PHD') ) ) )
IN SQLDEMO.EMPLAREA;
*********************************************************************
CREATE TABLE COVERAGE
(PLAN_CODE CHAR(03) NOT NULL,
EMP_ID UNSIGNED NUMERIC(4,0) NOT NULL,
SELECTION_DATE DATE NOT NULL WITH DEFAULT,
TERMINATION_DATE DATE ,
NUM_DEPENDENTS UNSIGNED NUMERIC(2,0) NOT NULL WITH DEFAULT)
IN SQLDEMO.EMPLAREA;
*********************************************************************
CREATE TABLE DEPARTMENT
(DEPT_ID UNSIGNED NUMERIC(4,0) NOT NULL,
DEPT_HEAD_ID UNSIGNED NUMERIC(4,0) ,
DIV_CODE CHAR(03) NOT NULL,
DEPT_NAME CHAR(40) NOT NULL)
IN SQLDEMO.INFOAREA;
*********************************************************************
*********************************************************************
CREATE TABLE DIVISION
(DIV_CODE CHAR(03) NOT NULL,
DIV_HEAD_ID UNSIGNED NUMERIC(4,0) ,
DIV_NAME CHAR(40) NOT NULL)
IN SQLDEMO.INFOAREA;
*********************************************************************
CREATE TABLE EMPLOYEE
(EMP_ID UNSIGNED NUMERIC(4,0) NOT NULL,
MANAGER_ID UNSIGNED NUMERIC(4,0) , EMP_FNAME CHAR(20) NOT NULL, EMP_LNAME CHAR(20) NOT NULL, DEPT_ID UNSIGNED NUMERIC(4,0) NOT NULL, STREET CHAR(40) NOT NULL, CITY CHAR(20) NOT NULL, STATE CHAR(02) NOT NULL,
ZIP_CODE CHAR(09) NOT NULL,
PHONE CHAR(10) ,
STATUS CHAR NOT NULL,
SS_NUMBER UNSIGNED NUMERIC(9,0) NOT NULL,
START_DATE DATE NOT NULL,
TERMINATION_DATE DATE ,
BIRTH_DATE DATE ,
CHECK ( ( EMP_ID <= 8999 ) AND (STATUS IN ('A', 'S', 'L', 'T') ) ) )
IN SQLDEMO.EMPLAREA;
*********************************************************************
CREATE TABLE INSURANCE_PLAN (PLAN_CODE CHAR(03) NOT NULL, COMP_NAME CHAR(40) NOT NULL, STREET CHAR(40) NOT NULL, CITY CHAR(20) NOT NULL, STATE CHAR(02) NOT NULL, ZIP_CODE CHAR(09) NOT NULL,
PHONE CHAR(10) NOT NULL,
GROUP_NUMBER UNSIGNED NUMERIC(4,0) NOT NULL,
DEDUCT UNSIGNED DECIMAL(9,2) ,
MAX_LIFE_BENEFIT UNSIGNED DECIMAL(9,2) ,
FAMILY_COST UNSIGNED DECIMAL(9,2) ,
DEP_COST UNSIGNED DECIMAL(9,2) ,
EFF_DATE DATE NOT NULL)
IN SQLDEMO.INFOAREA;
*********************************************************************
CREATE TABLE JOB (JOB_ID UNSIGNED NUMERIC(4,0) NOT NULL, JOB_TITLE CHAR(20) NOT NULL, MIN_RATE UNSIGNED DECIMAL(10,2) , MAX_RATE UNSIGNED DECIMAL(10,2) , SALARY_IND CHAR(01) , NUM_OF_POSITIONS UNSIGNED DECIMAL(4,0) ,
EFF_DATE DATE ,
JOB_DESC_LINE_1 VARCHAR(60) ,
JOB_DESC_LINE_2 VARCHAR(60) ,
CHECK ( SALARY_IND IN ('S', 'H') ) )
IN SQLDEMO.INFOAREA;
*********************************************************************
CREATE TABLE POSITION
(EMP_ID UNSIGNED NUMERIC(4,0) NOT NULL,
JOB_ID UNSIGNED NUMERIC(4,0) NOT NULL, START_DATE DATE NOT NULL, FINISH_DATE DATE , HOURLY_RATE UNSIGNED DECIMAL(7,2) , SALARY_AMOUNT UNSIGNED DECIMAL(10,2) , BONUS_PERCENT UNSIGNED DECIMAL(7,3) , COMM_PERCENT UNSIGNED DECIMAL(7,3) , OVERTIME_RATE UNSIGNED DECIMAL(5,2) , CHECK ( (HOURLY_RATE IS NOT NULL AND SALARY_AMOUNT IS NULL)
OR (HOURLY_RATE IS NULL AND SALARY_AMOUNT IS NOT NULL) ) )
IN SQLDEMO.EMPLAREA;
*********************************************************************
********************************************************************* CREATE SCHEMA DEMOPROJ; SET SESSION CURRENT SCHEMA DEMOPROJ; *********************************************************************
* Create the tables that belong to the schema DEMOPROJ. Each
* table is associated with an area in the segment PROJSEG.
*********************************************************************
CREATE TABLE ASSIGNMENT
(EMP_ID UNSIGNED NUMERIC(4,0) NOT NULL,
PROJ_ID CHAR(10) NOT NULL,
START_DATE DATE NOT NULL,
END_DATE DATE )
IN PROJSEG.PROJAREA;
********************************************************************* CREATE TABLE CONSULTANT (CON_ID UNSIGNED NUMERIC(4,0) NOT NULL, CON_FNAME CHAR(20) NOT NULL, CON_LNAME CHAR(20) NOT NULL, MANAGER_ID UNSIGNED NUMERIC(4,0) NOT NULL, DEPT_ID UNSIGNED NUMERIC(4,0) NOT NULL, PROJ_ID CHAR(10) , STREET CHAR(40) , CITY CHAR(20) NOT NULL,
STATE CHAR(02) NOT NULL,
ZIP_CODE CHAR(09) NOT NULL,
PHONE CHAR(10) ,
BIRTH_DATE DATE ,
START_DATE DATE NOT NULL,
SS_NUMBER UNSIGNED NUMERIC(9,0) NOT NULL,
RATE UNSIGNED DECIMAL(7,2) ,
CHECK ( (CON_ID >= 9000 AND CON_ID <= 9999) ) )
IN PROJSEG.PROJAREA;
*********************************************************************
CREATE TABLE EXPERTISE
(EMP_ID UNSIGNED NUMERIC(4,0) NOT NULL,
SKILL_ID UNSIGNED NUMERIC(4,0) NOT NULL,
SKILL_LEVEL CHAR(02) ,
EXP_DATE DATE )
IN PROJSEG.PROJAREA;
*********************************************************************
*********************************************************************
CREATE TABLE PROJECT (PROJ_ID CHAR(10) NOT NULL, PROJ_LEADER_ID UNSIGNED NUMERIC(4,0) , EST_START_DATE DATE , EST_END_DATE DATE , ACT_START_DATE DATE , ACT_END_DATE DATE , EST_MAN_HOURS UNSIGNED DECIMAL(7,2) ,
ACT_MAN_HOURS UNSIGNED DECIMAL(7,2) ,
PROJ_DESC VARCHAR(60) NOT NULL)
IN PROJSEG.PROJAREA;
*********************************************************************
CREATE TABLE SKILL
(SKILL_ID UNSIGNED NUMERIC(4,0) NOT NULL,
SKILL_NAME CHAR(20) NOT NULL,
SKILL_DESC VARCHAR(60) )
IN PROJSEG.PROJAREA;
*********************************************************************
* Name calc keys for above tables (in order that they were defined)
*********************************************************************
CREATE UNIQUE CALC KEY ON DEMOEMPL.DEPARTMENT(DEPT_ID);
CREATE UNIQUE CALC KEY ON DEMOEMPL.DIVISION(DIV_CODE);
CREATE UNIQUE CALC KEY ON DEMOEMPL.EMPLOYEE(EMP_ID);
CREATE UNIQUE CALC KEY ON DEMOEMPL.INSURANCE_PLAN(PLAN_CODE);
CREATE UNIQUE CALC KEY ON DEMOEMPL.JOB(JOB_ID); CREATE UNIQUE CALC KEY ON DEMOPROJ.CONSULTANT(CON_ID); CREATE UNIQUE CALC KEY ON DEMOPROJ.PROJECT(PROJ_ID); CREATE UNIQUE CALC KEY ON DEMOPROJ.SKILL(SKILL_ID); *********************************************************************
* Create unique indexes for tables in order in which they were defined
*********************************************************************
CREATE UNIQUE INDEX AS_EMPROJ_NDX ON
DEMOPROJ.ASSIGNMENT(EMP_ID,PROJ_ID);
CREATE UNIQUE INDEX EX_EMPSKILL_NDX ON
DEMOPROJ.EXPERTISE(EMP_ID, SKILL_ID);
*********************************************************************
* Create nonunique indexes for tables in order in which they
* were defined
*********************************************************************
CREATE INDEX CO_CODE_NDX ON DEMOEMPL.COVERAGE(PLAN_CODE)
IN SQLDEMO.INDXAREA;
CREATE INDEX DE_CODE_NDX ON DEMOEMPL.DEPARTMENT(DIV_CODE);
CREATE INDEX DI_HEAD_NDX ON DEMOEMPL.DIVISION(DIV_HEAD_ID);
CREATE INDEX DE_HEAD_NDX ON DEMOEMPL.DEPARTMENT(DEPT_HEAD_ID);
CREATE INDEX EM_MANAGER_NDX ON DEMOEMPL.EMPLOYEE(MANAGER_ID)
IN SQLDEMO.INDXAREA;
CREATE INDEX EM_NAME_NDX ON DEMOEMPL.EMPLOYEE(EMP_LNAME, EMP_FNAME)
IN SQLDEMO.INDXAREA;
CREATE INDEX EM_DEPT_NDX ON DEMOEMPL.EMPLOYEE(DEPT_ID)
IN SQLDEMO.INDXAREA;
CREATE INDEX IN_NAME_NDX ON DEMOEMPL.INSURANCE_PLAN(COMP_NAME)
COMPRESSED;
CREATE INDEX PO_JOB_NDX ON DEMOEMPL.POSITION(JOB_ID)
IN SQLDEMO.INDXAREA;
CREATE INDEX CN_NAME_NDX
ON DEMOPROJ.CONSULTANT(CON_LNAME,CON_FNAME);
*********************************************************************
* Create referential constraints
*********************************************************************
CREATE CONSTRAINT EMP_BENEFITS
DEMOEMPL.BENEFITS (EMP_ID) REFERENCES
DEMOEMPL.EMPLOYEE (EMP_ID)
LINKED CLUSTERED
ORDER BY (FISCAL_YEAR DESC);
CREATE CONSTRAINT INSPLAN_COVERAGE
DEMOEMPL.COVERAGE (PLAN_CODE) REFERENCES
DEMOEMPL.INSURANCE_PLAN (PLAN_CODE)
UNLINKED;
CREATE CONSTRAINT EMP_COVERAGE
DEMOEMPL.COVERAGE (EMP_ID) REFERENCES
DEMOEMPL.EMPLOYEE (EMP_ID)
LINKED CLUSTERED
ORDER BY ( PLAN_CODE) UNIQUE;
CREATE CONSTRAINT DIVISION_DEPT
DEMOEMPL.DEPARTMENT (DIV_CODE) REFERENCES
DEMOEMPL.DIVISION (DIV_CODE)
UNLINKED;
CREATE CONSTRAINT EMP_DEPT_HEAD
DEMOEMPL.DEPARTMENT (DEPT_HEAD_ID) REFERENCES
DEMOEMPL.EMPLOYEE (EMP_ID)
UNLINKED;
CREATE CONSTRAINT EMP_DIV_HEAD
DEMOEMPL.DIVISION (DIV_HEAD_ID) REFERENCES
DEMOEMPL.EMPLOYEE (EMP_ID)
UNLINKED;
CREATE CONSTRAINT DEPT_EMPLOYEE
DEMOEMPL.EMPLOYEE (DEPT_ID) REFERENCES
DEMOEMPL.DEPARTMENT (DEPT_ID)
UNLINKED;
CREATE CONSTRAINT MANAGER_EMP
DEMOEMPL.EMPLOYEE (MANAGER_ID) REFERENCES
DEMOEMPL.EMPLOYEE (EMP_ID)
UNLINKED;
CREATE CONSTRAINT SKILL_EXPERTISE
DEMOPROJ.EXPERTISE (SKILL_ID) REFERENCES
DEMOPROJ.SKILL (SKILL_ID)
LINKED CLUSTERED;
CREATE CONSTRAINT EMP_POSITION
DEMOEMPL.POSITION (EMP_ID) REFERENCES
DEMOEMPL.EMPLOYEE (EMP_ID)
LINKED CLUSTERED
ORDER BY (JOB_ID) UNIQUE;
CREATE CONSTRAINT JOB_POSITION
DEMOEMPL.POSITION (JOB_ID) REFERENCES
DEMOEMPL.JOB (JOB_ID)
UNLINKED;
CREATE CONSTRAINT PROJECT_ASSIGN
DEMOPROJ.ASSIGNMENT (PROJ_ID) REFERENCES
DEMOPROJ.PROJECT (PROJ_ID)
LINKED CLUSTERED;
CREATE CONSTRAINT PROJECT_CONSULT
DEMOPROJ.CONSULTANT (PROJ_ID) REFERENCES
DEMOPROJ.PROJECT (PROJ_ID)
LINKED INDEX
ORDER BY (PROJ_ID);
*********************************************************************
* Alter tables to remove default indexes as necessary
*********************************************************************
ALTER TABLE DEMOEMPL.COVERAGE
DROP DEFAULT INDEX;
ALTER TABLE DEMOEMPL.DEPARTMENT
DROP DEFAULT INDEX;
ALTER TABLE DEMOEMPL.DIVISION
DROP DEFAULT INDEX;
ALTER TABLE DEMOEMPL.EMPLOYEE
DROP DEFAULT INDEX;
ALTER TABLE DEMOEMPL.INSURANCE_PLAN
DROP DEFAULT INDEX;
ALTER TABLE DEMOEMPL.POSITION
DROP DEFAULT INDEX;
ALTER TABLE DEMOPROJ.ASSIGNMENT
DROP DEFAULT INDEX;
ALTER TABLE DEMOPROJ.CONSULTANT
DROP DEFAULT INDEX;
ALTER TABLE DEMOPROJ.EXPERTISE
DROP DEFAULT INDEX;
*********************************************************************
* Create views
*********************************************************************
CREATE VIEW DEMOEMPL.EMP_VACATION
(EMP_ID, DEPT_ID, VAC_TIME)
AS SELECT E.EMP_ID, DEPT_ID, SUM(VAC_ACCRUED) - SUM(VAC_TAKEN)
FROM DEMOEMPL.EMPLOYEE E, DEMOEMPL.BENEFITS B
WHERE E.EMP_ID = B.EMP_ID
GROUP BY DEPT_ID, E.EMP_ID;
CREATE VIEW DEMOEMPL.OPEN_POSITIONS
(JOB_ID, JOB_NAME, OPEN_POS)
AS SELECT J.JOB_ID, J.JOB_TITLE,
(J.NUM_OF_POSITIONS - COUNT(P.JOB_ID))
FROM DEMOEMPL.JOB J, DEMOEMPL.POSITION P
WHERE P.FINISH_DATE IS NULL AND P.JOB_ID = J.JOB_ID
PRESERVE DEMOEMPL.JOB
GROUP BY J.JOB_ID, J.JOB_TITLE, J.NUM_OF_POSITIONS
HAVING (J.NUM_OF_POSITIONS - COUNT(P.JOB_ID)) > 0;
*********************************************************************
* Create updatable views
*********************************************************************
CREATE VIEW DEMOEMPL.EMP_HOME_INFO
AS SELECT EMP_ID, EMP_LNAME, EMP_FNAME, STREET, CITY, STATE,
ZIP_CODE, PHONE
FROM DEMOEMPL.EMPLOYEE;
CREATE VIEW DEMOEMPL.EMP_WORK_INFO
AS SELECT EMP_ID, MANAGER_ID, START_DATE, TERMINATION_DATE
FROM DEMOEMPL.EMPLOYEE;
|
Copyright © 2013 CA.
All rights reserved.
|
|