-- Copyright © Oracle Corporation 1995. All Rights Reserved. -- -- MF_PERSONNEL definitions: domains, tables, storage maps, -- and hashed indexes (storage of some records via hashed indexes). -- One sorted index is defined in this procedure (prior to running -- load programs) so that a table is stored via its sorted index. -- Other sorted indexes are defined after the load programs -- have finished. -- -- ************************************************************* -- -- *** 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: -- CREATE TABLE WORK_STATUS ( STATUS_CODE STATUS_CODE_DOM PRIMARY KEY DEFERRABLE, STATUS_NAME STATUS_NAME_DOM, CONSTRAINT STATUS_NAME_VALUES CHECK ( STATUS_NAME IN ('ACTIVE', 'INACTIVE') OR STATUS_NAME IS NULL ) DEFERRABLE, STATUS_TYPE STATUS_DESC_DOM, CONSTRAINT STATUS_TYPE_VALUES CHECK ( STATUS_TYPE IN ('RECORD EXPIRED', 'FULL TIME', 'PART TIME') OR STATUS_TYPE IS NULL ) DEFERRABLE ); -- -- Employees table -- CREATE TABLE EMPLOYEES ( EMPLOYEE_ID ID_DOM PRIMARY KEY DEFERRABLE, 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', '?') ) DEFERRABLE, BIRTHDAY DATE_DOM, STATUS_CODE STATUS_CODE_DOM, CONSTRAINT EMP_STATUS_CODE_VALUES CHECK ( STATUS_CODE IN ('0', '1', '2', 'N') ) DEFERRABLE ); -- -- JOBS table: -- CREATE TABLE JOBS ( JOB_CODE JOB_CODE_DOM PRIMARY KEY DEFERRABLE, WAGE_CLASS WAGE_CLASS_DOM, CONSTRAINT WAGE_CLASS_VALUES CHECK ( WAGE_CLASS IN ('1', '2', '3', '4') OR WAGE_CLASS IS NULL ) DEFERRABLE, JOB_TITLE JOB_TITLE_DOM, MINIMUM_SALARY SALARY_DOM, MAXIMUM_SALARY SALARY_DOM ); -- -- DEPARTMENTS table: -- CREATE TABLE DEPARTMENTS ( DEPARTMENT_CODE DEPARTMENT_CODE_DOM PRIMARY KEY DEFERRABLE, 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 REFERENCES EMPLOYEES (EMPLOYEE_ID) DEFERRABLE, ----------------------------------------------- -- 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 REFERENCES JOBS (JOB_CODE) DEFERRABLE, JOB_START DATE_DOM, JOB_END DATE_DOM, DEPARTMENT_CODE DEPARTMENT_CODE_DOM REFERENCES DEPARTMENTS (DEPARTMENT_CODE) DEFERRABLE, SUPERVISOR_ID ID_DOM ); -- -- SALARY_HISTORY table: -- CREATE TABLE SALARY_HISTORY ( EMPLOYEE_ID ID_DOM REFERENCES EMPLOYEES (EMPLOYEE_ID) DEFERRABLE, SALARY_AMOUNT SALARY_DOM, SALARY_START DATE_DOM, SALARY_END DATE_DOM ); -- -- COLLEGES table: -- CREATE TABLE COLLEGES ( COLLEGE_CODE COLLEGE_CODE_DOM PRIMARY KEY DEFERRABLE, 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 REFERENCES EMPLOYEES (EMPLOYEE_ID) DEFERRABLE, COLLEGE_CODE COLLEGE_CODE_DOM REFERENCES COLLEGES (COLLEGE_CODE) DEFERRABLE, YEAR_GIVEN YEAR_DOM, DEGREE DEGREE_DOM, CONSTRAINT DEG_DEGREE_VALUES CHECK ( DEGREE IN ('BA','BS','MA','MS','AA','PhD') OR DEGREE IS NULL ) DEFERRABLE, DEGREE_FIELD DEGREE_FIELD_DOM ); -- -- CANDIDATES table: -- CREATE TABLE CANDIDATES ( LAST_NAME LAST_NAME_DOM CONSTRAINT CANDIDATES_LAST_NAME_NOT_NULL NOT NULL DEFERRABLE, 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). -- CREATE TABLE RESUMES (EMPLOYEE_ID ID_DOM UNIQUE DEFERRABLE REFERENCES EMPLOYEES (EMPLOYEE_ID) DEFERRABLE, RESUME RESUME_DOM ); -- -- ************************************************************** -- -- *** 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 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'; COMMIT; -- -- -- -- +++ Begin Multifile structures -- -- Horizontal partitioning and hashed access. Create multiple storage maps -- using multiple files to increase overall I/O throughput. -- -- Two HASHED indexes, each using EMPLOYEE_ID as the key, are defined -- for the EMPLOYEES and JOB_HISTORY relations to alleviate record locking -- conflicts (and locking conflicts seen on SORTED indexes), and to serve as -- a record placement mechanism that enhances data retrieval. -- -- That is, EMPLOYEE records are clustered with JOB_HISTORY records in order -- to place parent/child records together. At the same time, the EMPLOYEE -- and JOB_HISTORY records are being stored together across three files to -- better distribute the records. -- -- DEPARTMENT records are stored via a SORTED index. -- -- Other tables share the same storage area file, while some tables -- are defined as being the only record type in a particular file. -- -- print 'Creating hashed indexes and storage maps for MF_PERSONNEL'; -- CREATE UNIQUE INDEX EMPLOYEES_HASH -- Hashed index for EMPLOYEES table ON EMPLOYEES (EMPLOYEE_ID) TYPE IS HASHED STORE USING (EMPLOYEE_ID) IN EMPIDS_LOW WITH LIMIT OF ('00200') IN EMPIDS_MID WITH LIMIT OF ('00400') OTHERWISE IN EMPIDS_OVER; -- CREATE STORAGE MAP EMPLOYEES_MAP FOR EMPLOYEES -- Employees partitioned by 200 400 * STORE USING (EMPLOYEE_ID) IN EMPIDS_LOW WITH LIMIT OF ('00200') IN EMPIDS_MID WITH LIMIT OF ('00400') OTHERWISE IN EMPIDS_OVER PLACEMENT VIA INDEX EMPLOYEES_HASH; -- -- Record clustering to place Employees and Job_history records with the same -- employee_id on the same database page. -- -- CREATE INDEX JOB_HISTORY_HASH --Hashed index for JOB_HISTORY table ON JOB_HISTORY (EMPLOYEE_ID) TYPE IS HASHED STORE USING (EMPLOYEE_ID) IN EMPIDS_LOW WITH LIMIT OF ('00200') IN EMPIDS_MID WITH LIMIT OF ('00400') OTHERWISE IN EMPIDS_OVER; -- CREATE STORAGE MAP JOB_HISTORY_MAP FOR JOB_HISTORY -- Employees partitioned by 200 400 * STORE USING (EMPLOYEE_ID) IN EMPIDS_LOW WITH LIMIT OF ('00200') IN EMPIDS_MID WITH LIMIT OF ('00400') OTHERWISE IN EMPIDS_OVER PLACEMENT VIA INDEX JOB_HISTORY_HASH; -- -- Sorted index to store departments records ordered by department_code -- to aid in record retrieval by groups of sequential department codes. -- -- print 'Creating one sorted index prior to starting load programs'; -- CREATE UNIQUE INDEX DEPARTMENTS_INDEX --Sorted index for DEPARTMENTS table ON DEPARTMENTS (DEPARTMENT_CODE) TYPE IS SORTED STORE IN DEPARTMENTS; -- CREATE STORAGE MAP DEPARTMENTS_MAP FOR DEPARTMENTS STORE IN DEPARTMENTS PLACEMENT VIA INDEX DEPARTMENTS_INDEX; -- -- The following storage map, while not necessary, is explicitly defined -- for documentation purposes. The destination storage area for the -- CANDIDATES relation is defined here as the default storage area, RDB$SYSTEM -- (file name MF_PERS_DEFAULT). This map definition is "unnecessary" because -- CANDIDATES records would have been placed in RDB$SYSTEM anyway had -- no explicit storage map been defined. -- CREATE STORAGE MAP CANDIDATES_MAP FOR CANDIDATES STORE IN RDB$SYSTEM; -- CREATE STORAGE MAP SALARY_HISTORY_MAP FOR SALARY_HISTORY STORE IN SALARY_HISTORY; -- CREATE STORAGE MAP COLLEGES_MAP FOR COLLEGES STORE IN EMP_INFO; -- CREATE STORAGE MAP DEGREES_MAP FOR DEGREES STORE IN EMP_INFO; -- CREATE STORAGE MAP WORK_STATUS_MAP FOR WORK_STATUS STORE IN EMP_INFO; -- CREATE STORAGE MAP JOBS_MAP FOR JOBS STORE IN JOBS; -- CREATE STORAGE MAP LISTS_MAP STORE LISTS IN RESUME_LISTS for (RESUMES) IN RDB$SYSTEM; -- CREATE STORAGE MAP RESUMES_MAP FOR RESUMES STORE IN RESUMES; -- -- COMMIT; -- -- ************************************************************* -- print 'Storing sample data in several tables. (Other tables to'; print 'be loaded later by separate programs.)'; -- -- *** 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;