转：HR schema

###createe

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)
) ;

（编辑：宣城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!