! Copyright © Oracle Corporation 1995.  All Rights Reserved.
!
! MF_PERSONNEL definitions: fields, relations, 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 relation is stored via its sorted index.
! Constraints and other sorted indexes defined after the load programs
! have finished.
!
!
SET NOVERIFY
START_TRANSACTION READ_WRITE
!
PRINT "Defining global fields for MF_PERSONNEL"
! 
! *** Define all global fields for the MF_PERSONNEL database ***
!
DEFINE FIELD  ID_NUMBER
        DESCRIPTION IS /* Generic employee ID */
        DATATYPE IS TEXT SIZE IS 5
        MISSING_VALUE IS "     ".
!
!         Caution: If you use the CHANGE FIELD statement to change the
!         missing value for this field, do the following afterwards:
!          1. Delete the trigger EMPLOYEE_ID_CASCADE_DELETE.
!          2. Then define the trigger EMPLOYEE_ID_CASCADE_DELETE again.
!             (This definition is included in the file
!              RDM$DEMO:TRIGGERS_RDO.RDO).
!         This will ensure that "missing" department managers in the
!         DEPARTMENTS relation are handled as you intend by Oracle Rdb.
!         (That is, Oracle Rdb must re-evaluate the RDB$MISSING() function
!         so that the trigger uses the newly defined missing value.
!         Otherwise, the old missing value will be assigned and the MISSING
!         operator will not select those records.)
!
DEFINE FIELD  LAST_NAME
        DESCRIPTION IS /* Generic last name */
        DATATYPE IS TEXT SIZE IS 14.
!
DEFINE FIELD  FIRST_NAME
        DESCRIPTION IS/* Generic first name */
        DATATYPE IS TEXT SIZE IS 10.
!
DEFINE FIELD  MIDDLE_INITIAL
        DESCRIPTION IS /* Generic middle initial */
        DATATYPE IS TEXT SIZE IS 1
        EDIT_STRING FOR DATATRIEVE IS 'X.'
        MISSING_VALUE IS ' '.
!
DEFINE FIELD  ADDRESS_DATA_1
        DESCRIPTION IS /* Street name */
        DATATYPE IS TEXT SIZE IS 25
        MISSING_VALUE IS '                         '.
!
DEFINE FIELD  ADDRESS_DATA_2
        DESCRIPTION IS /* Mail stops, suite addresses, street numbers, etc.*/
        DATATYPE IS TEXT SIZE IS 25
        MISSING_VALUE IS '                         '.
!
DEFINE FIELD  CITY
        DESCRIPTION IS /* City name */
        DATATYPE IS TEXT SIZE IS 20
        MISSING_VALUE IS '                    '.
!
DEFINE FIELD  STATE
        DESCRIPTION IS /* State abbreviation (or DISTRICT) */
        DATATYPE IS TEXT SIZE IS 2
        MISSING_VALUE IS '  '.
!
DEFINE FIELD  POSTAL_CODE
        DESCRIPTION IS /* Postal code (in US = ZIP)*/
        DATATYPE IS TEXT SIZE IS 5
        MISSING_VALUE IS '     '.
!
DEFINE FIELD  SEX
       DESCRIPTION IS /* M, F */
        DATATYPE IS TEXT SIZE IS 1
	MISSING_VALUE IS '?'
        VALID IF SEX = 'M' OR SEX = 'F' OR
	SEX MISSING.
!
DEFINE FIELD  STANDARD_DATE
        DESCRIPTION IS /* Generic date field */
        DATATYPE IS DATE
        MISSING_VALUE IS '17-NOV-1858 00:00:00.00'
        EDIT_STRING FOR DATATRIEVE IS 'DD-MMM-YYYY'.
!
DEFINE FIELD  SALARY
        DESCRIPTION IS /* Generic salary field */
        DATATYPE IS SIGNED LONGWORD SCALE -2
        VALID IF SALARY > 0 OR 
		 SALARY MISSING
        EDIT_STRING FOR DATATRIEVE IS '$$$$,$$9.99'.
!
DEFINE FIELD  RESUME
        DESCRIPTION IS /* Employee resume */
        DATATYPE IS SEGMENTED STRING.
!
DEFINE FIELD  DEPARTMENT_CODE
        DESCRIPTION IS /* Department code or abbreviation */
        DATATYPE IS TEXT 4
        MISSING_VALUE IS 'None'.
!
DEFINE FIELD  JOB_CODE
        DESCRIPTION IS /* Generic job code */
        DATATYPE IS TEXT SIZE IS 4
        MISSING_VALUE IS 'None'.
!
DEFINE FIELD  WAGE_CLASS
        DESCRIPTION IS /* Wage class -- 1 to 4 */
        DATATYPE IS TEXT SIZE IS 1
        VALID IF WAGE_CLASS = '1' OR
                 WAGE_CLASS = '2' OR
                 WAGE_CLASS = '3' OR
                 WAGE_CLASS = '4' OR
		 WAGE_CLASS MISSING.
!
DEFINE FIELD  JOB_TITLE
        DESCRIPTION IS /* Generic job title */
        DATATYPE IS TEXT SIZE IS 20
        MISSING_VALUE IS 'None'.
!
DEFINE FIELD  DEPARTMENT_NAME
        DESCRIPTION IS /* Department name */
        DATATYPE IS TEXT SIZE IS 30
        MISSING_VALUE IS 'None'.
!
DEFINE FIELD  BUDGET
        DESCRIPTION IS /* Generic budget data */
        DATATYPE IS SIGNED LONGWORD SCALE 0
        EDIT_STRING FOR DATATRIEVE IS '$$$,$$$,$$$'.
!
DEFINE FIELD  COLLEGE_NAME
        DESCRIPTION IS /* Halls of ivy */
        DATATYPE IS TEXT SIZE IS 25.
!
DEFINE FIELD  COLLEGE_CODE
        DESCRIPTION IS /* Four-letter college code */
        DATATYPE IS TEXT SIZE IS 4.
!
DEFINE FIELD  YEAR_GIVEN
        DESCRIPTION IS /* Year degree awarded */
        DATATYPE IS SIGNED WORD.
!
DEFINE FIELD  DEGREE
        DESCRIPTION IS /* Degree awarded */
        DATATYPE IS TEXT SIZE IS 3
        VALID IF DEGREE = 'BA ' OR
                 DEGREE = 'BS ' OR
                 DEGREE = 'MA ' OR
                 DEGREE = 'MS ' OR
                 DEGREE = 'PhD' OR
		 DEGREE MISSING.
!
DEFINE FIELD  DEGREE_FIELD
        DESCRIPTION IS /* Field in which degree was awarded */
        DATATYPE IS TEXT SIZE IS 15
        MISSING_VALUE IS 'Unknown'.
!
DEFINE FIELD  STATUS_CODE
        DESCRIPTION IS /* A number */
        DATATYPE IS TEXT SIZE IS 1
	MISSING_VALUE IS 'N'
        VALID IF STATUS_CODE = '0' OR
                 STATUS_CODE = '1' OR
                 STATUS_CODE = '2' OR
		 STATUS_CODE MISSING.
!
DEFINE FIELD  STATUS_NAME
        DESCRIPTION IS /* Active or inactive */
        DATATYPE IS TEXT SIZE IS 8
        VALID IF STATUS_NAME = 'ACTIVE' OR
                 STATUS_NAME = 'INACTIVE' OR
		 STATUS_NAME MISSING.
!
DEFINE FIELD        STATUS_TYPE
        DESCRIPTION IS /* Full-time, part-time, or expired */
        DATATYPE IS TEXT SIZE IS 14
        VALID IF STATUS_TYPE = 'RECORD EXPIRED' OR
                 STATUS_TYPE = 'FULL TIME' OR 
                 STATUS_TYPE = 'PART TIME' OR
		 STATUS_TYPE MISSING.
!
DEFINE FIELD         CANDIDATE_STATUS
         DESCRIPTION IS /* Hiring status of candidate */
         DATATYPE IS VARYING STRING SIZE IS 255
         VALID IF CANDIDATE_STATUS MISSING
             OR CANDIDATE_STATUS GT "    "
             OR CANDIDATE_STATUS =  "    ".
