CPSC/DATA 350 — Applications of Databases — Fall 2026

Assignment #2

Possible experience: +40XP

Due: Wednesday, Sep. 30, midnight

Creating an SQLite database

1. Normalizing your schema
   1. 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.
   2. Identify the functional dependencies in each of your tables, and the key(s) that emerge from that analysis. Demonstrate that every table is normalized; i.e., for every table, every non-key attribute is functionally dependent on the key, the whole key, and nothing but the key. If any of your tables are revealed not to be normalized by this analysis, normalize the offending table by breaking it up into smaller tables which are normalized. Come see Stephen if you're unclear how to do this.
2. Log on to the cpsc.umw.edu server

   Make sure you can successfully log on to cpsc. You may need to take another look at Ian's outstanding CPSC 225 notes for how to do that. (And if you need a password reset, please email Ian at ifinlay _AT_ umw _DOT_ edu.)

3. Create and populate your database
   1. 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". Thanks. For instance, "jsmith19_airline.db" is a suitable name.)
      
      The way you will accomplish this is to log on to cpsc, cd to the directory/folder you want to keep your class projects in, and type:

      $ sqlite3 yourumwid_airline.db
      

      Pro tip: instead of typing everything interactively at the sqlite> prompt, create a new text document with vim in which you type your commands and save them. 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 ( ...
      

   2. 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 Daphne is certified to fly both the Boeing 747 and also the Airbus 320.
      6. Pilot Jeannette has been qualified to fly both Boeing aircraft, as well as the Cessna.
      7. Pilot Divine is exclusively a Boeing 737 pilot.
      8. Yoshi 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 Feifei 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. This Sep 1st's American 1865 had Feifei as pilot and Daphne as co-pilot. They flew 39 passengers on aircraft #1111, departing from gate 37 and de-planing at gate 5.
      21. This Sep 14th's American 1865 had Daphne as pilot and Feifei as co-pilot. They flew 139 passengers on aircraft #1111, departing from gate 7 and de-planing at gate 45.
      22. On Groundhog Day this year, American 1865 had Feifei as pilot and Daphne as co-pilot. They flew 122 passengers on aircraft #1112, departing from gate 37 and de-planing at gate 54.
      23. The 132 passengers on today's (Sep 21st, 2026's) United flight 735 — which departs daily at noon from Cleveland to Ft. Lauderdale — are currently airborne. Jeannette and Divine 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.
      24. Jeannette has been assigned to pilot next Friday's (Set. 25th, 2026'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 governor Abigail Spanberger may be aboard this flight, and so the airline is very excited!
      25. For the rest of this month (Sept. 26th and following, we have Jeannette and Daphne alternating as pilot and co-pilot on AirBlue 101, starting with Jeannette as pilot on the 26th, Daphne on the 27th, and so forth. These flight assignments go through the end of October.
      26. 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.
      Note: Ask Google of ChatGPT how large the fuel tanks are on Boeing 737's, Boeing 747's, and any other aircraft you need the information for.
      
      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!
   3. 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.)
   4. 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.
   5. 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

   Download your .db database file from cpsc.umw.edu to your own computer, using something like scp or Filezilla. You may need to refer back to Ian's section on Filezilla to accomplish this.

   Then, 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.

   Note carefully that although I counseled you to store all of your database work in a text file with a .sql extension, for each of use, the thing you should turn in to me for this assignnment is not a .sql text file! If you can open the file in vim or VS Code or MS Word or any other editor, and get sensibly-looking comments, that's the wrong file to submit. Instead, you should be attaching a file with a .db file extension, which you have opened to populated via SQLite. If you cannot open this file in vim or VS Code or MS Word or any other editor and get sensibly-looking comments, that's probably the right file to submit.

   Getting help

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