pain.sql

-- year: integer,
-- weekday: varchar(10)
-- airport code: varchar(3)
-- airport name: varchar(30)
-- country: varchar(30)
-- departure time: time
-- profit factor: double
-- route price: double
-- weekday factor: double
-- flight id: integer
-- reservation number: integer
-- name: varchar(30)
-- passport number: integer
-- email: varchar(30)
-- phone number: bigint
-- credit card number: bigint

/*
question 2: table creation
*/


SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS year;

DROP TABLE IF EXISTS weekday;

DROP TABLE IF EXISTS airport;

DROP TABLE IF EXISTS route;

DROP TABLE IF EXISTS weekly_flight;

DROP TABLE IF EXISTS flight;

DROP TABLE IF EXISTS reservation;

DROP TABLE IF EXISTS passenger;

DROP TABLE IF EXISTS contact;

DROP TABLE IF EXISTS booking;

DROP TABLE IF EXISTS ticket;

SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE year (
    num integer primary key not null,
    profit_factor double not null
);

CREATE TABLE weekday (
    day varchar(10) not null,
    year integer  not null,
    factor double not null,
    PRIMARY KEY (day, year)
);

CREATE TABLE airport (
    id integer primary key not null auto_increment,
    airport_code varchar(3) unique not null,
    name varchar(30) not null,
    country varchar(30) not null
);

CREATE TABLE route (
    id integer primary key not null auto_increment,
    departure_airport_code varchar(3) not null,
    arrival_airport_code varchar(3) not null,
    year integer not null,
    route_price double not null,
    constraint fk_route_departure
        foreign key (departure_airport_code) references airport (airport_code)
        on delete cascade
        on update restrict,
    constraint fk_route_arrival
        foreign key (arrival_airport_code) references airport (airport_code)
        on delete cascade
        on update restrict
);

CREATE TABLE weekly_flight (
    flight_nr integer primary key not null auto_increment,
    route integer not null,
    year integer not null,
    weekday varchar(10) not null,
    departure_time time not null,
    constraint fk_weekly_flight_route
        foreign key (route) references route (id)
        on delete cascade
        on update restrict,
    constraint fk_weekly_flight_year
        foreign key (year) references year (num)
        on delete cascade
        on update restrict,
    constraint fk_weekly_flight_weekday
        foreign key (weekday, year) references weekday (day, year)
        on delete cascade
        on update restrict
);

CREATE TABLE flight (
    id integer primary key not null auto_increment,
    week integer not null,
    weekly_flight_nr integer not null,
    constraint fk_flight_weekly_flight_nr
        foreign key (weekly_flight_nr) references weekly_flight (flight_nr)
        on delete cascade
        on update restrict
);

CREATE TABLE reservation (
    id integer primary key not null auto_increment,
    flight_id integer not null,
    num_passengers integer not null,
    constraint fk_reservation_flight_id
        foreign key (flight_id) references flight (id)
        on delete cascade
        on update restrict
);

CREATE TABLE passenger (
    id integer primary key not null auto_increment,
    reservation_id integer not null,
    passport_number integer not null,
    name varchar(30) not null,
    constraint fk_passenger_reservation_id
        foreign key (reservation_id) references reservation (id)
        on delete cascade
        on update restrict
);

CREATE TABLE contact (
    passenger_id integer primary key not null,
    phone bigint not null,
    email varchar (30) not null,
    constraint fk_contact_passenger_id
        foreign key (passenger_id) references passenger (id)
        on delete cascade
        on update restrict
);

CREATE TABLE booking (
    reservation_id integer primary key not null,
    cardholder_name varchar(30) not null,
    credit_card_number bigint not null,
    total_price double not null,
    constraint fk_booking_reservation_id
        foreign key (reservation_id) references reservation (id)
        on delete cascade
        on update restrict
);

CREATE TABLE ticket (
    ticket_number integer primary key not null,
    passenger_id integer not null,
    constraint fk_ticket_passenger_id
        foreign key (passenger_id) references passenger (id)
        on delete cascade
        on update restrict
);

