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
|
CREATE TABLE IF NOT EXISTS VehicleVersion (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
constructionType ENUM('NORMAL', 'HOCHDACH', 'MITTELHOCHDACH') NOT NULL,
type ENUM('BKTW', 'KTW_B', 'KTW', 'RTW', 'NEF', 'NAH') NOT NULL,
hasNef BOOLEAN NOT NULL,
);
CREATE TABLE IF NOT EXISTS Vehicle (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
version BIGINT NOT NULL,
status ENUM('ABGEMELDET', 'FREI_WACHE', 'FREI_FUNK', 'ZUM_BERUFUNGSORT', 'AM_BERUFUNGSORT',
'ZUM_ZIELORT', 'AM_ZIELORT', 'DELETED') NOT NULL,
FOREIGN KEY (version) REFERENCES VehicleVersion(id),
);
CREATE TABLE IF NOT EXISTS EmployeeVersion (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
birthday DATE NOT NULL,
educationLevel ENUM('RS', 'NFS', 'NKV', 'NKA', 'NKI', 'NA') NOT NULL,
isDriver BOOLEAN NOT NULL,
isPilot BOOLEAN NOT NULL,
);
CREATE TABLE IF NOT EXISTS Employee (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
version BIGINT NOT NULL,
FOREIGN KEY (version) REFERENCES EmployeeVersion(id),
);
CREATE TABLE IF NOT EXISTS Registration (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
vehicleId BIGINT NOT NULL,
employeeId BIGINT NOT NULL,
start TIMESTAMP NOT NULL,
end TIMESTAMP NOT NULL,
active BOOLEAN NOT NULL,
FOREIGN KEY (vehicleId) REFERENCES VehicleVersion(id),
FOREIGN KEY (employeeId) REFERENCES EmployeeVersion(id),
);
CREATE TABLE IF NOT EXISTS Operation (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
opCode VARCHAR(20) NOT NULL,
severity ENUM('A', 'B', 'C', 'D', 'E', 'O') NOT NULL,
created TIMESTAMP NOT NULL,
destination VARCHAR(100) NOT NULL,
additionalInfo VARCHAR(100),
status ENUM('ACTIVE', 'COMPLETED', 'CANCELLED'),
);
CREATE TABLE IF NOT EXISTS VehicleOperation (
vehicleId BIGINT NOT NULL,
operationId BIGINT NOT NULL,
FOREIGN KEY (vehicleId) REFERENCES VehicleVersion(id),
FOREIGN KEY (operationId) REFERENCES Operation(id),
PRIMARY KEY (vehicleId, operationId),
);
|
