· 6 years ago · Jun 10, 2019, 11:00 AM
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);
125/*PaymentInfo has no foreignkeys*/
126
127ALTER TABLE IncludedIn
128ADD CONSTRAINT fk_includedin_resid FOREIGN KEY (reservationid) REFERENCES Reservation(id),
129ADD CONSTRAINT fk_includedin_passid FOREIGN KEY (passid) REFERENCES Passenger(passnr);
130
131
132SELECT 'CREATING PROCEDURE/////////////////////////////////////////////////////////////////////';
133
134
135DELIMITER //
136CREATE PROCEDURE addYear
137(IN year INT, IN factor DOUBLE)
138BEGIN
139 INSERT INTO ProfitFactor (year,factor) VALUES(year,factor);
140END //
141DELIMITER ;
142
143DELIMITER //
144CREATE PROCEDURE addDay
145(IN year INT, IN day VARCHAR(10), IN factor DOUBLE)
146BEGIN
147 INSERT INTO Day(year,day,factor) VALUES(year, day, factor);
148END //
149DELIMITER ;
150
151
152DELIMITER //
153CREATE PROCEDURE addDestination
154(IN airport_code VARCHAR(3), IN name VARCHAR(30), IN country VARCHAR(30))
155BEGIN
156 INSERT INTO City(airportid,airportname,country) VALUES(airport_code, name, country);
157END //
158DELIMITER ;
159
160
161DELIMITER //
162CREATE PROCEDURE addRoute
163(IN departure_airport_code VARCHAR(3), IN arrival_airport_code VARCHAR(3), IN year INT, IN routeprice FLOAT)
164BEGIN
165 INSERT INTO Route(price,`to`,`from`,year) VALUES(routeprice,arrival_airport_code,departure_airport_code,year);
166END //
167DELIMITER ;
168
169DELIMITER //
170CREATE PROCEDURE addFlight
171(IN departure_airport_code VARCHAR(3), IN arrival_airport_code VARCHAR(3), IN year INT, IN day VARCHAR(10),IN departure_time TIME)
172BEGIN
173 DECLARE lastWeekid INT;
174 DECLARE i INT DEFAULT 0;
175 INSERT INTO WeeklySchedule(`to`,`from`,year,day,deptime) VALUES(arrival_airport_code, departure_airport_code, year, day, departure_time);
176
177
178 SET lastWeekid = LAST_INSERT_ID();
179
180 WHILE i < 52 DO
181 INSERT INTO Flight(scheduleid,week) VALUES(lastWeekid,i+1);
182 SET i = i + 1;
183 END WHILE;
184
185END //
186DELIMITER ;
187
188
189DELIMITER //
190CREATE FUNCTION calculateFreeSeats(flightnumber INT)
191RETURNS INT
192BEGIN
193 DECLARE takenSeats INT;
194
195 SELECT count(*) INTO takenSeats
196 FROM IncludedIn INNER JOIN Reservation ON IncludedIn.reservationid = Reservation.id AND Reservation.flightid = flightnumber
197 INNER JOIN Booking ON Reservation.id = Booking.id;
198
199 RETURN 40 - takenSeats;
200END //
201DELIMITER ;
202
203DELIMITER //
204CREATE FUNCTION calculatePrice(flightnumber INT)
205RETURNS DOUBLE
206BEGIN
207
208 DECLARE seatFactor DOUBLE;
209 DECLARE routePrice DOUBLE;
210 DECLARE weekDayFactor DOUBLE;
211 DECLARE profitFactor DOUBLE;
212 DECLARE scheduleid INT;
213
214
215
216 SELECT Flight.scheduleid INTO scheduleid
217 FROM Flight
218 WHERE Flight.id = flightnumber;
219
220
221 SELECT Route.price INTO routeprice
222 FROM Route INNER JOIN WeeklySchedule
223 ON Route.`from` = WeeklySchedule.`from` AND Route.`to` = WeeklySchedule.`to` AND WeeklySchedule.id = scheduleid AND WeeklySchedule.year = Route.year;
224
225 SELECT Day.factor INTO weekDayFactor
226 FROM Day INNER JOIN WeeklySchedule
227 ON WeeklySchedule.id = scheduleid AND WeeklySchedule.day = Day.day AND WeeklySchedule.year = Day.year;
228
229 SET seatFactor = (41 - calculateFreeSeats(flightnumber)) / 40;
230
231 SELECT ProfitFactor.factor INTO profitFactor
232 FROM ProfitFactor INNER JOIN WeeklySchedule
233 ON WeeklySchedule.year = ProfitFactor.year AND WeeklySchedule.id = scheduleid;
234
235 RETURN routeprice * weekDayFactor *profitFactor * seatFactor;
236
237END //
238DELIMITER ;
239
240
241
242DELIMITER //
243CREATE FUNCTION genUniqueTicketid()
244RETURNS INT
245BEGIN
246
247 DECLARE newTicketid Integer;
248 LOOP
249 SET newTicketid = RAND()*1000000000;
250 IF NOT EXISTS (SELECT ticketid FROM IncludedIn WHERE ticketid = newTicketid) THEN
251 RETURN newTicketid;
252 END IF;
253 END LOOP;
254END //
255DELIMITER ;
256
257DELIMITER //
258CREATE TRIGGER generateTicket AFTER INSERT ON Booking FOR EACH ROW
259BEGIN
260 UPDATE IncludedIn
261 SET ticketid = genUniqueTicketid()
262 WHERE reservationid = NEW.id;
263END //
264DELIMITER ;
265
266DELIMITER //
267CREATE 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)
268BEGIN
269DECLARE scheduleid INT;
270DECLARE flightid INT;
271
272SELECT id INTO scheduleid
273FROM WeeklySchedule
274WHERE WeeklySchedule.`to` = arrival_airport_code AND WeeklySchedule.`from` = departure_airport_code AND WeeklySchedule.year = year AND WeeklySchedule.day = day AND WeeklySchedule.deptime = time;
275
276SELECT Flight.id INTO flightid
277FROM Flight
278WHERE Flight.scheduleid = scheduleid AND Flight.week = week;
279IF flightid IS NULL THEN
280 SELECT' There exist no flight for the given route, date and time' AS 'Message';
281ELSEIF number_of_passengers > calculateFreeSeats(flightid) THEN
282 SELECT 'There are not enough seats available on the chosen flight' AS 'Message';
283ELSEIF (scheduleid IS NOT NULL) THEN
284 INSERT INTO Reservation(flightid) VALUES(flightid);
285 SET output_reservation_nr = LAST_INSERT_ID();
286
287END IF;
288
289END //
290DELIMITER ;
291
292DELIMITER //
293CREATE PROCEDURE addPassenger
294(IN reservation_nr INT, IN passport_number INT,IN name VARCHAR(30))
295BEGIN
296IF NOT EXISTS (SELECT passnr FROM Passenger WHERE passnr = passport_number) THEN
297INSERT INTO Passenger(passnr,name) VALUES(passport_number, name);
298END IF;
299
300IF EXISTS (SELECT * FROM Booking WHERE id = reservation_nr) THEN
301 SELECT 'The booking has already been payed and no futher passengers can be added' AS 'Message';
302ELSEIF EXISTS (SELECT id FROM Reservation WHERE id = reservation_nr) THEN
303 INSERT INTO IncludedIn(reservationid,passid) VALUES(reservation_nr, passport_number);
304ELSE
305 SELECT 'The given reservation number does not exist' AS 'Message';
306END IF;
307
308END //
309DELIMITER ;
310
311DELIMITER //
312CREATE PROCEDURE addContact(IN reservation_nr INT, IN passport_number INT,IN email VARCHAR(30), IN phone BIGINT)
313BEGIN
314 IF NOT EXISTS (SELECT * FROM IncludedIn WHERE IncludedIn.reservationid = reservation_nr) THEN
315 SELECT 'The given reservation number does not exist' AS 'Message';
316 ELSEIF NOT EXISTS (SELECT * FROM IncludedIn WHERE IncludedIn.passid = passport_number AND IncludedIn.reservationid = reservation_nr) THEN
317 SELECT 'The person is not a passenger of the reservation' AS 'Message';
318 ELSE
319 INSERT IGNORE INTO ContactPerson(email,phone) VALUES (email, phone);
320 UPDATE Reservation SET contact = email WHERE Reservation.id = reservation_nr;
321 END IF;
322END //
323DELIMITER ;
324
325DELIMITER //
326CREATE PROCEDURE addPayment (IN reservation_nr INT,
327IN cardholder_name VARCHAR(30), IN credit_card_number BIGINT)
328BEGIN
329 DECLARE flightid INT;
330 DECLARE numPassengers INT;
331
332
333 SELECT flightid INTO flightid FROM Reservation WHERE id = reservation_nr;
334 SELECT count(*) INTO numPassengers FROM IncludedIn WHERE reservationid = reservation_nr;
335 IF (numPassengers > calculateFreeSeats(flightid)) THEN
336 SELECT 'There are not enough seats available on the chosen flight, deleting reservation' AS 'Message';
337 DELETE FROM IncludedIn WHERE reservationid = reservation_nr;
338 DELETE FROM Reservation WHERE id = reservation_nr;
339
340 ELSEIF NOT EXISTS (SELECT * FROM Reservation WHERE Reservation.id = reservation_nr) THEN
341 SELECT 'The given reservation number does not exist' AS 'Message';
342 ELSEIF NOT EXISTS (SELECT contact FROM Reservation WHERE Reservation.id = reservation_nr) THEN
343 SELECT 'The reservation has no contact yet' AS 'Message';
344
345
346 ELSE
347 INSERT IGNORE INTO PaymentInfo(cardnum, cardholder) VALUES(credit_card_number, cardholder_name);
348 INSERT IGNORE INTO Booking(id, totprice, cardnum) VALUES(reservation_nr, calculatePrice(flightid), credit_card_number);
349
350 END IF;
351
352END //
353DELIMITER ;
354
355CREATE VIEW allFlights AS
356SELECT 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'
357FROM Flight INNER JOIN WeeklySchedule
358ON Flight.scheduleid = WeeklySchedule.id
359INNER JOIN Route
360ON WeeklySchedule.`to` = Route.`to` AND WeeklySchedule.`from` = Route.`from` AND WeeklySchedule.year = Route.year
361INNER JOIN City AS arr
362ON Route.`to` = arr.airportid
363INNER JOIN City AS dep
364ON Route.`from` = dep.airportid;
365
366SELECT * from allFlights;
367
368
369/*
370
3718a
372We can protect credit card information by encrypting the information. This could potentially be done using a trigger whenever a payment is added.
3738b
3741. It's more secure in the sense that the front-end-user cannot modify procedure.
3752. Less data has to be transferred. The front-end-user simply calls for a procedure instead of transfers all the code for the procedure.
3763. We can be sure that the front-end-user only accesses/update data that we want it to be able to access.
377
378
379
380
381/////////////////////////////////////////////////////////
382
3839b
384The reservation isnt visible in B since the transaction from A has not been comitted yet and B is in its own isolated transaction.
3859c It would not be able to be modified since the transaction has not been commited yet and is not available to session b. Both transactions are isolated, so the reservation made in A is locked to A until the transaction is done.
386
387
38810a)
389*/