The goal of this lab is to give you some practice with constraints in SQL. We will modify and extend the airline flight bookings database example.
Primary Keys
Our original example was quite limited. Tables had primary key indicators, and there was no coordination between tables. We will fix that now. Take the Aircraft table as an example:
CREATE TABLE Aircraft (aircraft TEXT, maker TEXT, seats INTEGER, built INTEGER, service TEXT);
First of all, to make it easier to see what is going on we will format the command onto several lines, one per column. That way we can easily add descriptors and qualifiers. The first thing we will do is identify a key. The aircraft column is intended as a unique id, so this should be the key.
CREATE TABLE Aircraft (
aircraft TEXT PRIMARY KEY,
maker TEXT,
seats INTEGER,
built INTEGER,
lastService TEXT
);
This particular key is has text values, so they must be specified by hand. If it was an integer key, we could also specify AUTOINCREMENT to let the system keep track of numbering and ensuring uniqueness.
To Do: Copy all the table creation commands into a text file and format them as shown above. Then modify the commands to identify primary keys in each of the other tables. (For the Seats table you will need a slightly different syntax because it has a joint key that includes several columns.) Test all your commands in SQLite to make sure they work.
References
Now that we have keys in each table, we need to address the connections between tables. Flights and Seats reference the primary key in Aircraft. Bookings references a key from Flights, and so on. We can indicate these links with a REFERENCES constraint. Here's an example showing one line from the Flights table creation command
aircraft TEXT REFERENCES Aircraft(aircraft),
With this constraint in place, we ensure that a flight cannot be created using a nonexistent aircraft. (Although you might imagine that this is designed to stop hackers, the most likely scenario is a simple typo.)
To Do: Modify the table creation commands to identify all the linkages between tables. Test all your commands in SQLite to make sure they work. (SQLite has liminted support for adding constraints to exsiting tables, so you may need to delete the tables created for the previous section before re-adding them with your new changes.)
Value Checks
SQLite supports only a handful of data types. For example, all string values are designated as TEXT. We can and should enforce more stringent checking on columns whose values should be more constrained. In the Seats table, the seat field should be a capital letter between A and J. We can state this with a CHECK constraint.
seat TEXT CONSTRAINT SeatIsLetter CHECK (seat IN ('A','B','C','D','E','F','G','H','I','J')),
The reason for specifying a name for this constraint is so that it can be modified later -- for example, if newer wide-body planes are purchased that have more than ten seats per row. The check constraint can test a wide range of conditions. In most SQL variants you can even perform sub-queries, but this is not supported by SQLite. However, we can do things like testing the formatting of a field. Airport names are three-letter abbreviations, and we can indicate that in the Flights table.
origin TEXT CONSTRAINT OriginIsAIA CHECK (origin LIKE '___'),
destination TEXT CONSTRAINT DestIsAIA CHECK (dest LIKE '___'),
To Do: Modify the table creation commands to make the following changes:
- In the Seats table, constrain the values of the category and class columns.
- Departure and arrival time of flights is a four-digit string.
- Notice that in our starter file, different date fields use different formats. Modify the constraints so that all dates in the database are specified in YYYY/MM/DD format.
Triggers
Triggers are a very powerful way to enforce arbitrary restrictions and make updates across tables. They create a rule that must be checked every time a table that they mention is changed. For this reason, they should only be used when a simpler approach will not work.
As an example, we might want to prevent the creation of bookings for seats not listed in the Seats database. This is a complicated task, since it also requires looking up the aircraft id from the tt>Flights table in order to find the correct rows of Seats. Here's an example trigger rule that does what we want. (The syntax here is designed for SQLite, and differs a bit from standard MySQL. Also note that most of the tuples we added to the table would fail if this rule were already in place.)
CREATE TRIGGER SeatInsertedMustExist
BEFORE INSERT ON Bookings
BEGIN
SELECT
CASE
WHEN NOT EXISTS (SELECT seat AS s, rowNum AS r, flight AS f FROM Seats JOIN Flights ON Seats.aircraft=Flights.aircraft WHERE r= NEW.rowNum AND s=NEW.seat AND f=NEW.flight) THEN
RAISE(ABORT, 'Seat does not exist')
END;
END;
Test out this rule b adding a few more bookings, at least one for an existing seat and another for an imaginary seat. Make sure that only the correct entries are accepted.
To Do: Add a new table to the database called Maintenance using the command below. This will hold service records. Once this is in place, create a new trigger rule that will run after a maintenance record is inserted, and update the lastService record in Aircraft. (You will want to use the MAX function to find the most recent service date in the table for a given aircraft. This will work as expected because we already enforce the YYYY/MM/DD format.)
CREATE TABLE Maintenance (
aircraft TEXT REFERENCES Aircraft(aircraft),
performedBy TEXT,
serviceDate TEXT CONSTRAINT LastServiceIsYYYYMMDD CHECK (serviceDate LIKE '____/__/__'),
PRIMARY KEY (aircraft, serviceDate)
);
Test your trigger by adding a few maintenance records and checking that the service dates are updating as expected.