
# ME314 Managing and Visualizing Data

## Day 3 Assignment, LSE ME314

---

### 1. Normalizing data

This question uses this table:
![Not normalized data](http://www.essentialsql.com/wp-content/uploads/2014/06/Intro-Table-Not-Normalized.png)

from the lecture notes.  For each answer, please explain as fully as possible, and feel free to use tables or diagrams if you prefer.

a)  Why does this table violate the first normal form, and what would be required to make it 1NF?

**The rules to satisfy 1st normal form are:**

-  **That the data is in a database table.**
-  **The table stores information in rows and columns where one or more columns, called the primary key, uniquely identify each row.**
-  **Each column contains atomic values, and there are not repeating groups of columns.**

**This table violates the 1NF because it contains data customer data in more than one column, in this case, `Customer1`, `Customer2`, and `Customer3`.**

**We could solve this by moving the Customer information to its own table, and linking its primary key to a matching foreign key in the original table related to SalesStaff.**

**See [this website](https://www.essentialsql.com/get-ready-to-learn-sql-8-database-first-normal-form-explained-in-simple-english/) for more details.**

b)  What additional steps would be needed to make the table 2NF, and why?

**A table is in 2nd Normal Form if:**
-  **The table is in 1st normal form, and**
-  **All the non-key columns are dependent on the table’s primary key.**

**We know that the tables are not in 1NF, but assuming that we had fixed that, there would still issues, for example, the `SalesStaff` table has two columns that do not depend on the EmployeeID:  `SalesOffice` and `OfficeNumber`.  We would better creating a separate table for Sales offices, and linking its primary key to the `SalesStaff` table via a matching foreign key in the `SalesStaff` table.**

**There are other potential issues that wherein, depending on how you achieved the 1NF step, there could still be table attributes that do not completely rely on that tables's primary key.  For more examples, see [here](https://www.essentialsql.com/get-ready-to-learn-sql-10-database-second-normal-form-explained-in-simple-english/).**


c)  Why might we not want to normalize data to the fullest extent possible?

**Sometimes a database table that is not fully normalized still provides sufficient information for our needs, without the complexity of full normalization providing gains that outweigh the loss of simplicity.  Third and higher form normalization are often ignored, for instance, for small-scale databases built for specific purposes.  For large scale database scheme that need the ability to be extended easily, however, and for which data integrity is critical, full normalization is generally the best strategy.**

d)  In the table below, which of the three normalization rules does this violate, if any, and why?

   |  countryID  |  countryName    |   EUmember   |  EUjoindate  |
   | -----------:|:----------------|:------------:|:------------:|
   | 00001       | France          |  `true`      |  1958-01-01  |
   | 00004       | Hungary         |  `true`      |  2004-05-01  |
   | 00003       | Serbia          |  `false`     |       `NULL` |
   | 00004       | Finland         |  `true`      |  1995-01-01  |
   | 00005       | Russia          |  `false`     |       `NULL` |
   | 00006       | Ireland, UK     |  `true`      |  1973-01-01  |

   Are there any other problems with the table, besides normalization?

**Yes:**  
-  **1NF is violated because because `countryName` contains multiple country values in the last row.**
-  **2NF is violated because we could have created a table of EU membership statuses (including soon, sadly, un-joining) that would link to the Country table via `CountryID`.**
-  **3NF is violates because the `EUmember` can be determined by whether the `EUjoindate` is not `NULL`.**

e)  What would it take to full (1NF-3NF) normalize this dataset?

   Write out these tables, and describe why this meets each of the normal forms.  This is a database of movies watched on NetBricks, a streaming movie service.

   | Name           | Address    |   Movies Rented   |  Salutation  | Category |
   |:---------------|:-----------|:------------------|:------------:|----------|
   | Bob Smith      | 1 Houghton Street    | _Star Wars_, _Inception_ |  Dr.   |  Scifi, Scifi |
   | Pry Ministair  | 10 Downing St     |  _Brexit the Movie_      |  Lady  | Tragedy |
   | Joe Bloggs     | 8 Myhatt St.      |  _Fast and Furious 6_, _Fast and Furious 7_     | Mr. | Action, Action |

**Create tables for:**

-  **User, with a `UserID`, which would record the address, and salutation**
-  **Movies, with a `MovieID`**
-  **Categories, with a `CategoryID` linking to the Movies table**
-  **Rental Table, linking to Movies and User tables, with added fields for the date rented**

```{r}
# create user table
user_table <- read.csv(textConnection("userid, Name, Address, Salutation
     1,     Bob Smith, 1 Houghton Street,        Dr.
     2, Pry Ministair,     10 Downing St,       Lady
     3,    Joe Bloggs,      8 Myhatt St.,        Mr."),
     stringsAsFactors = FALSE)

# create movies table
movie_table <- data.frame(movieid = 1:5,
                          title = c("Star Wars", "Inception", "Brexit the Movie", 
                                    "Fast and Furious 6", "Fast and Furious 7"),
                          categoryid = c(1,1, 2, 3, 3),
                          stringsAsFactors = FALSE)

# create rental table
rental_table <- data.frame(userid = c(1, 1, 2, 3, 3),
                           movieid = 1:5)

# create category table
category_table <- data.frame(categoryid = 1:3,
                             genres = c("Scifi", "Tragedy", "Action"),
                             stringsAsFactors = FALSE)
```

**Here are the four tables in fully normalized database** 

#### User table

```{r echo = FALSE, results = 'asis'}
library("knitr")
kable(user_table, caption = 'User Table')
```

#### Rental table
```{r echo = FALSE, results = 'asis'}
kable(rental_table, caption = 'Rental Table')
```

