HR.EMPLOYEES (table)

From dbscript Online Help
Revision as of 06:14, 24 June 2010 by Bot (talk | contribs)
Jump to: navigation, search

wikibot

Table HR.EMPLOYEES
Description employees table. Contains 107 rows. References with departments,

jobs, job_history tables. Contains a self reference.

Columns

Column Data Type Nullable Default Description
EMPLOYEE_ID NUMBER(6, 0) not null Primary key of employees table.
FIRST_NAME VARCHAR2(20) null First name of the employee. A not null column.
LAST_NAME VARCHAR2(25) not null Last name of the employee. A not null column.
EMAIL VARCHAR2(25) not null Email id of the employee
PHONE_NUMBER VARCHAR2(20) null Phone number of the employee; includes country code and area code
HIRE_DATE DATE(7) not null Date when the employee started on this job. A not null column.
JOB_ID VARCHAR2(10) not null Current job of the employee; foreign key to job_id column of the

jobs table. A not null column.

SALARY NUMBER(8, 2) null Monthly salary of the employee. Must be greater

than zero (enforced by constraint emp_salary_min)

COMMISSION_PCT NUMBER(2, 2) null Commission percentage of the employee; Only employees in sales

department elgible for commission percentage

MANAGER_ID NUMBER(6, 0) null 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) null 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

Indexes

Index Type Columns
EMP_EMAIL_UK Unique EMAIL
EMP_MANAGER_IX MANAGER_ID
EMP_JOB_IX JOB_ID
EMP_DEPARTMENT_IX DEPARTMENT_ID
EMP_NAME_IX LAST_NAME, FIRST_NAME

Check Constraints

Check Constraint Expression Description
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

Relation Column Referenced Column
HR.DEPARTMENTS DEPARTMENT_ID DEPARTMENT_ID
HR.JOBS JOB_ID JOB_ID
HR.EMPLOYEES MANAGER_ID EMPLOYEE_ID

Detail Tables

Detail Table Column Referencing Column
OE.CUSTOMERS EMPLOYEE_ID ACCOUNT_MGR_ID
HR.DEPARTMENTS EMPLOYEE_ID MANAGER_ID
HR.EMPLOYEES EMPLOYEE_ID MANAGER_ID
HR.JOB_HISTORY EMPLOYEE_ID EMPLOYEE_ID
OE.ORDERS EMPLOYEE_ID SALES_REP_ID

Dependencies

Reference Type Object Type Referencing Object
Select View HR.EMP_DETAILS_VIEW
Select View OE.DEPTVIEW