$ ! Copyright © Oracle Corporation 1995.  All Rights Reserved.
$ SET VERIFY
$ !
$ ! This command procedure builds the multifile database, INTRO_PERSONNEL, 
$ ! which is described in Getting Started with Oracle Rdb.
$ !
$ ! Before you execute this procedure, define the following logical names
$ ! to point to your directory:
$ !      - ROOT 
$ !      - STOR_1
$ !
$ ! Define the following logical name to point to a data dictionary 
$ ! directory:
$ !      - CDD$DEFAULT
$ !
$ !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
$ !
$ ! Invoke SQL.
$ SQL$
-- 
--Create the Oracle Rdb database and create a data dictionary node,
--INTRO_PERS_CDD, for the database by specifying the
--PATHNAME clause.
--
CREATE DATABASE FILENAME 'ROOT:INTRO_PERSONNEL'
                PATHNAME 'CDD$DEFAULT:INTRO_PERS_CDD'
--
-- Reserve storage area slots and journals for later use.
--
RESERVE 10 STORAGE AREAS
RESERVE 5 JOURNALS
--
-- Create the default storage area.
--
CREATE STORAGE AREA RDB$SYSTEM
       FILENAME 'ROOT:INTRO_PERS_DEFAULT'
       PAGE FORMAT IS UNIFORM
       SNAPSHOT FILENAME 'ROOT:INTRO_PERS_DEFAULT'
--
-- Create a storage area to hold data from tables and indexes.

CREATE STORAGE AREA INTRO_PERS_STOR
       FILENAME 'STOR_1:INTRO_PERS_STOR'
       ALLOCATION IS 50 PAGES
       PAGE FORMAT IS MIXED
       SNAPSHOT FILENAME 'STOR_1:INTRO_PERS_STOR'
       SNAPSHOT ALLOCATION IS 10 PAGES;
--
-- Create the domains.
--
-- Create a domain for all employee ID columns.
CREATE DOMAIN ID_DOM CHAR(5);
--
-- Create a domain for all columns with the DATE data type.
CREATE DOMAIN DATE_DOM DATE VMS
EDIT STRING IS 'DD-MMM-YYYY';
--
-- Create a domain for all department code columns.
-- If no department code is entered, use the default value, "None".
CREATE DOMAIN DEPARTMENT_CODE_DOM CHAR(4)
       DEFAULT 'None';
--
-- Create the domain for all columns containing budget items.
-- Use an edit string to format the data.
CREATE DOMAIN BUDGET_DOM INTEGER
       EDIT STRING IS '$$$,$$$,$$$';
--
-- Create the EMPLOYEES table.
--
CREATE TABLE EMPLOYEES
   (
   EMPLOYEE_ID      ID_DOM
     PRIMARY KEY
     DEFERRABLE,
   LAST_NAME        CHAR(14),
   FIRST_NAME       CHAR(10),
   MIDDLE_INITIAL   CHAR(1),
   ADDRESS_DATA     CHAR(25),
   CITY             CHAR(20),
   STATE            CHAR(2),
   POSTAL_CODE      CHAR(5),
--
-- Specify a default value for the SEX column.
   SEX              CHAR(1)   DEFAULT '?',
--
-- Create a table constraint for the SEX column.
     CONSTRAINT     EMP_SEX_VALUES
     CHECK          (
                    SEX IN ('M', 'F', '?')
                    )
     DEFERRABLE,
   BIRTHDAY         DATE_DOM
   );
--
-- Create the DEPARTMENTS table.
--
CREATE TABLE DEPARTMENTS
   (
   DEPARTMENT_CODE  DEPARTMENT_CODE_DOM
     PRIMARY KEY
     DEFERRABLE,
   DEPARTMENT_NAME  CHAR(30),
   MANAGER_ID       ID_DOM,
   BUDGET_PROJECTED BUDGET_DOM,
   BUDGET_ACTUAL    BUDGET_DOM
   );
