Exercises on triggers

Exercise 1: Flight booking

Remember the schema from last week about flights and airports:

Airports(_code_,city)
FlightCodes(_code_, airlineName)
Flights(origin, destination, departure, arrival, _code_)
  origin → Airports.code
  destination → Airports.code
  code → FlightCodes.code

Assume the following two tables are added to this schema: a table listing for each flight the number of available seats and the price per ticket:

AvailableFlights(_flight_, _date_, numberOfFreeSeats, price)
  flight → Flights.code

And another one listing the passengers that have been booked for each flight, with the price they have paid and a unique booking reference number (an integer, to keep things simple):

Bookings(_reference_, flight, date, passenger, price)
  (flight, date) → AvailableFlights.(code, date)

Question 1 Create a view that lists booking references, passengers, flight codes, date, and departure and destination cities.

Question 2 Create a trigger on the view defined in quesiton one. This trigger takes care of booking a new passenger to a flight. It is fired by an insertion of a passenger and a flight code to the view, for instance, “book Annie Adams for AF666 on 2016-03-18”. Its effect should be the following:

  • if the number of free seats on AF666 at this date is positive, decrement it by one; the booking is successful
  • if there are no free seats, the booking fails
  • if the booking is successful, add Annie Adams and AF666 to Bookings at the given date, with the price given in AvailableFlights when booking her; also add a booking reference which is the maximum of the previous references (for all flights) plus one
  • if the booking is successful, increment the price by 50 SEK for the next passenger (thus the fuller the flight, the more you pay)

Question 3 The airline decides to upgrade its database to keep track of its fleet. This means adding a table to list the available planes and updating the AvailableFlights in the following way:

Planes(_regnr_, capacity)
AvailableFlights(_flight_, _date_, numberOfFreeSeats, price, plane)
  (flight, date) → Flights.(code, date)
  plane → Planes.regnr

We now have duplicated information in the database: we can compute the number of free seats by subtracting the number of booked seats to the capacity of the plane. One solution would be to simply remove the numberOfFreeSeats column but, as it is often the case with production databases, it is impossible to update all the systems accessing the database at once to make use of the new schema, which means that the duplicated field needs to be present in the database during a transition period.

To make the transition easier, we would like to have the value of the numberOfFreeSeats automatically updated. In particular, if a flight becomes full well before its departure date, the airline company would like to be able to change the airplane for a bigger one. Your job is to create a trigger that automatically update numberOfSeats when this happens.

Exercise 2: At the restaurant

In this exercise, we are creating the following database for a restaurant:

Tables(_number_, seats)
Bookings(_name_, _time_, nbpeople, table)
  table → Tables.number

For the sake of simplicity, we assume that the database only needs to hold bookings for the current day and that bookings are always done on the hour (i.e. time is an integer between 0 and 23).

Finally, tables are always booked for two hours. This means that if table 1 is booked at 19.00, it can’t be booked at 20.00 but it can be booked again at 21.00.

Question 1 Write a view that lists the times at which tables are blocked by a booking. In the example above, where table 1 is booked at 19.00, the view should contain the following rows:

table time
1 18
1 19
1 20

Question 2 Write a trigger on the table Bookings that automatically assign a table to new rows if none is specified. The assigned table should respect the following rules:

  • it should be free for the duration of the booking.
  • it should be big enough for the number of people in the party
  • it should be the smallest possible table to accommodate this number of people

Exercise 3: Wiki

In this exercise, we are creating a database for a wiki. A wiki is a website that allows collaborative modification of its content and structure directly from the web browser (Wikipedia).

To make collaborative edition easier, we needs to keep an history of the modifications of each page. To achieve this, we will use a simple model that simply keeps each version of each page as a separate row:

PageRevision(_name_, _date_, author, text)

Question 1 To make it easier to access the wiki, creat a view Page(name, last_author, text) that shows only the latest version of each page.

Question 2 Create a trigger on your newly created view so that when a user tries to update a given page, a new revision is created instead.

Sometimes, pages on the wiki needs to be completely deleted (for instance, if a page contains sensitive information or copyrighted content). In that case, we want to remove all revisions of the page from the database but we still want to remember that the page has existed but has been deleted. To this end, we add the following table to our database:

DeleteLog(_pagename_, _date_)

Question 3 Write a trigger on the Page view such that when a page is deleted:

  • all its revisions are removed from the database
  • the deletion is recorded in the DeleteLog.

Solutions

Material used during the tutorial: slides, handout, database setup (exercise 1), database setup (exercise 2), database setup (exercise 3).

Exercise 1

Question 1
CREATE OR REPLACE VIEW Itineraries AS
  SELECT
    reference, passenger, flight, date,
    departure.city as departure, destination.city as destination
  FROM
    Bookings
    JOIN Flights ON Bookings.flight = Flights.code
    JOIN Airports AS Departure ON departureAirport = Departure.code
    JOIN Airports AS Destination ON destinationAirport = Destination.code;
