-- Copyright © Oracle Corporation 1995. All Rights Reserved. ---------------------------------------------------------------------------- -- Definitions for sample multischema database, corporate_data -- -- The procedure personnel creates the database and calls this file to create -- the definitions for its catalogs, schemas, tables and so forth. -- To build the complete corporate_data database, you also need -- the files msdb_data_load and msdb_indexes.sql. ---------------------------------------------------------------------------- SET CATALOG 'ADMINISTRATION'; SET SCHEMA 'PERSONNEL'; SET ANSI DATE ON; -- -- -- This catalog, ADMINISTRATION, contains three schemas: -- -- PERSONNEL - Employee information for Human Resources -- Department -- RECRUITING - Job applicants -- ACCOUNTING - Accounting and payroll information -- -- Highlights: -- One catalog (may have multiple catalogs) -- Multiple schemas -- Cross-schema constraints -- Cross-schema triggers -- Cross-schema views used for corporate information -- Duplicate domain names across schemas -- Duplicate table names across schemas -- References to domains from another schema -- Examples of date/time variables -- ---------------------------------------------------------------------------- -- PRINT 'Creating ADMINISTRATION catalog, its schemas, domains, and tables.'; CREATE CATALOG ADMINISTRATION -- Create the schema PERSONNEL. CREATE SCHEMA PERSONNEL -- Create domains for the schema PERSONNEL. CREATE DOMAIN ID CHAR(5) CREATE DOMAIN NAME CHAR(20) CREATE DOMAIN MIDDLE_INITIAL CHAR(1) DEFAULT ' ' EDIT STRING IS 'X' CREATE DOMAIN ADDRESS_LINE CHAR(25) CREATE DOMAIN STATE_CODE CHAR (4) CREATE DOMAIN POSTAL_CODE CHAR(5) CREATE DOMAIN SALARY INTEGER(2) EDIT STRING IS '$$$$,$$9.99' CREATE DOMAIN STATUS_CODE CHAR(1) CREATE DOMAIN CODE CHAR(4) -- Create the tables for the schema PERSONNEL. CREATE TABLE EMPLOYEES (EMPLOYEE_ID ID PRIMARY KEY DEFERRABLE, LAST_NAME NAME, FIRST_NAME NAME, MIDDLE_INITIAL MIDDLE_INITIAL, ADDRESS_DATA_1 ADDRESS_LINE, ADDRESS_DATA_2 ADDRESS_LINE, CITY NAME, STATE STATE_CODE, ZIP_CODE POSTAL_CODE, SEX CHAR(1), CONSTRAINT EMP_SEX_VALUES CHECK (SEX in ('M', 'F')) DEFERRABLE, BIRTHDAY DATE, STATUS STATUS_CODE, CONSTRAINT STATUS_CODE_VALUES CHECK ( STATUS in ('0','1','2','N') ) DEFERRABLE ) CREATE TABLE DEPARTMENTS (DEPARTMENT_CODE CODE PRIMARY KEY DEFERRABLE, DEPARTMENT_NAME NAME ) CREATE TABLE JOB_HISTORY (EMPLOYEE_ID ID REFERENCES EMPLOYEES (EMPLOYEE_ID) DEFERRABLE, JOB_CODE CODE, JOB_START DATE NOT NULL DEFERRABLE, JOB_END DATE, DEPARTMENT_CODE CODE REFERENCES PERSONNEL.DEPARTMENTS (DEPARTMENT_CODE) DEFERRABLE, SUPERVISOR_ID ID, LAST_REVIEW DATE ) CREATE TABLE SALARY_HISTORY (EMPLOYEE_ID ID REFERENCES EMPLOYEES (EMPLOYEE_ID) DEFERRABLE, SALARY_AMOUNT SALARY, SALARY_START DATE NOT NULL DEFERRABLE, SALARY_END DATE ) CREATE TABLE HOURLY_HISTORY (EMPLOYEE_ID ID REFERENCES EMPLOYEES (EMPLOYEE_ID) DEFERRABLE, HOURLY_RATE INTEGER(2), HOURLY_START DATE NOT NULL DEFERRABLE, HOURLY_END DATE ) ; -- End of schema PERSONNEL -- Create the schema RECRUITING. PRINT ' Creating RECRUITING schema, its domains and tables.'; CREATE SCHEMA RECRUITING -- Create domains for the schema RECRUITING. CREATE DOMAIN ID STORED NAME is RECRUIT_ID CHAR(5) -- Create tables for the schema RECRUITING. -- Some of the columns are based on domains in the PERSONNEL schema. -- Create a table for job candidates. CREATE TABLE CANDIDATES (CANDIDATE_ID ID PRIMARY KEY DEFERRABLE, LAST_NAME PERSONNEL.NAME CONSTRAINT CANDIDATES_LAST_NAME_NOT_NULL NOT NULL DEFERRABLE, FIRST_NAME PERSONNEL.NAME, MIDDLE_INITIAL PERSONNEL.MIDDLE_INITIAL, CANDIDATE_STATUS PERSONNEL.STATUS_CODE ) -- Create a table for the resumes of job candidates. (Store the resumes on -- file for 2 years maximum.) CREATE TABLE RESUMES (CANDIDATE_ID ID REFERENCES CANDIDATES (CANDIDATE_ID) DEFERRABLE, EMPLOYEE_ID PERSONNEL.ID REFERENCES PERSONNEL.EMPLOYEES (EMPLOYEE_ID) DEFERRABLE, RESUME LIST OF BYTE VARYING (100), DATE_RECEIVED DATE, DATE_EXPIRES COMPUTED BY (DATE_RECEIVED + INTERVAL '2' YEAR) ) CREATE TABLE COLLEGES (COLLEGE_CODE PERSONNEL.CODE PRIMARY KEY DEFERRABLE, COLLEGE_NAME PERSONNEL.NAME, CITY PERSONNEL.NAME, STATE PERSONNEL.STATE_CODE, ZIP_CODE PERSONNEL.POSTAL_CODE ) CREATE TABLE DEGREES (CANDIDATE_ID ID REFERENCES CANDIDATES (CANDIDATE_ID) DEFERRABLE, EMPLOYEE_ID PERSONNEL.ID REFERENCES PERSONNEL.EMPLOYEES (EMPLOYEE_ID) DEFERRABLE, COLLEGE_CODE PERSONNEL.CODE REFERENCES RECRUITING.COLLEGES (COLLEGE_CODE) DEFERRABLE, YEAR_GRADUATED SMALLINT, DEGREE CHAR(3), CONSTRAINT DEGREE_VAL CHECK ( DEGREE in ('BA','BS','MA','MS','AA','PhD') ) DEFERRABLE, DEGREE_FIELD CHAR(15) ) ; -- End of schema RECRUITING. -- Create the schema ACCOUNTING. PRINT 'Creating ACCOUNTING schema, its domains and tables.'; CREATE SCHEMA ACCOUNTING -- Create domains for the schema ACCOUNTING. CREATE DOMAIN CODE STORED NAME IS DEPT_CODE CHAR(4) CREATE DOMAIN BUDGET INTEGER EDIT STRING IS '$$$,$$$,$$$' -- Create tables for the schema ACCOUNTING. -- Some of the columns are based on domains in the PERSONNEL schema. CREATE TABLE WORK_STATUS (STATUS_CODE PERSONNEL.STATUS_CODE PRIMARY KEY DEFERRABLE, STATUS_NAME CHAR(8), CONSTRAINT STATUS_NAME_VALUES CHECK ( STATUS_NAME in ('ACTIVE', 'INACTIVE') or STATUS_NAME IS NULL ) DEFERRABLE, STATUS_TYPE CODE, CONSTRAINT STATUS_TYPE_VALUES CHECK ( STATUS_TYPE in ('EXPR','FULL', 'PART') or STATUS_TYPE IS NULL) NOT DEFERRABLE ) CREATE TABLE PAYROLL (JOB_CODE CODE PRIMARY KEY DEFERRABLE, WAGE_CLASS CHAR(1), CONSTRAINT WAGE_CLASS_VALUES CHECK ( WAGE_CLASS in ('1','2','3','4') or WAGE_CLASS IS NULL ) DEFERRABLE, JOB_TITLE PERSONNEL.NAME, MINIMUM_SALARY PERSONNEL.SALARY, MAXIMUM_SALARY PERSONNEL.SALARY ) CREATE TABLE DEPARTMENTS (DEPARTMENT_CODE CODE NOT NULL DEFERRABLE unique DEFERRABLE REFERENCES PERSONNEL.DEPARTMENTS (DEPARTMENT_CODE) DEFERRABLE, DEPARTMENT_NAME PERSONNEL.NAME, MANAGER_ID PERSONNEL.ID, BUDGET_PROJECTED BUDGET, BUDGET_ACTUAL BUDGET ) CREATE TABLE DAILY_HOURS (EMPLOYEE_ID PERSONNEL.ID, START_TIME TIMESTAMP, END_TIME TIMESTAMP, HOURS_WORKED COMPUTED BY (END_TIME - START_TIME) HOUR TO SECOND ) ; -- End of the schema ACCOUNTING. ------------------------------------------------------------------------------- -- Create views across schemas to display information. ------------------------------------------------------------------------------- SET SCHEMA 'PERSONNEL'; PRINT 'Creating views'; -- -- Create a view for 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 PERSONNEL.JOB_HISTORY JH, PERSONNEL.EMPLOYEES E WHERE JH.EMPLOYEE_ID = E.EMPLOYEE_ID AND JH.JOB_END IS NULL; -- -- Create a view for 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 PERSONNEL.SALARY_HISTORY SH, PERSONNEL.EMPLOYEES E WHERE SH.EMPLOYEE_ID = E.EMPLOYEE_ID and SH.SALARY_END IS NULL; -- -- Create a view for 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, P.JOB_TITLE, CJ.JOB_START, CS.SALARY_START, CS.SALARY_AMOUNT FROM ADMINISTRATION.PERSONNEL.CURRENT_JOB CJ, ADMINISTRATION.PERSONNEL.DEPARTMENTS D, ADMINISTRATION.ACCOUNTING.PAYROLL p, ADMINISTRATION.PERSONNEL.CURRENT_SALARY CS WHERE CJ.DEPARTMENT_CODE = D.DEPARTMENT_CODE AND CJ.JOB_CODE = P.JOB_CODE AND CJ.EMPLOYEE_ID = CS.EMPLOYEE_ID; -- -- Create a view for current information on employee review dates. -- CREATE VIEW REVIEW_DATE (FIRST_NAME, LAST_NAME, TODAY, LAST_REVIEW, NEXT_REVIEW_DUE) AS select E.FIRST_NAME, E.LAST_NAME, CURRENT_DATE, JH.LAST_REVIEW, EXTRACT (MONTH FROM ((JH.LAST_REVIEW + INTERVAL '1' YEAR) - CURRENT_DATE) MONTH) FROM PERSONNEL.EMPLOYEES E, PERSONNEL.JOB_HISTORY JH WHERE JH.EMPLOYEE_ID = E.EMPLOYEE_ID and JH.JOB_END IS NULL; -- -- End of view definitions. -- -- -- Create triggers. -- -- -- If an employee is terminated, remove all associated rows from the -- JOB_HISTORY, and SALARY_HISTORY tables. Also remove employee's RESUMES -- and DEGREE records. -- -- PRINT 'Creating triggers.'; CREATE TRIGGER EMPLOYEE_ID_CASCADE_DELETE BEFORE DELETE ON EMPLOYEES (DELETE FROM RECRUITING.RESUMES R WHERE R.EMPLOYEE_ID = EMPLOYEES.EMPLOYEE_ID) FOR EACH ROW (DELETE FROM RECRUITING.DEGREES D WHERE D.EMPLOYEE_ID = EMPLOYEES.EMPLOYEE_ID) FOR EACH ROW (DELETE FROM JOB_HISTORY JH WHERE JH.EMPLOYEE_ID = EMPLOYEES.EMPLOYEE_ID) FOR EACH ROW (DELETE FROM SALARY_HISTORY SH WHERE SH.EMPLOYEE_ID = EMPLOYEES.EMPLOYEE_ID) FOR EACH ROW -- -- Also, if an employee is terminated and that employee -- is the manager of a department, set the manager_id -- null for that department. -- (UPDATE ACCOUNTING.DEPARTMENTS D SET D.MANAGER_ID = NULL WHERE D.MANAGER_ID = EMPLOYEES.EMPLOYEE_ID) FOR EACH ROW; -- -- If the status_code in WORK_STATUS table changes, make -- the change in all affected EMPLOYEES rows also. -- CREATE TRIGGER STATUS_CODE_CASCADE_UPDATE BEFORE UPDATE OF STATUS_CODE ON ACCOUNTING.WORK_STATUS REFERENCING OLD AS OLD_WORK_STATUS NEW AS NEW_WORK_STATUS (UPDATE PERSONNEL.EMPLOYEES E SET E.STATUS = NEW_WORK_STATUS.STATUS_CODE WHERE E.STATUS = OLD_WORK_STATUS.STATUS_CODE) FOR EACH ROW; COMMIT WORK; exit