Projects > The SEATS Airline Ticketing Systems Benchmark

  1. Introduction

    To be written…

  2. Tables

    The benchmark scales with an integer scaling parameter N. It can be set to any value of interest, though we have in mind a number between 1 and 100.

    1. COUNTRY

      Read-only table of countries.

      PRIMARY KEY (CO_ID)

      Column Type Cardinality References Description
      CO_ID BIGINT
      CO_NAME VARCHAR(64)
      CO_CODE_2 VARCHAR(2) UNIQUE
      CO_CODE_3 VARCHAR(3) UNIQUE
    2. AIRPORT

      Read-only table of airports.

      PRIMARY KEY (AP_ID)

      Column Type Cardinality References Description
      AP_ID BIGINT
      AP_CODE VARCHAR(3) UNIQUE
      AP_NAME VARCHAR(128)
      AP_CITY VARCHAR(64)
      AP_POSTAL_CODE VARCHAR(12)
      AP_CO_ID BIGINT COUNTRY (CO_ID)
      AP_LONGITUDE FLOAT
      AP_LATITUDE FLOAT
      AP_GMT_OFFSET FLOAT
      AP_WAC BIGINT
      AP_IATTR00 BIGINT
      AP_IATTR01 BIGINT
      AP_IATTR02 BIGINT
      AP_IATTR03 BIGINT
      AP_IATTR04 BIGINT
      AP_IATTR05 BIGINT
      AP_IATTR06 BIGINT
      AP_IATTR07 BIGINT
      AP_IATTR08 BIGINT
      AP_IATTR09 BIGINT
      AP_IATTR10 BIGINT
      AP_IATTR11 BIGINT
      AP_IATTR12 BIGINT
      AP_IATTR13 BIGINT
      AP_IATTR14 BIGINT
      AP_IATTR15 BIGINT
    3. AIRPORT_DISTANCE

      Read-only table of the distance (in miles) from each airport to all other airports.

      PRIMARY KEY (D_AP_ID0, D_AP_ID1)

      Column Type Cardinality References Description
      D_AP_ID0 BIGINT AIRPORT (AP_ID)
      D_AP_ID1 BIGINT AIRPORT (AP_ID)
      D_DISTANCE FLOAT
    4. AIRLINE

      Read-only table of airlines.

      PRIMARY KEY (AL_ID)

      Column Type Cardinality References Description
      AL_ID BIGINT
      AL_IATA_CODE VARCHAR(3) UNIQUE
      AL_ICAO_CODE VARCHAR(3) UNIQUE
      AL_CALL_SIGN VARCHAR(32)
      AL_NAME VARCHAR(128)
      AL_CO_ID BIGINT COUNTRY (CO_ID)
      AL_IATTR00 BIGINT
      AL_IATTR01 BIGINT
      AL_IATTR02 BIGINT
      AL_IATTR03 BIGINT
      AL_IATTR04 BIGINT
      AL_IATTR05 BIGINT
      AL_IATTR06 BIGINT
      AL_IATTR07 BIGINT
      AL_IATTR08 BIGINT
      AL_IATTR09 BIGINT
      AL_IATTR10 BIGINT
      AL_IATTR11 BIGINT
      AL_IATTR12 BIGINT
      AL_IATTR13 BIGINT
      AL_IATTR14 BIGINT
      AL_IATTR15 BIGINT
    5. CUSTOMER

      A Customer table. There are N *10 **6 customers, each with an integer customer identifier (CID) and 40 fields of attribute data, e.g. name, address frequent flier number, etc.. Twenty are character strings with a length of 8 bytes and 20 are four byte integers. Hence, total record length is 244 bytes. For N = 1 this is 244 Mbytes. For larger N, do the math. CIDs can be assumed to be positive integers between 1 and N * 10 **6.

      PRIMARY KEY (C_ID)

      Column Type Cardinality References Description
      C_ID BIGINT
      C_BASE_AP_ID BIGINT AIRPORT (AP_ID)
      C_SATTR00 VARCHAR(8)
      C_SATTR01 VARCHAR(8)
      C_SATTR02 VARCHAR(8)
      C_SATTR03 VARCHAR(8)
      C_SATTR04 VARCHAR(8)
      C_SATTR05 VARCHAR(8)
      C_SATTR06 VARCHAR(8)
      C_SATTR07 VARCHAR(8)
      C_SATTR08 VARCHAR(8)
      C_SATTR09 VARCHAR(8)
      C_SATTR10 VARCHAR(8)
      C_SATTR11 VARCHAR(8)
      C_SATTR12 VARCHAR(8)
      C_SATTR13 VARCHAR(8)
      C_SATTR14 VARCHAR(8)
      C_SATTR15 VARCHAR(8)
      C_SATTR16 VARCHAR(8)
      C_SATTR17 VARCHAR(8)
      C_SATTR18 VARCHAR(8)
      C_SATTR19 VARCHAR(8)
      C_IATTR00 BIGINT
      C_IATTR01 BIGINT
      C_IATTR02 BIGINT
      C_IATTR03 BIGINT
      C_IATTR04 BIGINT
      C_IATTR05 BIGINT
      C_IATTR06 BIGINT
      C_IATTR07 BIGINT
      C_IATTR08 BIGINT
      C_IATTR09 BIGINT
      C_IATTR10 BIGINT
      C_IATTR11 BIGINT
      C_IATTR12 BIGINT
      C_IATTR13 BIGINT
      C_IATTR14 BIGINT
      C_IATTR15 BIGINT
      C_IATTR16 BIGINT
      C_IATTR17 BIGINT
      C_IATTR18 BIGINT
      C_IATTR19 BIGINT
    6. FREQUENT_FLYER

      Customer frequent flyer information per airline.

      PRIMARY KEY (FF_C_ID, FF_AL_ID)

      Column Type Cardinality References Description
      FF_C_ID BIGINT CUSTOMER (C_ID)
      FF_AL_ID BIGINT AIRLINE (AL_ID)
      FF_IATTR00 BIGINT
      FF_IATTR01 BIGINT
      FF_IATTR02 BIGINT
      FF_IATTR03 BIGINT
      FF_IATTR04 BIGINT
      FF_IATTR05 BIGINT
      FF_IATTR06 BIGINT
      FF_IATTR07 BIGINT
      FF_IATTR08 BIGINT
      FF_IATTR09 BIGINT
      FF_IATTR10 BIGINT
      FF_IATTR11 BIGINT
      FF_IATTR12 BIGINT
      FF_IATTR13 BIGINT
      FF_IATTR14 BIGINT
      FF_IATTR15 BIGINT
    7. FLIGHT

      A Flight table. There are N * 10 **5 flights. Each flight has a flight identifier (F_ID), an airline (AL_ID), departure airport (DEPART_AP_ID), departure time, arrival airport (ARRIVE_AP_ID), arrival time, and 30 misscellaneous data attributes. Each attribute is an integer. Total record length is XXX bytes.

      Each F_ID is a 64-bit composite ID containing departure/arrival airport ids, the flight date, and a unique identifier. This allows the benchmark driver to execute various transactions

      PRIMARY KEY (F_ID)

      Column Type Cardinality References Description
      F_ID BIGINT
      F_AL_ID BIGINT AIRLINE (AL_ID)
      F_DEPART_AP_ID BIGINT AIRPORT (AP_ID)
      F_DEPART_TIME TIMESTAMP
      F_ARRIVE_AP_ID BIGINT AIRPORT (AP_ID)
      F_ARRIVE_TIME TIMESTAMP
      F_STATUS BIGINT
      F_SEATS_LEFT BIGINT
      F_IATTR00 BIGINT
      F_IATTR01 BIGINT
      F_IATTR02 BIGINT
      F_IATTR03 BIGINT
      F_IATTR04 BIGINT
      F_IATTR05 BIGINT
      F_IATTR06 BIGINT
      F_IATTR07 BIGINT
      F_IATTR08 BIGINT
      F_IATTR09 BIGINT
      F_IATTR10 BIGINT
      F_IATTR11 BIGINT
      F_IATTR12 BIGINT
      F_IATTR13 BIGINT
      F_IATTR14 BIGINT
      F_IATTR15 BIGINT
      F_IATTR16 BIGINT
      F_IATTR17 BIGINT
      F_IATTR18 BIGINT
      F_IATTR19 BIGINT
      F_IATTR20 BIGINT
      F_IATTR21 BIGINT
      F_IATTR22 BIGINT
      F_IATTR23 BIGINT
      F_IATTR24 BIGINT
      F_IATTR25 BIGINT
      F_IATTR26 BIGINT
      F_IATTR27 BIGINT
      F_IATTR28 BIGINT
      F_IATTR29 BIGINT
    8. RESERVATION

      A Reservation table. Each reservation applies to a single customer and a single flight, each randomly chosen. A reservation has 10 data fields, each an integer (for example the cost of the flight and the seat chosen, if any). RIDs can also be sequence numbers.

      There are sufficient reservations loaded at the start of the benchmark, so that every flight is between 60 and 100% full, with an average of 80%. Every plane is assumed to have 150 seats. Hence, there are 12M reservations. Total size of the reservation table is 624 Mbytes for N = 1. As the number of flights scales with N, so will Reservations.

      PRIMARY KEY (R_ID, R_C_ID)

      Column Type Cardinality References Description
      R_ID BIGINT
      R_C_ID BIGINT CUSTOMER (C_ID)
      R_F_ID BIGINT FLIGHT (F_ID)
      R_SEAT BIGINT
      R_IATTR00 BIGINT
      R_IATTR01 BIGINT
      R_IATTR02 BIGINT
      R_IATTR03 BIGINT
      R_IATTR04 BIGINT
      R_IATTR05 BIGINT
      R_IATTR06 BIGINT
      R_IATTR07 BIGINT
      R_IATTR08 BIGINT
  3. Stored Procedures

    1. ChangeSeat

      Source Code

    2. FindFlightByAirport

      Source Code

    3. FindFlightByNearbyAirport

      Source Code

    4. FindOpenSeats

      Source Code

    5. NewReservation

      Source Code

    6. UpdateFrequentFlyer

      Source Code

    7. UpdateReservation

      Source Code