HR

Basic Index   Expanded Index

Table Name: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.
Primary Keys:EMPLOYEE_ID
START_DATE
FieldTypeDefaultsNulls?Comments
EMPLOYEE_IDNUMBER (6)NoneNoA not null column in the complex primary key employee_id+start_date.
Foreign key to employee_id column of the employee table
START_DATEDATENoneNoA 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_DATEDATENoneNoLast 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_IDVARCHAR2 (10)NoneNoJob role in which the employee worked in the past; foreign key to
job_id column in the jobs table. A not null column.
DEPARTMENT_IDNUMBER (4)NoneYesDepartment id in which the employee worked in the past; foreign key to deparment_id column in the departments table

IndexTypeUnique?Fields
JHIST_DEPARTMENT_IXNORMALNORMALDEPARTMENT_ID
JHIST_EMPLOYEE_IXNORMALNORMALEMPLOYEE_ID
JHIST_EMP_ID_ST_DATE_PKNORMALNORMALEMPLOYEE_ID
START_DATE
JHIST_JOB_IXNORMALNORMALJOB_ID

Check ConstraintText
JHIST_DATE_INTERVALend_date > start_date

Internal Foreign Key ConstraintAffected FieldSource TableSource Schema
JHIST_DEPT_FKDEPARTMENT_IDDEPARTMENTSHR
JHIST_EMP_FKEMPLOYEE_IDEMPLOYEESHR
JHIST_JOB_FKJOB_IDJOBSHR

No primary keys as foreign key constraints
No triggers