COMP3311 Final Exam 21T3

The University of New South Wales COMP3311 Database Systems Final Exam 21T3

Database Systems

Question 9 (7 marks)

Consider the following (partial) flight booking schema:

Flights(flight_no, aircraft, nseats, nbooked, departure, ...)
Bookings(flight_no, passenger, seat_no)

The nbooked attribute is maintained by the triggers below and aims to record the total number of seats booked on the flight. Each booking is for one passenger in a particular seat. Bookings cannot be updated, only insertdeleted.

Now consider the triggers that maintain nbooked and do other checks.

create trigger pre_booking before insert or delete on Bookings
for each row execute procedure pre_booking_check();

create function pre_booking_check() returns trigger
as $$
declare
   fno text;
   flight record;
begin
   if TG_OP = 'INSERT' then
      fno := new.flight_no
   else
      fno := old.flight_no
   end if;
   select * into flight from Flights where flight_no = fno;
   if not found then
      raise exception 'Booking error';
   end if;
   if TG_OP = 'DELETE' then
      return old;
   else
      if flight.nbooked = flight.nseats then
         raise exception 'Booking error';
      end if;
      return new;
   end if;
end;
$$ language plpgsql;

create trigger post_booking after insert or delete on Bookings
for each row execute procedure post_booking_update();

create function post_booking_update() returns trigger
as $$
declare
   fno text;
   flight record;
begin
   if TG_OP = 'INSERT' then
      update Flights set nbooked = nbooked+1 where flight_no = new.flight_no;
   else
      update Flights set nbooked = nbooked-1 where flight_no = old.flight_no;
   end if;
end;
$$ language plpgsql;

You should assume that the above code is syntactically correct.

1. Describe what conditions are being checked in pre_booking_check()?

2. Suggest what other conditions could be checked in pre_booking_check()?

3. Describe what happens when the following SQL statement is executed on flight QF01, where seat 25-D is currently empty

   insert into Bookings values ('QF01','John Smith','25-D');
   

4. Describe what happens when the following SQL statement is executed on a full flight QF02

   insert into Bookings values ('QF02','John Smith','25-D');
   

5. Describe what happens when the following SQL statement is executed on flight QF03, which already has a booking in seat 15-F

   delete from Bookings where flight_no = 'QF03' and seat_no = '15-F';
   

Instructions:

• Type your answers to this question into a file called q9.txt
• Submit via:   give cs3311 exam_q9 q9.txt  or  Webcms3