· 6 years ago · Jun 09, 2019, 06:36 PM
1SET FOREIGN_KEY_CHECKS = 0;
2DROP TABLE IF EXISTS ContactPerson CASCADE;
3DROP TABLE IF EXISTS Passenger CASCADE;
4DROP TABLE IF EXISTS IncludedIn CASCADE;
5DROP TABLE IF EXISTS PaymentInfo CASCADE;
6DROP TABLE IF EXISTS Booking CASCADE;
7DROP TABLE IF EXISTS Reservation CASCADE;
8DROP TABLE IF EXISTS Flight CASCADE;
9DROP TABLE IF EXISTS City CASCADE;
10DROP TABLE IF EXISTS Route CASCADE;
11DROP TABLE IF EXISTS Day CASCADE;
12DROP TABLE IF EXISTS ProfitFactor CASCADE;
13DROP TABLE IF EXISTS WeeklySchedule CASCADE;
14DROP VIEW IF EXISTS allFlights;
15SET FOREIGN_KEY_CHECKS = 1;
16
17
18DROP PROCEDURE IF EXISTS addYear;
19DROP PROCEDURE IF EXISTS addDay;
20DROP PROCEDURE IF EXISTS addDestination;
21DROP PROCEDURE IF EXISTS addRoute;
22DROP PROCEDURE IF EXISTS addFlight;
23DROP FUNCTION IF EXISTS calculateFreeSeats;
24DROP FUNCTION IF EXISTS calculatePrice;
25DROP FUNCTION IF EXISTS genUniqueTicketid;
26DROP TRIGGER IF EXISTS generateTicket;
27
28DROP PROCEDURE IF EXISTS addReservation;
29DROP PROCEDURE IF EXISTS addPassenger;
30DROP PROCEDURE IF EXISTS addContact;
31DROP PROCEDURE IF EXISTS addPayment;
32
33SELECT 'Creating tables' AS 'Message';
34
35
36CREATE TABLE WeeklySchedule(
37 id INT PRIMARY KEY AUTO_INCREMENT,
38 deptime TIME,
39 `to` VARCHAR(3),
40 `from` VARCHAR(3),
41 year INT NOT NULL,
42 day VARCHAR(10) NOT NULL
43);
44CREATE TABLE ProfitFactor(
45 year INT PRIMARY KEY,
46 factor DOUBLE
47);
48
49CREATE TABLE Day(
50 year INT NOT NULL,
51 day VARCHAR(10) NOT NULL,
52 factor DOUBLE,
53
54 CONSTRAINT pk_day PRIMARY KEY (year,day)
55);
56CREATE TABLE Route(
57 price FLOAT,
58 `to` VARCHAR(3),
59 `from` VARCHAR(3),
60 `year` INT,
61
62 CONSTRAINT pk_route PRIMARY KEY (`to`,`from`,`year`)
63);
64CREATE TABLE City(
65 airportid VARCHAR(3) PRIMARY KEY,
66 airportname VARCHAR(30),
67 country VARCHAR(30)
68
69);
70CREATE TABLE Flight(
71 id INT PRIMARY KEY AUTO_INCREMENT,
72 week INT,
73 scheduleid INT
74);
75CREATE TABLE Reservation(
76 id INT PRIMARY KEY AUTO_INCREMENT,
77 seat INT,
78 flightid INT,
79 contact VARCHAR(30)
80);
81
82CREATE TABLE Booking(
83 id INT PRIMARY KEY,
84 totprice INT,
85 cardnum BIGINT
86);
87CREATE TABLE PaymentInfo(
88 cardnum BIGINT PRIMARY KEY,
89 cardholder VARCHAR(30) /*fk to */
90);
91CREATE TABLE IncludedIn(
92 reservationid INT,
93 passid INT,
94 ticketid INT UNIQUE,
95
96 CONSTRAINT pk_IncludedIn PRIMARY KEY (reservationid, passid)
97);
98CREATE TABLE Passenger(
99 passnr INT PRIMARY KEY,
100 name VARCHAR(30)
101 );
102CREATE TABLE ContactPerson(
103 phone BIGINT,
104 email VARCHAR(30) PRIMARY KEY
105);
106/*CREATE FOREIGN KEYS FROM RIGHT TO LEFT IN EER*/
107SELECT 'CREATING FOREIGN KEYS' AS 'Message ';
108ALTER TABLE WeeklySchedule ADD CONSTRAINT fk_weeklyschedule_day FOREIGN KEY (year,day) REFERENCES Day(year,day);
109ALTER TABLE WeeklySchedule ADD CONSTRAINT fk_weeklyschedule_year FOREIGN KEY (`year`) REFERENCES ProfitFactor(`year`);
110ALTER TABLE WeeklySchedule ADD CONSTRAINT fk_weeklyschedule_route FOREIGN KEY (`to`,`from`,year) REFERENCES Route(`to`,`from`,year);
111
112/*ProfitFactor has no FK*/
113
114ALTER TABLE Route ADD CONSTRAINT fk_route_to FOREIGN KEY (`to`) REFERENCES City(airportid);
115ALTER TABLE Route ADD CONSTRAINT fk_route_from FOREIGN KEY (`from`) REFERENCES City(airportid);
116
117ALTER TABLE Flight ADD CONSTRAINT fk_flight_scheduleid FOREIGN KEY(scheduleid) REFERENCES WeeklySchedule(id);
118
119ALTER TABLE Reservation ADD CONSTRAINT fk_reservation_flight FOREIGN KEY (flightid) REFERENCES Flight(id);
120ALTER TABLE Reservation ADD CONSTRAINT fk_reservation_contact FOREIGN KEY (contact) REFERENCES ContactPerson(email);
121
122
123ALTER TABLE Booking ADD CONSTRAINT fk_booking_cardnumber FOREIGN KEY (cardnum) REFERENCES PaymentInfo(cardnum);
124ALTER TABLE Booking ADD CONSTRAINT fk_bookingid_resid FOREIGN KEY (id) REFERENCES Reservation(id);
125select'test';
126/*PaymentInfo has no foreignkeys*/
127
128ALTER TABLE IncludedIn
129ADD CONSTRAINT fk_includedin_resid FOREIGN KEY (reservationid) REFERENCES Reservation(id),
130ADD CONSTRAINT fk_includedin_passid FOREIGN KEY (passid) REFERENCES Passenger(passnr);
131
132
133SELECT 'CREATING PROCEDURE/////////////////////////////////////////////////////////////////////';
134
135
136DELIMITER //
137CREATE PROCEDURE addYear
138(IN year INT, IN factor DOUBLE)
139BEGIN
140 INSERT INTO ProfitFactor (year,factor) VALUES(year,factor);
141END //
142DELIMITER ;
143
144DELIMITER //
145CREATE PROCEDURE addDay
146(IN year INT, IN day VARCHAR(10), IN factor DOUBLE)
147BEGIN
148 INSERT INTO Day(year,day,factor) VALUES(year, day, factor);
149END //
150DELIMITER ;
151
152
153DELIMITER //
154CREATE PROCEDURE addDestination
155(IN airport_code VARCHAR(3), IN name VARCHAR(30), IN country VARCHAR(30))
156BEGIN
157 INSERT INTO City(airportid,airportname,country) VALUES(airport_code, name, country);
158END //
159DELIMITER ;
160
161
162DELIMITER //
163CREATE PROCEDURE addRoute
164(IN departure_airport_code VARCHAR(3), IN arrival_airport_code VARCHAR(3), IN year INT, IN routeprice FLOAT)
165BEGIN
166 INSERT INTO Route(price,`to`,`from`,year) VALUES(routeprice,arrival_airport_code,departure_airport_code,year);
167END //
168DELIMITER ;
169
170DELIMITER //
171CREATE PROCEDURE addFlight
172(IN departure_airport_code VARCHAR(3), IN arrival_airport_code VARCHAR(3), IN year INT, IN day VARCHAR(10),IN departure_time TIME)
173BEGIN
174 DECLARE lastWeekid INT;
175 DECLARE i INT DEFAULT 0;
176 INSERT INTO WeeklySchedule(`to`,`from`,year,day,deptime) VALUES(arrival_airport_code, departure_airport_code, year, day, departure_time);
177
178
179 SET lastWeekid = LAST_INSERT_ID();
180
181 WHILE i < 52 DO
182 INSERT INTO Flight(scheduleid,week) VALUES(lastWeekid,i+1);
183 SET i = i + 1;
184 END WHILE;
185
186END //
187DELIMITER ;
188
189
190DELIMITER //
191CREATE FUNCTION calculateFreeSeats(flightnumber INT)
192RETURNS INT
193BEGIN
194 DECLARE takenSeats INT;
195
196 SELECT count(*) INTO takenSeats
197 FROM IncludedIn
198 WHERE reservationid in (SELECT Reservation.id FROM Reservation WHERE Reservation.flightid = flightnumber)
199 AND reservationid in (SELECT Booking.id FROM Booking);
200
201 RETURN 40 - takenSeats;
202END //
203DELIMITER ;
204
205DELIMITER //
206CREATE FUNCTION calculatePrice(flightnumber INT)
207RETURNS DOUBLE
208BEGIN
209 DECLARE seatFactor INT;
210 DECLARE routePrice INT;
211 DECLARE weekDayFactor INT;
212 DECLARE profitFactor INT;
213 DECLARE scheduleid INT;
214
215 SELECT Flight.scheduleid INTO scheduleid
216 FROM Flight
217 WHERE Flight.id = flightnumber;
218
219 SELECT Route.price INTO routePrice
220 FROM Route, WeeklySchedule, Flight
221 WHERE Route.`to` = WeeklySchedule.`to` AND Route.`from` = WeeklySchedule.`from` AND WeeklySchedule.id = scheduleid;
222
223 SELECT Day.factor INTO weekDayFactor
224 FROM Day INNER JOIN WeeklySchedule
225 WHERE WeeklySchedule.id = scheduleid AND WeeklySchedule.year = Day.year AND WeeklySchedule.day = Day.day;
226
227 SET seatFactor = (41 - calculateFreeSeats(flightnumber) / 40) ;
228
229 SELECT ProfitFactor.factor INTO profitFactor
230 FROM ProfitFactor INNER JOIN WeeklySchedule
231 WHERE WeeklySchedule.id = scheduleid AND WeeklySchedule.year = ProfitFactor.year;
232
233 RETURN routePrice * weekDayFactor * seatFactor * profitFactor;
234
235END //
236DELIMITER ;
237
238
239
240DELIMITER //
241CREATE FUNCTION genUniqueTicketid()
242RETURNS INT
243BEGIN
244
245 DECLARE newTicketid Integer;
246 LOOP
247 SET newTicketid = RAND()*1000000000;
248 IF NOT EXISTS (SELECT ticketid FROM IncludedIn WHERE ticketid = newTicketid) THEN
249 RETURN newTicketid;
250 END IF;
251 END LOOP;
252END //
253DELIMITER ;
254
255DELIMITER //
256CREATE TRIGGER generateTicket AFTER INSERT ON Booking FOR EACH ROW
257BEGIN
258 UPDATE IncludedIn
259 SET ticketid = genUniqueTicketid()
260 WHERE reservationid = NEW.id;
261END //
262DELIMITER ;
263
264DELIMITER //
265CREATE PROCEDURE addReservation(IN departure_airport_code VARCHAR(3), IN arrival_airport_code VARCHAR(3),IN year INT,IN week INT,IN day VARCHAR(10),IN time TIME,IN number_of_passengers INT, OUT output_reservation_nr INT)
266BEGIN
267DECLARE scheduleid INT;
268DECLARE flightid INT;
269
270SELECT id INTO scheduleid
271FROM WeeklySchedule
272WHERE WeeklySchedule.`to` = arrival_airport_code AND WeeklySchedule.`from` = departure_airport_code AND WeeklySchedule.year = year AND WeeklySchedule.day = day AND WeeklySchedule.deptime = time;
273
274SELECT Flight.id INTO flightid
275FROM Flight
276WHERE Flight.scheduleid = scheduleid AND Flight.week = week;
277IF flightid IS NULL THEN
278 SELECT' There exist no flight for the given route, date and time' AS 'Message';
279ELSEIF number_of_passengers > calculateFreeSeats(flightid) THEN
280 SELECT 'There are not enough seats available on the chosen flight' AS 'Message';
281ELSEIF (scheduleid IS NOT NULL) THEN
282 INSERT INTO Reservation(flightid) VALUES(flightid);
283 SET output_reservation_nr = LAST_INSERT_ID();
284
285END IF;
286
287END //
288DELIMITER ;
289
290DELIMITER //
291CREATE PROCEDURE addPassenger
292(IN reservation_nr INT, IN passport_number INT,IN name VARCHAR(30))
293BEGIN
294IF NOT EXISTS (SELECT passnr FROM Passenger WHERE passnr = passport_number) THEN
295INSERT INTO Passenger(passnr,name) VALUES(passport_number, name);
296END IF;
297
298IF EXISTS (SELECT * FROM Booking WHERE id = reservation_nr) THEN
299 SELECT 'The booking has already been payed and no futher passengers can be added' AS 'Message';
300ELSEIF EXISTS (SELECT id FROM Reservation WHERE id = reservation_nr) THEN
301 INSERT INTO IncludedIn(reservationid,passid) VALUES(reservation_nr, passport_number);
302ELSE
303 SELECT 'The given reservation number does not exist' AS 'Message';
304END IF;
305
306END //
307DELIMITER ;
308
309DELIMITER //
310CREATE PROCEDURE addContact(IN reservation_nr INT, IN passport_number INT,IN email VARCHAR(30), IN phone BIGINT)
311BEGIN
312 IF NOT EXISTS (SELECT * FROM IncludedIn WHERE IncludedIn.reservationid = reservation_nr) THEN
313 SELECT 'The given reservation number does not exist' AS 'Message';
314 ELSEIF NOT EXISTS (SELECT * FROM IncludedIn WHERE IncludedIn.passid = passport_number AND IncludedIn.reservationid = reservation_nr) THEN
315 SELECT 'The person is not a passenger of the reservation' AS 'Message';
316 ELSE
317 INSERT INTO ContactPerson(email,phone) VALUES (email, phone);
318 UPDATE Reservation SET contact = email WHERE Reservation.id = reservation_nr;
319 END IF;
320END //
321DELIMITER ;
322
323DELIMITER //
324CREATE PROCEDURE addPayment (IN reservation_nr INT,
325IN cardholder_name VARCHAR(30), IN credit_card_number BIGINT)
326BEGIN
327 DECLARE flightid INT;
328 DECLARE numPassengers INT;
329
330
331 SELECT flightid INTO flightid FROM Reservation WHERE id = reservation_nr;
332 SELECT count(*) INTO numPassengers FROM IncludedIn WHERE reservationid = reservation_nr;
333
334 IF NOT EXISTS (SELECT * FROM Reservation WHERE Reservation.id = reservation_nr) THEN
335 SELECT 'The given reservation number does not exist' AS 'Message';
336 ELSEIF NOT EXISTS (SELECT contact FROM Reservation WHERE Reservation.id = reservation_nr) THEN
337 SELECT 'The reservation has no contact yet' AS 'Message';
338 ELSEIF numPassengers > calculateFreeSeats(flightid) THEN
339 SELECT 'There are not enough seats available on the chosen flight, deleting reservation' AS 'Message';
340 DELETE FROM IncludedIn WHERE reservationid = reservation_nr;
341 DELETE FROM Reservation WHERE id = reservation_nr;
342
343
344 ELSE
345 INSERT IGNORE INTO PaymentInfo(cardnum, cardholder) VALUES(credit_card_number, cardholder_name);
346 INSERT IGNORE INTO Booking(id, totprice, cardnum) VALUES(reservation_nr, calculatePrice(flightid), credit_card_number);
347
348 END IF;
349
350END //
351DELIMITER ;
352/*
353 departure_city_name, destination_city_name, departure_time,
354departure_day, departure_week, departure_year, nr_of_free_seats,
355current_price_per_seat.
356*/
357/*
358CREATE VIEW allFlights AS
359SELECT dep.airportname AS departure_city_name, arr.airportname AS destination_city_name, WeeklySchedule.deptime AS departure_time, WeeklySchedule.day AS departure_day, Flight.week AS departure_week, WeeklySchedule.year AS departure_year, calculateFreeSeats(Flight.id) AS nr_of_free_seats, calculatePrice(Flight.id) AS current_price_per_seat
360FROM WeeklySchedule INNER JOIN
361Flight ON WeeklySchedule.id = Flight.scheduleid
362INNER JOIN Route ON WeeklySchedule.`to` = Route.`to` AND WeeklySchedule.`from` = Route.`from` AND WeeklySchedule.year = Route.year
363INNER JOIN City as dep ON Route.`from` = dep.airportid
364INNER JOIN City as arr ON Route.`to` = arr.airportid;
365*/
366
367CREATE VIEW allFlights AS
368SELECT f.airportname AS departure_city_name, t.airportname AS destination_city_name, WeeklySchedule.deptime AS departure_time, WeeklySchedule.day AS departure_day, Flight.week AS departure_week, WeeklySchedule.year AS departure_year, calculateFreeSeats(Flight.id) AS nr_of_free_seats, calculatePrice(Flight.id) AS current_price_per_seat
369FROM Flight
370INNER JOIN WeeklySchedule ON Flight.scheduleid = WeeklySchedule.id
371INNER JOIN Route ON WeeklySchedule.`to` = Route.`to` AND WeeklySchedule.`from` = Route.`from` AND WeeklySchedule.year = Route.year
372INNER JOIN City AS t ON Route.`to` = t.airportid
373INNER JOIN City AS f ON Route.`from` = f.airportid;
374
375SELECT * from allFlights;