/*
question 3: procedures for populating the database
*/

DROP PROCEDURE IF EXISTS addYear;

DROP PROCEDURE IF EXISTS addDay;

DROP PROCEDURE IF EXISTS addDestination;

DROP PROCEDURE IF EXISTS addRoute;

DROP PROCEDURE IF EXISTS addFlight;

create procedure addYear(in num integer, in profit_factor double)
    begin
        insert into year (num, profit_factor) values (num, profit_factor);
    end;
//

create procedure addDay(in year integer, in weekday varchar(10), in factor double)
    begin
        insert into weekday (year, day, factor) values (year, weekday, factor);
    end;
//

create procedure addDestination(in airport_code varchar(3),in name varchar(30), in country varchar(30))
    begin
        insert into airport (airport_code, name, country) values (airport_code, name, country);
    end;
//

create procedure addRoute(
    in departure_airport_code varchar(3),
    in arrival_airport_code varchar(3),
    in year integer,
    in route_price double)
    begin
        insert into route (departure_airport_code, arrival_airport_code, year, route_price)
        values (departure_airport_code, arrival_airport_code, year, route_price);
    end;
//

create procedure addFlight(
    in departure_airport_code varchar(3),
    in arrival_airport_code varchar(3),
    in year integer,
    in weekday varchar(10),
    in departure_time time)
    begin
        declare flight_nr integer;

        insert into weekly_flight (route, year, weekday, departure_time)
        values (
            (select id from route
            where departure_airport_code = route.departure_airport_code
            and arrival_airport_code = route.arrival_airport_code
            and year = route.year),
            year,
            weekday,
            departure_time
        );

        select LAST_INSERT_ID() into flight_nr;

        insert into flight (week, weekly_flight_nr)
        WITH RECURSIVE cte AS
        (
            SELECT 1 AS N

            UNION ALL

            SELECT N + 1
            FROM cte
            WHERE N < 52
        )
            select
                N as week,
                flight_nr as weekly_flight_nr
            from cte;
    end;
//

/*
question 4: helper functions
*/

DROP FUNCTION IF EXISTS calculateFreeSeats;

DROP FUNCTION IF EXISTS calculatePrice;

CREATE FUNCTION calculateFreeSeats(flight_id integer) RETURNS integer
    begin
        declare count_passengers integer;

        select count(*) from passenger
        where reservation_id in (select id from reservation where reservation.flight_id = flight_id)
        into count_passengers;

        return 40 - count_passengers;
    end;
//

CREATE FUNCTION calculatePrice(flight_id integer) RETURNS double
    begin
        declare route_price double;
        declare weekday_factor double;
        declare profit_factor double;
        declare booked_passengers integer;
        declare seat_price double;

        select route.route_price into route_price from route
        inner join weekly_flight on route.id = weekly_flight.route
        inner join flight on flight.weekly_flight_nr = weekly_flight.flight_nr
        where flight.id = flight_id;

        select weekday.factor into weekday_factor from weekday
        inner join weekly_flight on weekday.day = weekly_flight.weekday
        and weekday.year = weekly_flight.year
        inner join flight on flight.weekly_flight_nr = weekly_flight.flight_nr
        where flight.id = flight_id;

        select year.profit_factor into profit_factor from year
        inner join weekly_flight on year.num = weekly_flight.year
        inner join flight on weekly_flight.flight_nr = flight.weekly_flight_nr
        where flight.id = flight_id;

        select 40 - calculateFreeSeats(flight_id) into booked_passengers;

        select route_price * weekday_factor * (booked_passengers + 1)/40 * profit_factor
        into seat_price;

        return round(seat_price, 5);
    end;
//

/*
question 5: ticket trigger
*/

DROP TRIGGER IF EXISTS issue_tickets;

