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¶
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;
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();
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¶
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;
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¶
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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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();
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();