1
-- Sample employee database
2
-- See changelog table for details
3
-- Copyright (C) 2007,2008, MySQL AB
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
9
-- Current schema by Giuseppe Maxia
10
-- Data conversion from XML to relational by Patrick Crews
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.
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.
25
DROP DATABASE IF EXISTS employees;
26
CREATE DATABASE IF NOT EXISTS employees;
29
SELECT 'CREATING DATABASE STRUCTURE' as 'INFO';
31
DROP TABLE IF EXISTS dept_emp,
38
-- set storage_engine = InnoDB;
39
set storage_engine = MyISAM;
40
-- set storage_engine = Falcon;
41
-- set storage_engine = PBXT;
42
-- set storage_engine = Maria;
44
select CONCAT('storage engine: ', @@storage_engine) as INFO;
46
CREATE TABLE employees (
48
birth_date DATE NOT NULL,
49
first_name VARCHAR(14) NOT NULL,
50
last_name VARCHAR(16) NOT NULL,
51
gender ENUM ('M','F') NOT NULL,
52
hire_date DATE NOT NULL,
56
CREATE TABLE departments (
57
dept_no CHAR(4) NOT NULL,
58
dept_name VARCHAR(40) NOT NULL,
59
PRIMARY KEY (dept_no),
60
UNIQUE KEY (dept_name)
63
CREATE TABLE dept_manager (
64
dept_no CHAR(4) NOT NULL,
66
from_date DATE NOT NULL,
67
to_date DATE NOT NULL,
70
# FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
71
# FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
72
PRIMARY KEY (emp_no,dept_no)
75
CREATE TABLE dept_emp (
77
dept_no CHAR(4) NOT NULL,
78
from_date DATE NOT NULL,
79
to_date DATE NOT NULL,
82
# FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
83
# FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
84
PRIMARY KEY (emp_no,dept_no)
89
title VARCHAR(50) NOT NULL,
90
from_date DATE NOT NULL,
93
# FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
94
PRIMARY KEY (emp_no,title, from_date)
99
partition by range (year(from_date))
101
partition p01 values less than (1985),
102
partition p02 values less than (1986),
103
partition p03 values less than (1987),
104
partition p04 values less than (1988),
105
partition p05 values less than (1989),
106
partition p06 values less than (1990),
107
partition p07 values less than (1991),
108
partition p08 values less than (1992),
109
partition p09 values less than (1993),
110
partition p10 values less than (1994),
111
partition p11 values less than (1995),
112
partition p12 values less than (1996),
113
partition p13 values less than (1997),
114
partition p14 values less than (1998),
115
partition p15 values less than (1999),
116
partition p16 values less than (2000),
117
partition p17 values less than (2001),
118
partition p18 values less than (2002),
119
partition p19 values less than (MAXVALUE)
123
CREATE TABLE salaries (
126
from_date DATE NOT NULL,
127
to_date DATE NOT NULL,
129
# FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
130
PRIMARY KEY (emp_no, from_date)
134
partition by range (year(from_date))
136
partition p01 values less than (1985),
137
partition p02 values less than (1986),
138
partition p03 values less than (1987),
139
partition p04 values less than (1988),
140
partition p05 values less than (1989),
141
partition p06 values less than (1990),
142
partition p07 values less than (1991),
143
partition p08 values less than (1992),
144
partition p09 values less than (1993),
145
partition p10 values less than (1994),
146
partition p11 values less than (1995),
147
partition p12 values less than (1996),
148
partition p13 values less than (1997),
149
partition p14 values less than (1998),
150
partition p15 values less than (1999),
151
partition p16 values less than (2000),
152
partition p17 values less than (2001),
153
partition p18 values less than (2002),
154
partition p19 values less than (MAXVALUE)
158
SELECT 'LOADING departments' as 'INFO';
159
source load_departments.dump ;
160
SELECT 'LOADING employees' as 'INFO';
161
source load_employees.dump ;
162
SELECT 'LOADING dept_emp' as 'INFO';
163
source load_dept_emp.dump ;
164
SELECT 'LOADING dept_manager' as 'INFO';
165
source load_dept_manager.dump ;
166
SELECT 'LOADING titles' as 'INFO';
167
source load_titles.dump ;
168
SELECT 'LOADING salaries' as 'INFO';
169
source load_salaries.dump ;