The following example shows how a real database is built from logical propositions.
An Airline Reservation System
As a concrete example of the logical underpinnings of database constraints, consider an airline reservation. The following proposition can be stated about any reservation, and an instance of the proposition can be represented by a unique row in a table:
The reservation identified by reservation number (reservation_number), made for flight (arrival_flight_number), has a scheduled arrival date (arrival_date) and time (arrival_time) at gate (arrival_gate_number) and a scheduled departure flight of (depart_flight_number) with departure date (depart_date) and time (depart_time) from gate (depart_gate_number).
The variables enclosed within parentheses in this proposition are placeholders for the values they represent. When you substitute actual values for these placeholders, you produce a proposition about a specific airline reservation. For example, suppose you have the following set of values:
(88079, 317, 10/19/2001, 13:59, 60, 1138, 10/25/2001, 05:40, 82)
When substituted into the predicate framework, these values produce the following logical proposition:
The reservation identified by reservation number 88079, made for flight 317, has a scheduled arrival date 10/19/2001 and time 13:59 at gate 60 and has a scheduled departure flight of 1138 with departure date 10/25/2001 and time 05:40 from gate 82.
This proposition, which represents an instantiation of the table predicate, can be represented in a "flight_reservations" table by the following row.
| flight_reservations | ||||||||
|---|---|---|---|---|---|---|---|---|
| res_num | a_flt_num | a_date | a_time | a_gate | d_flt_num | d_date | d_time | d_gate |
| PK | ||||||||
| 88079 | 317 | 10/19/2001 | 13:59 | 60 | 1138 | 10/25/2001 | 05:40 | 82 |
Constraints can be developed for the columns and table supporting this proposition.
Stated as simple sentences, these are the following.
- Column constraints:
- Reservation numbers must be unique values drawn from a defined integer domain.
- Arrival and departure flight numbers must be unique values drawn from a defined integer domain.
- Arrival and departure dates must be unique values drawn from a defined date domain.
- Arrival and departure times must be unique values drawn from a defined time domain.
- Arrival and departure gate numbers must be unique values drawn from a defined integer domain.
- Table constraints, such as:
- Departure dates must be greater than or equal to arrival dates.
- If departure dates are equal to arrival dates, departure times must be greater than arrival times.
- Arrival flight numbers and departure flight numbers that occur on the same date cannot be equal.