ATL (Sentimental)

Aerial view of Hartsfield-Jackson International Airport An aerial view of Hartsfield-Jackson International Airport, by Craig Butz

Problem to Solve

As you may remember, Hartsfield-Jackson International Airport—better known as “ATL” for its IATA airport code—has been the busiest airport in the world since 1998. ATL served 108 million passengers in 2024. Maybe you were one of them!

Previously, you wrote a schema in SQLite to help ATL design their database system. In reality, though, ATL would likely use a database management system that’s more fully featured. Enter MySQL!

Specification

Below, in Requirements, are some CREATE TABLE statements written for MySQL. In groups, critique each of the MySQL CREATE TABLE statements using what you know about MySQL’s features. Be prepared to share your critiques with a larger group when we come back from breakout rooms.

Requirements

Passengers

When it comes to our passengers, we just need to have the essentials in line: the first name, last name, and age. That’s all we need to know—nothing more.

CREATE TABLE `ATL_passengerFile` (
    `passenger_id` BIGINT AUTO_INCREMENT,
    `fName` VARCHAR(128) NOT NULL,
    `last_name` CHAR(64) NOT NULL
);

If you’re feeling like you want to learn more about these types, you can read about them in the MySQL documentation:

Check-Ins

When passengers arrive at ATL, they’ll often “check in” to their flights. That’s them telling us they’re here and all set to board. We’d like to keep a tidy log of such moments. And what would we need to log, you ask? Well, here’s what we need:

  • The exact date and time at which our passenger checked in
  • The flight they are checking in for, of course. Can’t lose track of where they’re headed, now can we?
CREATE TABLE `Checkins` (
    `checkin` SMALLINT,
    `flight_id` VARCHAR(15) DEFAULT '',
    `passenger_id` CHAR(6),
    `checkin_date` DATE NOT NULL,
    `checkin_clock` TIME NOT NULL,
    PRIMARY KEY (`checkin`)
);

If you’re feeling like you want to learn more about these types, you can read about them in the MySQL documentation:

Flights

We serve as many as 1,000 flights daily. To ensure that our passengers are never left wondering, we need to give them all the critical details about their flight. Here’s what we’d like to store:

  • The flight number. For example, “900”. Just know that we sometimes re-use flight numbers.
  • The airline operating the flight. You can keep it simple and assume one flight is operated by one airline.
  • The code of the airport they’re departing from. For example, “ATL” or “BOS”.
  • The code of the airport they’re heading to
  • The expected departure date and time (to the minute, of course!)
  • The expected arrival date and time, to the very same accuracy
CREATE TABLE `flights` (
    `flight_id` INT UNSIGNED AUTO_INCREMENT,
    `flightNo` INT UNSIGNED AUTO_INCREMENT,
    `airline` ENUM('American Airlines', 'Delta', 'JetBlue', 'Southwest', 'United'),
    `orig_airport` CHAR(5) DEFAULT '',
    `dest_airport` CHAR(5) DEFAULT '',
    `dep_sched_dt` TIMESTAMP,
    `arr_sched_dt` TIMESTAMP,
    PRIMARY KEY (`flightNo`)
);

If you’re feeling like you want to learn more about these types, you can read about them in the MySQL documentation:

Usage

To use mysql, you first need to start a MySQL server, as with:

docker container run --name mysql -p 3306:3306 -v /workspaces/$RepositoryName:/mnt -e MYSQL_ROOT_PASSWORD=crimson -d mysql

You can then connect to the server with:

mysql -h 127.0.0.1 -P 3306 -u root -p

Type crimson as your password.

If this is your first time logging into your mysql server, you’ll need to create a new database on the server. At your mysql> prompt, try the following:

CREATE DATABASE `atl`;

You should see something along the lines of “Query OK, 1 row affected.” Afterwards, to ensure your future SQL statements are run on your new atl database, execute the following:

USE `atl`;

You can always type quit to close your connection to the MySQL database.