Latest revision |
Your text |
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 |
− | |- 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"
| |
− | |- style="background:silver"
| |
− | | '''Column
| |
− | | '''Data Type
| |
− | | '''Nullable
| |
− | | '''Default
| |
− | | '''Description
| |
− | | |
− | |- valign="top"
| |
− | | 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
| |
− | |- valign="top"
| |
− | | 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)
| |
− | |- valign="top"
| |
− | | 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)
| |
− | |- valign="top"
| |
− | | 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.
| |
− | |- valign="top"
| |
− | | 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 ===
| |
− | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
| |
− | |- style="background:silver"
| |
− | | '''Primary Key
| |
− | | '''Columns
| |
− | |- valign="top"
| |
− | | 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
| |
− | | |
− | |- valign="top"
| |
− | | JHIST_DEPARTMENT_IX
| |
− | |
| |
− | | DEPARTMENT_ID
| |
− | |- valign="top"
| |
− | | JHIST_EMPLOYEE_IX
| |
− | |
| |
− | | EMPLOYEE_ID
| |
− | |- valign="top"
| |
− | | JHIST_JOB_IX
| |
− | |
| |
− | | JOB_ID
| |
− | |}
| |
− | | |
− | === Check Constraints ===
| |
− | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
| |
− | |- style="background:silver"
| |
− | | '''Check Constraint
| |
− | | '''Expression
| |
− | | '''Description
| |
− | | |
− | |- valign="top"
| |
− | | JHIST_DATE_INTERVAL
| |
− | | end_date > start_date
| |
− | |
| |
− | |- valign="top"
| |
− | | JHIST_EMPLOYEE_NN
| |
− | | "EMPLOYEE_ID" IS NOT NULL
| |
− | |
| |
− | |- valign="top"
| |
− | | JHIST_END_DATE_NN
| |
− | | "END_DATE" IS NOT NULL
| |
− | |
| |
− | |- valign="top"
| |
− | | JHIST_JOB_NN
| |
− | | "JOB_ID" IS NOT NULL
| |
− | |
| |
− | |- valign="top"
| |
− | | JHIST_START_DATE_NN
| |
− | | "START_DATE" IS NOT NULL
| |
− | |
| |
− | |}
| |
− | | |
− | === Foreign Keys ===
| |
− | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
| |
− | |- style="background:silver"
| |
− | | '''Relation
| |
− | | '''Column
| |
− | | '''Referenced Column
| |
− | | |
− | |- valign="top"
| |
− | | [[HR.DEPARTMENTS_(table)|HR.DEPARTMENTS]]
| |
− | | DEPARTMENT_ID
| |
− | | DEPARTMENT_ID
| |
− | |- valign="top"
| |
− | | [[HR.EMPLOYEES_(table)|HR.EMPLOYEES]]
| |
− | | EMPLOYEE_ID
| |
− | | EMPLOYEE_ID
| |
− | |- valign="top"
| |
− | | [[HR.JOBS_(table)|HR.JOBS]]
| |
− | | JOB_ID
| |
− | | JOB_ID
| |
| |} | | |} |