Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DROP DATABASE IF EXISTS employees;
- CREATE DATABASE IF NOT EXISTS employees;
- USE employees;
- DROP TABLE IF EXISTS dept_emp,
- dept_manager,
- titles,
- salaries,
- employees,
- departments;
- CREATE TABLE employees (
- emp_no INT NOT NULL,
- birth_date DATE NOT NULL,
- first_name VARCHAR(14) NOT NULL,
- last_name VARCHAR(16) NOT NULL,
- gender ENUM ('M','F') NOT NULL,
- hire_date DATE NOT NULL,
- PRIMARY KEY (emp_no)
- );
- CREATE TABLE departments (
- dept_no CHAR(4) NOT NULL,
- dept_name VARCHAR(40) NOT NULL,
- PRIMARY KEY (dept_no),
- UNIQUE KEY (dept_name)
- );
- CREATE TABLE dept_manager (
- emp_no INT NOT NULL,
- dept_no CHAR(4) NOT NULL,
- from_date DATE NOT NULL,
- to_date DATE NOT NULL,
- FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
- FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
- PRIMARY KEY (emp_no,dept_no)
- );
- CREATE TABLE dept_emp (
- emp_no INT NOT NULL,
- dept_no CHAR(4) NOT NULL,
- from_date DATE NOT NULL,
- to_date DATE NOT NULL,
- FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
- FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
- PRIMARY KEY (emp_no,dept_no)
- );
- CREATE TABLE titles (
- emp_no INT NOT NULL,
- title VARCHAR(50) NOT NULL,
- from_date DATE NOT NULL,
- to_date DATE,
- FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
- PRIMARY KEY (emp_no,title, from_date)
- );
- CREATE TABLE salaries (
- emp_no INT NOT NULL,
- salary INT NOT NULL,
- from_date DATE NOT NULL,
- to_date DATE NOT NULL,
- FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
- PRIMARY KEY (emp_no, from_date)
- );
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement