· 8 years ago · Jul 31, 2018, 08:02 PM
1-- Sample employee database
2-- See changelog table for details
3-- Copyright (C) 2007,2008, MySQL AB
4--
5-- Original data created by Fusheng Wang and Carlo Zaniolo
6-- http://www.cs.aau.dk/TimeCenter/software.htm
7-- http://www.cs.aau.dk/TimeCenter/Data/employeeTemporalDataSet.zip
8--
9-- Current schema by Giuseppe Maxia
10-- Data conversion from XML to relational by Patrick Crews
11--
12-- This work is licensed under the
13-- Creative Commons Attribution-Share Alike 3.0 Unported License.
14-- To view a copy of this license, visit
15-- http://creativecommons.org/licenses/by-sa/3.0/ or send a letter to
16-- Creative Commons, 171 Second Street, Suite 300, San Francisco,
17-- California, 94105, USA.
18--
19-- DISCLAIMER
20-- To the best of our knowledge, this data is fabricated, and
21-- it does not correspond to real people.
22-- Any similarity to existing people is purely coincidental.
23--
24
25DROP DATABASE IF EXISTS employees;
26CREATE DATABASE IF NOT EXISTS employees;
27USE employees;
28
29SELECT 'CREATING DATABASE STRUCTURE' as 'INFO';
30
31DROP TABLE IF EXISTS dept_emp,
32 dept_manager,
33 titles,
34 salaries,
35 employees,
36 departments;
37
38/*!50503 set default_storage_engine = InnoDB */;
39/*!50503 select CONCAT('storage engine: ', @@default_storage_engine) as INFO */;
40
41CREATE TABLE employees (
42 emp_no INT NOT NULL,
43 birth_date DATE NOT NULL,
44 first_name VARCHAR(14) NOT NULL,
45 last_name VARCHAR(16) NOT NULL,
46 gender ENUM ('M','F') NOT NULL,
47 hire_date DATE NOT NULL,
48 PRIMARY KEY (emp_no)
49);
50
51CREATE TABLE departments (
52 dept_no CHAR(4) NOT NULL,
53 dept_name VARCHAR(40) NOT NULL,
54 PRIMARY KEY (dept_no),
55 UNIQUE KEY (dept_name)
56);
57
58CREATE TABLE dept_manager (
59 emp_no INT NOT NULL,
60 dept_no CHAR(4) NOT NULL,
61 from_date DATE NOT NULL,
62 to_date DATE NOT NULL,
63 FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
64 FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
65 PRIMARY KEY (emp_no,dept_no)
66);
67
68CREATE TABLE dept_emp (
69 emp_no INT NOT NULL,
70 dept_no CHAR(4) NOT NULL,
71 from_date DATE NOT NULL,
72 to_date DATE NOT NULL,
73 FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
74 FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
75 PRIMARY KEY (emp_no,dept_no)
76);
77
78CREATE TABLE titles (
79 emp_no INT NOT NULL,
80 title VARCHAR(50) NOT NULL,
81 from_date DATE NOT NULL,
82 to_date DATE,
83 FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
84 PRIMARY KEY (emp_no,title, from_date)
85)
86;
87
88CREATE TABLE salaries (
89 emp_no INT NOT NULL,
90 salary INT NOT NULL,
91 from_date DATE NOT NULL,
92 to_date DATE NOT NULL,
93 FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
94 PRIMARY KEY (emp_no, from_date)
95)
96;
97
98CREATE OR REPLACE VIEW dept_emp_latest_date AS
99 SELECT emp_no, MAX(from_date) AS from_date, MAX(to_date) AS to_date
100 FROM dept_emp
101 GROUP BY emp_no;
102
103# shows only the current department for each employee
104CREATE OR REPLACE VIEW current_dept_emp AS
105 SELECT l.emp_no, dept_no, l.from_date, l.to_date
106 FROM dept_emp d
107 INNER JOIN dept_emp_latest_date l
108 ON d.emp_no=l.emp_no AND d.from_date=l.from_date AND l.to_date = d.to_date;
109
110flush /*!50503 binary */ logs;
111
112SELECT 'LOADING departments' as 'INFO';
113source load_departments.dump ;
114SELECT 'LOADING employees' as 'INFO';
115source load_employees.dump ;
116SELECT 'LOADING dept_emp' as 'INFO';
117source load_dept_emp.dump ;
118SELECT 'LOADING dept_manager' as 'INFO';
119source load_dept_manager.dump ;
120SELECT 'LOADING titles' as 'INFO';
121source load_titles.dump ;
122SELECT 'LOADING salaries' as 'INFO';
123source load_salaries1.dump ;
124source load_salaries2.dump ;
125source load_salaries3.dump ;
126
127source show_elapsed.sql ;