!
COMMIT
!
!**********************************************************
!
! *** Define Relations ***
!
PRINT "Defining relations for MF_PERSONNEL"
!
DEFINE RELATION EMPLOYEES.
   EMPLOYEE_ID
      BASED ON ID_NUMBER.
   LAST_NAME.
   FIRST_NAME.
   MIDDLE_INITIAL.
   ADDRESS_DATA_1.
   ADDRESS_DATA_2.
   CITY.
   STATE.
   POSTAL_CODE.
   SEX.
   BIRTHDAY
      BASED ON STANDARD_DATE.
   STATUS_CODE.
END EMPLOYEES RELATION.
!
! Job_History Relation:
!
DEFINE RELATION JOB_HISTORY.
   EMPLOYEE_ID
      BASED ON ID_NUMBER.
   JOB_CODE.
   JOB_START
      BASED ON STANDARD_DATE.
   JOB_END
      BASED ON STANDARD_DATE.
   DEPARTMENT_CODE.
   SUPERVISOR_ID
      BASED ON ID_NUMBER.
END JOB_HISTORY RELATION.
!
! Salary_History Relation:
!
DEFINE RELATION SALARY_HISTORY.
   EMPLOYEE_ID
      BASED ON ID_NUMBER.
   SALARY_AMOUNT
      BASED ON SALARY.        
   SALARY_START
      BASED ON STANDARD_DATE.
   SALARY_END
      BASED ON STANDARD_DATE.
END SALARY_HISTORY RELATION.
!
! Jobs Relation:
!
DEFINE RELATION JOBS.
   JOB_CODE.
   WAGE_CLASS.
   JOB_TITLE.
   MINIMUM_SALARY
      BASED ON SALARY.
   MAXIMUM_SALARY
      BASED ON SALARY.
END JOBS RELATION.
!
! Departments Relation:
!
DEFINE RELATION DEPARTMENTS.
   DEPARTMENT_CODE.
   DEPARTMENT_NAME.
   MANAGER_ID
      BASED ON ID_NUMBER.
   BUDGET_PROJECTED
      BASED ON BUDGET.
   BUDGET_ACTUAL
      BASED ON BUDGET.
END DEPARTMENTS RELATION.
!
! Colleges Relation:
!
DEFINE RELATION COLLEGES.
   COLLEGE_CODE.
   COLLEGE_NAME.
   CITY.
   STATE.
   POSTAL_CODE.
END COLLEGES RELATION.
!
! Degrees Relation:
!
DEFINE RELATION DEGREES.
   EMPLOYEE_ID
      BASED ON ID_NUMBER.
   COLLEGE_CODE.
   YEAR_GIVEN.
   DEGREE.
   DEGREE_FIELD.
END DEGREES RELATION.
!
! Work_Status Relation:
!
DEFINE RELATION WORK_STATUS.
   STATUS_CODE.
   STATUS_NAME.
   STATUS_TYPE.
END WORK_STATUS RELATION. 
!
! Resumes Relation:
!
DEFINE RELATION RESUMES.
   EMPLOYEE_ID
      BASED ON ID_NUMBER.
   RESUME.
END RESUMES RELATION. 
!
! Candidates relation:
!
DEFINE RELATION CANDIDATES.
   LAST_NAME.
   FIRST_NAME.
   MIDDLE_INITIAL.  
   CANDIDATE_STATUS.
   RESUME.
END CANDIDATES RELATION. 
!
!
! +++ Begin Multi-file 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 relations share the same storage area file, while some relations
! are defined as being the only record type in a particular file.
!
!
PRINT "Defining hashed indexes and storage maps for MF_PERSONNEL"
!
DEFINE INDEX EMPLOYEES_HASH 
  DESCRIPTION IS /* hash index for employees */   
  FOR EMPLOYEES 
  DUPLICATES ARE NOT ALLOWED
  STORE USING EMPLOYEE_ID
    WITHIN 
    EMPIDS_LOW WITH LIMIT OF "00200";
    EMPIDS_MID WITH LIMIT OF "00400";
    EMPIDS_OVER;
  TYPE IS HASHED.
  EMPLOYEE_ID.
