Difference between revisions of "HR.JOB HISTORY (table)"
| (One intermediate revision by the same user not shown) | |||
| Line 2: | Line 2: | ||
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | ||
| − | |- | + | |- valign="top" |
| '''Table | | '''Table | ||
| HR.JOB_HISTORY | | HR.JOB_HISTORY | ||
| Line 12: | Line 12: | ||
employee. Contains a complex primary key: employee_id+start_date. | employee. Contains a complex primary key: employee_id+start_date. | ||
Contains 25 rows. References with jobs, employees, and departments tables. | Contains 25 rows. References with jobs, employees, and departments tables. | ||
| − | |||
|} | |} | ||
| − | |||
=== Columns === | === Columns === | ||
| Line 24: | Line 22: | ||
| '''Default | | '''Default | ||
| '''Description | | '''Description | ||
| − | + | ||
| − | |- | + | |- valign="top" |
| EMPLOYEE_ID | | EMPLOYEE_ID | ||
| NUMBER(6, 0) | | NUMBER(6, 0) | ||
| Line 32: | Line 30: | ||
| A not null column in the complex primary key employee_id+start_date. | | A not null column in the complex primary key employee_id+start_date. | ||
Foreign key to employee_id column of the employee table | Foreign key to employee_id column of the employee table | ||
| − | |- | + | |- valign="top" |
| START_DATE | | START_DATE | ||
| DATE(7) | | DATE(7) | ||
| Line 40: | Line 38: | ||
Must be less than the end_date of the job_history table. (enforced by | Must be less than the end_date of the job_history table. (enforced by | ||
constraint jhist_date_interval) | constraint jhist_date_interval) | ||
| − | |- | + | |- valign="top" |
| END_DATE | | END_DATE | ||
| DATE(7) | | DATE(7) | ||
| Line 48: | Line 46: | ||
greater than the start_date of the job_history table. | greater than the start_date of the job_history table. | ||
(enforced by constraint jhist_date_interval) | (enforced by constraint jhist_date_interval) | ||
| − | |- | + | |- valign="top" |
| JOB_ID | | JOB_ID | ||
| VARCHAR2(10) | | VARCHAR2(10) | ||
| Line 55: | Line 53: | ||
| Job role in which the employee worked in the past; foreign key to | | Job role in which the employee worked in the past; foreign key to | ||
job_id column in the jobs table. A not null column. | job_id column in the jobs table. A not null column. | ||
| − | |- | + | |- valign="top" |
| DEPARTMENT_ID | | DEPARTMENT_ID | ||
| NUMBER(4, 0) | | NUMBER(4, 0) | ||
| Line 68: | Line 66: | ||
| '''Primary Key | | '''Primary Key | ||
| '''Columns | | '''Columns | ||
| − | |- | + | |- valign="top" |
| JHIST_EMP_ID_ST_DATE_PK | | JHIST_EMP_ID_ST_DATE_PK | ||
| EMPLOYEE_ID, START_DATE | | EMPLOYEE_ID, START_DATE | ||
| Line 79: | Line 77: | ||
| '''Type | | '''Type | ||
| '''Columns | | '''Columns | ||
| − | + | ||
| − | |- | + | |- valign="top" |
| JHIST_DEPARTMENT_IX | | JHIST_DEPARTMENT_IX | ||
| | | | ||
| DEPARTMENT_ID | | DEPARTMENT_ID | ||
| − | |- | + | |- valign="top" |
| + | | JHIST_EMPLOYEE_IX | ||
| + | | | ||
| + | | EMPLOYEE_ID | ||
| + | |- valign="top" | ||
| JHIST_JOB_IX | | JHIST_JOB_IX | ||
| | | | ||
| JOB_ID | | JOB_ID | ||
| − | |||
| − | |||
| − | |||
| − | |||
|} | |} | ||
| Line 100: | Line 98: | ||
| '''Expression | | '''Expression | ||
| '''Description | | '''Description | ||
| − | + | ||
| − | |- | + | |- valign="top" |
| JHIST_DATE_INTERVAL | | JHIST_DATE_INTERVAL | ||
| end_date > start_date | | end_date > start_date | ||
| | | | ||
| − | |- | + | |- valign="top" |
| JHIST_EMPLOYEE_NN | | JHIST_EMPLOYEE_NN | ||
| "EMPLOYEE_ID" IS NOT NULL | | "EMPLOYEE_ID" IS NOT NULL | ||
| | | | ||
| − | |- | + | |- valign="top" |
| JHIST_END_DATE_NN | | JHIST_END_DATE_NN | ||
| "END_DATE" IS NOT NULL | | "END_DATE" IS NOT NULL | ||
| | | | ||
| − | |- | + | |- valign="top" |
| JHIST_JOB_NN | | JHIST_JOB_NN | ||
| "JOB_ID" IS NOT NULL | | "JOB_ID" IS NOT NULL | ||
| | | | ||
| − | |- | + | |- valign="top" |
| JHIST_START_DATE_NN | | JHIST_START_DATE_NN | ||
| "START_DATE" IS NOT NULL | | "START_DATE" IS NOT NULL | ||
| Line 129: | Line 127: | ||
| '''Column | | '''Column | ||
| '''Referenced Column | | '''Referenced Column | ||
| − | + | ||
| − | |- | + | |- valign="top" |
| [[HR.DEPARTMENTS_(table)|HR.DEPARTMENTS]] | | [[HR.DEPARTMENTS_(table)|HR.DEPARTMENTS]] | ||
| DEPARTMENT_ID | | DEPARTMENT_ID | ||
| DEPARTMENT_ID | | DEPARTMENT_ID | ||
| − | |- | + | |- valign="top" |
| [[HR.EMPLOYEES_(table)|HR.EMPLOYEES]] | | [[HR.EMPLOYEES_(table)|HR.EMPLOYEES]] | ||
| EMPLOYEE_ID | | EMPLOYEE_ID | ||
| EMPLOYEE_ID | | EMPLOYEE_ID | ||
| − | |- | + | |- valign="top" |
| [[HR.JOBS_(table)|HR.JOBS]] | | [[HR.JOBS_(table)|HR.JOBS]] | ||
| JOB_ID | | JOB_ID | ||
| JOB_ID | | JOB_ID | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
|} | |} | ||
Latest revision as of 11:33, 27 August 2011
wikibot[edit]
| 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[edit]
| 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[edit]
| Primary Key | Columns |
| JHIST_EMP_ID_ST_DATE_PK | EMPLOYEE_ID, START_DATE |
Indexes[edit]
| Index | Type | Columns |
| JHIST_DEPARTMENT_IX | DEPARTMENT_ID | |
| JHIST_EMPLOYEE_IX | EMPLOYEE_ID | |
| JHIST_JOB_IX | JOB_ID |
Check Constraints[edit]
| 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[edit]
| Relation | Column | Referenced Column |
| HR.DEPARTMENTS | DEPARTMENT_ID | DEPARTMENT_ID |
| HR.EMPLOYEES | EMPLOYEE_ID | EMPLOYEE_ID |
| HR.JOBS | JOB_ID | JOB_ID |
