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
|
CREATE TABLE IF NOT EXISTS VehicleVersion (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
constructionType VARCHAR NOT NULL,
type VARCHAR NOT NULL,
hasNef BOOLEAN NOT NULL,
CHECK constructionType IN ('Normal', 'Hochdach', 'Mittelhochdach'),
CHECK type IN ('BKTW', 'KTW-B', 'KTW', 'RTW', 'NEF', 'NAH')
);
CREATE TABLE IF NOT EXISTS Vehicle (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
version BIGINT NOT NULL,
status VARCHAR NOT NULL,
FOREIGN KEY (version) REFERENCES VehicleVersion(id),
CHECK status IN ('abgemeldet', 'frei_wache', 'zum_berufungsort', 'am_berufungsort', 'zum_zielort',
'am_zielort', 'frei_funk', 'deleted')
);
CREATE TABLE IF NOT EXISTS EmployeeVersion (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
birthday DATE NOT NULL,
educationLevel VARCHAR NOT NULL,
isDriver BOOLEAN NOT NULL,
isPilot BOOLEAN NOT NULL,
CHECK educationLevel IN ('RS', 'NFS', 'NKV', 'NKA', 'NKI', 'NA')
);
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 VARCHAR NOT NULL,
created TIMESTAMP NOT NULL,
destination VARCHAR(100) NOT NULL,
additionalInfo VARCHAR(100),
CHECK severity IN ('A', 'B', 'C', 'D', 'E', 'O')
);
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),
);
|