END EMPLOYEES_HASH.
!
DEFINE STORAGE MAP EMPLOYEES_MAP 
  DESCRIPTION IS /* employees partitioned by "00200" "00400" */    
  FOR EMPLOYEES   
  STORE USING EMPLOYEE_ID
    WITHIN 
    EMPIDS_LOW WITH LIMIT OF "00200";
    EMPIDS_MID WITH LIMIT OF "00400";
    EMPIDS_OVER
    PLACEMENT VIA INDEX EMPLOYEES_HASH
END EMPLOYEES_MAP STORAGE MAP.
!
! Record clustering to place Employees and Job_history records with the same 
! employee_id on the same database page. 
!
DEFINE INDEX JOB_HISTORY_HASH 
  DESCRIPTION IS /* hash index for job_history */   
  FOR JOB_HISTORY   
  DUPLICATES ARE ALLOWED
  STORE USING EMPLOYEE_ID
    WITHIN 
    EMPIDS_LOW WITH LIMIT OF "00200";
    EMPIDS_MID WITH LIMIT OF "00400";
    EMPIDS_OVER;
  TYPE IS HASHED.
  EMPLOYEE_ID.
END JOB_HISTORY_HASH.
!
DEFINE STORAGE MAP JOB_HISTORY_MAP 
  DESCRIPTION IS /* job_history partioned by "00200" "00400" */  
  FOR JOB_HISTORY   
  STORE USING EMPLOYEE_ID
    WITHIN 
    EMPIDS_LOW WITH LIMIT OF "00200";
    EMPIDS_MID WITH LIMIT OF "00400";
    EMPIDS_OVER
    PLACEMENT VIA INDEX JOB_HISTORY_HASH
END JOB_HISTORY_MAP STORAGE MAP.
!
! Sorted index to store departments records ordered by department_code
! to aid in record retrieval by groups of sequential department codes
!
!
PRINT "Defining one sorted index prior to starting load programs"
!
DEFINE INDEX DEPARTMENTS_INDEX 
  DESCRIPTION IS /* sorted index for departments */   
  FOR DEPARTMENTS  
  DUPLICATES ARE NOT ALLOWED
  STORE WITHIN DEPARTMENTS
  TYPE IS SORTED.
  DEPARTMENT_CODE.
END DEPARTMENTS_INDEX INDEX.
!
DEFINE STORAGE MAP DEPARTMENTS_MAP 
  DESCRIPTION IS /* departments storage map */   
  FOR DEPARTMENTS      
  STORE WITHIN DEPARTMENTS
  PLACEMENT VIA INDEX DEPARTMENTS_INDEX
END DEPARTMENTS_MAP STORAGE MAP.
!
! 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.
!
DEFINE STORAGE MAP CANDIDATES_MAP 
  DESCRIPTION IS /* CANDIDATES storage map */  
  FOR CANDIDATES 
  STORE WITHIN RDB$SYSTEM
END CANDIDATES_MAP STORAGE MAP.
!
DEFINE STORAGE MAP SALARY_HISTORY_MAP 
  DESCRIPTION IS /* salary_history storage map */       
  FOR SALARY_HISTORY  
  STORE WITHIN SALARY_HISTORY
END SALARY_HISTORY_MAP STORAGE MAP.
!
DEFINE STORAGE MAP COLLEGES_MAP 
  DESCRIPTION IS /* colleges storage map */   
  FOR COLLEGES  
  STORE WITHIN EMP_INFO
END COLLEGES_MAP STORAGE MAP.
!
DEFINE STORAGE MAP DEGREES_MAP 
  DESCRIPTION IS /* degrees storage map */      
  FOR DEGREES     
  STORE WITHIN EMP_INFO
END DEGREES_MAP STORAGE MAP.
!
DEFINE STORAGE MAP RESUMES_MAP 
  DESCRIPTION IS /* resumes storage map */
  FOR RESUMES  
  STORE WITHIN EMP_INFO
END RESUMES_MAP STORAGE MAP.
!
DEFINE STORAGE MAP WORK_STATUS_MAP 
  DESCRIPTION IS /* work_status storage map */  
  FOR WORK_STATUS        
  STORE WITHIN EMP_INFO
