내용

글번호 759
작성자 허진경
작성일 2017-10-08 16:24:04
제목 MySQL에 오라클 HR 스키마의 테이블을 생성하는 문장(MySQL HR DDL)
내용 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;
첨부파일 MySQL_HR_DDL.sql (5,720byte)