CREATE TRIGGER issue_tickets
AFTER INSERT ON booking
FOR EACH ROW
    begin
        insert into ticket (ticket_number, passenger_id)
            select
                FLOOR(0 + RAND() * (2147483647 - 0 +1)) as ticket_number,
                passenger.id
            from passenger
            where passenger.reservation_id = NEW.reservation_id;
    end;
//

/*
question 6: frontend interaction
*/

DROP PROCEDURE IF EXISTS addReservation;

DROP PROCEDURE IF EXISTS addPassenger;

DROP PROCEDURE IF EXISTS addContact;

DROP PROCEDURE IF EXISTS addPayment;

create procedure addReservation(
    in departure_airport_code varchar(3),
    in arrival_airport_code varchar(3),
    in year integer,
    in week integer,
    in day varchar(10),
    in departure_time time,
    in number_of_passengers integer,
    out output_reservation_nr integer)
    proc:
    begin
        declare route_id integer;
        declare flight_nr integer;
        declare flight_id integer;
        declare free_seats integer;

        select id from route
            where departure_airport_code = route.departure_airport_code
            and arrival_airport_code = route.arrival_airport_code
            and year = route.year
            into route_id;

        select weekly_flight.flight_nr from weekly_flight
        where
            weekly_flight.route = route_id AND
            weekly_flight.year = year AND
            weekly_flight.weekday = day AND
            weekly_flight.departure_time = departure_time
        into flight_nr;

        select id from flight
            where flight.week = week AND flight.weekly_flight_nr = flight_nr
        into flight_id;

        if flight_id is null then
            select 'There exist no flight for the given route, date and time' as "Message";
            leave proc;
        end if;

        select calculateFreeSeats(flight_id) into free_seats;

        if free_seats < number_of_passengers then
            select 'There are not enough seats available on the chosen flight' as "Message";
            leave proc;
        end if;

        insert into reservation (flight_id, num_passengers) values (flight_id, number_of_passengers);

        select LAST_INSERT_ID() into output_reservation_nr;
    end;
//

create procedure addPassenger(
    in reservation_nr integer,
    in passport_number integer,
    in name varchar(30))
    proc:
    begin
        declare matching_reservation integer;
        declare matching_booking integer;
        select reservation_nr IN (select id from reservation) into matching_reservation;
        IF matching_reservation < 1 then   
            select 'The given reservation number does not exist' as "Message";
            leave proc;
        end if;

        select reservation_nr IN (select reservation_id from booking) into matching_booking;
        IF matching_booking > 0 then   
            select 'The booking has already been payed and no futher passengers can be added' as "Message";
            leave proc;
        end if;

        insert into passenger (reservation_id, passport_number, name)
        values (reservation_nr, passport_number, name);
    end;
//

create procedure addContact(
    in reservation_nr integer,
    in passport_number integer,
    in email varchar(30),
    in phone bigint)
    proc:
    begin
        declare passenger_id integer;
        declare matching_passenger integer;
        declare matching_reservation integer;

        select id from passenger
        where reservation_nr = passenger.reservation_id
        and passenger.passport_number = passport_number
        into passenger_id;

        select reservation_nr IN
        (select id
        from reservation) into matching_reservation;

        IF matching_reservation < 1 then
            select 'The given reservation number does not exist' as "Message";
            leave proc;
        end if;

        select passenger_id IN
        (select id 
        from passenger
        where passenger.reservation_id = reservation_nr) into matching_passenger;

        IF passenger_id is null or matching_passenger < 1 then
            select 'The person is not a passenger of the reservation' as "Message";
            leave proc;
        end if;

        insert into contact (passenger_id, phone, email)
        values (passenger_id, phone, email);
    end;
//

