HR

Basic Index   Expanded Index

Table Name:EMPLOYEES
Description:employees table. Contains 107 rows. References with departments, jobs, job_history tables. Contains a self reference.
Primary Keys:EMPLOYEE_ID
FieldTypeDefaultsNulls?Comments
EMPLOYEE_IDNUMBER (6)NoneNoPrimary key of employees table.
FIRST_NAMENVARCHAR2 (20)NoneYesFirst name of the employee. A not null column.
LAST_NAMEVARCHAR2 (25)NoneNoLast name of the employee. A not null column.
EMAILVARCHAR2 (25)NoneNoEmail id of the employee
PHONE_NUMBERVARCHAR2 (20)NoneYesPhone number of the employee; includes country code and area code
HIRE_DATEDATENoneNoDate when the employee started on this job. A not null column.
JOB_IDVARCHAR2 (10)NoneNoCurrent job of the employee; foreign key to job_id column of the
jobs table. A not null column.
SALARYNUMBER (8, 2)NoneYesMonthly salary of the employee. Must be greater
than zero (enforced by constraint emp_salary_min)
COMMISSION_PCTNUMBER (2, 2)NoneYesCommission percentage of the employee; Only employees in sales
department elgible for commission percentage
MANAGER_IDNUMBER (6)NoneYesManager id of the employee; has same domain as manager_id in
departments table. Foreign key to employee_id column of employees table.
(useful for reflexive joins and CONNECT BY query)
DEPARTMENT_IDNUMBER (4)NoneYesDepartment id where employee works; foreign key to department_id
column of the departments table
JUNKNCHAR (10)NoneYesNone

IndexTypeUnique?Fields
EMP_DEPARTMENT_IXNORMALYesDEPARTMENT_ID
EMP_EMAIL_UKNORMALYesEMAIL
EMP_EMP_ID_PKNORMALNoEMPLOYEE_ID
EMP_JOB_IXNORMALNoJOB_ID
EMP_MANAGER_IXNORMALNoMANAGER_ID
EMP_NAME_IXNORMALNoLAST_NAME
FIRST_NAME

Check ConstraintText
EMP_SALARY_MINsalary > 0

Internal Foreign Key ConstraintAffected FieldSource TableSource Schema
EMP_DEPT_FKDEPARTMENT_IDDEPARTMENTSHR
EMP_JOB_FKJOB_IDJOBSHR
EMP_MANAGER_FKMANAGER_IDEMPLOYEESHR

Primary Key as Foreign Key ConstraintAffected SchemaAffected TableAffected Field
CUSTOMERS_ACCOUNT_MANAGER_FKOECUSTOMERSACCOUNT_MGR_ID
DEPT_MGR_FKHRDEPARTMENTSMANAGER_ID
JHIST_EMP_FKHRJOB_HISTORYEMPLOYEE_ID
ORDERS_SALES_REP_FKOEORDERSSALES_REP_ID

TriggerText
UPDATE_JOB_HISTORYTRIGGER update_job_history
AFTER UPDATE OF job_id, department_id ON employees
FOR EACH ROW
BEGIN
add_job_history(:old.employee_id, :old.hire_date, sysdate,
:old.job_id, :old.department_id);
END;