Bs3MFqya

1DROP DATABASE IF EXISTS employees;
2CREATE DATABASE IF NOT EXISTS employees;
3USE employees;
4
5SELECT 'CREATING DATABASE STRUCTURE' as 'INFO';
6
7DROP TABLE IF EXISTS dept_emp,
8                     dept_manager,
9                     titles,
10                     salaries, 
11                     employees, 
12                     departments;
13
14/*!50503 set default_storage_engine = InnoDB */;
15/*!50503 select CONCAT('storage engine: ', @@default_storage_engine) as INFO */;
16
17CREATE TABLE employees (
18    emp_no      INT             NOT NULL,
19    birth_date  DATE            NOT NULL,
20    first_name  VARCHAR(14)     NOT NULL,
21    last_name   VARCHAR(16)     NOT NULL,
22    gender      ENUM ('M','F')  NOT NULL,    
23    hire_date   DATE            NOT NULL,
24    PRIMARY KEY (emp_no)
25);
26
27CREATE TABLE departments (
28    dept_no     CHAR(4)         NOT NULL,
29    dept_name   VARCHAR(40)     NOT NULL,
30    PRIMARY KEY (dept_no),
31    UNIQUE  KEY (dept_name)
32);
33
34CREATE TABLE dept_manager (
35   emp_no       INT             NOT NULL,
36   dept_no      CHAR(4)         NOT NULL,
37   from_date    DATE            NOT NULL,
38   to_date      DATE            NOT NULL,
39   FOREIGN KEY (emp_no)  REFERENCES employees (emp_no)    ON DELETE CASCADE,
40   FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
41   PRIMARY KEY (emp_no,dept_no)
42); 
43
44CREATE TABLE dept_emp (
45    emp_no      INT             NOT NULL,
46    dept_no     CHAR(4)         NOT NULL,
47    from_date   DATE            NOT NULL,
48    to_date     DATE            NOT NULL,
49    FOREIGN KEY (emp_no)  REFERENCES employees   (emp_no)  ON DELETE CASCADE,
50    FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
51    PRIMARY KEY (emp_no,dept_no)
52);
53
54CREATE TABLE titles (
55    emp_no      INT             NOT NULL,
56    title       VARCHAR(50)     NOT NULL,
57    from_date   DATE            NOT NULL,
58    to_date     DATE,
59    FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
60    PRIMARY KEY (emp_no,title, from_date)
61) 
62; 
63
64CREATE TABLE salaries (
65    emp_no      INT             NOT NULL,
66    salary      INT             NOT NULL,
67    from_date   DATE            NOT NULL,
68    to_date     DATE            NOT NULL,
69    FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
70    PRIMARY KEY (emp_no, from_date)
71);