nycflights13

suppressPackageStartupMessages(library("tidyverse"))

package 㤼㸱tidyverse㤼㸲 was built under R version 3.6.3

suppressPackageStartupMessages(library("nycflights13"))

package 㤼㸱nycflights13㤼㸲 was built under R version 3.6.3

1. Imagine you wanted to draw (approximately) the route each plane flies from its origin to its destination. What variables would you need? What tables would you need to combine?

Drawing the routes requires the latitude and longitude of the origin and the destination airports of each flight. This requires the flights and airports tables. The flights table has the origin (origin) and destination (dest) airport of each flight. The airports table has the longitude (lon) and latitude (lat) of each airport. To get the latitude and longitude for the origin and destination of each flight, requires two joins for flights to airports, once for the latitude and longitude of the origin airport, and once for the latitude and longitude of the destination airport. I use an inner join in order to drop any flights with missing airports since they will not have a longitude or latitude.

flights_latlon <- flights %>%
  inner_join(select(airports, origin = faa, origin_lat = lat, origin_lon = lon),
    by = "origin"
  ) %>%
  inner_join(select(airports, dest = faa, dest_lat = lat, dest_lon = lon),
    by = "dest"
  )

This plots the approximate flight paths of the first 100 flights in the flights dataset.

flights_latlon %>%
  slice(1:100) %>%
  ggplot(aes(
    x = origin_lon, xend = dest_lon,
    y = origin_lat, yend = dest_lat
  )) +
  borders("state") +
  geom_segment(arrow = arrow(length = unit(0.1, "cm"))) +
  coord_quickmap() +
  labs(y = "Latitude", x = "Longitude")

2. I forgot to draw the relationship between weather and airports. What is the relationship and how should it appear in the diagram?

The column airports$faa is a foreign key of weather$origin. The line representing the new relation between weather and airports is colored black. The lines representing the old relations are gray and thinner.

3. Weather only contains information for the origin (NYC) airports. If it contained weather records for all airports in the USA, what additional relation would it define with flights?

If the weather was included for all airports in the US, then it would provide the weather for the destination of each flight. The weather data frame columns (year, month, day, hour, origin) are a foreign key for the flights data frame columns (year, month, day, hour, dest). This would provide information about the weather at the destination airport at the time of the flight take off, unless the arrival date-time were calculated.

So why was this not a relationship prior to adding additional rows to the weather table? In a foreign key relationship, the collection columns of columns in the child table must refer to a unique collection of columns in the parent table. When the weather table only contained New York airports, there were many values of (year, month, day, hour, dest) in flights that did not appear in the weather table. Therefore, it was not a foreign key. It was only after all combinations of year, month, day, hour, and airports that are defined in flights were added to the weather table that there existed this relation between these tables.

4. We know that some days of the year are “special”, and fewer people than usual fly on them. How might you represent that data as a data frame? What would be the primary keys of that table? How would it connect to the existing tables?

I would add a table of special dates, similar to the following table.

special_days <- tribble(
  ~year, ~month, ~day, ~holiday,
  2013, 01, 01, "New Years Day",
  2013, 07, 04, "Independence Day",
  2013, 11, 29, "Thanksgiving Day",
  2013, 12, 25, "Christmas Day"
)

The primary key of the table would be the (year, month, day) columns. The (year, month, day) columns could be used to join special_days with other tables.