Difference between revisions of "HR.EMPLOYEES (table)"

From dbscript Online Help
Jump to: navigation, search
 
(2 intermediate revisions by the same user not shown)
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.EMPLOYEES
 
| HR.EMPLOYEES
−
|-
+
|- valign="top"
 +
| '''Description
 +
| employees table. Contains 107 rows. References with departments,
 +
jobs, job_history tables. Contains a self reference.
 
|}
 
|}
  
−
 
+
=== Columns ===
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
Line 15: Line 18:
 
| '''Nullable
 
| '''Nullable
 
| '''Default
 
| '''Default
−
| '''PK
+
| '''Description
−
| '''Index
+
 
−
+
|- valign="top"
−
|-
 
 
| EMPLOYEE_ID
 
| EMPLOYEE_ID
 
| NUMBER(6, 0)
 
| NUMBER(6, 0)
 
| not null
 
| not null
 
|  
 
|  
−
| EMP_EMP_ID_PK
+
| Primary key of employees table.
−
|
+
|- valign="top"
−
|-
 
 
| FIRST_NAME
 
| FIRST_NAME
 
| VARCHAR2(20)
 
| VARCHAR2(20)
 
| null
 
| null
 
|  
 
|  
−
|  
+
| First name of the employee. A not null column.
−
| EMP_NAME_IX
+
|- valign="top"
−
|-
 
 
| LAST_NAME
 
| LAST_NAME
 
| VARCHAR2(25)
 
| VARCHAR2(25)
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Last name of the employee. A not null column.
−
| EMP_NAME_IX
+
|- valign="top"
−
|-
 
 
| EMAIL
 
| EMAIL
 
| VARCHAR2(25)
 
| VARCHAR2(25)
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Email id of the employee
−
| EMP_EMAIL_UK
+
|- valign="top"
−
|-
 
 
| PHONE_NUMBER
 
| PHONE_NUMBER
 
| VARCHAR2(20)
 
| VARCHAR2(20)
 
| null
 
| null
 
|  
 
|  
−
|  
+
| Phone number of the employee; includes country code and area code
−
|
+
|- valign="top"
−
|-
 
 
| HIRE_DATE
 
| HIRE_DATE
 
| DATE(7)
 
| DATE(7)
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Date when the employee started on this job. A not null column.
−
|
+
|- valign="top"
−
|-
 
 
| JOB_ID
 
| JOB_ID
 
| VARCHAR2(10)
 
| VARCHAR2(10)
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Current job of the employee; foreign key to job_id column of the
−
| EMP_JOB_IX
+
jobs table. A not null column.
−
|-
+
|- valign="top"
 
| SALARY
 
| SALARY
 
| NUMBER(8, 2)
 
| NUMBER(8, 2)
 
| null
 
| null
 
|  
 
|  
−
|  
+
| Monthly salary of the employee. Must be greater
−
|
+
than zero (enforced by constraint emp_salary_min)
−
|-
+
|- valign="top"
 
| COMMISSION_PCT
 
| COMMISSION_PCT
 
| NUMBER(2, 2)
 
| NUMBER(2, 2)
 
| null
 
| null
 
|  
 
|  
−
|  
+
| Commission percentage of the employee; Only employees in sales
−
|
+
department elgible for commission percentage
−
|-
+
|- valign="top"
 
| MANAGER_ID
 
| MANAGER_ID
 
| NUMBER(6, 0)
 
| NUMBER(6, 0)
 
| null
 
| null
 
|  
 
|  
−
|  
+
| Manager id of the employee; has same domain as manager_id in
−
| EMP_MANAGER_IX
+
departments table. Foreign key to employee_id column of employees table.
−
|-
+
(useful for reflexive joins and CONNECT BY query)
 +
|- valign="top"
 
| DEPARTMENT_ID
 
| DEPARTMENT_ID
 
| NUMBER(4, 0)
 
| NUMBER(4, 0)
 
| null
 
| null
 
|  
 
|  
 +
| Department id where employee works; foreign key to department_id
 +
column of the departments table
 +
|}
 +
 +
=== Primary Key ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Primary Key
 +
| '''Columns
 +
|- valign="top"
 +
| EMP_EMP_ID_PK
 +
| EMPLOYEE_ID
 +
|}
 +
 +
=== Indexes ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Index
 +
| '''Type
 +
| '''Columns
 +
 +
|- valign="top"
 +
| EMP_DEPARTMENT_IX
 +
|
 +
| DEPARTMENT_ID
 +
|- valign="top"
 +
| EMP_EMAIL_UK
 +
|
 +
| EMAIL
 +
|- valign="top"
 +
| EMP_JOB_IX
 +
|
 +
| JOB_ID
 +
|- valign="top"
 +
| EMP_MANAGER_IX
 +
|
 +
| MANAGER_ID
 +
|- valign="top"
 +
| EMP_NAME_IX
 
|  
 
|  
−
| EMP_DEPARTMENT_IX
+
| LAST_NAME, FIRST_NAME
 
|}
 
|}
  
 +
=== Check Constraints ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Check Constraint
 +
| '''Expression
 +