Question 2
CREATE OR REPLACE FUNCTION insert_itinerary() RETURNS TRIGGER AS $$
DECLARE
  ticket_price INTEGER;
  new_reference INTEGER;
BEGIN
  -- First, check that there are at least one seat left on the flight
  IF NOT 0 < (SELECT numberOfFreeSeats FROM AvailableFlights WHERE flight = NEW.flight)
    THEN RAISE EXCEPTION 'Flight fully booked';
  END IF;

  ticket_price := (SELECT price FROM AvailableFlights WHERE flight = NEW.flight);
  new_reference := (
    SELECT COALESCE(MAX(reference), 0) + 1
    FROM Bookings
    WHERE flight = new.flight
  );

  INSERT INTO Bookings(reference, flight, date, passenger, price)
    VALUES (new_reference, NEW.flight, NEW.date, NEW.passenger, ticket_price);

  UPDATE AvailableFlights
  SET price = price + 1
  WHERE flight = new.flight;

  RETURN NEW;
END
$$ LANGUAGE 'plpgsql';

DROP TRIGGER IF EXISTS InsertItinerary ON Itineraries;

CREATE TRIGGER InsertItinerary INSTEAD OF INSERT ON Itineraries
  FOR EACH ROW EXECUTE PROCEDURE insert_itinerary();
Question 3
CREATE OR REPLACE FUNCTION update_plane() RETURNS TRIGGER AS $$
DECLARE
  size_difference INTEGER;
BEGIN
  size_difference :=
    (SELECT capacity FROM Planes WHERE regnr=new.plane)
    - (SELECT capacity FROM Planes WHERE regnr = old.plane);

  if (new.numberOfFreeSeats + size_difference < 0) THEN
    RAISE EXCEPTION 'Plane too small!';
  ELSE
    new.numberOfFreeSeats := new.numberOfFreeSeats + size_difference;
  END IF;

  RETURN new;
END
$$ LANGUAGE 'plpgsql';

DROP TRIGGER IF EXISTS UpdatePlane ON AvailableFlights;

CREATE TRIGGER UpdatePlane BEFORE UPDATE ON AvailableFlights
  FOR EACH ROW
  WHEN (new.plane <> old.plane)
  EXECUTE PROCEDURE update_plane();

Exercise 2

Question 1
CREATE OR REPLACE VIEW BlockedTables AS
SELECT tablenum, time - 1 FROM Bookings WHERE time > 0
UNION
SELECT tablenum, time FROM Bookings
UNION
SELECT tablenum, time + 1 FROM Bookings WHERE time < 23;
Question 2
CREATE OR REPLACE FUNCTION assign_table() RETURNS TRIGGER AS $$
BEGIN

  NEW.tablenum :=(
    WITH possible_tables AS (
      SELECT number, seats
      FROM tables
      WHERE (number, new.time) NOT IN (SELECT * FROM BlockedTables)
        AND seats >= new.nbpeople
    )
    SELECT MIN(number)
    FROM possible_tables
    WHERE seats = (SELECT MIN(seats) FROM possible_tables));

    IF (NEW.tablenum IS NULL) THEN
      RAISE EXCEPTION 'No table available';
    END IF;

  RETURN NEW;
END
$$ LANGUAGE 'plpgsql';

DROP TRIGGER IF EXISTS AssignTable ON Bookings;

CREATE TRIGGER AssignTable BEFORE INSERT ON Bookings
  FOR EACH ROW
  WHEN (NEW.tablenum IS NULL)
  EXECUTE PROCEDURE assign_table();

Exercise 3

Question 1
CREATE OR REPLACE VIEW Pages AS
SELECT name, PR1.author AS last_author, PR1.text
FROM  PageRevisions AS PR1 JOIN PageRevisions AS PR2 USING (name)
GROUP BY name, PR1.date
HAVING pr1.date = MAX(pr2.date) ;
Question 2
-- ~~~ Question 2 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE OR REPLACE FUNCTION insert_revision() RETURNS TRIGGER AS $$
BEGIN
  Insert into pageRevisions(name, date, author, text)
  values (new.name, now(), new.last_author, NEW.text);

  RETURN NEW;
END
$$ LANGUAGE 'plpgsql';

DROP TRIGGER IF EXISTS EditPage ON Pages;

CREATE TRIGGER EditPage INSTEAD OF INSERT OR UPDATE ON Pages
FOR EACH ROW
EXECUTE PROCEDURE insert_revision();
Question 3
CREATE OR REPLACE FUNCTION delete_page() RETURNS TRIGGER AS $$
BEGIN
  DELETE FROM PageRevisions WHERE name = OLD.name;
  INSERT INTO deletelog VALUES (old.name, NOW());

  RETURN OLD;
END
$$ LANGUAGE 'plpgsql';

DROP TRIGGER IF EXISTS DeletePage ON Pages;

CREATE TRIGGER DeletePage INSTEAD OF DELETE ON Pages
FOR EACH ROW
EXECUTE PROCEDURE delete_page();