CPSC/DATA 350 — Applications of Databases — Fall 2024

Assignment #2

Possible experience: +40XP

Due: Wednesday, Sept. 25, midnight

Creating an SQLite database

  1. Download SQLite3 for your platform, if you don't already have it. (Note to Linux users: you can instead install the version in your distro's repositories, a la "sudo apt-get install sqlite3," but you may not get the most recent version of SQLite3 with the latest features that way). Look on the Download tab for "Pre-compiled binaries" for your platform, and choose the option that begins "A bundle of command-line tools..." not just the DLL.
  2. Take your conceptual model from assignment #1, make any corrections I noted on your assignment (come see me if they aren't clear), and then convert it into a relational database schema. This should be a set of tables, each of which has a name, a set of attribute names and their domains, and one or more keys.
  3. Use SQLite to create a new database. The name of your database file should be "yourumwid_airline.db". (Please do not name your file anything else. Please do not omit the suffix, or change the capitalization, or add/remove underscores, or do any other clever or creative things. Please call it exactly yourumwid_airline.db, with your actual (lowercase) UMW Net ID substituted for "yourumwid". For instance, "jsmith19_airline.db" is a correct name.)

    The way you will accomplish this is to open a command line, change to the directory/folder you keep your class projects in, and type: 
    $ sqlite3 yourumwid_airline.db
    ("Command line" is a synonym for the terms "terminal" and "shell" in Linux/Mac, and for "PowerShell" in Windows.) This should open up an interface to your new SQLite database, accessible through the sqlite> prompt.

    Pro tip: instead of typing everything interactively at the sqlite> prompt, open an editor in which you type your commands and save them. (Editors include vim, Notepad, Notepad++, Spyder, IDLE, IntelliJ, NetBeans, and anything else that can write a plain text file). Save the file that has your commands in it with a ".sql" extension (just a suggestion), and put it in the same directory/folder as your yourumwid_airline.db file. Then, whenever you want to execute those commands in SQLite, type this at the SQLite prompt: 
    sqlite> .read yourCommandFile.sql
    (Yes, that's a dot (".") before the word "read".)

    Pro tip #2: you'll discover that if you try to create a new table with the same name as an existing table, it (understandably) won't work. So in your command file, it often makes sense to delete ("drop") the existing table, if any, right before creating your new one. In other words: 
    DROP TABLE IF EXISTS myTableName;
CREATE TABLE myTableName ( ...
  4. Before proceeding, run your eyeballs over the following list of facts which you will be inserting into your database. If any of them don't look like they would "fit" in your schema, you must adjust your schema appropriately so that they do "fit." The more your assignment #1 solution diverged from correctness, the harder this adjustment will be. Please send email with questions!

    Here are the facts, ma'am:
    1. The Boeing 737 is a mid-sized aircraft that seats 189 passengers, can fly up to 41,000 feet, and requires a runway of at least 4500 feet to take off and land.
    2. The Airbus 320 is a revolutionary model that seats 150 passengers, can fly up to 39,000 feet, and requires a runway of at least 4500 feet to take off and land.
    3. The Boeing 747 is a jumbo aircraft that can seat a whopping 524 passengers, can fly up to 48,000 feet, and requires a runway of at least 6,000 feet to take off and land.
    4. The Cessna 414 ("Chancellor") is a small, twin-engine plane that seats up to 8 passengers and can ascend to 30,800 feet. It requires a runway of a little under 2,000 feet.
    5. Pilot Bob is certified to fly both the 747 and the Airbus.
    6. Pilot Suzie has been qualified to fly both Boeing aircraft, as well as the Cessna.
    7. Pilot Stan is exclusively a Boeing 737 pilot.
    8. Ken is an aspiring pilot, who is busy studying for his piloting exams and thus has not as of yet been certified on anything. He has ambitions though.
    9. Pilot Phyllis recently joined the United team, bringing with her years of experience flying the Airbus 320, the Boeing 747, and the Boeing 737.
    10. Reagan National (DCA) is a small regional airport with three runways, one of which (the one running due north) can be used for ILS approaches. The headings and lengths are N (6869 feet), SE (5204 feet), and NE (4911 feet.)
    11. Denver International Airport (DEN) has six operational runways, all of them instrument equipped. One is 16,000 feet long at heading SSE; the others are each 12,000 feet long, and are at headings NEE, E, SE, S, and SEE.
    12. Cleveland-Hopkins International Airport (CLE) has four runways, bearing NE (9,000 feet in length), NEE (8,999 feet), NNE (7,096 feet), and SEE (6,017 feet.) The first two of these support ILS.
    13. Ft. Lauderdale Airport (FLL) has three runways, one instrument-capable long one (9,000 feet) running SEE, and two shorter runways, running southeast (6930 feet) and east (one mile even.)
    14. Each operational aircraft has a unique AID (aircraft ID), similar to an automobile's VIN number.
    15. A 2009 Airbus 320 with AID #1111 has a full tank (6,850 gallons) and 92,000 nautical miles. A sister aircraft, manufactured in the same year, is also in daily operation; its AID is #1112 and has flown 86,000 miles. Its tank is also full at the moment. Both aircraft are on the ground in Denver.
    16. Vehicle #2345 is a Boeing 737 aircraft that was produced in 2006, and has flown for 486,000 miles. It fuel gauge reads nearly empty as it awaits refueling at Cleveland-Hopkins International.
    17. American Airlines flight 1865 leaves from Reagan National every morning at 5:30am, touching ground in Denver at 10:54am. It uses Airbus 320 aircraft for these daily journeys.
    18. AirBlue runs a quick half-hour shuttle flight (flight #101) from DCA to Stafford Regional Airport (RMN) in Fredericksburg every afternoon at 1pm.
    19. American 4501 is a daily afternoon trip on a Boeing 737 from DCA to Cleveland. It departs at 3:15pm and lands at 4:45.
    20. The 132 passengers on today's (Sep. 8th, 2024's) United flight 735 — which departs daily at noon from Cleveland to Ft. Lauderdale — are currently airborne. Suzie and Stan are the cockpit crew for this flight (the pilot and co-pilot, respectively) and they are piloting the aircraft #5566. They pulled out of Cleveland-Hopkins gate C-09, have 5000 gallons left in the tank, and are on schedule for a timely arrival in Florida at 4:34pm. They have reserved the east runway, and will pull into gate B46 after they land.
    21. Suzie has been assigned to pilot next Friday's (Sept. 17th, 2024's) AirBlue 101 flight. No co-pilot has yet been assigned, but an aircraft has: a brand new Cessna 414 with AID #52982. It is rumored that Virginia gubernatorial candidate Abigail Spanberger may be aboard this flight.
    22. United's #5566 aircraft is an old Boeing 737, having been manufactured in 1999 and flown about a million miles, with several superficial but ugly dents in its exterior. It's set to be retired soon.
    23. According to ChatGPT, the fuel tank on a Boeing 737's and Boeing 747's can hold about 7,200 gallons.
    Again, if you have trouble inserting any of these facts into your schema, it probably means that your schema has an error and must be corrected! Adjust the schema in light of the information the database is expected to hold, and insert the facts. Come see me if you have problems or questions!
  5. Actually materialize your relational schema in this file, using CREATE TABLE statements as described in class. To check your work, you can use the .schema command. (Again, I recommend you put your commands in a .sql text file and execute them with the .read command instead of typing them all interactively.)
  6. Actually put all the above data into your database, using INSERT INTO statements as described in class. To check your work, you can use "SELECT * FROM tableName" commands. (Again, I recommend you put your commands in a .sql text file and execute them with the .read command instead of typing them all interactively.)

    Note: in populating your database with the above facts, there are a few places where you will not know what value to include in a database row. In those instances, simply set the column's value to NULL for that row. Do not use NULL for any situation in which you can deduce the correct value, though.
  7. You can exit the SQLite command line at any time by typing ".quit" at the sqlite> prompt. (Yes, that's a dot (".") before the word "quit".)

    Note: because all SQL statements must end with a semicolon (";"), it can sometimes be frustrating to exit the SQLite command line when you can't get it to recognize your ".quit" as a command. Often, you just need to press the semicolon button followed by Enter (which will barf because the stuff you just typed is malformed SQL, but never you mind), and then you'll get a clean sqlite> prompt at which you can type ".quit".

Turning it in

Send me an email with subject line "CPSC/DATA 350 Assignment #2 turn-in" with your .db file attached. (Double-check that it's actually attached!) In the body of the email, tell me your favorite airplane joke.

Getting help

Come to office hours, or send me email with subject line "CPSC/DATA 350 Assignment #2 help!!"