Sample SQL Database Definition
Sample SQL Database Definition
Sample Database Definition
CREATE SCHEMA DEMOEMPL;
SET SESSION CURRENT SCHEMA 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 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;
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 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 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 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 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 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 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 © 2014 CA.
All rights reserved.
|
|