SHARE
TWEET

Untitled

a guest May 22nd, 2019 73 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  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.  
  13. SET FOREIGN_KEY_CHECKS=0;
  14.  
  15. DROP TABLE IF EXISTS destination;
  16. DROP TABLE IF EXISTS year;
  17. DROP TABLE IF EXISTS route;
  18. DROP TABLE IF EXISTS day;
  19. DROP TABLE IF EXISTS weeklyschedule;
  20. DROP TABLE IF EXISTS flight;
  21. DROP TABLE IF EXISTS reservation;
  22. DROP TABLE IF EXISTS creditcard;
  23. DROP TABLE IF EXISTS payment;
  24. DROP TABLE IF EXISTS passenger;
  25. DROP TABLE IF EXISTS contact;
  26. DROP TABLE IF EXISTS ticket;
  27.  
  28. SET FOREIGN_KEY_CHECKS=1;
  29.  
  30.  
  31.  
  32. # create table 'destination'
  33. CREATE 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
  41. CREATE 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
  48. CREATE 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'
  60. CREATE 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
  68. CREATE 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
  80. CREATE 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'
  90. CREATE 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'
  99. CREATE 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'
  107. CREATE 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'
  117. CREATE 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?
  127. CREATE 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'
  139. CREATE 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);
  155. call addDay(2019, 'Monday', 10.0);
  156. call addDestination('SKA', 'Skavsta', 'Sweden');
  157. call addDestination('GBG', 'Göteborg', 'Sweden');
  158. call addRoute('SKA', 'GBG', 2019, 100.0);*/
  159.  
  160.  
  161. /*====================================================================================================================================
  162.  
  163. =====================================================================================================================================*/
  164.  
  165. # addYear
  166. DROP PROCEDURE IF EXISTS addYear;
  167.  
  168. DELIMITER //
  169. CREATE PROCEDURE addYear(IN vyear INT, IN factor DOUBLE)
  170. BEGIN
  171.  
  172. IF (SELECT year FROM year WHERE year = vyear) IS NULL THEN
  173. INSERT INTO `year`(`year`, `profitfactor`) VALUES (vyear, factor);
  174. END IF;
  175. END //
  176. DELIMITER ;
  177.  
  178.  
  179. /*====================================================================================================================================
  180.  
  181. =====================================================================================================================================*/
  182. # addDay
  183. DROP PROCEDURE IF EXISTS addDay;
  184.  
  185. DELIMITER //
  186. CREATE PROCEDURE addDay(IN year INT, IN day VARCHAR(10), IN factor DOUBLE)
  187. BEGIN
  188. INSERT INTO `day`(`year`, `dayname`, `weekdayfactor`) VALUES (year, day, factor);
  189. END //
  190. DELIMITER ;
  191.  
  192.  
  193. /*====================================================================================================================================
  194.  
  195. =====================================================================================================================================*/
  196. # add destination
  197. DROP PROCEDURE IF EXISTS addDestination;
  198.  
  199. DELIMITER //
  200. CREATE PROCEDURE addDestination(IN airport_code VARCHAR(3), IN name VARCHAR(30), IN country VARCHAR(30))
  201. BEGIN
  202.  
  203. INSERT INTO `destination`(`code`, `name`, `country`) VALUES (airport_code, name, country);
  204.  
  205.  
  206.  
  207.  
  208. END //
  209. DELIMITER ;
  210.  
  211.  
  212. /*====================================================================================================================================
  213.  
  214. =====================================================================================================================================*/
  215.  
  216. # add route
  217. DROP PROCEDURE IF EXISTS addRoute;
  218.  
  219. DELIMITER //
  220. CREATE PROCEDURE addRoute(IN departure_airport_code VARCHAR(3), IN arrival_airport_code VARCHAR(3), IN year INT, IN routeprice DOUBLE)
  221. BEGIN
  222. INSERT INTO `route`(`departure`, `arrival`, `route_year`, `price`) VALUES (departure_airport_code, arrival_airport_code, year, routeprice);
  223. END //
  224. DELIMITER ;
  225.  
  226.  
  227. /*====================================================================================================================================
  228.  
  229. =====================================================================================================================================*/
  230.  
  231. # add weeklyflight
  232. DROP PROCEDURE IF EXISTS addFlight;
  233.  
  234. DELIMITER //
  235. CREATE PROCEDURE addFlight(IN departure_airport_code VARCHAR(3), IN arrival_airport_code VARCHAR(3), IN year INT, IN day VARCHAR(10), IN time TIME)
  236. BEGIN
  237.  
  238. DECLARE route_id INT;
  239. DECLARE route_price DOUBLE;
  240. DECLARE schedule_id INT;
  241. DECLARE i INT;
  242.  
  243. # find the route id
  244.  
  245. SELECT id INTO route_id FROM route WHERE departure = departure_airport_code AND arrival = arrival_airport_code AND route_year = year;
  246.  
  247.  
  248. SELECT price INTO route_price FROM route WHERE id = route_id;
  249.  
  250. INSERT INTO `weeklyschedule`(`route`, `departure_time`, `wday`) VALUES (route_id, time, day);
  251.  
  252. SELECT 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.  
  256. IF schedule_id IS NOT NULL THEN
  257. SET 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;
  262. END IF;
  263.  
  264. END //
  265. DELIMITER ;
  266.  
  267.  
  268.  
  269.  
  270. /*====================================================================================================================================
  271.  
  272. =====================================================================================================================================*/
  273.  
  274. DROP FUNCTION IF EXISTS calculateFreeSeats;
  275. DELIMITER //
  276.  
  277. CREATE FUNCTION calculateFreeSeats(flight_number INT)
  278. RETURNS INT
  279. BEGIN
  280.  
  281. DECLARE seats INT;
  282.  
  283.  
  284. SELECT COUNT(*) INTO seats FROM ticket WHERE flight_number = flight;
  285.  
  286.  
  287. SET seats = (SELECT available_seats FROM flight WHERE flight_number = flightnumber) - seats;
  288.  
  289.  
  290. UPDATE flight SET available_seats = seats WHERE flight_number = flightnumber;
  291.  
  292. RETURN seats;
  293.  
  294. END //
  295. DELIMITER ;
  296. /*====================================================================================================================================
  297.  
  298. =====================================================================================================================================*/
  299. DROP FUNCTION IF EXISTS calculatePrice;
  300. DELIMITER //
  301.  
  302. CREATE FUNCTION calculatePrice(flight_number INT)
  303. RETURNS DOUBLE
  304. BEGIN
  305.  
  306. DECLARE weekday_factor DOUBLE;
  307. DECLARE booked_passengers INT;
  308. DECLARE profit_factor DOUBLE;
  309.  
  310. # get weekly flight schedule to get route
  311.  
  312. # route price
  313. DECLARE route_price DOUBLE;
  314. SELECT 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.  
  318. SELECT 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
  321. SELECT calculateFreeSeats(flight_number) INTO booked_passengers;
  322.  
  323. # profitmargin
  324. SELECT 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.  
  328. RETURN route_price * weekday_factor * (seats + 1)/40 * profit_factor;
  329. END //
  330. DELIMITER ;
  331.  
  332. /*====================================================================================================================================
  333.  
  334. =====================================================================================================================================*/
  335.  
  336.  
  337. DELIMITER //
  338. # create a trigger for ticket
  339. CREATE TRIGGER ticketNumber AFTER INSERT ON payment
  340. FOR EACH ROW
  341. BEGIN
  342.  
  343. DECLARE reservation_number INT;
  344. SELECT MAX(id) INTO reservation_number FROM payment;
  345.  
  346. INSERT INTO `ticket`(`id`, `payment`, `passenger`/*, `flight`*/)
  347. SELECT
  348. rand()*1000,
  349. reservation,
  350. id FROM passenger WHERE reservation = reservation_number;/*
  351. flight FROM reservation WHERE reservationnumber = reservation_number;
  352. */
  353.  
  354. UPDATE ticket SET flight = (SELECT flight FROM reservation WHERE reservationnumber = reservation_number);
  355.  
  356. END //
  357. DELIMITER ;
  358.  
  359.  
  360. /*====================================================================================================================================
  361.  
  362. =====================================================================================================================================*/
  363.  
  364.  
  365. DROP PROCEDURE IF EXISTS addReservation;
  366. DELIMITER //
  367.  
  368. CREATE 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)
  369. BEGIN
  370.  
  371. DECLARE flight_number INT;
  372. DECLARE route_id INT;
  373. DECLARE schedule_id INT;
  374.  
  375. SELECT id INTO route_id FROM route WHERE departure = departure_airport_code AND arrival = arrival_airport_code AND route_year = year;
  376.  
  377. IF route_id IS NULL THEN
  378. SELECT 'route_id is null' AS 'Message';
  379. END IF;
  380.  
  381.  
  382. SELECT id INTO schedule_id FROM weeklyschedule WHERE wday = day AND departure_time = time AND route = route_id;
  383.  
  384. IF schedule_id IS NULL THEN
  385. SELECT 'schedule_id is null' AS 'Message';
  386. END IF;
  387.  
  388. SELECT flightnumber INTO flight_number FROM flight WHERE weekly_schedule = schedule_id AND fweek = inweek;
  389. /*
  390. SELECT 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. /*
  395. SELECT @week AS 'Week';
  396. SELECT @flight_number AS 'Flightnumber';*/
  397.  
  398. IF flight_number IS NULL THEN
  399. SELECT 'There exist no flight for the given route, date and time' AS 'Message';
  400.  
  401. ELSE
  402. IF calculateFreeSeats(flight_number) < number_of_passengers THEN
  403. SELECT "There are not enough seats available on the chosen flight" AS 'Message';
  404.  
  405.  
  406. ELSE
  407. INSERT INTO `reservation`(`flight`, `passengers`) VALUES(flight_number, number_of_passengers);
  408. SELECT MAX(reservationnumber) INTO output_reservation_nr FROM reservation;
  409. END IF;
  410. END IF;
  411.  
  412.  
  413.  
  414. END //
  415. DELIMITER ;
  416.  
  417.  
  418. /*====================================================================================================================================
  419.  
  420. =====================================================================================================================================*/
  421.  
  422.  
  423. DROP PROCEDURE IF EXISTS addPassenger;
  424. DELIMITER //
  425.  
  426. CREATE PROCEDURE addPassenger(IN reservation_nr INT, IN passport_number INT, IN name VARCHAR(30))
  427. BEGIN
  428.  
  429. IF (SELECT reservationnumber FROM reservation WHERE reservationnumber = reservation_nr) IS NULL THEN
  430. SELECT 'The given reservation number does not exist' AS 'Message';
  431.  
  432. ELSE
  433.  
  434.  
  435. IF (SELECT reservation FROM payment WHERE reservation = reservation_nr) IS NOT NULL THEN
  436. SELECT 'The booking has already been payed and no futher passengers can be added' AS 'Message';
  437.  
  438.  
  439. ELSE
  440. INSERT INTO `passenger`(`passport_number`, `name`, `reservation`) VALUES (passport_number, name, reservation_nr);
  441. END IF;
  442. END IF;
  443. END //
  444. DELIMITER ;
  445.  
  446.  
  447. /*====================================================================================================================================
  448.  
  449. =====================================================================================================================================*/
  450.  
  451.  
  452. DROP PROCEDURE IF EXISTS addContact;
  453. DELIMITER //
  454.  
  455. CREATE PROCEDURE addContact(IN reservation_nr INT, IN INpassport_number INT, IN email VARCHAR(45), IN phone INT)
  456. BEGIN
  457.  
  458.  
  459. IF (SELECT min(reservationnumber) FROM reservation WHERE reservationnumber = reservation_nr) IS NULL THEN
  460. SELECT 'The given reservation number does not exist' AS 'Message';
  461.  
  462. ELSE
  463. IF (SELECT MIN(id) FROM passenger WHERE passport_number = INpassport_number AND reservation = reservation_nr) IS NULL THEN
  464. SELECT 'The person is not a passenger of the reservation' AS 'Message';
  465. ELSE
  466. INSERT INTO `contact`(`passport_number`, `email`, `phone`) VALUES (INpassport_number, email, phone);
  467. END IF;
  468. END IF;
  469.  
  470. END //
  471. DELIMITER ;
  472.  
  473.  
  474.  
  475. /*====================================================================================================================================
  476.  
  477. =====================================================================================================================================*/
  478.  
  479. DROP PROCEDURE IF EXISTS addPayment;
  480. DELIMITER //
  481.  
  482. CREATE PROCEDURE addPayment (IN reservation_nr INT, IN cardholder_name VARCHAR(30), IN credit_card_number BIGINT)
  483. BEGIN
  484.  
  485.  
  486. DECLARE seats INT;
  487. DECLARE flight_number INT;
  488. DECLARE tmp INT;
  489.  
  490. /* Get potential contact */
  491. SELECT passport_number INTO tmp FROM contact WHERE passport_number = (SELECT MIN(passport_number) FROM passenger WHERE reservation = reservation_nr);
  492.  
  493. /* Get flight number */
  494. SELECT flight INTO flight_number FROM reservation WHERE reservationnumber = reservation_nr;
  495.  
  496. /* Get number of seats in reservation */
  497. SELECT COUNT(*) INTO seats FROM passenger WHERE reservation = reservation_nr;
  498.  
  499.  
  500. IF (SELECT `reservationnumber` FROM reservation WHERE `reservationnumber` = reservation_nr) IS NULL THEN
  501. SELECT 'The given reservation number does not exist' AS 'Message';
  502.  
  503. ELSE
  504.  
  505. IF tmp IS NULL THEN
  506. SELECT 'The reservation has no contact yet' as 'Message';
  507.  
  508. ELSE
  509.  
  510.  
  511. IF (seats > calculateFreeSeats(flight_number)) THEN
  512. SELECT 'There are not enough seats available on the flight anymore, deleting reservation' AS 'Message';
  513. SET SQL_SAFE_UPDATES=0;
  514. DELETE FROM passenger WHERE reservation = reservation_nr;
  515. DELETE FROM reservation WHERE reservationnumber = reservation_nr;
  516. SET SQL_SAFE_UPDATES=1;
  517.  
  518. ELSE
  519.  
  520.  
  521. IF (SELECT card_number FROM creditcard WHERE card_number = credit_card_number) IS NULL THEN
  522. INSERT INTO creditcard(card_number, name) VALUES (credit_card_number, cardholder_name);
  523. END IF;
  524.  
  525.  
  526. INSERT INTO payment(card_number, reservation) VALUES (credit_card_number, reservation_nr);
  527.  
  528. END IF;
  529. END IF;
  530. END IF;
  531.  
  532. END //
  533. DELIMITER ;
  534.  
  535.  
  536. /*====================================================================================================================================
  537.  
  538. =====================================================================================================================================*/
  539.  
  540.  
  541. DROP VIEW IF EXISTS allFlights;
  542. CREATE VIEW IF NOT EXISTS allFlights AS
  543.  
  544. SELECT
  545. C.name AS 'departure_city_name', /* VARCHAR(30) */
  546. C.name AS 'destination_city_name', /* VARCHAR(30) */
  547. B.departure_time AS 'departure_time', /* TIME */
  548. B.wday AS 'departure_day', /* VARCHAR(10) */
  549. D.fweek AS 'departure_week', /* INT? */
  550. A.route_year AS 'departure_year', /* INT */
  551. calculateFreeSeats(D.flightnumber) AS 'nr_of_free_seats', /* INTEGER */
  552. calculatePrice(D.flightnumber) AS 'current_price_per_seat' /* DOUBLE */
  553.  
  554. FROM route A, weeklyschedule B, destination C, flight D
  555. WHERE A.id = B.route AND D.weekly_schedule = B.id AND A.departure = C.code AND A.arrival = C.code;
  556.  
  557.  
  558. /*
  559. A route = departure, arrival, year
  560. B weeklyschedule = route, departure_time, day
  561. C destination = name
  562. D flight = flightnumber, fweek
  563.  
  564. */
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top