Below is a listing for the SQL definition of the Commonweather Corporation database for the design shown.
Schema Statement
CREATE SCHEMA EMPSCHM; SET SESSION CURRENT SCHEMA EMPSCHM;
Table Statements
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 )
COVERAGE-TYPE CHAR(01) NOT NULL,
IN SQLDEMO.INS_DEMO_REGION;
CREATE TABLE DENTAL_CLAIM
(CLAIM_DATE DATE NOT NULL,
PATIENT_FIRST_NAME CHAR(10) ,
PATIENT_LAST_NAME CHAR(15) ,
PATIENT_BIRTH_DATE DATE ,
PATIENT_SEX CHAR(01) ,
RELATION_TO_EMPLOYEE CHAR(10) ,
EMP_ID UNSIGNED NUMERIC(4,0) NOT NULL,
PLAN_CODE CHAR(03) ,
DENTIST_FIRST_NAME CHAR(10) ,
DENTIST_LAST_NAME CHAR(15) ,
DENTIST_STREET CHAR(20) ,
DENTIST_CITY CHAR(15) ,
DENTIST_STATE CHAR(2) ,
DENTIST_ZIP_FIRST_FIVE CHAR(05) ,
DENTIST_ZIP_LAST_FOUR CHAR(04) ,
DENTIST_LICENSE_NUMBER UNSIGNED NUMERIC(6,0) )
IN SQLDEMO.INS_DEMO_REGION;
CREATE TABLE DENTAL_PROCEDURE
(EMP_ID UNSIGNED NUMERIC(4,0) NOT NULL,
PLAN_CODE CHAR(03) NOT NULL,
SERVICE_DATE DATE NOT NULL,
TOOTH_NUMBER UNSIGNED NUMERIC(2,0) ,
PROCEDURE_CODE UNSIGNED NUMERIC(4,0) NOT NULL,
FEE DECIMAL(9,2) ,
DESCRIPTION VARCHAR(60) )
IN SQLDEMO.INS_DEMO_REGION;
CREATE TABLE DEPARTMENT
(DEPT_ID UNSIGNED NUMERIC(4,0) NOT NULL,
DEPT_HEAD_ID UNSIGNED NUMERIC(4,0) ,
DEPT_NAME CHAR(40) NOT NULL)
IN SQLDEMO.ORG_DEMO_REGION;
CREATE TABLE EMPLOYEE
(EMP_ID UNSIGNED NUMERIC(4,0) NOT NULL,
EMP_FIRST_NAME CHAR(20) NOT NULL,
EMP_LAST_NAME CHAR(20) NOT NULL,
DEPT_ID UNSIGNED NUMERIC(4,0) NOT NULL,
OFFICE_CODE UNSIGNED NUMERIC(4,0) NOT NULL,
STREET CHAR(40) ,
CITY CHAR(20) NOT NULL,
STATE CHAR(02) NOT NULL,
ZIP_FIRST_FIVE CHAR(05) NOT NULL,
ZIP_LAST_FOUR CHAR(04) NOT NULL,
PHONE CHAR(10) ,
STATUS CHAR(01) 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 ('01', '02', '03', '04', '05') ) ) )
IN SQLDEMO.EMP_DEMO_REGION;
CREATE TABLE EMPOSITION
(EMP_ID UNSIGNED NUMERIC(4,0) NOT NULL,
JOB_ID UNSIGNED NUMERIC(4,0) NOT NULL,
START_DATE DATE NOT NULL,
FINISH_DATE DATE ,
SALARY_GRADE UNSIGNED NUMERIC(2,0) )
IN SQLDEMO.EMP_DEMO_REGION;
CREATE TABLE HOSPITAL_CLAIM
(CLAIM_DATE DATE NOT NULL,
PATIENT_FIRST_NAME CHAR(10) ,
PATIENT_LAST_NAME CHAR(15) ,
PATIENT_BIRTH_DATE DATE ,
PATIENT_SEX CHAR(01) ,
RELATION_TO_EMPLOYEE CHAR(10) ,
EMP_ID UNSIGNED NUMERIC(4,0) NOT NULL,
PLAN_CODE CHAR(03) ,
HOSPITAL_NAME CHAR(25) ,
HOSPITAL_STREET CHAR(20) ,
HOSPITAL_CITY CHAR(15) ,
HOSPITAL_STATE CHAR(2) ,
HOSPITAL_ZIP_FIRST_FIVE CHAR(05) ,
HOSPITAL_ZIP_LAST_FOUR CHAR(04) ,
ADMIT_DATE DATE ,
DISCHARGE_DATE DATE ,
DIAGNOSIS CHAR(120) ,
WARD_DAYS UNSIGNED NUMERIC(5,0) ,
WARD_RATE DECIMAL(9,2) ,
WARD_TOTAL DECIMAL(9,2) ,
SEMI_DAYS UNSIGNED NUMERIC(5,0) ,
SEMI_RATE DECIMAL(9,2) ,
SEMI_TOTAL DECIMAL(9,2) ,
DELIVERY_COST DECIMAL(9,2) ,
ANESTHESIA_COST DECIMAL(9,2) ,
LAB_COST DECIMAL(9,2) )
IN SQLDEMO.INS_DEMO_REGION;
CREATE TABLE INSURANCE_PLAN
(PLAN_CODE CHAR(03) NOT NULL,
COMP_NAME CHAR(40) NOT NULL,
STREET CHAR(20) ,
CITY CHAR(15) NOT NULL,
STATE CHAR(02) NOT NULL,
ZIP_FIRST_FIVE CHAR(05) ,
ZIP_LAST_FOUR CHAR(04) ,
PHONE CHAR(10) NOT NULL,
GROUP_NUMBER UNSIGNED NUMERIC(6,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) )
IN SQLDEMO.INS_DEMO_REGION;
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(3,0) ,
NUM_OPEN UNSIGNED DECIMAL(3,0) ,
EFF_DATE DATE ,
JOB_DESC_LINE_1 VARCHAR(60) ,
JOB_DESC_LINE_2 VARCHAR(60) ,
REQUIREMENTS VARCHAR(120) ,
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) )
IN SQLDEMO.ORG_DEMO_REGION;
CREATE TABLE EXPERTISE
(EMP_ID UNSIGNED NUMERIC(4,0) NOT NULL,
SKILL_CODE UNSIGNED NUMERIC(4,0) NOT NULL,
SKILL_LEVEL CHAR(02) ,
EXP_DATE DATE ,
CHECK ( SKILL_LEVEL IN ('01', '02', '03', '04', '05') ) )
IN PROJSEG.EMP_DEMO_REGION;
CREATE TABLE NON_HOSP_CLAIM
(CLAIM_DATE DATE NOT NULL,
PATIENT_FIRST_NAME CHAR(10) ,
PATIENT_LAST_NAME CHAR(15) ,
PATIENT_BIRTH_DATE DATE ,
PATIENT_SEX CHAR(01) ,
RELATION_TO_EMPLOYEE CHAR(10) ,
EMP_ID UNSIGNED NUMERIC(4,0) NOT NULL,
PLAN_CODE CHAR(03) ,
PHYS_FIRST_NAME CHAR(10) ,
PHYS_LAST_NAME CHAR(15) ,
PHYS_STREET CHAR(20) ,
PHYS_CITE CHAR(15) ,
PHYS_STATE CHAR(2) ,
PHYS_ZIP_FIRST_FIVE CHAR(05) ,
PHYS_ZIP_LAST_FOUR CHAR(04) ,
PHYSICIAN_ID UNSIGNED NUMERIC(6,0) ,
DIAGNOSIS VARCHAR(120) )
IN SQLDEMO.INS_DEMO_REGION;
CREATE TABLE NON_HOSP_PROCEDURE
(EMP_ID UNSIGNED NUMERIC(4,0) NOT NULL,
PLAN_CODE CHAR(03) NOT NULL,
SERVICE_DATE DATE NOT NULL,
PROCEDURE_CODE UNSIGNED NUMERIC(4,0) NOT NULL,
FEE DECIMAL(9,2) ,
DESCRIPTION VARCHAR(60) )
IN SQLDEMO.INS_DEMO_REGION;
CREATE TABLE OFFICE
(OFFICE_CODE UNSIGNED NUMERIC(4,0) NOT NULL,
STREET CHAR(20) ,
CITY CHAR(15) ,
STATE CHAR(2) ,
ZIP_FIRST_FIVE CHAR(05) ,
ZIP_LAST_FOUR CHAR(04) ,
SPEED_DIAL CHAR(03) ,
AREA_CODE CHAR(03) ,
PHONE_1 UNSIGNED NUMERIC(7,0) ,
PHONE_2 UNSIGNED NUMERIC(7,0) ,
PHONE_3 UNSIGNED NUMERIC(7,0) )
IN SQLDEMO.ORG_DEMO_REGION;
CREATE TABLE PROJECT
(PROJECT_CODE UNSIGNED NUMERIC(4,0) NOT NULL,
DESCRIPTION CHAR(40) ,
EST_BEGIN_DATE DATE ,
ACT_BEGIN_DATE DATE ,
EST_END_DATE DATE ,
ACT_END_DATE DATE ,
LDR_EMP_ID UNSIGNED NUMERIC(4,0) )
IN SQLDEMO.EMP_DEMO_REGION;
CREATE TABLE SKILL
(SKILL_CODE UNSIGNED NUMERIC(4,0) NOT NULL,
SKILL_NAME CHAR(20) NOT NULL,
SKILL_DESC VARCHAR(60) )
IN PROJSEG.ORG_DEMO_REGION;
CREATE TABLE WORKER
(PROJECT_CODE UNSIGNED NUMERIC(4,0) NOT NULL,
EMP_ID UNSIGNED NUMERIC(4,0) NOT NULL,
BEGIN_DATE DATE ,
END_DATE DATE )
IN SQLDEMO.EMP_DEMO_REGION;
CALC Key Statements
CREATE UNIQUE CALC KEY ON DEPARTMENT (DEPT_ID); CREATE UNIQUE CALC KEY ON EMPLOYEE (EMP_ID); CREATE UNIQUE CALC KEY ON INSURANCE_PLAN (PLAN_CODE); CREATE UNIQUE CALC KEY ON JOB (JOB_ID); CREATE UNIQUE CALC KEY ON SKILL (SKILL_CODE); CREATE UNIQUE CALC KEY ON PROJECT (PROJECT_CODE); CREATE UNIQUE CALC KEY ON OFFICE (OFFICE_CODE);
Index Statements
-----------------------------------------------------------------------------
-- Create unique indexes
-----------------------------------------------------------------------------
CREATE UNIQUE INDEX SKILL_NAME_NDX ON SKILL(SKILL_NAME);
CREATE UNIQUE INDEX JOB_TITLE_NDX ON JOB(JOB_TITLE);
CREATE UNIQUE INDEX COV_NDX ON COVERAGE (PLAN_CODE, COVERAGE_TYPE, EMP_ID);
-----------------------------------------------------------------------------
-- Create nonunique indexes
-----------------------------------------------------------------------------
CREATE INDEX LNAME_NDX ON EMPLOYEE(EMP_LAST_NAME, EMP_FIRST_NAME)
IN SQLDEMO.INDXAREA;
Constraint Statements
----------------------------------------------------------------------
-- Create referential constraints
----------------------------------------------------------------------
CREATE CONSTRAINT EMP_COVERAGE
COVERAGE (EMP_ID) REFERENCES
EMPLOYEE (EMP_ID)
UNLINKED CLUSTERED;
CREATE CONSTRAINT DEPT_EMPLOYEE
EMPLOYEE (DEPT_ID) REFERENCES
DEPARTMENT (DEPT_ID)
LINKED INDEX
ORDER BY (EMP_LNAME, EMP_FNAME);
CREATE CONSTRAINT MANAGES_REPORTS_TO
EMPLOYEE (SUPR_EMP_ID) REFERENCES
EMPLOYEE (EMP_ID)
LINKED INDEX;
CREATE CONSTRAINT SKILL_EXPERTISE
EXPERTISE (SKILL_CODE) REFERENCES
SKILL (SKILL_CODE)
LINKED INDEX
ORDER BY (SKILL_LEVEL DESC);
CREATE CONSTRAINT EMP_EMPOSITION
EMPOSITION (EMP_ID) REFERENCES
EMPLOYEE (EMP_ID)
LINKED CLUSTERED
ORDER BY (START_DATE DESC) UNIQUE;
CREATE CONSTRAINT JOB_EMPOSITION
EMPOSITION (JOB_ID) REFERENCES
JOB (JOB_ID)
LINKED INDEX;
CREATE CONSTRAINT OFFICE_EMPLOYEE
EMPLOYEE (OFFICE_CODE) REFERENCES
OFFICE (OFFICE_CODE)
LINKED INDEX
ORDER BY (EMP_LNAME, EMP_FNAME);
CREATE CONSTRAINT EMP_EXPERTISE
EXPERTISE (EMP_ID) REFERENCES
EMPLOYEE (EMP_ID)
LINKED CLUSTERED
ORDER BY (SKILL_CODE DESC) UNIQUE;
CREATE CONSTRAINT EMP_PROJECT
EMPLOYEE (LDR_EMP_ID) REFERENCES
PROJECT (EMP_ID)
LINKED INDEX;
CREATE CONSTRAINT PROJECT_WORKER
WORKER (PROJECT_CODE) REFERENCES
PROJECT (PROJECT_CODE)
LINKED CLUSTERED;
CREATE CONSTRAINT EMP_WORKER
WORKER (EMP_ID) REFERENCES
EMPLOYEE (EMP_ID)
LINKED INDEX;
CREATE CONSTRAINT COVERAGE_NHC
NON_HOSP_CLAIM (EMP_ID, PLAN_CODE) REFERENCES
COVERAGE (EMP_ID, PLAN_CODE)
LINKED CLUSTERED;
CREATE CONSTRAINT COVERAGE_HC
HOSPITAL_CLAIM (EMP_ID, PLAN_CODE) REFERENCES
COVERAGE (EMP_ID, PLAN_CODE)
LINKED CLUSTERED;
CREATE CONSTRAINT COVERAGE_DC
DENTAL_CLAIM (EMP_ID, PLAN_CODE) REFERENCES
COVERAGE (EMP_ID, PLAN_CODE)
LINKED CLUSTERED;
CREATE CONSTRAINT DCLAIM_PROC
DENTAL_PROCEDURE (DENTAL_CLAIM_ID) REFERENCES
DENTAL_CLAIM (DENTAL_CLAIM_ID)
LINKED CLUSTERED;
CREATE CONSTRAINT NHCLAIM_PROC
NON_HOSP_PROCEDURE (NON_HOSP_CLAIM_ID) REFERENCES
NON_HOSP_CLAIM (NON_HOSP_CLAIM_ID)
LINKED CLUSTERED;
Remove Default Indexes
ALTER TABLE COVERAGE
DROP DEFAULT INDEX;
ALTER TABLE DEPARTMENT
DROP DEFAULT INDEX;
ALTER TABLE EMPLOYEE
DROP DEFAULT INDEX;
ALTER TABLE INSURANCE_PLAN
DROP DEFAULT INDEX;
ALTER TABLE EMPOSITION
DROP DEFAULT INDEX;
ALTER TABLE EXPERTISE
DROP DEFAULT INDEX;
ALTER TABLE SALARY_GRADE
DROP DEFAULT INDEX;
ALTER TABLE PROJECT
DROP DEFAULT INDEX;
ALTER TABLE WORKER
DROP DEFAULT INDEX;
ALTER TABLE PHONE
DROP DEFAULT INDEX;
ALTER TABLE DENTAL_PROCEDURE
DROP DEFAULT INDEX;
ALTER TABLE NON_HOSP_PROCEDURE
DROP DEFAULT INDEX;
ALTER TABLE OFFICE
DROP DEFAULT INDEX;
ALTER TABLE SKILL
DROP DEFAULT INDEX;
ALTER TABLE DENTAL_CLAIM
DROP DEFAULT INDEX;
ALTER TABLE HOSPITAL_CLAIM
DROP DEFAULT INDEX;
ALTER TABLE NON_HOSP_CLAIM
DROP DEFAULT INDEX;
|
Copyright © 2014 CA.
All rights reserved.
|
|