Rem Rem $Header: hr_cre.sql 29-aug-2002.11:44:03 hyeh Exp $ Rem Rem hr_cre.sql Rem Rem Copyright (c) 2001, 2002, Oracle Corporation. All rights reserved. Rem Rem NAME Rem hr_cre.sql - Create data objects for HR schema Rem Rem DESCRIPTION Rem This script creates six tables, associated constraints Rem and indexes in the human resources (HR) schema. Rem Rem NOTES Rem Rem CREATED by Nancy Greenberg, Nagavalli Pataballa - 06/01/00 Rem Rem MODIFIED (MM/DD/YY) Rem hyeh 08/29/02 - hyeh_mv_comschema_to_rdbms Rem ahunold 09/14/00 - Added emp_details_view Rem ahunold 02/20/01 - New header Rem vpatabal 03/02/01 - Added regions table, modified regions Rem column in countries table to NUMBER. Rem Added foreign key from countries table Rem to regions table on region_id. Rem Removed currency name, currency symbol Rem columns from the countries table. Rem Removed dn columns from employees and Rem departments tables. Rem Added sequences. Rem Removed not null constraint from Rem salary column of the employees table. SET FEEDBACK 1 SET NUMWIDTH 10 SET LINESIZE 80 SET TRIMSPOOL ON SET TAB OFF SET PAGESIZE 100 SET ECHO OFF REM ******************************************************************** REM Create the REGIONS table to hold region information for locations REM HR.LOCATIONS table has a foreign key to this table. Prompt ****** Creating REGIONS table .... CREATE TABLE regions ( region_id NUMBER CONSTRAINT region_id_nn NOT NULL , region_name VARCHAR2(25) ); CREATE UNIQUE INDEX reg_id_pk ON regions (region_id); ALTER TABLE regions ADD ( CONSTRAINT reg_id_pk PRIMARY KEY (region_id) ) ; REM ******************************************************************** REM Create the COUNTRIES table to hold country information for customers REM and company locations. REM OE.CUSTOMERS table and HR.LOCATIONS have a foreign key to this table. Prompt ****** Creating COUNTRIES table .... CREATE TABLE countries ( country_id CHAR(2) CONSTRAINT country_id_nn NOT NULL , country_name VARCHAR2(40) , region_id NUMBER , CONSTRAINT country_c_id_pk PRIMARY KEY (country_id) ) ORGANIZATION INDEX; ALTER TABLE countries ADD ( CONSTRAINT countr_reg_fk FOREIGN KEY (region_id) REFERENCES regions(region_id) ) ; REM ******************************************************************** REM Create the LOCATIONS table to hold address information for company departments. REM HR.DEPARTMENTS has a foreign key to this table. Prompt ****** Creating LOCATIONS table .... CREATE TABLE locations ( location_id NUMBER(4) , street_address VARCHAR2(40) , postal_code VARCHAR2(12) , city VARCHAR2(30) CONSTRAINT loc_city_nn NOT NULL , state_province VARCHAR2(25) , country_id CHAR(2) ) ; CREATE UNIQUE INDEX loc_id_pk ON locations (location_id) ; ALTER TABLE locations ADD ( CONSTRAINT loc_id_pk PRIMARY KEY (location_id) , CONSTRAINT loc_c_id_fk FOREIGN KEY (country_id) REFERENCES countries(country_id) ) ; Rem Useful for any subsequent addition of rows to locations table Rem Starts with 3300 CREATE SEQUENCE locations_seq START WITH 3300 INCREMENT BY 100 MAXVALUE 9900 NOCACHE NOCYCLE; REM ******************************************************************** REM Create the DEPARTMENTS table to hold company department information. REM HR.EMPLOYEES and HR.JOB_HISTORY have a foreign key to this table. Prompt ****** Creating DEPARTMENTS table .... CREATE TABLE departments ( department_id NUMBER(4) , department_name VARCHAR2(30) CONSTRAINT dept_name_nn NOT NULL , manager_id NUMBER(6) , location_id NUMBER(4) ) ; CREATE UNIQUE INDEX dept_id_pk ON departments (department_id) ; ALTER TABLE departments ADD ( CONSTRAINT dept_id_pk PRIMARY KEY (department_id) , CONSTRAINT dept_loc_fk FOREIGN KEY (location_id) REFERENCES locations (location_id) ) ; Rem Useful for any subsequent addition of rows to departments table Rem Starts with 280 CREATE SEQUENCE departments_seq START WITH 280 INCREMENT BY 10 MAXVALUE 9990 NOCACHE NOCYCLE; REM ******************************************************************** REM Create the JOBS table to hold the different names of job roles within the company. REM HR.EMPLOYEES has a foreign key to this table. Prompt ****** Creating JOBS table .... CREATE TABLE jobs ( job_id VARCHAR2(10) , job_title VARCHAR2(35) CONSTRAINT job_title_nn NOT NULL , min_salary NUMBER(6) , max_salary NUMBER(6) ) ; CREATE UNIQUE INDEX job_id_pk ON jobs (job_id) ; ALTER TABLE jobs ADD ( CONSTRAINT job_id_pk PRIMARY KEY(job_id) ) ; REM ******************************************************************** REM Create the EMPLOYEES table to hold the employee personnel REM information for the company. REM HR.EMPLOYEES has a self referencing foreign key to this table. Prompt ****** Creating EMPLOYEES table .... CREATE TABLE employees ( employee_id NUMBER(6) , first_name VARCHAR2(20) , last_name VARCHAR2(25) CONSTRAINT emp_last_name_nn NOT NULL , email VARCHAR2(25) CONSTRAINT emp_email_nn NOT NULL , phone_number VARCHAR2(20) , hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL , job_id VARCHAR2(10) CONSTRAINT emp_job_nn NOT NULL , salary NUMBER(8,2) , commission_pct NUMBER(2,2) , manager_id NUMBER(6) , department_id NUMBER(4) , CONSTRAINT emp_salary_min CHECK (salary > 0) , CONSTRAINT emp_email_uk UNIQUE (email) ) ; CREATE UNIQUE INDEX emp_emp_id_pk ON employees (employee_id) ; ALTER TABLE employees ADD ( CONSTRAINT emp_emp_id_pk PRIMARY KEY (employee_id) , CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments , CONSTRAINT emp_job_fk FOREIGN KEY (job_id) REFERENCES jobs (job_id) , CONSTRAINT emp_manager_fk FOREIGN KEY (manager_id) REFERENCES employees ) ; ALTER TABLE departments ADD ( CONSTRAINT dept_mgr_fk FOREIGN KEY (manager_id) REFERENCES employees (employee_id) ) ; Rem Useful for any subsequent addition of rows to employees table Rem Starts with 207 CREATE SEQUENCE employees_seq START WITH 207 INCREMENT BY 1 NOCACHE NOCYCLE; REM ******************************************************************** REM Create the JOB_HISTORY table to hold the history of jobs that REM employees have held in the past. REM HR.JOBS, HR_DEPARTMENTS, and HR.EMPLOYEES have a foreign key to this table. Prompt ****** Creating JOB_HISTORY table .... CREATE TABLE job_history ( employee_id NUMBER(6) CONSTRAINT jhist_employee_nn NOT NULL , start_date DATE CONSTRAINT jhist_start_date_nn NOT NULL , end_date DATE CONSTRAINT jhist_end_date_nn NOT NULL , job_id VARCHAR2(10) CONSTRAINT jhist_job_nn NOT NULL , department_id NUMBER(4) , CONSTRAINT jhist_date_interval CHECK (end_date > start_date) ) ; CREATE UNIQUE INDEX jhist_emp_id_st_date_pk ON job_history (employee_id, start_date) ; ALTER TABLE job_history ADD ( CONSTRAINT jhist_emp_id_st_date_pk PRIMARY KEY (employee_id, start_date) , CONSTRAINT jhist_job_fk FOREIGN KEY (job_id) REFERENCES jobs , CONSTRAINT jhist_emp_fk FOREIGN KEY (employee_id) REFERENCES employees , CONSTRAINT jhist_dept_fk FOREIGN KEY (department_id) REFERENCES departments ) ; REM ******************************************************************** REM Create the EMP_DETAILS_VIEW that joins the employees, jobs, REM departments, jobs, countries, and locations table to provide details REM about employees. Prompt ****** Creating EMP_DETAILS_VIEW view ... CREATE OR REPLACE VIEW emp_details_view (employee_id, job_id, manager_id, department_id, location_id, country_id, first_name, last_name, salary, commission_pct, department_name, job_title, city, state_province, country_name, region_name) AS SELECT e.employee_id, e.job_id, e.manager_id, e.department_id, d.location_id, l.country_id, e.first_name, e.last_name, e.salary, e.commission_pct, d.department_name, j.job_title, l.city, l.state_province, c.country_name, r.region_name FROM employees e, departments d, jobs j, locations l, countries c, regions r WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND l.country_id = c.country_id AND c.region_id = r.region_id AND j.job_id = e.job_id WITH READ ONLY; COMMIT;