NLIDB Home Description Corpus Demo Bibliography
 

 

 

Description of the ATIS Database

 

ATIS (Air Travel Information Service) is a relational database (DB) for storing information on flights, and it is used as benchmark for understanding spoken and written language. This document presents a diagram of the DB schema and details of the DB tables.

 

 Schema 

The ATIS database schema describes the DB tables and the links (relations) between tables (Figure 1). The tables most used in queries to the database are: flight, fare, airline, airport, compound_class, and transport.

Figure 1. ATIS database schema

 

 

Table and Column Descriptions

In order to provide an adequate understanding of the ATIS database contents, this document presents information on the contents of each DB table and column. It is important to mention that the descriptions for tables and columns are constituted by a word or syntagma (phrase) of the following syntactic categories: noun, verb, adjective and preposition.

Next, the descriptions of the DB columns of the ATIS tables are presented, as well as the description of each DB table, which is shown at the top of each table. For each DB column, the first table column contains the name of the DB column, the second column indicates the data type of the DB column, and the third column contains the descriptor for the DB column (both: in English and Spanish). It is important to make clear that the NLIDB of this demo has been developed for answering queries in Spanish. Therefore, the Spanish descriptors were used for the automatic customization of the NLIDB. Notes: There exist different slightly different versions of the ATIS database. The following description of the database includes two tables (month_name and time_interval) that are not defined in some DB versions; additionally, some necessary primary and foreign keys, which are not defined in some versions, are included in this description.

Table: aircraft            Description: Aircraft (Avión)

Column

Type

Description

aircraft_code

Text

Aircraft code (Código de avión)

aircraft_type

Text

Aircraft type (Tipo de avión)

engines

Numeric

Number of engines (Número de motores)

category

Text

Category (Categoría)

wide_body

Text

Fuselage width (Tamaño del fuselaje)

wing_span

Numeric

Wingspan (Extensión de alas)

length1

Numeric

Length (Longitud)

weight

Numeric

Weight (Peso)

capacity

Numeric

Capacity (Capacidad)

pay_load

Numeric

Payload (Carga)

cruising_speed

Numeric

Cruising speed (Velocidad de crucero)

range_miles

Numeric

Range miles (Alcance de vuelo)

pressurized

Text

Pressurized (Presurización)

PRIMARY KEY:     aircraft_code

 

Table: airline             Description: Airline (Aerolínea)

Column

Type

Description|

airline_code

Text

Airline code (Código de aerolínea)

airline_name

Text

Airline name (Nombre de aerolínea)

notes

Text

Notes (Notas)

PRIMARY KEY:     airline_code

FOREIGN KEY:      airline (airline_code) – restrict_carrier (airline_code)

 

Table: airport            Description: Airport (Aeropuerto)

Column

Type

Description

airport_code

Text

Airport code (Código de aeropuerto)

airport_name

Text

Airport name (Nombre de aeropuerto)

location

Text

Location (Ubicación)

state_code

Text

State code (Código de estado)

time_zone_code

Text

Time zone code (Código de zona horaria)

PRIMARY KEY:     airport_code

FOREIGN KEY:      airport (state_code) – state (state_code)

                                   airport (time_zone_code) – time_zone (time_zone_code)

 

Table: airport_service           Description: Airport service (Servicio de aeropuerto)

Column

Type

Description

city_code

Text

City code (Código de ciudad)

airport_code

Text

Airport code (Código de aeropuerto)

miles_distant

Numeric

Miles distant (Distancia en millas)

direction

Text

Airport direction (Dirección)

minutes_distant

Numeric

Minutes distant (Distancia en minutos)

PRIMARY KEY:     city_code, airpot_code

FOREIGN KEY:      airport_service (airport_code) – airport (airport_code)

                                   airport_service (city_code) – city (city_code)

                                   airport_service(airport_code) – ground_service (airport_code)

                                   airport_service (city_code) – ground_service (city_code)

 

Table: city      Description: City (Ciudad)

Column

Type

Description

city_code

Text

City code (Código de ciudad)

city_name

Text

City name (Nombre de ciudad)

state_code

Text

State code (Código de estado)

time_zone_code

Text

Time zone code (Código de zona horaria)

PRIMARY KEY:     city_code

FOREIGN KEY:      city (state_code) – state (state_code)

                                   city (time_zone_code) – time_zone (time_zone_code)

 

Table: class_of_service         Description: Class of service (Clase de servicio)

Column

Type

Description

class_code

Text

Class code (Código de clase de servicio)

rank

Numeric

Rank (Rango)

class_description

Text

Class description  (Descripción de clase de servicio)

PRIMARY KEY:     class_code

 

Table: code_description        Description: Code description (Descripción de código)

Column

Type

Description

code

Text

Código (Código)

description

Text

Code description (Descripción de código)

PRIMARY KEY:     code

 

Table: compound_class         Description: Compound class (Clase compuesta)