#### Movie table
```{r echo = FALSE, results = 'asis'}
kable(movie_table, caption = 'Movie Table')
```

#### Category table
```{r echo = FALSE, results = 'asis'}
kable(category_table, caption = 'Category Table')
```

**These tables fits to the 3 levels of normalization because**

- **For achieving 1NF, the problem was the multiple rental records in each row. By separating this as the rental table and making each row correspond with a single rental record, this issue is resolved**.
- **For achieving 2NF, the partial dependency of records on the primary keys would be an issue. This could be an issue if we have both movieid and movie information in rental record because the movie information can be uniquely identified by the movie id. By separating this information into a separate table (i.e. Movie Table), this issue was resolved.**
- **For achieving 3NF, the tables should not have transitive dependency. Since the movie categories does not have any information other than the titles of categories, this cannot be an issue here unless we have a secondary key of categoryid in addition to the category name, but I created a new table categoryid just in case.**

### 2.  Reshaping data

For this exercise, we will use the **nycflights13** R package, whose tables have been output in `.csv` form [here](nycflights13/).  You may do the following in either R or Python.  Note that this example is developed extensively in [_R for Data Science_](http://r4ds.had.co.nz/relational-data.html).

a)  Create a subtable of the `flights` data, that departed before 05:53 on 2013-02-28.  How many rows and columns does this subtable have?  

```{r echo = FALSE, eval = FALSE}
flights <- read.csv("nycflights13/flights.csv")
library("dplyr")
library("lubridate")
flights$dep_datetime <- sprintf("%s-%02d-%02d %02d:%02d",
                                flights$year,
                                flights$month,
                                flights$day, 
                                flights$hour,
                                flights$minute) %>% 
    lubridate::ymd_hm()
flights_sub <- flights %>% 
    filter(dep_datetime < lubridate::ymd_hm("2013-02-28 05:53"))
nrow(flights_sub)
```
```{r}
flights <- read.csv("nycflights13/flights.csv")
library(dplyr)
library(lubridate)
flights_sub <- flights %>% 
    filter(year == 2013 & month == 2 & day == 28 & dep_time < 553)
sprintf("the selected data.frame has %s rows and %s columns\n", 
        nrow(flights_sub), ncol(flights_sub)) %>% 
    cat()
```

b)  Merge or join the subtable from a. `flights` data, to produce a result that includes:  
   *  Departure time
   *  Carrier (two digit code, from `carrier`)
   *  Flight number
   *  Destination airport name (hint: you will need to get this from the `airports` table)  


```{r}
airports <- read.csv("nycflights13/airports.csv")
tmp <- merge(flights_sub, airports, by.x = "dest", by.y = "faa", all.x = TRUE)
answer2 <- select(tmp, c("dep_time", "carrier", "flight", "name"))
print(answer2)

```

c)  For every airline that had flights in the `flights` data compute the average age of the planes it flew from the entire dataset.  Age here will be defined as 2013 minus the `year` variable from the `planes` data.  Hint: This involves a join operation on `tailnum`, but also a grouped mean to compute the age (and subtracting 2013, which you can do before or after the computation of the mean).


```{r}
## your code
planes <- read.csv("nycflights13/planes.csv")

tmp <- flights %>% 
  filter(!duplicated(tailnum)) %>% 
  select(c("tailnum", "carrier") ) %>% 
  left_join(planes, by = "tailnum")

airlines <- read.csv("nycflights13/airlines.csv")

tmp %>% group_by(carrier) %>% 
  summarize(mean_age = mean(2013 - year, na.rm = TRUE)) %>%
  left_join(airlines, by = "carrier") %>% 
  select(c("name", "mean_age"))
```

### 3. Working with SQL

a)  Create a relational dataset in SQLite using the `.csv` data found [here](nycflights13/).  Name each table so that it matches the base filenames of the input data.  

```{r}
library("RSQLite")
mydb <- dbConnect(SQLite(), "")

dbWriteTable(mydb, "flights", read.csv("nycflights13/flights.csv"))
dbWriteTable(mydb, "airlines", read.csv("nycflights13/airlines.csv"))
dbWriteTable(mydb, "airports", read.csv("nycflights13/airports.csv"))
dbWriteTable(mydb, "weather", read.csv("nycflights13/weather.csv"))
dbWriteTable(mydb, "planes", read.csv("nycflights13/planes.csv"))
```

b)  Replicate 2b above using an SQL query, including both the command and the output.

```{r}
# dep_datetime is stored as the epoch second
# calculate the epoch sec with as.integer(lubridate::ymd_hm())
answer3b <- dbGetQuery(mydb, "SELECT dep_time, carrier, flight, name FROM 
            flights AS fl 
            LEFT JOIN airports AS ap ON
            fl.dest = ap.faa
            WHERE fl.year = 2013 AND month = 2 AND day = 28 AND dep_time < 553")
print(answer3b)
```

c)  Replicate 2c above using an SQL query, including both the command and the output.

```{r}
tmp <- dbGetQuery(mydb, "SELECT flights.carrier,
                  2013 - AVG(planes.year) FROM 
                  (SELECT DISTINCT tailnum, carrier FROM flights) AS flights 
                  LEFT JOIN planes ON flights.tailnum = planes.tailnum
                  GROUP BY flights.carrier")
tmp
```
```{r eval = FALSE, echo = FALSE}
tmp2 <- dbGetQuery(mydb, "SELECT flights.carrier, flights.tailnum, 
                  2013 - planes.year FROM planes 
                  LEFT JOIN (SELECT DISTINCT tailnum, carrier FROM flights) as flights 
                  ON flights.tailnum = planes.tailnum
                  ")
tmp2
```