pkdEyztx

1DROP DATABASE IF EXISTS mzk;
2CREATE DATABASE mzk;
3USE mzk;
4
5CREATE TABLE drivers(
6    `driver_id` INT(11) NOT NULL AUTO_INCREMENT UNIQUE,
7    `name` VARCHAR(30),
8    `surname` VARCHAR(30),
9    PRIMARY KEY(`driver_id`)
10);
11
12CREATE TABLE buses(
13    `bus_id` INT(11) NOT NULL AUTO_INCREMENT UNIQUE,
14    `brand` VARCHAR(30),
15    `model` VARCHAR(30),
16    `licence_plate` VARCHAR(8),
17    PRIMARY KEY(`bus_id`)
18);
19
20CREATE TABLE bus_stops(
21    `bus_stop_id` INT(11) NOT NULL AUTO_INCREMENT UNIQUE,
22    `name` VARCHAR(40) NOT NULL,
23    `street` VARCHAR(40) NOT NULL,
24    PRIMARY KEY (`bus_stop_id`)
25);
26
27CREATE TABLE bus_lines(
28    `line_no` INT(11) NOT NULL UNIQUE,
29    PRIMARY KEY (`line_no`)
30);
31
32CREATE TABLE line_routes(
33    `line_no` INT(11) NOT NULL,
34    `route` VARCHAR(40) NOT NULL,
35    FOREIGN KEY (line_no) REFERENCES bus_lines(`line_no`),
36    CONSTRAINT line_route PRIMARY KEY (`line_no`, `route`)
37);
38
39CREATE TABLE courses(
40    `course_no` INT(11) NOT NULL AUTO_INCREMENT UNIQUE,
41    `driver_id` INT(11) NOT NULL,
42    `line_no` INT(11) NOT NULL,
43    `route` VARCHAR(40) NOT NULL,
44    `bus_id` INT(11) NOT NULL,
45    `date` DATETIME,
46    FOREIGN KEY (`driver_id`) REFERENCES drivers(`driver_id`),
47    CONSTRAINT line_route FOREIGN KEY (`line_no`, `route`) REFERENCES line_routes(`line_no`, `route`),
48    FOREIGN KEY (`bus_id`) REFERENCES buses(`bus_id`),
49    PRIMARY KEY(`course_no`)
50);
51
52CREATE TABLE routes(
53    `order` INT(3) NOT NULL,
54    `line_no` INT(11) NOT NULL,
55    `route` VARCHAR(40) NOT NULL,
56    `bus_stop_id` INT(11) NOT NULL,
57    `time` INT(3) NOT NULL,
58    CONSTRAINT FK_line_route FOREIGN KEY (`line_no`, `route`) REFERENCES line_routes(`line_no`, `route`),
59    FOREIGN KEY (bus_stop_id) REFERENCES bus_stops(bus_stop_id),
60    CONSTRAINT route_no PRIMARY KEY (`order`, `line_no`, `route`)
61);
62
63CREATE TABLE timetables(
64    `time` VARCHAR(5) NOT NULL,
65    `order` INT(3) NOT NULL,
66    `line_no` INT(11) NOT NULL,
67    `route` VARCHAR(40) NOT NULL,
68    CONSTRAINT route_no FOREIGN KEY(`order`, `line_no`, `route`) REFERENCES routes(`order`, `line_no`, `route`),
69    CONSTRAINT timetable_no PRIMARY KEY(`time`, `order`, `line_no`, `route`)
70);