--
-- Create the JOB_HISTORY Table.
--
CREATE TABLE JOB_HISTORY
   (
   EMPLOYEE_ID      ID_DOM
--
-- Create a foreign key constraint which ensures that you cannot store
-- a row in this table unless the EMPLOYEE_ID matches a EMPLOYEE_ID in
-- the EMPLOYEES table.
     REFERENCES EMPLOYEES (EMPLOYEE_ID)
     DEFERRABLE,
   JOB_CODE         CHAR(4),
   JOB_START        DATE_DOM,
   JOB_END          DATE_DOM,
   DEPARTMENT_CODE  DEPARTMENT_CODE_DOM
--
-- Create a foreign key constraint which ensures that you cannot store
-- a row in this table unless the DEPARTMENT_CODE matches a DEPARTMENT_CODE
-- in the DEPARTMENTS table.
     REFERENCES DEPARTMENTS (DEPARTMENT_CODE)
     DEFERRABLE,
   SUPERVISOR_ID    ID_DOM
   );

-- Create the indexes and storage maps.
--
-- Create a hashed index for the EMPLOYEES table.
CREATE UNIQUE INDEX EMPLOYEES_HASH
        ON EMPLOYEES (EMPLOYEE_ID)
        TYPE IS HASHED
        STORE IN INTRO_PERS_STOR;

-- Create a storage map for the EMPLOYEES table.
 CREATE STORAGE MAP EMPLOYEES_MAP FOR EMPLOYEES
        STORE IN INTRO_PERS_STOR
        PLACEMENT VIA INDEX EMPLOYEES_HASH;

-- Create a hashed index for the JOB_HISTORY table.
CREATE INDEX JOB_HISTORY_HASH
        ON JOB_HISTORY (EMPLOYEE_ID)
        TYPE IS HASHED
        STORE IN INTRO_PERS_STOR;

-- Create a storage map for the JOB_HISTORY  table.
CREATE STORAGE MAP JOB_HISTORY_MAP FOR JOB_HISTORY
        STORE IN INTRO_PERS_STOR
        PLACEMENT VIA INDEX JOB_HISTORY_HASH;

-- Create sorted index for the DEPARTMENT tables.
CREATE UNIQUE INDEX DEPARTMENTS_INDEX
        ON DEPARTMENTS (DEPARTMENT_CODE)
        TYPE IS SORTED
        STORE IN INTRO_PERS_STOR;

-- Create a storage map for the DEPARTMENT tables.
CREATE STORAGE MAP DEPARTMENTS_MAP FOR DEPARTMENTS
        STORE IN INTRO_PERS_STOR
        PLACEMENT VIA INDEX DEPARTMENTS_INDEX;

-- Create sorted indexes for the EMPLOYEES table.
CREATE UNIQUE INDEX EMP_EMPLOYEE_ID ON EMPLOYEES (EMPLOYEE_ID);
CREATE INDEX EMP_LAST_NAME ON EMPLOYEES (LAST_NAME);
--
-- Create a sorted index for the JOB_HISTORY table:
CREATE INDEX JH_EMPLOYEE_ID ON JOB_HISTORY (EMPLOYEE_ID);
--
-- Create a trigger that deletes an employee's  records from
-- the JOB_HISTORY table before that employee's record is deleted
-- from the EMPLOYEES table. In addition, if that employee is the 
-- manager of a department, the trigger sets the manager_id for that
-- department to a null value.
--
CREATE TRIGGER EMPLOYEE_ID_CASCADE_DELETE
       BEFORE DELETE ON EMPLOYEES
             (DELETE FROM JOB_HISTORY JH 
              WHERE JH.EMPLOYEE_ID = EMPLOYEES.EMPLOYEE_ID)
       FOR EACH ROW
             (UPDATE DEPARTMENTS D  
              SET D.MANAGER_ID = NULL
              WHERE D.MANAGER_ID = EMPLOYEES.EMPLOYEE_ID)
       FOR EACH ROW;

--
-- Commit the changes.
--
COMMIT;