Databases (HT2014)

Tutorial 4

Triggers and assertions

  1. A database system managing information about bids placed for items in an on-line auction has relation Bids(item,customer,amount), where item is a unique item identifier, customer is a unique customer identifier, and amount is the amount bid. All bids are recorded permanently (i.e. no rows in this relation are ever updated or deleted).

    Each bid made on an item must be at least as high as the previous bids made on that item. Write trigger code that enforces this constraint.

  2. A database system used by a hospital to record information about patients and wards has the following relations:

    Wards(wid, numBeds)
    PatientInWard(pid, ward)
      ward → Wards.wid

    Each ward is identified by a unique ward identifier, wid, and numBeds is the total num of beds in that ward (which may be occupied or unoccupied).

    1. The number of patients in a ward cannot exceed the number of beds in that ward. Write an assertion that checks this constraint.

    2. Create a view FreeBeds(ward, numBeds) where ward is a ward identifier, and numBeds is the number of available beds in that ward.

    3. If an attempt is made to insert a new row into relation PatientInWard, and that ward is already full, then the patient should instead be assigned to a ward that has an available bed. If there are several wards with available beds, then the patient should be assigned to the one with the lowest ward identifier. Write a trigger that implements this.

    4. Implement the constraint in part (a) using triggers.

    5. How should we handle the deletion of rows from the Wards relation?

Book exercises

The following exercises from "Database Systems: The Complete Book, 2/E" are relevant:

If you have the first edition of the textbook, the following exercises are similar:


Last Modified: 17 September 2014 by Graham Kemp