Column

Type

Description

fare_class

Text

Fare class (Clase de tarifa)

base_class

Text

Base class (Clase base)

class_type

Text

Class type (Tipo de clase)

premium

Text

Premium class (Primera clase)

economy

Text

Economy class (Clase económica)

discounted

Text

Discounted (Descuento)

night

Text

Night flight (Vuelo nocturno)

season_fare

Text

Season fare (Tarifa de temporada)

class_days

Text

Days (Días)

PRIMARY KEY:     fare_class

FOREIGN KEY:      compound_class (base_class) – class_of_service (class_code)

 

Table: connect_leg                Description: Connect leg (Segmento de conexión)

Column

Type

Description

connect_code

Numeric

Connect code (Código de conexión)

leg_number

Numeric

Leg number (Número de segmento)

flight_code

Numeric

Flight code (Código de vuelo)

PRIMARY KEY:     connect_code, connect_leg.flight_code

FOREIGN KEY:      connect_leg (connect_code) – fconnection (connect_code)

                                   connect_leg (flight_code) – flight (flight_code)

 

Table: day_name       Description: Days (Día)

Columna

Type

Description

day_code

Numeric

Day code (Código de día)

day_name

Text

Day name (Nombre de día)

PRIMARY KEY:     day_code

 

Table: dual_carrier               Description: Dual carrier (Compañía dual)

Column

Type

Description

main_airline

Text

Main airline code (Código de aerolínea principal)

dual_airline

Text

Dual airline code (Código de compañía dual)

low_flight

Numeric

Low flight (Vuelo inferior)

high_flight

Numeric

High flight (Vuelo superior)

fconnection_name

Text

Connection name (Nombre de conexión)

PRIMARY KEY:     main_airline, dual_airline, low_flight

FOREIGN KEY:      dual_carrier (dual_airline) – airline (airline_code)

                                   dual_carrier (main_airline) – airline (airline_code)

 

Table: fare     Description: Fare (Tarifa)

Column

Type

Description

fare_code

Text

Fare code (Código de tarifa)

from_airport

Text

Origin airport (Aeropuerto de origen)

to_airport

Text

Destination airport (Aeropuerto de destino)

fare_class

Text

Fare class (Clase de tarifa)

fare_airline

Text

Airline code (Código de aerolínea)

restrict_code

Text

Restrict code (Código de restricción)

one_way_cost

Numeric

One-way fare (Tarifa de vuelo sencillo)

rnd_trip_cost

Numeric

Round-trip fare (Tarifa de vuelo redondo)

PRIMARY KEY:     fare_code

            FOREIGN KEY:      fare (restrict_code) – restriction (restrict_code)

                                               fare (fare_class) – compound_class (fare_class)

 

Table: fconnection                 Description: Connection (Conexión)

Column

Type

Description

connect_code

Numeric

Connect code (Código de conexión)

from_airport

Text

Origin airport (Aeropuerto de origen)

to_airport

Text

Destination airport (Aeropuerto de destino)

departure_time

Numeric

Departure time (Hora de salida)

arrival_time

Numeric

Arrival time (Hora de llegada)

flight_days

Text

Days (Días de vuelo)

stops

Numeric

Stops (Escalas)

connections

Numeric

Connections (Conexiones)

PRIMARY KEY:     connect_code

FOREIGN KEY:      fconnection (to_airport) – airport (airport_code)

                                   fconnection (from_airport) –             airport (airport_code)

 

Table: flight       Description: Flight (Vuelo)

Column

Type

Description

flight_code

Numeric

Flight code (Código de vuelo)

flight_days

Text

Flight days (Días de vuelo)

from_airport

Text

Origin airport (Aeropuerto de origen)

to_airport

Text

Destination airport (Aeropuerto de destino)

departure_time

Numeric

Departure time (Hora de salida)

arrrival_time

Numeric

Arrival time (Hora de llegada)

airline_code

Text

Airline code (Código de aerolínea)

flight_number

Numeric

Flight number (Número de vuelo)

class_string

Text

Class string (Clase de código)

aircraft_code

Text

Aircraft code (Código de avión)

meal_code

Text

Meal code (Código de comida)

stops

Numeric

Stops (Escalas)

dual_carrier

Text

Dual carrier (Empresa dual)

time_elapsed

Numeric

Flight time (Tiempo de vuelo)

PRIMARY KEY:     flight_code

FOREIGN KEY:      flight (aircraft_code) – aircraft (aircraft_code)

                                   flight (airline_code) – airline (airline_code)

                                   flight (from_airport) – airport (airport_code)

                                   flight (to_airport) – airport (airport_code)

INFORMAL LINK:            flight (flight_days) – flight_day (day_mask)

 

Table: flight_class      Description: Flight class (Clase de vuelo)

Column

Type

Description

flight_code

Numeric

Flight code (Código de vuelo)

fare_class

Text

