내용 |
DROP TABLE IF EXISTS regions CASCADE;
CREATE TABLE regions
( region_id INT NOT NULL
, region_name VARCHAR(25)
);
/*DROP INDEX reg_id_pk ON regions;*/
CREATE UNIQUE INDEX reg_id_pk
ON regions (region_id);
ALTER TABLE regions
ADD ( CONSTRAINT reg_id_pk
PRIMARY KEY (region_id)
) ;
DROP TABLE IF EXISTS countries CASCADE;
CREATE TABLE countries
( country_id CHAR(2) PRIMARY KEY
, country_name VARCHAR(40)
, region_id INT
);
ALTER TABLE countries
ADD ( CONSTRAINT countr_reg_fk
FOREIGN KEY (region_id)
REFERENCES regions(region_id)
) ;
DROP TABLE IF EXISTS locations CASCADE;
CREATE TABLE locations
( location_id INT(4)
, street_address VARCHAR(40)
, postal_code VARCHAR(12)
, city VARCHAR(30) NOT NULL
, state_province VARCHAR(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)
) ;
/*
CREATE SEQUENCE locations_seq
START WITH 3300
INCREMENT BY 100
MAXVALUE 9900
NOCACHE
NOCYCLE;
*/
DROP TABLE IF EXISTS departments CASCADE;
CREATE TABLE departments
( department_id INT(4)
, department_name VARCHAR(30) NOT NULL
, manager_id INT(6)
, location_id INT(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)
) ;
/*
CREATE SEQUENCE departments_seq
START WITH 280
INCREMENT BY 10
MAXVALUE 9990
NOCACHE
NOCYCLE;
*/
DROP TABLE IF EXISTS jobs CASCADE;
CREATE TABLE jobs
( job_id VARCHAR(10)
, job_title VARCHAR(35) NOT NULL
, min_salary INT(6)
, max_salary INT(6)
) ;
CREATE UNIQUE INDEX job_id_pk
ON jobs (job_id) ;
ALTER TABLE jobs
ADD ( CONSTRAINT job_id_pk
PRIMARY KEY(job_id)
) ;
DROP TABLE IF EXISTS employees CASCADE;
CREATE TABLE employees
( employee_id INT(6)
, first_name VARCHAR(20)
, last_name VARCHAR(25) NOT NULL
, email VARCHAR(25) NOT NULL
, phone_number VARCHAR(20)
, hire_date DATE NOT NULL
, job_id VARCHAR(10) NOT NULL
, salary DOUBLE(8,2)
, commission_pct DOUBLE(2,2)
, manager_id INT(6)
, department_id INT(4)
, CONSTRAINT emp_salary_min
CHECK (salary > 0)
, CONSTRAINT emp_email_uk
UNIQUE (email)
) ;
DROP INDEX emp_emp_id_pk ON employees;
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(department_id)
, CONSTRAINT emp_job_fk
FOREIGN KEY (job_id)
REFERENCES jobs (job_id)
, CONSTRAINT emp_manager_fk
FOREIGN KEY (manager_id)
REFERENCES employees(employee_id)
) ;
ALTER TABLE departments
ADD ( CONSTRAINT dept_mgr_fk
FOREIGN KEY (manager_id)
REFERENCES employees (employee_id)
) ;
/*
CREATE SEQUENCE employees_seq
START WITH 207
INCREMENT BY 1
NOCACHE
NOCYCLE;
*/
DROP TABLE IF EXISTS job_history CASCADE;
CREATE TABLE job_history
( employee_id INT(6) NOT NULL
, start_date DATE NOT NULL
, end_date DATE NOT NULL
, job_id VARCHAR(10) NOT NULL
, department_id INT(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(job_id)
, CONSTRAINT jhist_emp_fk
FOREIGN KEY (employee_id)
REFERENCES employees(employee_id)
, CONSTRAINT jhist_dept_fk
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
) ;
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 CASCADED CHECK OPTION;
COMMIT; |