Last active
September 3, 2022 05:38
-
-
Save vijaysharmay/e214bc9d4124821f8cc9b01182c71693 to your computer and use it in GitHub Desktop.
Employee Schema
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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) | |
| ) | |
| ; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment