Francois Neville's CS3507 Homepage

This is where I display grades and organize resources for CS3507 — Intro to Databases

Fall 2023

Links of Interest:

Hans Rosling: the best statistics you've ever seen
The powerful story of one woman's twin loves for data and cheeseburgers
More Hans Rosling Talks:

Assignments:

DB Final: Airfare Data Analysis — Due Fri 12/08
Details

HW6: Time-Series Model vs. ST Model — Due Mon 11/20
Details

HW5 E.C.: Help MPN Fix his HW5 Instructions — Due Mon 11/20
Details

HW5B: Practice Exporting and Importing in MS Access — Due Wed 11/08
Details

HW5: Linear-Model Wind Predictions — Due Wed 11/08
Details

(FYI) Winds Metadata

HW4: Wind Dataset Metadata Analysis — Due Wed 10/25
Details

HW3: Relational Algebra and SQL Examples — Due Wed 10/18
Details

HW2B: A Month of Airfares — Due Mon 10/02
Using the airfare data that you have been collecting this semester on the three flights below, create updated versions of the four graphs from HW2 (also repeated below).
Note: departure-date and return-date for all three flights should be the same as in the Semester Project description down below: Tue Nov 14 2023 to Sun Nov 19 2023
Your data need not cover an entire month (but I expect there should be at least three weeks worth!).
Collection date-range should be through the end of September, but may include through Oct 02 if you like.

Flights:
MSP-DFW
MSP-JFK
MSP-BOS (Reminder: should not be collected or even pinned during last two weeks of September)

Hand in a MS Word report to D2L by the due-date, including:
- A separate scatterplot graph of each flight's price-fluctuations so far (make markers visible on graph time-series, label axes) -- and one fourth graph showing all three data series at once -- all on a date-value x-axis
- One or two lines of description and relevant details for each flight tracked in the three single-flight graphs.
- In the unlikely event a flight you are tracking is ever cancelled, replace it with a similar flight and track that one from that point onwards (but use a different-color series for it in that graph!)

HW2: A Week of Airfares — Due Fri 9/01
Collect the daily values (according to the data schema below, discussed in class) of the three flights you are tracking for the Airline Fares Project over the next week. Import the resulting data to an Excel file (or spreadsheet application of your choice) if not already in such format, to make graph-creation easier.
Note: departure-date and return-date for all three flights should be the same as in the Semester Project description below: Tue Nov 14 2023 to Sun Nov 19 2023
You need not have seven days worth of data collected for full credit (but I expect at least five!)

Flights:
MSP-DFW (departure: not too early; arrival: not too late)
MSP-JFK direct domestic flight "control"
MSP-BOS different direct domestic flight "experimental"

Hand in a MS Word report to D2L on due-date, including:
- A separate graph of each flight's price-fluctuations so far (make markers visible on graph time-series, label axes) -- and one fourth graph showing all three data series at once
- One or two lines of description and relevant details for each flight tracked in the three single-flight graphs.
- In the unlikely event a flight you are tracking is ever cancelled, replace it with a similar flight and track that one from that point onwards (but use a different-color series for it in that graph!)

Semester Project: Airline Fares DB: Data Gathering — (Due 11/30 -- tentative)
- I have noticed that when I try to plan ahead and search for flights for a particular destination and date, and I save (or "pin") my favorite flight/fare without purchasing it, when I check back the next day the price has almost invariably increased by about $30, and it warns that only a few seats are left at that price. If I still do not purchase, the next day the fare has increased again!
- If I do not check in for a few weeks, I might see the fare drop back to the original price (or below) and display 9+ seats available again.
1. Choose a single, specific nonstop MSP-DFW (11/14/2023 - 11/19/2023) direct flight whose fares you will track over the next six weeks. Store the following data in a DB table from a site's airfare engine (e.g. flights.google.com, Kayak.com):
```
	      date          DATE
	      time          TIME
	      start         VARCHAR(3)  -- 3-char airport code
	      dest          VARCHAR(3)  -- 3-char airport code
	      depAirline    VARCHAR(2)  -- 2-char airline code
	      depFlightNo   VARCHAR(10)
	      retAirline    VARCHAR(2)  -- 2-char airline code
	      retFlightNo   VARCHAR(10)
	      fare          INT
	      comment       VARCHAR(200)   -- generic field for any additional info you want to keep
	    
```
2. Do the same (with the same flight-dates!) for a single, specific domestic direct flight: (MSP-JFK). This will be the "control" to the experimental flight #3 below.
3. Also, track a third (direct, domestic) flight (MSP-BOS) ---- but track it only for the first two weeks (now - Sep 15), completely ignore it for two weeks (Sep 15-30), then start tracking it again (Oct 1 - end). This is the "experimental" flight to test Neville's crackpot theory
- Check flights 1-3 at least once a day (or if manually collecting data multiple times a day, make sure there is at least one hour between checks). Should you forget to check flights for a few days, don't fret and definitely don't try to interpolate data -- save only actually-retrieved fares.
- Select flights for the same date-range (11/14/23 - 11/19/23) for all three tracked flights
- Pin/save your selected flights so you can track the same three flights each time. Do NOT just track the cheapest three flights each day -- you must commit to three specific flights!
- Extra credit if you build a program that auto-queries (a browser/the internet) for this info automatically and saves it into your DB. (presentation required!)
- An overall report on your collected data will (tentatively) be due Oct 30.
- Please start collecting this data immediately — or at least by Mon 8/28/23

HW1: Creating and Querying Access Tables — Due Fri 9/01
1. Create 2-3 tables (each at least 5 records long) containing related information.
  - e.g. in class I used a table of actor names (and IDs), and a table of movie names (and movieIDs) — but you should try to create a different situation than my "actors and movies" example used in class
  - And a third table tying the previous two tables together.
2. Use the Query's Design interface to designate the relevant data from your tables to retrieve. Try to build a single query to retrieve all necessary data from all tables at once
3. Write a Word report (~2 pgs) detailing how easy (or hard), intuitive (or not) it was to accomplish this in Access.
  - Include (cropped, appropriately-sized) screenshots of relevant steps in the process
  Display and provide an explanation for the SQL query that Access created to accomplish your data-retrieval request.