The following example illustrates 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 a particular 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 entire domain of 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 these values are substituted into the predicate framework, they 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, might 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 |
Several obvious 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.
- Departure times must be greater than arrival times if they occur on the same date.
- Arrival flight numbers and departure flight numbers cannot be equal if they occur on the same date.