Relational Data Homework

This homework is going to play around with relational joins. We will use some flight data from 2013 in New York City¹.

library( nycflights13 )

This library has five different data frames. Here is an overview of the raw data.

The Data

A table of airline carrier abbreviations (N = 16 different carriers).

head( airlines )

Information on the airports (N = 1458 airports).

head( airports )

Mostly in the US.

Weather conditions at each airport (a total of 26115 of them!)

head(weather)

And a list of airplanes (N = 3322).

head( planes )

From all of these auxiliary data, we can construct a series of flights which has the following variables in it.

names( flights )

 [1] "year"           "month"          "day"            "dep_time"      
 [5] "sched_dep_time" "dep_delay"      "arr_time"       "sched_arr_time"
 [9] "arr_delay"      "carrier"        "flight"         "tailnum"       
[13] "origin"         "dest"           "air_time"       "distance"      
[17] "hour"           "minute"         "time_hour"     

year

month

day

dep_time

sched_dep_time

dep_delay

arr_time

sched_arr_time

arr_delay

carrier

flight

tailnum

origin

dest

air_time

distance

hour

minute

time_hour

If you look at the flight table, you will see that several columns of data here are connected to the other data frames described above.

flights Foreign Key	Data From
carrier	airlines
dest	airports
origin	airport
tailnum	planes
time_hour	weather

The reason that these tables have keys connecting one to the other is that there is an economy of scale for very large data sets if we partition it into realtional components such as this.

Questions

Use the tables above to answer the following questions.

1. Which carriers have planes with over 100 flights in 2013 (and how many)?

2. During what 48 hour period were the worst delays? Is there any connection with weather?

3. What are the five best and five worst airlines for keeping on schedule?

4. Independent of the length of the flight, are you more likely to be delayed by taking a large plane or a small plane?

5. Do all planes have tail numbers?

---

1. If you get an error when attempting to load this library in, install it with install.packages("nycflights13") and then load it in again.↩︎