-- Copyright © Oracle Corporation 1995. All Rights Reserved. -- -- PERSONNEL definitions: domains, tables, views, and comments. -- -- ************************************************************* -- -- *** Create domains *** -- PRINT 'Creating domains for the sample database'; -- CREATE DOMAIN ID_DOM CHAR(5); -- CREATE DOMAIN LAST_NAME_DOM CHAR(14); -- CREATE DOMAIN FIRST_NAME_DOM CHAR(10); -- CREATE DOMAIN MIDDLE_INITIAL_DOM CHAR(1) DEFAULT ' ' EDIT STRING IS 'X'; -- CREATE DOMAIN ADDRESS_DATA_1_DOM CHAR(25) DEFAULT ' '; -- CREATE DOMAIN ADDRESS_DATA_2_DOM CHAR(20) DEFAULT ' '; -- CREATE DOMAIN CITY_DOM CHAR(20) DEFAULT ' '; -- CREATE DOMAIN STATE_DOM CHAR(2) DEFAULT ' '; -- CREATE DOMAIN POSTAL_CODE_DOM CHAR(5) DEFAULT ' '; -- CREATE DOMAIN SEX_DOM CHAR(1) DEFAULT '?'; -- CREATE DOMAIN DATE_DOM DATE EDIT STRING IS 'DD-MMM-YYYY'; -- CREATE DOMAIN SALARY_DOM INTEGER(2) EDIT STRING IS '$$$$,$$9.99'; -- CREATE DOMAIN DEPARTMENT_CODE_DOM CHAR(4) DEFAULT 'None'; -- CREATE DOMAIN JOB_CODE_DOM CHAR(4) DEFAULT 'None'; -- CREATE DOMAIN WAGE_CLASS_DOM CHAR(1); -- CREATE DOMAIN JOB_TITLE_DOM CHAR(20) DEFAULT 'None'; -- CREATE DOMAIN DEPARTMENT_NAME_DOM CHAR(30) DEFAULT 'None'; -- CREATE DOMAIN BUDGET_DOM INTEGER EDIT STRING IS '$$$,$$$,$$$'; -- CREATE DOMAIN COLLEGE_NAME_DOM CHAR(25); -- CREATE DOMAIN COLLEGE_CODE_DOM CHAR(4); -- CREATE DOMAIN YEAR_DOM SMALLINT; -- CREATE DOMAIN DEGREE_DOM CHAR(3); -- CREATE DOMAIN DEGREE_FIELD_DOM CHAR(15) DEFAULT 'Unknown'; -- CREATE DOMAIN STATUS_CODE_DOM CHAR(1) DEFAULT 'N'; -- CREATE DOMAIN STATUS_NAME_DOM CHAR(8); -- CREATE DOMAIN STATUS_DESC_DOM CHAR(14); -- CREATE DOMAIN CANDIDATE_STATUS_DOM VARCHAR(255); -- CREATE DOMAIN RESUME_DOM LIST OF BYTE VARYING; -- -- ************************************************************* -- -- *** Create tables *** -- print 'Creating tables for the sample database'; -- -- WORK_STATUS table: -- SET DIALECT 'SQL92'; CREATE TABLE WORK_STATUS ( STATUS_CODE STATUS_CODE_DOM CONSTRAINT WS_SC_PRIMARY PRIMARY KEY, STATUS_NAME STATUS_NAME_DOM, CONSTRAINT STATUS_NAME_VALUES CHECK ( STATUS_NAME IN ('ACTIVE', 'INACTIVE') OR STATUS_NAME IS NULL ), STATUS_TYPE STATUS_DESC_DOM, CONSTRAINT STATUS_TYPE_VALUES CHECK ( STATUS_TYPE IN ('RECORD EXPIRED', 'FULL TIME', 'PART TIME') OR STATUS_TYPE IS NULL ) ); -- -- EMPLOYEES table -- CREATE TABLE EMPLOYEES ( EMPLOYEE_ID ID_DOM CONSTRAINT EMPL_PRIMARY PRIMARY KEY, LAST_NAME LAST_NAME_DOM, FIRST_NAME FIRST_NAME_DOM, MIDDLE_INITIAL MIDDLE_INITIAL_DOM, ADDRESS_DATA_1 ADDRESS_DATA_1_DOM, ADDRESS_DATA_2 ADDRESS_DATA_2_DOM, CITY CITY_DOM, STATE STATE_DOM, POSTAL_CODE POSTAL_CODE_DOM, SEX SEX_DOM, CONSTRAINT EMP_SEX_VALUES CHECK ( SEX IN ('M', 'F', '?') ), BIRTHDAY DATE_DOM, STATUS_CODE STATUS_CODE_DOM, CONSTRAINT EMP_STATUS_CODE_VALUES CHECK ( STATUS_CODE IN ('0', '1', '2', 'N') ) ); -- -- JOBS table: -- CREATE TABLE JOBS ( JOB_CODE JOB_CODE_DOM CONSTRAINT JOB_CODE_PRIMARY PRIMARY KEY, WAGE_CLASS WAGE_CLASS_DOM, CONSTRAINT WAGE_CLASS_VALUES CHECK ( WAGE_CLASS IN ('1', '2', '3', '4') OR WAGE_CLASS IS NULL ), JOB_TITLE JOB_TITLE_DOM, MINIMUM_SALARY SALARY_DOM, MAXIMUM_SALARY SALARY_DOM ); -- -- DEPARTMENTS table: -- CREATE TABLE DEPARTMENTS ( DEPARTMENT_CODE DEPARTMENT_CODE_DOM CONSTRAINT DEPT_PRIMARY PRIMARY KEY, DEPARTMENT_NAME DEPARTMENT_NAME_DOM, MANAGER_ID ID_DOM, BUDGET_PROJECTED BUDGET_DOM, BUDGET_ACTUAL BUDGET_DOM ); -- -- JOB_HISTORY table: -- CREATE TABLE JOB_HISTORY ( EMPLOYEE_ID ID_DOM CONSTRAINT JH_EMPL_ID_FOREIGN REFERENCES EMPLOYEES (EMPLOYEE_ID), ----------------------------------------------- -- You can have many foreign key definitions to enforce referential integrity; -- that is, there must be a column with a matching value in another table. -- Note that foreign key designations assume a certain order for loading -- data; for example, you cannot store data into the JOB_HISTORY or -- SALARY_HISTORY table until the EMPLOYEES table is loaded. ------------------------------------------------ JOB_CODE JOB_CODE_DOM CONSTRAINT JH_JOB_CODE_FOREIGN REFERENCES JOBS (JOB_CODE), JOB_START DATE_DOM, JOB_END DATE_DOM, DEPARTMENT_CODE DEPARTMENT_CODE_DOM CONSTRAINT JH_DEPT_CODE_FOREIGN REFERENCES DEPARTMENTS (DEPARTMENT_CODE), SUPERVISOR_ID ID_DOM ); -- -- SALARY_HISTORY table: -- CREATE TABLE SALARY_HISTORY ( EMPLOYEE_ID ID_DOM CONSTRAINT SH_EMPL_ID_FOREIGN REFERENCES EMPLOYEES (EMPLOYEE_ID), SALARY_AMOUNT SALARY_DOM, SALARY_START DATE_DOM, SALARY_END DATE_DOM ); -- -- COLLEGES table: -- CREATE TABLE COLLEGES ( COLLEGE_CODE COLLEGE_CODE_DOM CONSTRAINT COLLEGE_CODE_PRIMARY PRIMARY KEY, COLLEGE_NAME COLLEGE_NAME_DOM, CITY CITY_DOM, STATE STATE_DOM, POSTAL_CODE POSTAL_CODE_DOM ); -- -- DEGREES table: -- CREATE TABLE DEGREES ( EMPLOYEE_ID ID_DOM CONSTRAINT DG_EMPL_ID_FOREIGN REFERENCES EMPLOYEES (EMPLOYEE_ID), COLLEGE_CODE COLLEGE_CODE_DOM CONSTRAINT DG_COLLEGE_CODE_FOREIGN REFERENCES COLLEGES (COLLEGE_CODE), YEAR_GIVEN YEAR_DOM, DEGREE DEGREE_DOM, CONSTRAINT DEG_DEGREE_VALUES CHECK ( DEGREE IN ('BA','BS','MA','MS','AA','PhD') OR DEGREE IS NULL ), DEGREE_FIELD DEGREE_FIELD_DOM ); -- -- CANDIDATES table: -- CREATE TABLE CANDIDATES ( LAST_NAME LAST_NAME_DOM CONSTRAINT CANDIDATES_LAST_NAME_NOT_NULL NOT NULL, FIRST_NAME FIRST_NAME_DOM, MIDDLE_INITIAL MIDDLE_INITIAL_DOM, CANDIDATE_STATUS CANDIDATE_STATUS_DOM ); -- -- RESUMES table: -- -- Foreign key constraint defined because resumes are kept only -- for actual employees for use in Human Resource Management -- applications (identifying employees with special backgrounds -- and skills for possible job assignments or promotions). -- SET DIALECT 'SQLV40'; CREATE TABLE RESUMES ( EMPLOYEE_ID ID_DOM CONSTRAINT RES_UNIQUE UNIQUE NOT DEFERRABLE CONSTRAINT RES_EMPL_ID_FOREIGN REFERENCES EMPLOYEES (EMPLOYEE_ID) NOT DEFERRABLE, RESUME RESUME_DOM ); SET DIALECT 'SQL92'; -- -- ************************************************************** -- -- *** Define three views to get current employee information *** -- print 'Creating views for the sample database'; -- -- Current job information -- CREATE VIEW CURRENT_JOB AS SELECT E.LAST_NAME, E.FIRST_NAME, E.EMPLOYEE_ID, JH.JOB_CODE, JH.DEPARTMENT_CODE, JH.SUPERVISOR_ID, JH.JOB_START FROM JOB_HISTORY JH, EMPLOYEES E WHERE JH.EMPLOYEE_ID = E.EMPLOYEE_ID AND JH.JOB_END IS NULL; -- -- -- Current salary information -- CREATE VIEW CURRENT_SALARY AS SELECT E.LAST_NAME, E.FIRST_NAME, E.EMPLOYEE_ID, SH.SALARY_START, SH.SALARY_AMOUNT FROM SALARY_HISTORY SH, EMPLOYEES E WHERE SH.EMPLOYEE_ID = E.EMPLOYEE_ID AND SH.SALARY_END IS NULL; -- -- -- Current salary and job information -- CREATE VIEW CURRENT_INFO (LAST_NAME, FIRST_NAME, ID, DEPARTMENT, JOB, JSTART, SSTART, SALARY) AS SELECT CJ.LAST_NAME, CJ.FIRST_NAME, CJ.EMPLOYEE_ID, D.DEPARTMENT_NAME, J.JOB_TITLE, CJ.JOB_START, CS.SALARY_START, CS.SALARY_AMOUNT FROM CURRENT_JOB CJ, DEPARTMENTS D, JOBS J, CURRENT_SALARY CS WHERE CJ.DEPARTMENT_CODE = D.DEPARTMENT_CODE AND CJ.JOB_CODE = J.JOB_CODE AND CJ.EMPLOYEE_ID = CS.EMPLOYEE_ID; COMMIT; -- -- -- Add comments -- print 'Adding comments for domain and table definitions'; -- -- SET TRANSACTION READ WRITE; COMMENT ON DOMAIN ID_DOM IS 'standard definition of employee id'; COMMENT ON DOMAIN LAST_NAME_DOM IS 'standard definition of last name'; COMMENT ON DOMAIN FIRST_NAME_DOM IS 'standard definition of first name'; COMMENT ON DOMAIN MIDDLE_INITIAL_DOM IS 'standard definition of middle initial'; COMMENT ON DOMAIN ADDRESS_DATA_1_DOM IS 'standard definition for street addresses'; COMMENT ON DOMAIN ADDRESS_DATA_2_DOM IS 'standard definition for apartments, suites, etc.'; COMMENT ON DOMAIN CITY_DOM IS 'standard definition of city or town'; COMMENT ON DOMAIN STATE_DOM IS 'standard definition of state'; COMMENT ON DOMAIN POSTAL_CODE_DOM IS 'standard definition of ZIP'; COMMENT ON DOMAIN SEX_DOM IS 'standard definition for sex'; COMMENT ON DOMAIN DATE_DOM IS 'standard definition for complete dates'; COMMENT ON DOMAIN SALARY_DOM IS 'standard definition of salary'; COMMENT ON DOMAIN DEPARTMENT_CODE_DOM IS 'standard definition of department code'; COMMENT ON DOMAIN JOB_CODE_DOM IS 'standard definition of job code'; COMMENT ON DOMAIN WAGE_CLASS_DOM IS 'standard definition for wage classification'; COMMENT ON DOMAIN JOB_TITLE_DOM IS 'standard definition for job title'; COMMENT ON DOMAIN DEPARTMENT_NAME_DOM IS 'standard definition for department name'; COMMENT ON DOMAIN BUDGET_DOM IS 'standard definition for departmental budget'; COMMENT ON DOMAIN COLLEGE_NAME_DOM IS 'standard definition for college name'; COMMENT ON DOMAIN COLLEGE_CODE_DOM IS 'standard definition of college code'; COMMENT ON DOMAIN YEAR_DOM IS 'standard definition for year-only date values'; COMMENT ON DOMAIN DEGREE_DOM IS 'standard definition for the kind of college degree awarded'; COMMENT ON DOMAIN DEGREE_FIELD_DOM IS 'standard definition for description of college degree field'; COMMENT ON DOMAIN STATUS_CODE_DOM IS 'standard definition of employment status codes'; COMMENT ON DOMAIN STATUS_NAME_DOM IS 'standard definition for active/inactive description'; COMMENT ON DOMAIN STATUS_DESC_DOM IS 'standard definition for full-time/part-time description'; COMMENT ON DOMAIN RESUME_DOM IS 'current resume of employee'; COMMENT ON TABLE EMPLOYEES IS 'personal information about each employee'; COMMENT ON TABLE JOBS IS 'information about different kinds of jobs'; COMMENT ON TABLE DEPARTMENTS IS 'information about departments in corporation'; COMMENT ON TABLE JOB_HISTORY IS 'jobs formerly and currently held by an employee'; COMMENT ON TABLE SALARY_HISTORY IS 'salaries formerly and currently given an employee'; COMMENT ON TABLE COLLEGES IS 'names and addresses of colleges attended by employees'; COMMENT ON TABLE DEGREES IS 'college degrees awarded an employee'; COMMENT ON TABLE WORK_STATUS IS 'information related to work status codes'; COMMENT ON TABLE RESUMES IS 'resumes of each employee'; COMMIT; -- -- -- ************************************************************* -- print 'Storing sample data in several tables. (Other tables to'; print 'be loaded later by separate programs.)'; PRINT ' '; -- -- *** Store three rows needed for WORK_STATUS table -- SET TRANSACTION READ WRITE RESERVING WORK_STATUS FOR SHARED WRITE; INSERT INTO WORK_STATUS (STATUS_CODE, STATUS_NAME, STATUS_TYPE) VALUES ('0', 'INACTIVE', 'RECORD EXPIRED'); INSERT INTO WORK_STATUS (STATUS_CODE, STATUS_NAME, STATUS_TYPE) VALUE ('1', 'ACTIVE', 'FULL TIME'); INSERT INTO WORK_STATUS (STATUS_CODE, STATUS_NAME, STATUS_TYPE) VALUES ('2', 'ACTIVE', 'PART TIME'); COMMIT; -- -- -- *** Store three rows needed for CANDIDATES table -- SET TRANSACTION READ WRITE RESERVING CANDIDATES FOR SHARED WRITE; INSERT INTO CANDIDATES (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, CANDIDATE_STATUS) VALUES ('Wilson', 'Oscar', 'M', 'Available part time Oct.-Dec. and full time starting in January'); INSERT INTO CANDIDATES (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, CANDIDATE_STATUS) VALUES ('Schwartz', 'Trixie', 'R', 'Available second week in November. Do not contact her at current job.'); INSERT INTO CANDIDATES (LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, CANDIDATE_STATUS) VALUES ('Boswick', 'Fred', 'W', 'Engineering Dept. not impressed. No offer made'); COMMIT; -- EXIT;