W6LjheC9

1DROP DATABASE IF EXISTS busbays;
2CREATE DATABASE busbays;
3
4CREATE TABLE driver (
5    driver_id INT NOT NULL AUTO_INCREMENT,
6    name VARCHAR(255) NOT NULL,
7    contact_number VARCHAR(14) NOT NULL,
8    PRIMARY KEY (driver_id)
9) ENGINE=InnoDB;
10
11CREATE TABLE supervisor (
12    supervisor_id INT NOT NULL AUTO_INCREMENT,
13    name VARCHAR(255) NOT NULL,
14    contact_number VARCHAR(14) NOT NULL,
15    PRIMARY KEY (supervisor_id)
16) ENGINE=InnoDB;
17
18CREATE TABLE district (
19    district_id INT NOT NULL AUTO_INCREMENT,
20    name VARCHAR(255) NOT NULL,
21    division VARCHAR(255) NOT NULL,
22    PRIMARY KEY (district_id)
23) ENGINE=InnoDB;
24
25CREATE TABLE bus_stop (
26    bus_stop_id INT NOT NULL AUTO_INCREMENT,
27    name VARCHAR(255) NOT NULL,
28    address VARCHAR(255) NOT NULL,
29	contact_number VARCHAR(255) NOT NULL,
30	district_id INT NOT NULL,
31    PRIMARY KEY (bus_stop_id),
32	FOREIGN KEY (district_id) REFERENCES district(district_id)
33) ENGINE=InnoDB;
34
35CREATE TABLE restaurant (
36    restaurant_id INT NOT NULL AUTO_INCREMENT,
37    name VARCHAR(255) NOT NULL,
38    address VARCHAR(255) NOT NULL,
39	contact_number VARCHAR(255) NOT NULL,
40	district_id INT NOT NULL,
41    PRIMARY KEY (restaurant_id),
42	FOREIGN KEY (district_id) REFERENCES district(district_id)
43) ENGINE=InnoDB;
44
45CREATE TABLE bus (
46    bus_id INT NOT NULL AUTO_INCREMENT,
47    model VARCHAR(255) NOT NULL,
48    brand VARCHAR(255) NOT NULL,
49	bus_type VARCHAR(255) NOT NULL,
50	total_seat INT NOT NULL,
51    PRIMARY KEY (bus_id)
52) ENGINE=InnoDB;
53
54CREATE TABLE route (
55    route_id INT NOT NULL AUTO_INCREMENT,
56    source_id INT NOT NULL,
57    destination_id INT NOT NULL,
58	bus_id INT NOT NULL, 
59	restaurant_id INT NOT NULL,
60	driver_id INT NOT NULL,
61	supervisor_id INT NOT NULL,
62	price INT NOT NULL,
63    PRIMARY KEY (route_id),
64	FOREIGN KEY (source_id) REFERENCES district(district_id),
65	FOREIGN KEY (destination_id) REFERENCES district(district_id),
66	FOREIGN KEY (bus_id) REFERENCES bus(bus_id),
67	FOREIGN KEY (restaurant_id) REFERENCES restaurant(restaurant_id),
68	FOREIGN KEY (driver_id) REFERENCES driver(driver_id),
69	FOREIGN KEY (supervisor_id) REFERENCES supervisor(supervisor_id)
70) ENGINE=InnoDB;
71
72CREATE TABLE route_bus_stop (
73    route_bus_stop_id INT NOT NULL AUTO_INCREMENT,
74    route_id INT NOT NULL,
75	bus_stop_id INT NOT NULL,
76    arrives_at TIME,
77	position INT NOT NULL,
78	distance INT NOT NULL,
79    PRIMARY KEY (route_bus_stop_id),
80	FOREIGN KEY (route_id) REFERENCES route(route_id),
81	FOREIGN KEY (bus_stop_id) REFERENCES bus_stop(bus_stop_id)
82) ENGINE=InnoDB;
83
84CREATE TABLE passenger (
85    passenger_id INT NOT NULL AUTO_INCREMENT,
86    name VARCHAR(255) NOT NULL,
87    contact_number VARCHAR(14) NOT NULL,
88	luggage_weight INT NOT NULL,
89    PRIMARY KEY (passenger_id)
90) ENGINE=InnoDB;
91
92CREATE TABLE booking_config (
93    config_id INT NOT NULL AUTO_INCREMENT,
94    duration TIME NOT NULL,
95	activation_date DATE NOT NULL,
96    PRIMARY KEY (config_id)
97) ENGINE=InnoDB;
98
99CREATE TABLE staff (
100    staff_id INT NOT NULL AUTO_INCREMENT,
101    name VARCHAR(255) NOT NULL,
102    contact_number VARCHAR(14) NOT NULL,
103	role VARCHAR(255) NOT NULL,
104	username VARCHAR(255) NOT NULL UNIQUE,
105	password VARCHAR(255) NOT NULL,
106    PRIMARY KEY (staff_id)
107) ENGINE=InnoDB;
108
109
110CREATE TABLE ticket (
111    ticket_id INT NOT NULL AUTO_INCREMENT,
112    config_id INT NOT NULL,
113    route_id INT NOT NULL,
114	passenger_id INT NOT NULL,
115	seat_no VARCHAR(255) NOT NULL,
116	purchase_date DATE NOT NULL,
117	journey_date DATE NOT NULL,
118	staff_id INT NOT NULL,
119    PRIMARY KEY (ticket_id),
120	FOREIGN KEY (config_id) REFERENCES booking_config(config_id),
121	FOREIGN KEY (route_id) REFERENCES route(route_id),
122	FOREIGN KEY (passenger_id) REFERENCES passenger(passenger_id),
123	FOREIGN KEY (staff_id) REFERENCES staff(staff_id)
124) ENGINE=InnoDB;