CPSC/DATA 350 — Applications of Databases — Fall 2024

Assignment #3

Possible experience: +40XP

Due:Sunday, Oct 6th Tuesday, Oct 8th, midnight

A database console interface

You will write a text-based (a.k.a. console-based) Python program called customer_service.py that will allow mythical customers to book reservations on airline flights. When it runs, your program should read from the SQLite database specified on the command line, and print a list of airlines for the customer to choose from. When they choose an airline, they should be presented with a list of known flight numbers for that airline. Finally, they'll be presented with information about the flight and have the opportunity to purchase tickets.

Here's an example session (text in blue is what the user types):

$ python3  customer_service.py  lgaga_airline.db
Welcome to the flight registration system!
Which airline are you interested in?
1. AirBlue
2. American
3. Frontier
4. Southwest
5. United
0. none (quit)
> 2
Choose a flight:
1. American 1865 - Reagan National to Denver International 
2. American 4501 - Reagan National to Cleveland-Hopkins International 
3. American 6007 - Ft. Lauderdale to Mos Eisley Intergalactic
> 1
What date would you like to purchase tickets for? (YYYY-MM-DD)
> 2024-10-28
American 1865 departs Reagan National at 5:30am on 2024-10-28.
There are 119 seats left on this flight.
Purchase how many tickets?
> 2
You are now booked on American 1865 for a 5:30am departure from Reagan National!

Which airline are you interested in?
1. AirBlue
2. American
3. Frontier
...

All information should be appropriately read from the database. For instance, you should not hardcode your airline menu to have the specific set of airlines that you created for homework 2; instead, any airline with a flight currently in your database's regularFlight table (or whatever you call it) should be present as an option.

Corner cases

  1. In case this isn't obvious, the customer should be able to purchase tickets for any future day's flight, not merely ones that already have an entry in your "scheduled_flight" table (or whatever you call it). If they are the first one to buy tickets for a particular day's flight, your program needs to be smart enough to insert a new row in scheduled_flight for that date and flight with the number of tickets they purchased. (If they are not the first one to buy tickets for that particular day's flight, then there will already be a row in scheduled_flight, and the number of tickets sold can just be updated.) To be super clear: your program should never say, "sorry there's no scheduled Frontier 68 flight for that day." The only times it should decline a user's request is if they specify a date in the past (see below), or if they try to purchase too many seats (see below).
  2. If the customer tries to purchase more tickets on a given day's flight than are remaining for that flight, your program should sell none of them and instead warn the customer there are not enough seats:
    ...
What date would you like to purchase tickets for? (YYYY-MM-DD)
> 2024-09-27
United 735 departs Cleveland-Hopkins International at 12:00pm on 2024-09-27.
There are 3 seats left on this flight.
Purchase how many tickets?
> 4
Sorry, not enough seats available for this purchase! Consider a different flight.

Which airline are you interested in?
1. AirBlue
2. American
3. Frontier
...
  3. If the customer tries to purchase tickets for a date in the past, your program should prevent it, and re-prompt for another date:
    ...
What date would you like to purchase tickets for? (YYYY-MM-DD)
> 2024-09-20
Sorry, that date has already passed!
What date would you like to purchase tickets for? (YYYY-MM-DD)
...
    You don't need to worry about time, only date. Buying a ticket for today always should work, even if the flight has already taken off (or even landed).

"Gitting" started

Important preamble

Let me make one point crystal clear right off the bat: you should not create a publicly-available git repo for this assignment on github.com, bitbucket.com, sourceforge.com, or any other hosting site. It is an Honor Code violation to do so. Putting your repo on github or other sites makes it trivially easy for other students, in this or future semesters, to spy on and steal your solution. DO NOT DO THIS! You're using git for this homework, but not github.

Setup instructions

Okay. If you've never used git on the machine you're developing on before (whether that's your own laptop/desktop, the cpsc department server, a Google Cloud instance, a virtual machine you created via your own OS, etc.), you'll need to tell git who you are, and set your default editor, by configuring these settings:

$ git  config  --global  user.email  "you@example.com"
$ git  config  --global  user.name  "Your Name"
$ git  config  --global  --replace  core.editor  vim

(You'll never have to do that part again. And if you've already set these things before on this machine/server/instance, you don't need to re-do it.)

Now you must create a git repo in which to house your project. First, create a project directory. If you're using the command-line, this can be achieved via this command:

$ mkdir  homework3

and you can go into it by typing:

$ cd  homework3

Now create your git repo by running:

$ git  init  .

Copy (or move) your .db (or .sqlite) database file from its location when you worked on homework 2 to this new directory.

Add your .db database to git by typing: 

$ git  add  lgaga_airline.db

Now you're ready to create your customer_service.py Python file. In a different window, create a .py file using the text editor of your choice (vim, Notepad++, TextEdit, Atom, Sublime, Emacs, whatever) and add some initial contents:

# CPSC/DATA 350 Assignment #3
# Airline project: Customer service console app
# Professor: Stephen Davies
# Student: Lady Gaga

from sqlite3 import *

print("Welcome to the flight registration system!")

Save the file, and add it to your repo via:

$ git  add  customer_service.py

And finally make your first commit:

$ git  commit  -a  -m  "Initial commit, with DB and Python placeholder."

Continue the habit, which you have already begun to build, of performing git commits regularly, every time you complete a cohesive unit of work.

Turning it in

You will turn this assignment in by attaching a git bundle to an email. (A "git bundle" is essentially a portable, zipped-up git repo.) The subject line of the email should be "CPSC/DATA 350 Assignment #3 turn-in".

To do this, first make sure that all your code is committed to git. Running "git status" and ensuring your workspace is clean is a great way to do that. Then, bundle up your git repo:

$ git  bundle  create  yourUmwUsername.git  --all

(Please do not name it literally "yourUmwUsername.git" Substitute your actual UMW username. For instance, "jsmith7.git".)

Finally, send me the yourUmwUsername.git file as an email attachment with subject line "CPSC/DATA 350 Assignment #3 turn-in". (Double-check that it's actually attached!) If you're using the Cloud (which includes the cpsc department server) for development, then in order to get the file on your local machine to attach it to an email, you will have to download it from the Cloud.

To receive full credit, I must be able to type the following commands in sequence, verbatim, with no variations, to run your program:

$ git  clone  the-name-of-the-repo-you-sent-in-your-email.git  temporary
$ cd  temporary
$ python3  customer_service.py  yourUmwUsername_airline.db

If I have to do any extra fiddling because these three commands, verbatim, in sequence, did not work, it's coming out of your grade.

Hint: test that this works before submitting your assignment. You can test it by creating a little temporary practice directory:

$ mkdir  pretendImStephen
$ cp  the-name-of-your-repo.git  pretendImStephen
$ cd  pretendImStephen

and then typing those three commands, above, to make sure they have the desired effect. (You can then get rid of that directory with "cd .." followed by "rm -rf pretendImStephen".)

Bonus: starting early and often

Note: the definition of "non-trivial" is "whatever Stephen deems is non-trivial."

I need help!

Send email with subject line "CPSC/DATA 350 Assignment #3 HELP!!!"