f7L8edeZ

1/*
2- make ID's unique
3- cross check types with lab instructions
4- double check NOT NULL 
5- added year to day
6*/
7
8# TO RUN
9# source /home/krila597/TDDD12/script.sql;
10
11#drop all the tables
12
13SET FOREIGN_KEY_CHECKS=0;
14
15DROP TABLE IF EXISTS destination;
16DROP TABLE IF EXISTS year;
17DROP TABLE IF EXISTS route;
18DROP TABLE IF EXISTS day;
19DROP TABLE IF EXISTS weeklyschedule;
20DROP TABLE IF EXISTS flight;
21DROP TABLE IF EXISTS reservation;
22DROP TABLE IF EXISTS creditcard;
23DROP TABLE IF EXISTS payment;
24DROP TABLE IF EXISTS passenger;
25DROP TABLE IF EXISTS contact;
26DROP TABLE IF EXISTS ticket;
27
28SET FOREIGN_KEY_CHECKS=1;
29
30
31
32# create table 'destination'
33CREATE TABLE IF NOT EXISTS destination(
34	code VARCHAR(3) NOT NULL,
35	name VARCHAR(30) NOT NULL,
36	country VARCHAR(30) NOT NULL,
37	PRIMARY KEY(code)
38);
39
40# create table 'year' that holds profit factor
41CREATE TABLE IF NOT EXISTS year(
42	year INT NOT NULL,
43	profitfactor DOUBLE,
44	PRIMARY KEY(year)
45);
46
47# create table 'route', arrival and departure are foreign keys from Destination. year from Year entity
48CREATE TABLE IF NOT EXISTS route(
49	id INT AUTO_INCREMENT,
50	departure VARCHAR(3),
51	arrival VARCHAR(3),
52	route_year INT NOT NULL,
53	price DOUBLE,
54	PRIMARY KEY(id),
55	FOREIGN KEY (departure) REFERENCES destination(code),
56	FOREIGN KEY (arrival) REFERENCES destination(code)
57);
58
59# create table 'day'
60CREATE TABLE IF NOT EXISTS day(
61	dayname VARCHAR(10) NOT NULL,
62	weekdayfactor DOUBLE NOT NULL,
63	year INT,
64	PRIMARY KEY(dayname)
65);
66
67# create table 'weeklyschedule', with route and day has foreign keys
68CREATE TABLE IF NOT EXISTS weeklyschedule(
69	id INT auto_increment NOT NULL,
70	route INT,
71	departure_time TIME,
72	wday VARCHAR(10) NOT NULL,
73	PRIMARY KEY (id),
74	FOREIGN KEY (wday) REFERENCES day(dayname),
75	FOREIGN KEY (route) REFERENCES route(id)
76);
77
78
79# create table 'flight', with weekly schedule as foreign key 
80CREATE TABLE IF NOT EXISTS flight(
81	flightnumber INT auto_increment NOT NULL,
82	fweek INT,
83	weekly_schedule INT,
84	available_seats INT,
85	PRIMARY KEY (flightnumber),
86	FOREIGN KEY (weekly_schedule) REFERENCES weeklyschedule(id)
87);
88
89# create table 'reservation'
90CREATE TABLE IF NOT EXISTS reservation(
91	reservationnumber INT auto_increment NOT NULL,
92	flight INT,
93	passengers INT,
94	PRIMARY KEY (reservationnumber),
95	FOREIGN KEY (flight) REFERENCES flight(flightnumber)
96);
97
98# create table 'creditcard'
99CREATE TABLE IF NOT EXISTS creditcard(
100	card_number BIGINT NOT NULL,
101	name VARCHAR(30),
102	PRIMARY KEY(card_number)
103);
104
105
106# create table 'payment'
107CREATE TABLE IF NOT EXISTS payment(
108	id INT auto_increment,
109	card_number BIGINT,
110	reservation INT,
111	PRIMARY KEY(id),
112	FOREIGN KEY (card_number) REFERENCES creditcard(card_number),
113	FOREIGN KEY (reservation) REFERENCES reservation(reservationnumber)
114);
115
116#create table 'passenger'
117CREATE TABLE IF NOT EXISTS passenger(
118	id INT auto_increment,
119	passport_number INT,
120	name VARCHAR(30),
121	reservation INT,
122	PRIMARY KEY (id),
123	FOREIGN KEY (reservation) REFERENCES reservation(reservationnumber)
124);
125
126# create table 'contact', no foreign key right?
127CREATE TABLE IF NOT EXISTS contact(
128	passport_number INT NOT NULL,
129	email VARCHAR(30),
130	phone BIGINT,
131	PRIMARY KEY (passport_number)/*,
132	FOREIGN KEY (passenger_id) REFERENCES passenger(id)*/
133);
134
135
136
137
138# create table 'ticket'
139CREATE TABLE IF NOT EXISTS ticket(
140	id INT NOT NULL,
141	payment INT,
142	passenger INT,
143	flight INT,
144	PRIMARY KEY (id),
145	FOREIGN KEY (payment) REFERENCES payment(id),
146	FOREIGN KEY (passenger) REFERENCES passenger(id),
147	FOREIGN KEY (flight) REFERENCES flight(flightnumber)
148);
149
150
151
152
153# PROCEDURES
154/*call addYear(2019, 2.5);
155call addDay(2019, 'Monday', 10.0);
156call addDestination('SKA', 'Skavsta', 'Sweden');
157call addDestination('GBG', 'Göteborg', 'Sweden');
158call addRoute('SKA', 'GBG', 2019, 100.0);*/
159
160
161/*====================================================================================================================================
162
163=====================================================================================================================================*/
164
165# addYear
166DROP PROCEDURE IF EXISTS addYear;
167
168DELIMITER //
169CREATE PROCEDURE addYear(IN vyear INT, IN factor DOUBLE)
170BEGIN
171
172IF (SELECT year FROM year WHERE year = vyear) IS NULL THEN
173INSERT INTO `year`(`year`, `profitfactor`) VALUES (vyear, factor);
174END IF;
175END //
176DELIMITER ;
177
178
179/*====================================================================================================================================
180
181=====================================================================================================================================*/
182# addDay
183DROP PROCEDURE IF EXISTS addDay;
184
185DELIMITER //
186CREATE PROCEDURE addDay(IN year INT, IN day VARCHAR(10), IN factor DOUBLE)
187BEGIN
188INSERT INTO `day`(`year`, `dayname`, `weekdayfactor`) VALUES (year, day, factor);
189END //
190DELIMITER ;
191
192
193/*====================================================================================================================================
194
195=====================================================================================================================================*/
196# add destination
197DROP PROCEDURE IF EXISTS addDestination;
198
199DELIMITER //
200CREATE PROCEDURE addDestination(IN airport_code VARCHAR(3), IN name VARCHAR(30), IN country VARCHAR(30))
201BEGIN
202
203INSERT INTO `destination`(`code`, `name`, `country`) VALUES (airport_code, name, country);
204
205
206
207
208END //
209DELIMITER ;
210
211
212/*====================================================================================================================================
213
214=====================================================================================================================================*/
215
216# add route
217DROP PROCEDURE IF EXISTS addRoute;
218
219DELIMITER //
220CREATE PROCEDURE addRoute(IN departure_airport_code VARCHAR(3), IN arrival_airport_code VARCHAR(3), IN year INT, IN routeprice DOUBLE)
221BEGIN
222INSERT INTO `route`(`departure`, `arrival`, `route_year`, `price`) VALUES (departure_airport_code, arrival_airport_code, year, routeprice);
223END //
224DELIMITER ;
225
226
227/*====================================================================================================================================
228
229=====================================================================================================================================*/
230
231# add weeklyflight
232DROP PROCEDURE IF EXISTS addFlight;
233
234DELIMITER //
235CREATE PROCEDURE addFlight(IN departure_airport_code VARCHAR(3), IN arrival_airport_code VARCHAR(3), IN year INT, IN day VARCHAR(10), IN time TIME)
236BEGIN
237
238DECLARE	route_id INT;
239DECLARE route_price DOUBLE;
240DECLARE schedule_id INT;
241DECLARE i INT;
242
243# find the route id
244
245SELECT id INTO route_id FROM route WHERE departure = departure_airport_code AND arrival = arrival_airport_code AND route_year = year;
246
247
248SELECT price INTO route_price FROM route WHERE id = route_id;
249
250INSERT INTO `weeklyschedule`(`route`, `departure_time`, `wday`) VALUES (route_id, time, day);
251
252SELECT id INTO schedule_id FROM weeklyschedule WHERE route = route_id AND departure_time = time  and wday = day;
253
254# add 52 flights to Flights, one for each week
255
256IF schedule_id IS NOT NULL THEN
257SET i = 1;
258	WHILE i <= 52 DO
259	INSERT INTO `flight`(`fweek`, `weekly_schedule`, `available_seats`) VALUES (i, schedule_id, 40);
260	SET i = i + 1;
261	END WHILE;
262END IF;
263
264END //
265DELIMITER ;
266
267
268
269
270/*====================================================================================================================================
271
272=====================================================================================================================================*/
273
274DROP FUNCTION IF EXISTS calculateFreeSeats;
275DELIMITER //
276
277CREATE FUNCTION calculateFreeSeats(flight_number INT)
278RETURNS INT
279BEGIN
280
281DECLARE seats INT;
282
283
284SELECT COUNT(*) INTO seats FROM ticket WHERE flight_number = flight;
285
286
287SET seats = (SELECT available_seats FROM flight WHERE flight_number = flightnumber) - seats;
288
289
290UPDATE flight SET available_seats = seats WHERE flight_number = flightnumber;
291
292RETURN seats;
293
294END //
295DELIMITER ;
296/*====================================================================================================================================
297
298=====================================================================================================================================*/
299DROP FUNCTION IF EXISTS calculatePrice;
300DELIMITER //
301
302CREATE FUNCTION calculatePrice(flight_number INT)
303RETURNS DOUBLE
304BEGIN
305
306DECLARE weekday_factor DOUBLE;
307DECLARE booked_passengers INT;
308DECLARE profit_factor DOUBLE;
309
310# get weekly flight schedule to get route
311
312# route price
313DECLARE route_price DOUBLE;
314SELECT price INTO route_price FROM route WHERE id = (SELECT route FROM weeklyschedule WHERE id = (SELECT weekly_schedule FROM flight where flightnumber = flight_number)); 
315
316# weekday factor
317
318SELECT weekdayfactor INTO weekday_factor FROM day WHERE dayname = (SELECT wday FROM weeklyschedule WHERE id = (SELECT weekly_schedule FROM flight where flightnumber = flight_number));
319
320# number of booked passengers
321SELECT calculateFreeSeats(flight_number) INTO booked_passengers;
322
323# profitmargin
324SELECT profitfactor INTO profit_factor FROM year WHERE year = (SELECT route_year FROM route WHERE id = (SELECT route FROM weeklyschedule WHERE id = (SELECT weekly_schedule FROM flight where flightnumber = flight_number))); 
325
326
327
328RETURN route_price * weekday_factor * (seats + 1)/40 * profit_factor;
329END //
330DELIMITER ;
331
332/*====================================================================================================================================
333
334=====================================================================================================================================*/
335
336
337DELIMITER //
338# create a trigger for ticket
339CREATE TRIGGER ticketNumber AFTER INSERT ON payment
340FOR EACH ROW
341BEGIN
342
343DECLARE reservation_number INT;
344SELECT MAX(id) INTO reservation_number FROM payment;
345
346INSERT INTO `ticket`(`id`, `payment`, `passenger`/*, `flight`*/)
347SELECT
348rand()*1000,
349reservation,
350id FROM passenger WHERE reservation = reservation_number;/*
351flight FROM reservation WHERE reservationnumber = reservation_number;
352*/
353
354UPDATE ticket SET flight = (SELECT flight FROM reservation WHERE reservationnumber = reservation_number);
355
356END //
357DELIMITER ;
358
359
360/*====================================================================================================================================
361
362=====================================================================================================================================*/
363
364
365DROP PROCEDURE IF EXISTS addReservation;
366DELIMITER //
367
368CREATE PROCEDURE addReservation(IN departure_airport_code VARCHAR(3), IN arrival_airport_code VARCHAR(3), IN year INT, IN inweek INT, IN day VARCHAR(10), IN time TIME, IN number_of_passengers INT, OUT output_reservation_nr INT)
369BEGIN
370
371DECLARE flight_number INT;
372DECLARE route_id INT;
373DECLARE schedule_id INT;
374
375SELECT id INTO route_id FROM route WHERE departure = departure_airport_code AND arrival = arrival_airport_code AND route_year = year;
376
377IF route_id IS NULL THEN
378SELECT 'route_id is null' AS 'Message';
379END IF;
380
381
382SELECT id INTO schedule_id FROM weeklyschedule WHERE wday = day AND departure_time = time AND route = route_id;
383
384IF schedule_id IS NULL THEN
385SELECT 'schedule_id is null' AS 'Message';
386END IF;
387
388SELECT flightnumber INTO flight_number FROM flight WHERE weekly_schedule = schedule_id AND fweek = inweek;
389/*
390SELECT flightnumber INTO flight_number FROM flight WHERE fweek = inweek; AND weekly_schedule = 
391(SELECT id from weeklyschedule WHERE day = day AND departure_time = time  AND route = 
392(SELECT id FROM route WHERE departure = departure_airport_code AND arrival = arrival_airport_code AND route_year = year));
393*/
394/*
395SELECT @week AS 'Week';
396SELECT @flight_number AS 'Flightnumber';*/
397
398IF flight_number IS NULL THEN
399SELECT 'There exist no flight for the given route, date and time' AS 'Message';
400
401ELSE
402IF calculateFreeSeats(flight_number) < number_of_passengers THEN
403SELECT "There are not enough seats available on the chosen flight" AS 'Message';
404
405
406ELSE
407INSERT INTO `reservation`(`flight`, `passengers`) VALUES(flight_number, number_of_passengers);
408SELECT MAX(reservationnumber) INTO output_reservation_nr FROM reservation;
409END IF;
410END IF;
411
412
413
414END //
415DELIMITER ;
416
417
418/*====================================================================================================================================
419
420=====================================================================================================================================*/
421
422
423DROP PROCEDURE IF EXISTS addPassenger;
424DELIMITER //
425
426CREATE PROCEDURE addPassenger(IN reservation_nr INT, IN passport_number INT, IN name VARCHAR(30))
427BEGIN
428
429IF (SELECT reservationnumber FROM reservation WHERE reservationnumber = reservation_nr) IS NULL THEN
430SELECT 'The given reservation number does not exist' AS 'Message';
431
432ELSE
433
434
435IF (SELECT reservation FROM payment WHERE reservation = reservation_nr) IS NOT NULL THEN
436SELECT 'The booking has already been payed and no futher passengers can be added' AS 'Message';
437
438
439ELSE
440INSERT INTO `passenger`(`passport_number`, `name`, `reservation`) VALUES (passport_number, name, reservation_nr);
441END IF;
442END IF;
443END //
444DELIMITER ;
445
446
447/*====================================================================================================================================
448
449=====================================================================================================================================*/
450
451
452DROP PROCEDURE IF EXISTS addContact;
453DELIMITER //
454
455CREATE PROCEDURE addContact(IN reservation_nr INT, IN INpassport_number INT, IN email VARCHAR(45), IN phone INT)
456BEGIN
457
458
459IF (SELECT min(reservationnumber) FROM reservation WHERE reservationnumber = reservation_nr) IS NULL THEN
460SELECT 'The given reservation number does not exist' AS 'Message';
461
462ELSE
463IF (SELECT MIN(id) FROM passenger WHERE passport_number = INpassport_number AND reservation = reservation_nr) IS NULL THEN
464SELECT 'The person is not a passenger of the reservation' AS 'Message';
465ELSE
466INSERT INTO `contact`(`passport_number`, `email`, `phone`) VALUES (INpassport_number, email, phone);
467END IF;
468END IF;
469
470END //
471DELIMITER ;
472
473
474
475/*====================================================================================================================================
476
477=====================================================================================================================================*/
478
479DROP PROCEDURE IF EXISTS addPayment;
480DELIMITER //
481
482CREATE PROCEDURE addPayment (IN reservation_nr INT, IN cardholder_name VARCHAR(30), IN credit_card_number BIGINT)
483BEGIN
484
485
486DECLARE seats INT;
487DECLARE flight_number INT;
488DECLARE tmp INT;
489
490/* Get potential contact */
491SELECT passport_number INTO tmp FROM contact WHERE passport_number = (SELECT MIN(passport_number) FROM passenger WHERE reservation = reservation_nr); 
492
493/* Get flight number */
494SELECT flight INTO flight_number FROM reservation WHERE reservationnumber = reservation_nr;
495
496/* Get number of seats in reservation */
497SELECT COUNT(*) INTO seats FROM passenger WHERE reservation = reservation_nr;
498
499
500IF (SELECT `reservationnumber` FROM reservation WHERE `reservationnumber` = reservation_nr) IS NULL THEN
501SELECT 'The given reservation number does not exist' AS 'Message';
502
503ELSE
504
505IF tmp IS NULL THEN
506SELECT 'The reservation has no contact yet' as 'Message';
507
508ELSE
509
510
511IF (seats > calculateFreeSeats(flight_number)) THEN
512SELECT 'There are not enough seats available on the flight anymore, deleting reservation' AS 'Message';
513SET SQL_SAFE_UPDATES=0;
514DELETE FROM passenger WHERE reservation = reservation_nr;
515DELETE FROM reservation WHERE reservationnumber = reservation_nr;
516SET SQL_SAFE_UPDATES=1;
517
518ELSE
519
520
521IF (SELECT card_number FROM creditcard WHERE card_number = credit_card_number) IS NULL THEN
522INSERT INTO creditcard(card_number, name) VALUES (credit_card_number, cardholder_name);
523END IF;
524
525
526INSERT INTO payment(card_number, reservation) VALUES (credit_card_number, reservation_nr);
527
528END IF;
529END IF;
530END IF;
531
532END //
533DELIMITER ;
534
535
536/*====================================================================================================================================
537
538=====================================================================================================================================*/
539
540
541DROP VIEW IF EXISTS allFlights;
542CREATE VIEW IF NOT EXISTS allFlights AS
543
544SELECT 
545C.name AS 'departure_city_name', /* VARCHAR(30) */
546C.name AS 'destination_city_name', /* VARCHAR(30) */
547B.departure_time AS 'departure_time', /* TIME */
548B.wday AS 'departure_day', /* VARCHAR(10) */
549D.fweek AS 'departure_week', /* INT? */
550A.route_year AS 'departure_year', /* INT */
551calculateFreeSeats(D.flightnumber) AS 'nr_of_free_seats', /* INTEGER */
552calculatePrice(D.flightnumber) AS 'current_price_per_seat' /* DOUBLE */
553
554FROM route A, weeklyschedule B, destination C, flight D
555WHERE A.id = B.route AND D.weekly_schedule = B.id AND A.departure = C.code AND A.arrival = C.code;
556
557
558/*
559A route = departure, arrival, year
560B weeklyschedule = route, departure_time, day
561C destination = name
562D flight = flightnumber, fweek
563
564*/