SchemaToDoc Enterprise Edition

SchemaToDoc Enterprise Edition - hr

Note: The Type column includes the maximum size for character fields and the precision/scale (if they have been set) for Number fields.

Table of Contents

All Tables (Tables)........................................................................................................................................ 2

COUNTRIES........................................................................................................................................... 2

DEPARTMENTS..................................................................................................................................... 2

EMPLOYEES.......................................................................................................................................... 3

JOBS........................................................................................................................................................ 3

JOB_HISTORY....................................................................................................................................... 3

LOCATIONS.......................................................................................................................................... 3

REGIONS................................................................................................................................................ 3

All Views (Views)......................................................................................................................................... 3

EMP_DETAILS_VIEW........................................................................................................................... 3

All Stored Procedures (Stored Procedures)................................................................................................... 3

ADD_JOB_HISTORY............................................................................................................................. 3

SECURE_DML........................................................................................................................................ 3

All Tables (Tables)

COUNTRIES

country table. Contains 25 rows. References with locations table.

Primary Key(s): COUNTRY_ID

Field	Type	Nulls?	Comments
COUNTRY_ID	CHAR (2)	No	Primary key of countries table.
COUNTRY_NAME	VARCHAR2 (40)	Yes	Country name
REGION_ID	NUMBER	Yes	Region ID for the country. Foreign key to region_id column in the departments table.

Index Name	Type	Unique	Fields
COUNTRY_C_ID_PK	IOT - TOP	Yes	COUNTRY_ID

No Check Constraints

Internal Foreign Key Constraint	Affected Field	Source Schema	Source Table
COUNTR_REG_FK	REGION_ID	HR	REGIONS

Primary Key as Foreign Key Constraint	Affected Schema	Affected Table	Affected Field
LOC_C_ID_FK	HR	LOCATIONS	COUNTRY_ID

No Enabled Triggers

DEPARTMENTS

Departments table that shows details of departments where employees

work. Contains 27 rows; references with locations, employees, and job_history tables.

Primary Key(s): DEPARTMENT_ID

Field	Type	Nulls?	Comments
DEPARTMENT_ID	NUMBER (4)	No	Primary key column of departments table.
DEPARTMENT_NAME	VARCHAR2 (30)	No	A not null column that shows name of a department. Administration, Marketing, Purchasing, Human Resources, Shipping, IT, Executive, Public Relations, Sales, Finance, and Accounting.
MANAGER_ID	NUMBER (6)	Yes	Manager_id of a department. Foreign key to employee_id column of employees table. The manager_id column of the employee table references this column.
LOCATION_ID	NUMBER (4)	Yes	Location id where a department is located. Foreign key to location_id column of locations table.

Index Name	Type	Unique	Fields
DEPT_ID_PK	NORMAL	Yes	DEPARTMENT_ID
DEPT_LOCATION_IX	NORMAL	No	LOCATION_ID

No Check Constraints

Internal Foreign Key Constraint	Affected Field	Source Schema	Source Table
DEPT_LOC_FK	LOCATION_ID	HR	LOCATIONS
DEPT_MGR_FK	MANAGER_ID	HR	EMPLOYEES

Primary Key as Foreign Key Constraint	Affected Schema	Affected Table	Affected Field
EMP_DEPT_FK	HR	EMPLOYEES	DEPARTMENT_ID
JHIST_DEPT_FK	HR	JOB_HISTORY	DEPARTMENT_ID

No Enabled Triggers

EMPLOYEES

employees table. Contains 107 rows. References with departments,

jobs, job_history tables. Contains a self reference.

Primary Key(s): EMPLOYEE_ID

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

Index Name	Type	Unique	Fields
EMP_DEPARTMENT_IX	NORMAL	No	DEPARTMENT_ID
EMP_EMAIL_UK	NORMAL	Yes	EMAIL
EMP_EMP_ID_PK	NORMAL	Yes	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 Name	Text
EMP_SALARY_MIN	salary > 0

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

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 Name	Trigger
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;

Trigger Name

Trigger

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;

JOBS

jobs table with job titles and salary ranges. Contains 19 rows.

References with employees and job_history table.

Primary Key(s): JOB_ID

Field	Type	Nulls?	Comments
JOB_ID	VARCHAR2 (10)	No	Primary key of jobs table.
JOB_TITLE	VARCHAR2 (35)	No	A not null column that shows job title, e.g. AD_VP, FI_ACCOUNTANT
MIN_SALARY	NUMBER (6)	Yes	Minimum salary for a job title.
MAX_SALARY	NUMBER (6)	Yes	Maximum salary for a job title

Index Name	Type	Unique	Fields
JOB_ID_PK	NORMAL	Yes	JOB_ID

No Check Constraints

No Internal Foreign Keys

Primary Key as Foreign Key Constraint	Affected Schema	Affected Table	Affected Field
EMP_JOB_FK	HR	EMPLOYEES	JOB_ID
JHIST_JOB_FK	HR	JOB_HISTORY	JOB_ID

No Enabled Triggers

JOB_HISTORY

Table that stores job history of the employees. If an employee

changes departments within the job or changes jobs within the department,

new rows get inserted into this table with old job information of the

employee. Contains a complex primary key: employee_id+start_date.

Contains 25 rows. References with jobs, employees, and departments tables.

Primary Key(s): EMPLOYEE_ID, START_DATE

Field	Type	Nulls?	Comments
EMPLOYEE_ID	NUMBER (6)	No	A not null column in the complex primary key employee_id+start_date. Foreign key to employee_id column of the employee table
START_DATE	DATE	No	A not null column in the complex primary key employee_id+start_date. Must be less than the end_date of the job_history table. (enforced by constraint jhist_date_interval)
END_DATE	DATE	No	Last day of the employee in this job role. A not null column. Must be greater than the start_date of the job_history table. (enforced by constraint jhist_date_interval)
JOB_ID	VARCHAR2 (10)	No	Job role in which the employee worked in the past; foreign key to job_id column in the jobs table. A not null column.
DEPARTMENT_ID	NUMBER (4)	Yes	Department id in which the employee worked in the past; foreign key to deparment_id column in the departments table

Index Name	Type	Unique	Fields
JHIST_DEPARTMENT_IX	NORMAL	No	DEPARTMENT_ID
JHIST_EMPLOYEE_IX	NORMAL	No	EMPLOYEE_ID
JHIST_EMP_ID_ST_DATE_PK	NORMAL	Yes	EMPLOYEE_ID, START_DATE
JHIST_JOB_IX	NORMAL