Fare class (Clase de tarifa)

PRIMARY KEY:     flight_code, fare_class

FOREIGN KEY:      flight_class (flight_code) – flight (flight_code)

                                   flight_class (fare_class) – fare (fare_class)

 

Table: flight_day        Description: Flight days (Día de vuelo)

Column

Type

Description

day_mask

Text

Day mask (Máscara de día)

day_code

Numeric

Day code (Código de día)

PRIMARY KEY:     day_mask, day_code

FOREIGN KEY:      flight_day (day_code) – day_name (day_code)

 

Table: flight_fare       Description: Flight fare (Tarifa de vuelo)

Column

Type

Description

flight_code

Numeric

Flight code (Código de vuelo)

fare_code

Text

Fare code (Código de tarifa)

PRIMARY KEY:     flight_code, fare_code

FOREIGN KEY:      flight_fare (fare_code) – fare (fare_code)

                                   flight_fare (flight_code) – flight (flight_code)

 

Table: food_service               Description: Food service (Servicio de comida)

Column

Type

Description

meal_code

Text

Meal code (Código de comida)

meal_number

Numeric

Meal number (Número de comida)

meal_class

Text

Meal class (Clase de comida)

meal_description

Text

Meal description (Descripción de comida)

PRIMARY KEY:     meal_code, meal_number, meal_class

FOREIGN KEY:      food_service (meal_code) – flight (meal_code)

 

Table: ground_service           Description: Ground service (Transporte terrestre)

Column

Type

Description

city_code

Text

City code (Código de ciudad)

airport_code

Text

Airport code (Código de aeropuerto)

transport_code

Text

Transport code (Código de transporte)

ground_fare

Numeric

Ground fare (Tarifa terrestre)

PRIMARY KEY:     city_code, airport_code, transport_code

FOREIGN KEY:      ground_service (transport_code) – transport (transport_code)

 

Table: month_name   Description: Month name (Nombre de mes)

Column

Type

Description

month_number

Numeric

Month number (Número de mes)

month_name

Text

Month name (Nombre de mes)

PRIMARY KEY:     month_number

 

Table: restriction       Description: Restriction (Restricción)

Column

Type

Description

restrict_code

Text

Restrict code (Código de restricción)

application

Text

Application (Aplicación)

no_discounts

Text

No discounts (Sin descuento)

reserve_ticket

Numeric

Reserve ticket (Boleto en reserva)

stopovers

Text

Stopovers (Escalas)

return_min

Numeric

Minimum return (Rendimiento mínimo)

return_max

Numeric

Maximum return (Rendimiento máximo)

PRIMARY KEY:     restrict_code

 

Table: restrict_carrier           Description: Restric carrier (Restricción de aerolínea)

Column

Type

Description

restrict_code

Text

Restrict code (Código de restricción)

airline_code

Text

Airline code (Código de aerolínea)

PRIMARY KEY:     restrict_code, airline_code

FOREIGN KEY:      restrict_carrier (restrict_code) – restriction (restrict_code)

 

Table: restrict_class              Description: Restrict class (Clase de restricción)

Column

Type

Description

restrict_code

Text

Restrict code (Código de restricción)

ex_fare_class

Text

Fare class (Clase de tarifa)

PRIMARY KEY:     ex_fare_class

FOREIGN KEY:      restrict_class (restrict_code) – restriction (restrict_code)

 

Table: state                Description: State (Estado)

Column

Type

Description

state_code

Text

State code (Código de estado)

state_name

Text

State name (Nombre de estado)

country_name

Text

Country name (Nombre de país)

PRIMARY KEY:     state_code

 

Table: stop                 Description: Stop (Escala)

Column

Type

Description

flight_code

Numeric

Flight code (Código de vuelo)

stop_number

Numeric

Stop number (Número de escala)

stop_flight

Numeric

Stop flight (Escala de vuelo)

PRIMARY KEY:     flight_code, stop_number

FOREIGN KEY:      stop (flight_code) – flight (flight_code)

                                   stop (stop_flight) – flight (flight_code)

 

Table: time_interval   Description: Time interval (Intervalo de tiempo)

Column

Type

Description

period

Text

Period (Periodo)

begin_time

Numeric

Begin time (Hora inicial)

end_time

Numeric

End time (Hora final)

PRIMARY KEY:     period, begin_time

 

Table: time_zone                   Description: Time zone (Zona horaria)

Column

Type

Description

time_zone_code

Text

Time zone code (Código de zona horaria)

time_zone_name

Text

Time zone name (Nombre de zona horaria)

PRIMARY KEY:     time_zone_code

 

Table: transport         Description: Transport (Transporte)

Column

Type

Description

transport_code

Texto

Transport code (Código de transporte)

transport_desc

Texto

Transport description (Descripción de transporte)

PRIMARY KEY:     transport_code

 

 

 

 

 

Powered by Instituto Tecnológico de Ciudad Madero