· 4 years ago · Dec 23, 2020, 08:32 PM
1SET foreign_key_checks = 0;
2
3DROP TABLE IF EXISTS passenger CASCADE;
4DROP TABLE IF EXISTS years CASCADE;
5DROP TABLE IF EXISTS weekdays CASCADE;
6DROP TABLE IF EXISTS contact CASCADE;
7DROP TABLE IF EXISTS weekly_schedule CASCADE;
8DROP TABLE IF EXISTS flight CASCADE;
9DROP TABLE IF EXISTS airport CASCADE;
10DROP TABLE IF EXISTS routes CASCADE;
11DROP TABLE IF EXISTS credit_card CASCADE;
12DROP TABLE IF EXISTS booking CASCADE;
13DROP TABLE IF EXISTS reservation CASCADE;
14DROP TABLE IF EXISTS ticket CASCADE;
15
16SET foreign_key_checks = 1;
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 PROCEDURE IF EXISTS addReservation;
24DROP PROCEDURE IF EXISTS addPassenger;
25DROP PROCEDURE IF EXISTS addContact;
26DROP PROCEDURE IF EXISTS addPayment;
27
28
29DROP FUNCTION IF EXISTS calculateFreeSeats;
30DROP FUNCTION IF EXISTS calculatePrice;
31
32DROP TRIGGER IF EXISTS setTicketNum;
33
34/*
35
36 -----: TABLES :-----
37
38*/
39
40CREATE TABLE airport(
41code varchar(3),
42airport_name varchar(30),
43country varchar(30),
44CONSTRAINT pk_code PRIMARY KEY (code)
45);
46
47CREATE TABLE booking(
48seat_price double,
49total_price double,
50fk_reservation int,
51fk_credit_card BIGINT
52);
53
54CREATE TABLE contact(
55fk_passport int,
56email varchar(30),
57phone BIGINT
58);
59
60CREATE TABLE credit_card(
61card_number BIGINT,
62card_holder varchar(60),
63CONSTRAINT pk_card_number PRIMARY KEY (card_number)
64);
65
66CREATE TABLE flight(
67flight_number int auto_increment,
68booked_passengers int,
69fk_schedule int,
70weeknumber int,
71CONSTRAINT pk_flight_number PRIMARY KEY (flight_number)
72);
73
74CREATE TABLE passenger(
75passport_number int,
76first_name varchar(30),
77last_name varchar(30),
78CONSTRAINT pk_passport PRIMARY KEY (passport_number)
79);
80
81CREATE TABLE reservation(
82reservation_number int,
83fk_flight int,
84fk_contact int,
85CONSTRAINT pk_reservation_number PRIMARY KEY (reservation_number)
86);
87
88CREATE TABLE routes(
89route_id int auto_increment,
90thisyear int,
91route_price double,
92fk_departure varchar(3),
93fk_arrival varchar(3),
94CONSTRAINT pk_route_id PRIMARY KEY (route_id)
95);
96
97CREATE TABLE ticket(
98ticket_number int,
99fk_reservation int,
100fk_passport int
101);
102
103CREATE TABLE weekdays(
104fk_year int,
105weekdays varchar(10),
106weekday_profit_factor double,
107CONSTRAINT pk_weekday PRIMARY KEY (weekdays)
108);
109
110CREATE TABLE weekly_schedule(
111schedule_id int auto_increment,
112departure_time time,
113fk_route int,
114fk_weekday varchar(10),
115CONSTRAINT pk_schedule PRIMARY KEY (schedule_id)
116);
117
118CREATE TABLE years(
119thisyear int,
120year_profit_factor double,
121CONSTRAINT pk_thisyear PRIMARY KEY (thisyear)
122);
123
124/*
125
126 -----: FOREIGN KEYS :-----
127
128*/
129
130ALTER TABLE weekdays ADD CONSTRAINT fk_yearid FOREIGN KEY (fk_year) REFERENCES years(thisyear);
131
132ALTER TABLE contact ADD CONSTRAINT fk_pass FOREIGN KEY (fk_passport) REFERENCES passenger(passport_number);
133
134ALTER TABLE flight ADD CONSTRAINT fk_week_id FOREIGN KEY (fk_schedule) REFERENCES weekly_schedule(schedule_id);
135
136ALTER TABLE routes ADD CONSTRAINT fk_departure_code FOREIGN KEY (fk_departure) REFERENCES airport(code);
137ALTER TABLE routes ADD CONSTRAINT fk_arrival_code FOREIGN KEY (fk_arrival) REFERENCES airport(code);
138
139ALTER TABLE booking ADD CONSTRAINT fk_credit_card_id FOREIGN KEY (fk_credit_card) REFERENCES credit_card(card_number);
140ALTER TABLE booking ADD CONSTRAINT fk_reservation_id FOREIGN KEY (fk_reservation) REFERENCES reservation(reservation_number);
141
142ALTER TABLE reservation ADD CONSTRAINT fk_flight FOREIGN KEY (fk_flight) REFERENCES flight(flight_number);
143ALTER TABLE reservation ADD CONSTRAINT fk_contact FOREIGN KEY (fk_contact) REFERENCES contact(fk_passport);
144
145ALTER TABLE ticket ADD CONSTRAINT fk_reservations_id FOREIGN KEY (fk_reservation) REFERENCES reservation(reservation_number);
146ALTER TABLE ticket ADD CONSTRAINT fk_passport_id FOREIGN KEY (fk_passport) REFERENCES passenger(passport_number);
147
148ALTER TABLE weekly_schedule ADD CONSTRAINT fk_routeid FOREIGN KEY (fk_route) REFERENCES routes(route_id);
149ALTER TABLE weekly_schedule ADD CONSTRAINT fk_weekdayid FOREIGN KEY (fk_weekday) REFERENCES weekdays(weekdays);
150
151/*
152
153 -----: PROCEDURES :-----
154
155*/
156
157DELIMITER //
158
159CREATE PROCEDURE addYear(IN _year int, IN _year_profit_factor double)
160BEGIN
161INSERT INTO years
162VALUES (_year, _year_profit_factor);
163END;
164//
165
166CREATE PROCEDURE addDay(IN _year int, IN _day varchar(10), IN _factor double)
167BEGIN
168INSERT INTO weekdays
169VALUES (_year, _day, _factor);
170END;
171//
172
173CREATE PROCEDURE addDestination(IN _code varchar(3), IN _name varchar(30), IN _country varchar(30))
174BEGIN
175INSERT INTO airport
176VALUES (_code, _name, _country);
177END;
178//
179
180CREATE PROCEDURE addRoute(IN _dep varchar(3), IN _arr varchar(3), IN _year int, IN _routeprice double)
181BEGIN
182INSERT INTO routes (fk_departure, fk_arrival, thisyear, route_price)
183VALUES (_dep, _arr, _year, _routeprice);
184END;
185//
186
187CREATE PROCEDURE addFlight(IN _dep varchar(3), IN _arr varchar(3), IN _year int, IN _day varchar(9), IN _dep_time time)
188BEGIN
189DECLARE _route_id, _schedulenum, i int;
190SELECT route_id INTO _route_id FROM routes WHERE fk_departure = _dep AND fk_arrival = _arr AND thisyear = _year;
191
192INSERT INTO weekly_schedule (fk_route, fk_weekday, departure_time)
193VALUES (_route_id, _day, _dep_time);
194
195SET _schedulenum = LAST_INSERT_ID();
196
197WHILE i < 53
198 DO
199 INSERT INTO flight(fk_schedule, weeknumber, booked_passengers)
200 VALUES(_schedulenum, i, 0);
201 SET i = i + 1;
202END WHILE;
203END;
204//
205
206-- ÄCKLIGT OSÄKER PÅ DENNA, kanske behövs triggers på denna?
207CREATE PROCEDURE addReservation
208(IN _depcode varchar(3), IN _arrcode varchar(3), IN _year int, IN _week int, IN _day varchar(10), IN _time time, IN _numpass int, OUT _resnum int)
209BEGIN
210DECLARE F int;
211
212SELECT flight_number INTO F FROM flight WHERE fk_schedule =
213 (SELECT schedule_id FROM weekly_schedule WHERE fk_route =
214 (SELECT route_id FROM routes WHERE fk_departure = _depcode AND fk_arrival = _arrcode and thisyear = _year)
215 AND departure_time = _time AND fk_weekday = (SELECT weekdays FROM weekdays WHERE weekdays = _day AND fk_year = _year))
216 AND weeknumber = _week;
217
218INSERT INTO reservation(reservation_number, fk_flight)
219VALUES (_resnum, F);
220END;
221//
222/*
223--Behöver vi ens first / last name?
224*/
225CREATE PROCEDURE addPassenger(IN _resnum int, IN _passport int, IN _name varchar(60))
226BEGIN
227DECLARE _first, _last varchar(30);
228SELECT SPLIT_STR(_name, ' ', 1) AS _first;
229SELECT SPLIT_STR(_name, ' ', 2) AS _last;
230
231INSERT INTO passenger(passport_number, first_name, last_name)
232VALUES(_passport, _first, _last);
233
234INSERT INTO ticket (fk_reservation, fk_passport)
235VALUES (_resnum, _passport);
236END;
237//
238
239/*
240Add a contact: Procedure call to handle: addContact(reservation_nr,
241passport_number, email, phone); where the contact already must be added as
242a passenger to the reservation.
243*/
244
245CREATE PROCEDURE addContact(IN _resnum int, IN _passport int, IN _email varchar(30), IN _phone BIGINT)
246BEGIN
247
248INSERT INTO contact (fk_passport, email, phone) VALUES (_passport, _email, _phone);
249
250UPDATE reservation
251SET fk_contact = _passport
252WHERE reservation_number = (
253SELECT fk_reservation
254FROM ticket
255WHERE fk_reservation = _resnum
256AND fk_passport = _passport);
257
258END;
259//
260
261/*
262kommer behöva lägga till error message?
263ska man ta bort reservation när den övergått till en booking..?
264*/
265CREATE PROCEDURE addPayment (IN _resnum int, IN _card_name varchar(60), IN _card_number BIGINT)
266BEGIN
267
268DECLARE _flight, _numpass int DEFAULT 0;
269DECLARE _seatPrice, _totPrice double DEFAULT 0;
270SELECT fk_flight INTO _flight FROM reservation WHERE reservation_number = _resnum;
271SELECT COUNT(*) INTO _numpass FROM ticket WHERE fk_reservation = _resnum;
272
273IF _numpass <= calculateFreeSeats(_flight) AND
274 (SELECT fk_contact FROM reservation WHERE reservation_number = _resnum) IS NOT NULL
275 THEN
276
277 SET _seatPrice = calculatePrice(_flight);
278 SET _totPrice = _seatPrice * _numpass;
279
280 INSERT INTO credit_card (card_number, card_holder)
281 VALUES (_card_number, _card_name);
282
283 INSERT INTO booking (seat_price, total_price, fk_reservation, fk_credit_card)
284 VALUES (_seatPrice, _totPrice, _resnum, _card_number);
285
286 UPDATE flight
287 SET booked_passengers = booked_passengers + _numpass
288 WHERE flight_number = _flight;
289
290ELSE
291 SELECT "No contact information or not enough seats on flight" AS "Message";
292END IF;
293END;
294//
295
296/*
297
298 -----: FUNCTIONS :-----
299
300*/
301
302CREATE FUNCTION calculateFreeSeats(_flightnumber int) RETURNS int
303BEGIN
304DECLARE S int;
305SELECT booked_passengers INTO S FROM flight WHERE flight_number = _flightnumber;
306RETURN (40-S);
307END;
308//
309
310CREATE FUNCTION calculatePrice(_flightnumber int) RETURNS double
311BEGIN
312
313DECLARE R, W, P double;
314DECLARE B int;
315
316SELECT route_price INTO R FROM routes WHERE routes.route_id =
317 (SELECT fk_route FROM weekly_schedule WHERE weekly_schedule.schedule_id =
318 (SELECT fk_schedule FROM flight WHERE flight.flight_number = _flightnumber));
319
320SELECT weekday_profit_factor INTO W FROM weekdays WHERE weekdays.weekdays =
321 (SELECT fk_weekday FROM weekly_schedule WHERE weekly_schedule.schedule_id =
322 (SELECT fk_schedule FROM flight WHERE flight.flight_number = _flightnumber));
323
324SELECT year_profit_factor INTO P FROM years WHERE years.years =
325 (SELECT fk_year FROM weekdays WHERE weekdays.weekdays =
326 (SELECT fk_weekday FROM weekly_schedule WHERE weekly_schedule.schedule_id =
327 (SELECT fk_schedule FROM flight WHERE flight.flight_number = _flightnumber)));
328
329SELECT booked_passengers INTO B FROM flight WHERE flight.flight_number = _flightnumber;
330
331RETURN (R*W*(B+1/40)*P);
332END;
333//
334
335/*
336
337 -----: TRIGGERS :-----
338
339*/
340
341/*
342Ny (bör dubbelkolla så varje ticket_number är unikt)
343*/
344CREATE TRIGGER setTicketNum
345AFTER INSERT ON booking
346FOR EACH ROW
347BEGIN
348DECLARE i, R int;
349SELECT fk_reservation INTO R FROM booking WHERE fk_reservation = NEW.fk_reservation;
350WHILE i < (SELECT COUNT(*) FROM ticket WHERE ticket.fk_reservation = NEW.fk_reservation)
351DO
352UPDATE ticket
353SET ticket_number = 10000000*rand()
354WHERE fk_reservation = R;
355
356SET i = i + 1;
357END WHILE;
358END;
359//
360
361
362/*
363CREATE TRIGGER getTicketNum
364AFTER INSERT ON booking
365FOR EACH ROW
366BEGIN
367DECLARE P, R, T int;
368
369SELECT * FROM reservation WHERE reservation.reservation_number = NEW.fk_reservation as Q;
370SELECT * FROM
371
372INSERT INTO ticket(ticket_number, fk_reservation)
373VALUES (T, NEW.reservation_number);
374
375skapa ticket;
376insert resnumber;
377insert ticketnumber;
378
379END
380//
381*/
382
383DELIMITER ;