HR - EMPLOYEES

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

Field	Type	Defaults	Nulls?	Comments
EMPLOYEE_ID	NUMBER (6)	None	No	Primary key of employees table.
FIRST_NAME	NVARCHAR2 (20)	None	Yes	First name of the employee. A not null column.
LAST_NAME	VARCHAR2 (25)	None	No	Last name of the employee. A not null column.
EMAIL	VARCHAR2 (25)	None	No	Email id of the employee
PHONE_NUMBER	VARCHAR2 (20)	None	Yes	Phone number of the employee; includes country code and area code
HIRE_DATE	DATE	None	No	Date when the employee started on this job. A not null column.
JOB_ID	VARCHAR2 (10)	None	No	Current job of the employee; foreign key to job_id column of the jobs table. A not null column.
SALARY	NUMBER (8, 2)	None	Yes	Monthly salary of the employee. Must be greater than zero (enforced by constraint emp_salary_min)
COMMISSION_PCT	NUMBER (2, 2)	None	Yes	Commission percentage of the employee; Only employees in sales department elgible for commission percentage
MANAGER_ID	NUMBER (6)	None	Yes	Manager 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_ID	NUMBER (4)	None	Yes	Department id where employee works; foreign key to department_id column of the departments table
JUNK	NCHAR (10)	None	Yes	None

Index	Type	Unique?	Fields
EMP_DEPARTMENT_IX	NORMAL	Yes	DEPARTMENT_ID
EMP_EMAIL_UK	NORMAL	Yes	EMAIL
EMP_EMP_ID_PK	NORMAL	No	EMPLOYEE_ID
EMP_JOB_IX	NORMAL	No	JOB_ID
EMP_MANAGER_IX	NORMAL	No	MANAGER_ID
EMP_NAME_IX	NORMAL	No	LAST_NAME FIRST_NAME

Check Constraint	Text
EMP_SALARY_MIN	salary > 0

Internal Foreign Key Constraint	Affected Field	Source Table	Source Schema
EMP_DEPT_FK	DEPARTMENT_ID	DEPARTMENTS	HR
EMP_JOB_FK	JOB_ID	JOBS	HR
EMP_MANAGER_FK	MANAGER_ID	EMPLOYEES	HR

Primary Key as Foreign Key Constraint	Affected Schema	Affected Table	Affected Field
CUSTOMERS_ACCOUNT_MANAGER_FK	OE	CUSTOMERS	ACCOUNT_MGR_ID
DEPT_MGR_FK	HR	DEPARTMENTS	MANAGER_ID
JHIST_EMP_FK	HR	JOB_HISTORY	EMPLOYEE_ID
ORDERS_SALES_REP_FK	OE	ORDERS	SALES_REP_ID

Trigger	Text
UPDATE_JOB_HISTORY	TRIGGER 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;