EMPLOYEES

Description

employees table. Contains 107 rows. References with departments, jobs, job_history tables. Contains a self reference.

Columns

Column	Datatype	Nullable	Default value	Comment
EMPLOYEE_ID	NUMBER(6,0)	N		Primary key of employees table.
FIRST_NAME	VARCHAR2(20)	Y		First name of the employee. A not null column.
LAST_NAME	VARCHAR2(25)	N		Last name of the employee. A not null column.
EMAIL	VARCHAR2(25)	N		Email id of the employee
PHONE_NUMBER	VARCHAR2(20)	Y		Phone number of the employee; includes country code and area code
HIRE_DATE	DATE	N		Date when the employee started on this job. A not null column.
JOB_ID	VARCHAR2(10)	N		Current job of the employee; foreign key to job_id column of the jobs table. A not null column.
SALARY	NUMBER(8,2)	Y		Monthly salary of the employee. Must be greater than zero (enforced by constraint emp_salary_min)
COMMISSION_PCT	NUMBER(2,2)	Y		Commission percentage of the employee; Only employees in sales department elgible for commission percentage
MANAGER_ID	NUMBER(6,0)	Y		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,0)	Y		Department id where employee works; foreign key to department_id column of the departments table

Primary key

Primary key	Columns
EMP_EMP_ID_PK	EMPLOYEE_ID

Check constraints

Check constraint	Check condition
EMP_EMAIL_NN	"EMAIL" IS NOT NULL
EMP_HIRE_DATE_NN	"HIRE_DATE" IS NOT NULL
EMP_JOB_NN	"JOB_ID" IS NOT NULL
EMP_LAST_NAME_NN	"LAST_NAME" IS NOT NULL
EMP_SALARY_MIN	salary > 0

Foreign keys

Foreign key	Referenced table	Referenced constraint	Delete rule	Columns
EMP_DEPT_FK	DEPARTMENTS	DEPT_ID_PK	NO ACTION	DEPARTMENT_ID
EMP_JOB_FK	JOBS	JOB_ID_PK	NO ACTION	JOB_ID
EMP_MANAGER_FK	EMPLOYEES	EMP_EMP_ID_PK	NO ACTION	MANAGER_ID

Unique keys

Unique key	Columns
EMP_EMAIL_UK	EMAIL

Indexes

Index	Type	Uniqueness	Columns
EMP_DEPARTMENT_IX	NORMAL	NONUNIQUE	DEPARTMENT_ID
EMP_EMAIL_UK	NORMAL	UNIQUE	EMAIL
EMP_EMP_ID_PK	NORMAL	UNIQUE	EMPLOYEE_ID
EMP_JOB_IX	NORMAL	NONUNIQUE	JOB_ID
EMP_MANAGER_IX	NORMAL	NONUNIQUE	MANAGER_ID
EMP_NAME_IX	NORMAL	NONUNIQUE	LAST_NAME, FIRST_NAME

Options

Option	Setting
Clustered	N
Generated by Oracle	N
Index Organized	N
Logging	Y
Partitioned	N
Temporary	N
Nested	N

Referenced by

Referenced by	Constraint
DEPARTMENTS	DEPT_MGR_FK
EMPLOYEES	EMP_MANAGER_FK
JOB_HISTORY	JHIST_EMP_FK

Triggers

Trigger

Code

SECURE_EMPLOYEES

1   :TRIGGER secure_employees
2   :  BEFORE INSERT OR UPDATE OR DELETE ON employees
3   :BEGIN
4   :  secure_dml;
5   :END secure_employees;

UPDATE_JOB_HISTORY

1   :TRIGGER update_job_history
2   :  AFTER UPDATE OF job_id, department_id ON employees
3   :  FOR EACH ROW
4   :BEGIN
5   :  add_job_history(:old.employee_id, :old.hire_date, sysdate,
6   :                  :old.job_id, :old.department_id);
7   :END;