Difference between revisions of "HR.JOB HISTORY (table)"

From dbscript Online Help
Jump to: navigation, search
Line 5: Line 5:
 
| '''Table
 
| '''Table
 
| HR.JOB_HISTORY
 
| HR.JOB_HISTORY
 +
|- valign="top"
 +
| '''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 ===
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
Line 15: Line 23:
 
| '''Nullable
 
| '''Nullable
 
| '''Default
 
| '''Default
−
| '''PK
+
| '''Description
−
| '''Index
 
 
 
 
|-
 
|-
Line 23: Line 30:
 
| not null
 
| not null
 
|  
 
|  
−
| JHIST_EMP_ID_ST_DATE_PK
+
| A not null column in the complex primary key employee_id+start_date.
−
| JHIST_EMPLOYEE_IX
+
Foreign key to employee_id column of the employee table
 
|-
 
|-
 
| START_DATE
 
| START_DATE
Line 30: Line 37:
 
| not null
 
| not null
 
|  
 
|  
−
| JHIST_EMP_ID_ST_DATE_PK
+
| 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
 
| END_DATE
Line 37: Line 45:
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| 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
 
| JOB_ID
Line 44: Line 53:
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Job role in which the employee worked in the past; foreign key to
−
| JHIST_JOB_IX
+
job_id column in the jobs table. A not null column.
 
|-
 
|-
 
| DEPARTMENT_ID
 
| DEPARTMENT_ID
Line 51: Line 60:
 
| null
 
| null
 
|  
 
|  
 +
| Department id in which the employee worked in the past; foreign key to deparment_id column in the departments table
 +
|}
 +
 +
=== Primary Key ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Primary Key
 +
| '''Columns
 +
|-
 +
| JHIST_EMP_ID_ST_DATE_PK
 +
| EMPLOYEE_ID, START_DATE
 +
|}
 +
 +
=== Indexes ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Index
 +
| '''Type
 +
| '''Columns
 +
 +
|-
 +
| JHIST_DEPARTMENT_IX
 +
|
 +
| DEPARTMENT_ID
 +
|-
 +
| JHIST_JOB_IX
 
|  
 
|  
−
| JHIST_DEPARTMENT_IX
+
| JOB_ID
 +
|-
 +
| JHIST_EMPLOYEE_IX
 +
|
 +
| EMPLOYEE_ID
 
|}
 
|}
  
 +
=== Check Constraints ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Check Constraint
 +
| '''Expression
 +
| '''Description
 +
 +
|-
 +
| 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 ===
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
Line 75: Line 142:
 
| JOB_ID
 
| JOB_ID
 
| JOB_ID
 
| JOB_ID
 +
|}
 +
 +
=== Dependencies ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Reference Type
 +
| '''Object Type
 +
| '''Referencing Object
 +
 +
|-
 +
| Insert
 +
| Procedure
 +
| [[HR.ADD_JOB_HISTORY_(procedure)|HR.ADD_JOB_HISTORY]]
 +
 
|}
 
|}

Revision as of 08:26, 11 February 2010

wikibot

Table HR.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 Data Type Nullable Default Description
EMPLOYEE_ID NUMBER(6, 0) not null 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(7) not null 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(7) not null 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) not null 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) null 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

Indexes

Index Type Columns
JHIST_DEPARTMENT_IX DEPARTMENT_ID
JHIST_JOB_IX JOB_ID
JHIST_EMPLOYEE_IX EMPLOYEE_ID

Check Constraints

Check Constraint Expression Description
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

Relation Column Referenced Column
HR.DEPARTMENTS DEPARTMENT_ID DEPARTMENT_ID
HR.EMPLOYEES EMPLOYEE_ID EMPLOYEE_ID
HR.JOBS JOB_ID JOB_ID

Dependencies

Reference Type Object Type Referencing Object
Insert Procedure HR.ADD_JOB_HISTORY
Retrieved from "http://dbshelp.devio.at/index.php?title=HR.JOB_HISTORY_(table)&oldid=2218"