! Copyright © Oracle Corporation 1995.  All Rights Reserved.
!
SET NOVERIFY
START_TRANSACTION READ_WRITE
!
!
PRINT "Defining global fields for PERSONNEL"
!
! 
! *** Define all global fields for the 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 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. 
!
!
COMMIT
!
!
!**********************************************************
! *** Define three views to get current information ***
!**********************************************************
!
! Current salary information
!
PRINT "Defining views for 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
!
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
!
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