/
employees_partitioned_5.1.sql
199 lines (181 loc) · 7.76 KB
/
employees_partitioned_5.1.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
-- Sample employee database
-- See changelog table for details
-- Copyright (C) 2007,2008, MySQL AB
--
-- Original data created by Fusheng Wang and Carlo Zaniolo
-- http://www.cs.aau.dk/TimeCenter/software.htm
-- http://www.cs.aau.dk/TimeCenter/Data/employeeTemporalDataSet.zip
--
-- Current schema by Giuseppe Maxia
-- Data conversion from XML to relational by Patrick Crews
--
-- This work is licensed under the
-- Creative Commons Attribution-Share Alike 3.0 Unported License.
-- To view a copy of this license, visit
-- http://creativecommons.org/licenses/by-sa/3.0/ or send a letter to
-- Creative Commons, 171 Second Street, Suite 300, San Francisco,
-- California, 94105, USA.
--
-- DISCLAIMER
-- To the best of our knowledge, this data is fabricated, and
-- it does not correspond to real people.
-- Any similarity to existing people is purely coincidental.
--
DROP DATABASE IF EXISTS employees;
CREATE DATABASE IF NOT EXISTS employees;
USE employees;
SELECT 'CREATING DATABASE STRUCTURE' as 'INFO';
DROP TABLE IF EXISTS dept_emp,
dept_manager,
titles,
salaries,
employees,
departments;
/*!50503 set default_storage_engine = InnoDB */;
/*!50503 select CONCAT('storage engine: ', @@default_storage_engine) as INFO */;
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)
);
/*!50130
ALTER TABLE titles
partition by range (to_days(from_date))
(
partition p01 values less than (to_days('1985-12-31')),
partition p02 values less than (to_days('1986-12-31')),
partition p03 values less than (to_days('1987-12-31')),
partition p04 values less than (to_days('1988-12-31')),
partition p05 values less than (to_days('1989-12-31')),
partition p06 values less than (to_days('1990-12-31')),
partition p07 values less than (to_days('1991-12-31')),
partition p08 values less than (to_days('1992-12-31')),
partition p09 values less than (to_days('1993-12-31')),
partition p10 values less than (to_days('1994-12-31')),
partition p11 values less than (to_days('1995-12-31')),
partition p12 values less than (to_days('1996-12-31')),
partition p13 values less than (to_days('1997-12-31')),
partition p14 values less than (to_days('1998-12-31')),
partition p15 values less than (to_days('1999-12-31')),
partition p16 values less than (to_days('2000-12-31')),
partition p17 values less than (to_days('2001-12-31')),
partition p18 values less than (to_days('2002-12-31')),
partition p19 values less than (to_days('3000-12-31'))
) */;
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)
);
/*!50130
ALTER TABLE salaries
partition by range (to_days(from_date))
(
partition p01 values less than (to_days('1985-01-01')),
partition p02 values less than (to_days('1986-01-01')),
partition p03 values less than (to_days('1987-01-01')),
partition p04 values less than (to_days('1988-01-01')),
partition p05 values less than (to_days('1989-01-01')),
partition p06 values less than (to_days('1990-01-01')),
partition p07 values less than (to_days('1991-01-01')),
partition p08 values less than (to_days('1992-01-01')),
partition p09 values less than (to_days('1993-01-01')),
partition p10 values less than (to_days('1994-01-01')),
partition p11 values less than (to_days('1995-01-01')),
partition p12 values less than (to_days('1996-01-01')),
partition p13 values less than (to_days('1997-01-01')),
partition p14 values less than (to_days('1998-01-01')),
partition p15 values less than (to_days('1999-01-01')),
partition p16 values less than (to_days('2000-01-01')),
partition p17 values less than (to_days('2001-01-01')),
partition p18 values less than (to_days('2001-02-01')),
partition p19 values less than (to_days('2001-03-01')),
partition p20 values less than (to_days('2001-04-01')),
partition p21 values less than (to_days('2001-05-01')),
partition p22 values less than (to_days('2001-06-01')),
partition p23 values less than (to_days('2001-07-01')),
partition p24 values less than (to_days('2001-08-01')),
partition p25 values less than (to_days('2001-09-01')),
partition p26 values less than (to_days('2001-10-01')),
partition p27 values less than (to_days('2001-11-01')),
partition p28 values less than (to_days('2001-12-01')),
partition p29 values less than (to_days('2002-01-01')),
partition p30 values less than (to_days('2002-02-01')),
partition p31 values less than (to_days('2002-03-01')),
partition p32 values less than (to_days('2002-04-01')),
partition p33 values less than (to_days('2002-05-01')),
partition p34 values less than (to_days('2002-06-01')),
partition p35 values less than (to_days('2002-07-01')),
partition p36 values less than (to_days('2002-08-01')),
partition p37 values less than (to_days('2002-09-01')),
partition p38 values less than (to_days('2002-10-01')),
partition p39 values less than (to_days('2002-11-01')),
partition p40 values less than (to_days('2002-12-01')),
partition p41 values less than (to_days('3000-01-01'))
)
*/;
CREATE OR REPLACE VIEW dept_emp_latest_date AS
SELECT emp_no, MAX(from_date) AS from_date, MAX(to_date) AS to_date
FROM dept_emp
GROUP BY emp_no;
# shows only the current department for each employee
CREATE OR REPLACE VIEW current_dept_emp AS
SELECT l.emp_no, dept_no, l.from_date, l.to_date
FROM dept_emp d
INNER JOIN dept_emp_latest_date l
ON d.emp_no=l.emp_no AND d.from_date=l.from_date AND l.to_date = d.to_date;
flush /*!50503 binary */ logs;
SELECT 'LOADING departments' as 'INFO';
source load_departments.dump ;
SELECT 'LOADING employees' as 'INFO';
source load_employees.dump ;
SELECT 'LOADING dept_emp' as 'INFO';
source load_dept_emp.dump ;
SELECT 'LOADING dept_manager' as 'INFO';
source load_dept_manager.dump ;
SELECT 'LOADING titles' as 'INFO';
source load_titles.dump ;
SELECT 'LOADING salaries' as 'INFO';
source load_salaries1.dump ;
source load_salaries2.dump ;
source load_salaries3.dump ;
source show_elapsed.sql ;