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

Last Updated on April 17th, 2012 by Andy Pavlo