Overview Tables Views Indexes Constraints Triggers Procedures Functions Packages Sequences Index

HR


Description  Columns  Primary key  Check constraints  Foreign keys  Unique keys  Indexes  Options  Referenced by  Triggers  

JOB_HISTORY

Description

Table that stores job history of the employees. If an employee changes departments within the job or changes jobs within the department, new rows get inserted into this table with old job information of the employee. Contains a complex primary key: employee_id+start_date. Contains 25 rows. References with jobs, employees, and departments tables.

Columns

Column Datatype Nullable Default value Comment
EMPLOYEE_ID NUMBER(6,0) N   A not null column in the complex primary key employee_id+start_date. Foreign key to employee_id column of the employee table
START_DATE DATE N   A not null column in the complex primary key employee_id+start_date. Must be less than the end_date of the job_history table. (enforced by constraint jhist_date_interval)
END_DATE DATE N   Last day of the employee in this job role. A not null column. Must be greater than the start_date of the job_history table. (enforced by constraint jhist_date_interval)
JOB_ID VARCHAR2(10) N   Job role in which the employee worked in the past; foreign key to job_id column in the jobs table. A not null column.
DEPARTMENT_ID NUMBER(4,0) Y   Department id in which the employee worked in the past; foreign key to deparment_id column in the departments table

Primary key

Primary key Columns
JHIST_EMP_ID_ST_DATE_PK EMPLOYEE_ID, START_DATE

Check constraints

Check constraint Check condition
JHIST_DATE_INTERVAL end_date > start_date
JHIST_EMPLOYEE_NN "EMPLOYEE_ID" IS NOT NULL
JHIST_END_DATE_NN "END_DATE" IS NOT NULL
JHIST_JOB_NN "JOB_ID" IS NOT NULL
JHIST_START_DATE_NN "START_DATE" IS NOT NULL

Foreign keys

Foreign key Referenced table Referenced constraint Delete rule Columns
JHIST_DEPT_FK DEPARTMENTS DEPT_ID_PK NO ACTION DEPARTMENT_ID
JHIST_EMP_FK EMPLOYEES EMP_EMP_ID_PK NO ACTION EMPLOYEE_ID
JHIST_JOB_FK JOBS JOB_ID_PK NO ACTION JOB_ID

Indexes

Index Type Uniqueness Columns
JHIST_DEPARTMENT_IX NORMAL NONUNIQUE DEPARTMENT_ID
JHIST_EMP_ID_ST_DATE_PK NORMAL UNIQUE EMPLOYEE_ID, START_DATE
JHIST_EMPLOYEE_IX NORMAL NONUNIQUE EMPLOYEE_ID
JHIST_JOB_IX NORMAL NONUNIQUE JOB_ID

Options

Option Setting
Clustered N
Generated by Oracle N
Index Organized N
Logging Y
Partitioned N
Temporary N
Nested N


Generated by
OraDoclet, Copyright © Oracle Corporation 2005      Last updated: 01.01.2005