Keys

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. Add a surrogate key to flights.

I add the column flight_id as a surrogate key. I sort the data prior to making the key, even though it is not strictly necessary, so the order of the rows has some meaning.

flights %>%
  arrange(year, month, day, sched_dep_time, carrier, flight) %>%
  mutate(flight_id = row_number()) %>%
  glimpse()

Observations: 336,776
Variables: 20
$ year           <int> 2013, 2013, 2013, 2013, 2013, 2013, 2013, 2013, 2013, 2013, 2013, 2013, ...
$ month          <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, ...
$ day            <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, ...
$ dep_time       <int> 517, 533, 542, 544, 554, 559, 558, 559, 558, 558, 557, NA, 601, 600, 555...
$ sched_dep_time <int> 515, 529, 540, 545, 558, 559, 600, 600, 600, 600, 600, 600, 600, 600, 60...
$ dep_delay      <dbl> 2, 4, 2, -1, -4, 0, -2, -1, -2, -2, -3, NA, 1, 0, -5, -6, -3, 8, 0, -2, ...
$ arr_time       <int> 830, 850, 923, 1004, 740, 702, 753, 941, 849, 853, 838, NA, 844, 851, 91...
$ sched_arr_time <int> 819, 830, 850, 1022, 728, 706, 745, 910, 851, 856, 846, 901, 850, 858, 8...
$ arr_delay      <dbl> 11, 20, 33, -18, 12, -4, 8, 31, -2, -3, -8, NA, -6, -7, 19, -25, -14, 32...
$ carrier        <chr> "UA", "UA", "AA", "B6", "UA", "B6", "AA", "AA", "B6", "B6", "B6", "B6", ...
$ flight         <int> 1545, 1714, 1141, 725, 1696, 1806, 301, 707, 49, 71, 79, 125, 343, 371, ...
$ tailnum        <chr> "N14228", "N24211", "N619AA", "N804JB", "N39463", "N708JB", "N3ALAA", "N...
$ origin         <chr> "EWR", "LGA", "JFK", "JFK", "EWR", "JFK", "LGA", "LGA", "JFK", "JFK", "J...
$ dest           <chr> "IAH", "IAH", "MIA", "BQN", "ORD", "BOS", "ORD", "DFW", "PBI", "TPA", "M...
$ air_time       <dbl> 227, 227, 160, 183, 150, 44, 138, 257, 149, 158, 140, NA, 147, 152, 158,...
$ distance       <dbl> 1400, 1416, 1089, 1576, 719, 187, 733, 1389, 1028, 1005, 944, 1069, 1023...
$ hour           <dbl> 5, 5, 5, 5, 5, 5, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, ...
$ minute         <dbl> 15, 29, 40, 45, 58, 59, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ...
$ time_hour      <dttm> 2013-01-01 05:00:00, 2013-01-01 05:00:00, 2013-01-01 05:00:00, 2013-01-...
$ flight_id      <int> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 2...

2. Identify the keys in the following datasets

1. Lahman::Batting
2. babynames::babynames
3. nasaweather::atmos
4. fueleconomy::vehicles
5. ggplot2::diamonds

(You might need to install some packages and read some documentation.)

The answer to each part follows.

The primary key for Lahman::Batting is (playerID, yearID, stint). The columns (playerID, yearID) are not a primary key because players can play on different teams within the same year.

Lahman::Batting %>%
  count(playerID, yearID, stint) %>%
  filter(n > 1) %>%
  nrow()

[1] 0

The primary key for babynames::babynames is (year, sex, name). The columns (year, name) are not a primary key since there are separate counts for each name for each sex, and the same names can be used by more

babynames::babynames %>%
  count(year, sex, name) %>%
  filter(n > 1) %>%
  nrow()

[1] 0

The primary key for nasaweather::atmos is (lat, long, year, month). The primary key represents the location and time that the measurement was taken.

nasaweather::atmos %>%
  count(lat, long, year, month) %>%
  filter(n > 1) %>%
  nrow()

[1] 0

The column id, the unique EPA identifier of the vehicle, is the primary key for fueleconomy::vehicles.

fueleconomy::vehicles %>%
  count(id) %>%
  filter(n > 1) %>%
  nrow()

[1] 0

There is no primary key for ggplot2::diamonds since there is no combination of variables that uniquely identifies each observation. This is implied by the fact that the number of distinct rows in the dataset is less than the total number of rows, meaning that there are some duplicate rows.

ggplot2::diamonds %>%
  distinct() %>%
  nrow()

[1] 53794

nrow(ggplot2::diamonds)

[1] 53940

If we need a unique identifier for our analysis, we could add a surrogate key.

diamonds <- mutate(ggplot2::diamonds, id = row_number())

3. Draw a diagram illustrating the connections between the Batting, Master, and Salaries tables in the Lahman package. Draw another diagram that shows the relationship between Master, Managers, AwardsManagers. How would you characterize the relationship between the Batting, Pitching, and Fielding tables?

For the Batting, Master, and Salaries tables:

• Master
  • Primary key: playerID
• Batting
  • Primary key: playerID, yearID, stint
  • Foreign keys: playerID = Master$playerID (many-to-1)
• Salaries
  • Primary key: yearID, teamID, playerID
  • Foreign keys: playerID = Master$playerID (many-to-1)

The columns teamID and lgID are not foreign keys even though they appear in multiple tables (with the same meaning) because they are not primary keys for any of the tables considered in this exercise. The teamID variable references Teams$teamID, and lgID does not have its own table.

Database schema diagrams are used to illustrate relations between the tables. Most flowchart or diagramming software can be used used to create database schema diagrams, as well as some specialized database software. These diagrams were created with OmniGraffle.

The following diagram shows the relations between the Master, Batting and Salaries tables.

For the Master, Manager, and AwardsManagers tables:

• Master
  • Primary key: playerID
• Managers
  • Primary key: yearID, teamID, inseason
  • Foreign keys: playerID references Master$playerID (many-to-1)
• AwardsManagers:
  • Primary key: playerID, awardID, yearID
  • Foreign keys: playerID references Master$playerID (many-to-1)

For AwardsManagers, the columns (awardID, yearID, lgID) are not a primary key because there can be, and have been ties, as indicated by the tie variable.

The relations between the Master, Managers, and AwardsManagers tables are shown in the following two diagrams: the first created manually with OmniGraffle.

The primary keys of Batting, Pitching, and Fielding are the following:

• Batting: (playerID, yearID, stint)
• Pitching: (playerID, yearID, stint)
• Fielding: (playerID, yearID, stint, POS).

While Batting and Pitching has one row per player, year, stint, the Fielding table has additional rows for each position (POS) a player played within a stint.

Since Batting, Pitching, and Fielding all share the playerID, yearID, and stint we would expect some foreign key relations between these tables. The columns (playerID, yearID, stint) in Pitching are a foreign key which references the same columns in Batting. We can check this by checking that all observed combinations of values of these columns appearing in Pitching also appear in Batting. To do this I use an anti-join, which is discussed in the Filtering Joins section of the lecture.

nrow(anti_join(Lahman::Pitching, Lahman::Batting,
  by = c("playerID", "yearID", "stint")
))

[1] 0

#> [1] 0

Similarly, the columns (playerID, yearID, stint) in Fielding are a foreign key which references the same columns in Batting.

nrow(anti_join(Lahman::Fielding, Lahman::Batting,
  by = c("playerID", "yearID", "stint")
))

[1] 0

The following diagram shows the relations between the Batting, Pitching, and Fielding tables.