| '''Description
  
 +
|- valign="top"
 +
| EMP_EMAIL_NN
 +
| "EMAIL" IS NOT NULL
 +
|
 +
|- valign="top"
 +
| EMP_HIRE_DATE_NN
 +
| "HIRE_DATE" IS NOT NULL
 +
|
 +
|- valign="top"
 +
| EMP_JOB_NN
 +
| "JOB_ID" IS NOT NULL
 +
|
 +
|- valign="top"
 +
| EMP_LAST_NAME_NN
 +
| "LAST_NAME" IS NOT NULL
 +
|
 +
|- valign="top"
 +
| EMP_SALARY_MIN
 +
| salary > 0
 +
|
 +
|}
  
 +
=== Foreign Keys ===
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
Line 104: Line 168:
 
| '''Column
 
| '''Column
 
| '''Referenced Column
 
| '''Referenced Column
−
+
 
−
|-
+
|- valign="top"
 
| [[HR.DEPARTMENTS_(table)|HR.DEPARTMENTS]]
 
| [[HR.DEPARTMENTS_(table)|HR.DEPARTMENTS]]
 
| DEPARTMENT_ID
 
| DEPARTMENT_ID
 
| DEPARTMENT_ID
 
| DEPARTMENT_ID
−
|-
+
|- valign="top"
 
| [[HR.JOBS_(table)|HR.JOBS]]
 
| [[HR.JOBS_(table)|HR.JOBS]]
 
| JOB_ID
 
| JOB_ID
 
| JOB_ID
 
| JOB_ID
−
|-
+
|- valign="top"
 
| [[HR.EMPLOYEES_(table)|HR.EMPLOYEES]]
 
| [[HR.EMPLOYEES_(table)|HR.EMPLOYEES]]
 
| MANAGER_ID
 
| MANAGER_ID
Line 119: Line 183:
 
|}
 
|}
  
−
 
+
=== Detail Tables ===
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
Line 125: Line 189:
 
| '''Column
 
| '''Column
 
| '''Referencing Column
 
| '''Referencing Column
−
+
 
−
|-
+
|- valign="top"
 
| [[OE.CUSTOMERS_(table)|OE.CUSTOMERS]]
 
| [[OE.CUSTOMERS_(table)|OE.CUSTOMERS]]
 
| EMPLOYEE_ID
 
| EMPLOYEE_ID
 
| ACCOUNT_MGR_ID
 
| ACCOUNT_MGR_ID
−
|-
+
|- valign="top"
 
| [[HR.DEPARTMENTS_(table)|HR.DEPARTMENTS]]
 
| [[HR.DEPARTMENTS_(table)|HR.DEPARTMENTS]]
 
| EMPLOYEE_ID
 
| EMPLOYEE_ID
 
| MANAGER_ID
 
| MANAGER_ID
−
|-
+
|- valign="top"
 
| [[HR.EMPLOYEES_(table)|HR.EMPLOYEES]]
 
| [[HR.EMPLOYEES_(table)|HR.EMPLOYEES]]
 
| EMPLOYEE_ID
 
| EMPLOYEE_ID
 
| MANAGER_ID
 
| MANAGER_ID
−
|-
+
|- valign="top"
 
| [[HR.JOB_HISTORY_(table)|HR.JOB_HISTORY]]
 
| [[HR.JOB_HISTORY_(table)|HR.JOB_HISTORY]]
 
| EMPLOYEE_ID
 
| EMPLOYEE_ID
 
| EMPLOYEE_ID
 
| EMPLOYEE_ID
−
|-
+
|- valign="top"
 
| [[OE.ORDERS_(table)|OE.ORDERS]]
 
| [[OE.ORDERS_(table)|OE.ORDERS]]
 
| EMPLOYEE_ID
 
| EMPLOYEE_ID
 
| SALES_REP_ID
 
| SALES_REP_ID
 
|}
 
|}

Latest revision as of 11:33, 27 August 2011

wikibot[edit]

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

jobs, job_history tables. Contains a self reference.

Columns[edit]

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[edit]

Primary Key Columns
EMP_EMP_ID_PK EMPLOYEE_ID

Indexes[edit]

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

Check Constraints[edit]

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[edit]

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

Detail Tables[edit]

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
Retrieved from "http://dbshelp.devio.at/index.php?title=HR.EMPLOYEES_(table)&oldid=3048"