16.10 - How Relational Databases Are Built From Logical Propositions - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)

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
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.