create procedure addPayment(
    in reservation_nr integer,
    in cardholder_name varchar(30),
    in credit_card_number bigint)
    proc:
    begin
        declare matching_reservation integer;
        declare has_contact integer;
        declare flight_id integer;
        declare free_seats integer;
        declare number_of_passengers integer;
        declare total_price double;

        select reservation_nr IN
        (select id
        from reservation) into matching_reservation;

        IF matching_reservation < 1 then
            select 'The given reservation number does not exist' as "Message"; 
            leave proc;
        end if;

        select count(*) from passenger
        inner join contact on passenger.id = contact.passenger_id
        where passenger.reservation_id = reservation_nr
        into has_contact;

        IF has_contact < 1 then
            select 'The reservation has no contact yet' as "Message";
            leave proc;
        end if;

        select reservation.flight_id from reservation where reservation.id = reservation_nr into flight_id;
        select calculateFreeSeats(flight_id) into free_seats;
        select count(*) from passenger where passenger.reservation_id = reservation_nr into number_of_passengers;

        if free_seats < number_of_passengers then
            delete from reservation where id = reservation_nr;
            select 'There are not enough seats available on the flight anymore, deleting reservation' as "Message";
            leave proc;
        end if;

        set total_price = calculatePrice(flight_id) * number_of_passengers;

        insert into booking (reservation_id, cardholder_name, credit_card_number, total_price)
        values (reservation_nr, cardholder_name, credit_card_number, total_price);
    end;
//

/*
question 7: all flights view
*/

DROP VIEW IF EXISTS allFlights;

create view allFlights AS
    select
        (select name from airport where airport_code = route.departure_airport_code) as departure_city_name,
        (select name from airport where airport_code = route.arrival_airport_code) as destination_city_name,
        weekly_flight.departure_time,
        weekly_flight.weekday as departure_day,
        flight.week as departure_week,
        weekly_flight.year as departure_year,
        calculateFreeSeats(flight.id) as nr_of_free_seats,
        calculatePrice(flight.id) as current_price_per_seat
    from flight
    inner join weekly_flight on flight.weekly_flight_nr = weekly_flight.flight_nr
    inner join route on weekly_flight.route = route.id;

/*
question 8
    a) If BrianAir *has* to store credit card numbers itself, the only existing option is some sort of at-rest encryption.
    b) Three advantages of using stored procedures to encapsulate this kind of logic are security, since access to raw data
        can only happen through a defined set of APIs. Network traffic can be reduced since data processing often does not have to
        cross between machines. It can also ease maintenance since updating the definition in the SQL server automatically applies to all its users
        on different machines.
*/


/*
question 9
    b) No, the new reservation is not visible in the other terminal since the changes have not been committed to the database yet.
    c) It's not yet possible to modify the reservation at all from B since from B's perpsective, the reservation does not exist yet.
       It doesn't exist yet since the changes A has made are so far isolated to it's view of the database from the transaction.
*/


/*
question 10
    a) Overbooking did and can occur, this is as a result of two different clients running the check
       and concluding that there are still free seats left, although there would not be free seats left
       after one client has inserted their booking.
    b) It can theoretically occur and rather easily so. The only requirement is that two clients
       execute AddPayment at the same time in transactions started before the one has committed.
       This is possible because the new booked seats will not be visible to another client and 
       even if one commits, no locks or conflict checks will catch.
       If the procedure calls aren't wrapped in transactions, both clients must complete the check in AddPayment
       `if free_seats < number_of_passengers then` and first then one can insert for the overbooking to occur.
    c) It was trivially possible to make thos occur by adding a sleep after the relevant check mentioned in 10b.
    d) With relevant modification to the test scripts using transaction and lock statements to prevent race conditions during booking
       the earlier encountered bugs were easily prevented.
*/

/*
Identify one case where a secondary index would be useful.
Design the index, describe and motivate your design.

One place a secondary index would be useful is as a compound index on the reservation number and passport ID columns
of the passenger table. Many operations (and quite likely future relevant ones) need to fetch or validate
passenger info. Currently, aside from a few side tables like contact where you can make a lookup by the
internal passenger ID, a sequential scan likely has to be made to find a given passenger using their passport
for a reservation.

A standard B-Tree index on the columns `(reservation_id, passport_number)` would help this.
*/