END WORK_STATUS_MAP STORAGE MAP.
!
DEFINE STORAGE MAP JOBS_MAP
  DESCRIPTION IS /* jobs storage map */  
  FOR JOBS     
  STORE WITHIN JOBS
END JOBS_MAP STORAGE MAP.
!
!
COMMIT
!
!
!**********************************************************
! *** Define three views to get current information ***
!**********************************************************
!
START_TRANSACTION READ_WRITE
!
! Current salary information
!
PRINT "Defining views for MF_PERSONNEL"
!
DEFINE VIEW CURRENT_JOB OF JH IN JOB_HISTORY
  CROSS E IN EMPLOYEES OVER EMPLOYEE_ID 
     WITH JH.JOB_END MISSING.
      E.LAST_NAME.
      E.FIRST_NAME.
      E.EMPLOYEE_ID.
      JH.JOB_CODE.
      JH.DEPARTMENT_CODE.
      JH.SUPERVISOR_ID.
      JH.JOB_START.
END VIEW.
!
!
! Current salary information
!
DEFINE VIEW CURRENT_SALARY OF SH IN SALARY_HISTORY
 CROSS E IN EMPLOYEES OVER EMPLOYEE_ID 
   WITH SH.SALARY_END MISSING.
     E.LAST_NAME.
     E.FIRST_NAME.
     E.EMPLOYEE_ID.
     SH.SALARY_START.
     SH.SALARY_AMOUNT.
END VIEW.
!
!
! Current salary and job information
!
DEFINE VIEW CURRENT_INFO OF CJ IN CURRENT_JOB
  CROSS D IN DEPARTMENTS OVER DEPARTMENT_CODE
  CROSS J IN JOBS OVER JOB_CODE
  CROSS CS IN CURRENT_SALARY OVER EMPLOYEE_ID.
     LAST_NAME FROM CJ.LAST_NAME.
     FIRST_NAME FROM CJ.FIRST_NAME.
     ID FROM CJ.EMPLOYEE_ID.
     DEPARTMENT FROM D.DEPARTMENT_NAME.
     JOB FROM J.JOB_TITLE.
     JSTART FROM CJ.JOB_START.
     SSTART FROM CS.SALARY_START.
     SALARY FROM CS.SALARY_AMOUNT.
END VIEW.
!
COMMIT
!
!	Store three Work Status Codes in WORK_STATUS relation
!
PRINT "Storing three records in WORK_STATUS relation"
!
START_TRANSACTION READ_WRITE RESERVING WORK_STATUS FOR SHARED WRITE
!
STORE W IN WORK_STATUS USING
W.STATUS_CODE="0";
W.STATUS_NAME="INACTIVE";
W.STATUS_TYPE="RECORD EXPIRED";END_STORE
!
STORE W IN WORK_STATUS USING
W.STATUS_CODE="1";
W.STATUS_NAME="ACTIVE";
W.STATUS_TYPE="FULL TIME";END_STORE
!
STORE W IN WORK_STATUS USING
W.STATUS_CODE="2";
W.STATUS_NAME="ACTIVE";
W.STATUS_TYPE="PART TIME";END_STORE
!
COMMIT
!
!	Store three records in the CANDIDATES relation
!
PRINT "Storing three records in CANDIDATES relation"
!
START_TRANSACTION READ_WRITE RESERVING CANDIDATES FOR SHARED WRITE
!
STORE C IN CANDIDATES USING
C.LAST_NAME = "Wilson";
C.FIRST_NAME = "Oscar";
C.MIDDLE_INITIAL = "M";
C.CANDIDATE_STATUS = "Available part time Oct.-Dec. and full time starting in January '87";
END_STORE
!
STORE C IN CANDIDATES USING
C.LAST_NAME = "Schwartz";
C.FIRST_NAME = "Trixie";
C.MIDDLE_INITIAL = "R";
C.CANDIDATE_STATUS = "Available second week November. Don't contact her at current job.";
END_STORE
!
STORE C IN CANDIDATES USING
C.LAST_NAME = "Boswick";
C.FIRST_NAME = "Fred";
C.MIDDLE_INITIAL = "W";
C.CANDIDATE_STATUS = "Engineering Dept. not impressed. No offer made.";
END_STORE
!
COMMIT
FINISH
EXIT