---
subtitle: "LSE ME204 Week 03 Day 01 - Lecture Material"
title: "Intro to databases"
author: "Dr. Jon Cardoso-Silva"
date: "2023-07-24"
date-format: "DD MMMM YYYY"
format: 
    html:
        df-print: kable
        self-contained: true
        link-external-icon: true
        toc: true
        theme: simplex
        code-fold: true
        page-layout: full
        fig-width: 10
        fig-height: 6
 
---

# ⚙️ Setup

Today, we're diving into a new data structure: databases! We'll use the `dbplyr` package to work with databases in R, so make sure you have it installed. If not, run the following code to install and load the required packages:

```r 
library(DBI)
library(RSQLite)
library(dbplyr)
```

# Part 1. 💽 What is a database?

At its core, a database is a collection of data stored in a computer system. It's like a structured storage space that allows us to perform various data-related operations, including:

- storing
- retrieving
- modifying, and 
- deleting data.

## Relational databases

The most common type of database is a **relational database**, where data is stored in tables that are related to one another. This allows for easy organization and access to data. Additionally, distributed databases can be replicated across multiple points in a network.

A key feature of relational databases is that they are persistent, meaning the data remains even after a computer crash or power outage.

## Database management systems

We use **database management systems** (DBMS) to interact with databases. These systems serve as intermediaries between users and the databases, enabling operations on the data. 

Some popular examples of DBMS include:

- [MySQL](https://www.mysql.com/)
- [PostgreSQL](https://www.postgresql.org/)
- [SQLite](https://www.sqlite.org/index.html)
- [Microsoft SQL Server](https://www.microsoft.com/en-us/sql-server/sql-server-2019)
- [Oracle Database](https://www.oracle.com/database/)
- [IBM DB2](https://www.ibm.com/analytics/db2)

Database administrators (**DBAs**) are responsible for managing databases. They handle tasks like installing or upgrading the DBMS, troubleshooting issues, and monitoring database performance and security. DBAs also contribute to planning and developing the database structure to cater to users' needs. 

## Database structure

A database consists of tables, each containing rows and columns. Every row represents a set of related data, and all rows in a table have the same structure. For instance, a table about people might have rows for each person, with columns for name, address, phone number, etc. 

_Sounds familiar?_ That is because the tidy data principles were inspired by relational databases, and the `tidyverse` embraces this relational approach.

Now, while diving deep into database theory could be a course on its own, we'll focus on hands-on applications. By the end of this session, you'll have a good grasp of using databases in R, even if you don't master all the intricacies. So, let's get started!

# Part 2. 📦 Creating a database

🧑‍🏫 **TEACHING MOMENT**

In this section, I'll demonstrate how one could go about creating a [SQlite]

## Installing SQLite Browser

- Download and install [SQLite Browser](https://sqlitebrowser.org/dl/).

## The data

To illustrate the process of creating a database, I'll use the [IMDb Non-Commercial Datasets](https://developer.imdb.com/non-commercial-datasets/). The IMDb datasets are available for non-commercial and personal use only. (For those who don't know: [IMDb](https://imdb.com) is a popular online database of information related to films, television programs, home videos, video games, and streaming content online.)

The IMDb datasets are available in [TSV](https://en.wikipedia.org/wiki/Tab-separated_values) format, which is a text file format that uses tabs to separate values -- similar to a CSV file, but with tabs instead of commas. You can download all files from [here](https://datasets.imdbws.com/).

If we were to load the TSV files directly into R, we would do something like this:

```r
# Careful, this file is huge 
# (bigger than the Camden crime data we have been exploring) 
# So this code will take a while to load!
title_basics <- readr::read_tsv("https://datasets.imdbws.com/title.basics.tsv.gz")
```

However, I am just using their data as an excuse to show you how to create a database. 

## Creating a schema

Once you have set up a database (check installation instructions for your selected database), you need to create a schema. A schema is a collection of database objects, including **tables**, **views**, **indexes**, etc.

Looking at the documentation of the IMDb datasets, we can see that there are 7 TSV files. Let's focus on the `title.basics.tsv` file, which contains information about titles, including:

| Column name   | Data Type         | Description                                                                              |
|---------------|-------------------|------------------------------------------------------------------------------------------|
| tconst        | (string)          | alphanumeric unique identifier of the title                                              |
| titleType     | (string)          | the type/format of the title (e.g. movie, short, tvseries, tvepisode, video, etc)        |
| primaryTitle  | (string)          | the more popular title / the title used by the filmmakers on promotional materials at the point of release |
| originalTitle | (string)          | original title, in the original language |
| isAdult       | (boolean)         | 0: non-adult title; 1: adult title |
| startYear     | (YYYY)            | represents the release year of a title. In the case of TV Series, it is the series start year |
| endYear       | (YYYY)            | TV Series end year. '\N' for all other title types |
| runtimeMinutes|                   | primary runtime of the title, in minutes |
| genres        | (string array)    | includes up to three genres associated with the title |

Here's the [SQL](https://www.w3schools.com/sql/) command to create a table for this data:

```sql
CREATE TABLE title_basics (
    tconst VARCHAR(255),
    titleType VARCHAR(255),
    primaryTitle VARCHAR(255),
    originalTitle VARCHAR(255),
    isAdult BOOLEAN,
    startYear INTEGER,
    endYear INTEGER,
    runtimeMinutes INTEGER,
    genres VARCHAR(255)
);
```

Translating to the world of data frames and tibbles, this is equivalent to creating an empty tibble with the following columns:

```r
title_basics <- tibble(
    tconst = character(),
    titleType = character(),
    primaryTitle = character(),
    originalTitle = character(),
    isAdult = logical(),
    startYear = integer(),
    endYear = integer(),
    runtimeMinutes = integer(),
    genres = character()
)
```

But there are a few key differences between a tibble and a database table:

- A tibble is stored in memory, while a database table is stored on disk.
- A tibble is ephemeral, while a database table is persistent.
- A tibble is a single object, while a database table is part of a larger database schema.

Also, notice how some data types are different in SQL and R. For instance, `VARCHAR` is equivalent to `character` in R but we can specify the maximum number of characters allowed. 

Data types supported by SQL include:

- `BOOLEAN` (equivalent to `logical` in R)
- `INTEGER` (equivalent to `integer` in R)
- `FLOAT` (equivalent to `double` in R)
- `VARCHAR(N)` (equivalent to `character` in R, but with a maximum of `N` characters)
- `TEXT` (equivalent to `character` in R)
- `DATE` (equivalent to `Date` in R)
- `TIMESTAMP` (equivalent to `POSIXct` in R)

In addition to those, each DBMS has its own set of data types. For instance, PostgreSQL has a `JSON` data type, which is not available in other DBMS.

# Part 3. SQL commands

We saw the `CREATE TABLE` SQL command above. SQL is a language used to interact with databases. It has many commands, including:

- `CREATE TABLE` - creates a new table
- `DROP TABLE` - deletes a table
- `INSERT INTO` - inserts new data into a table
- `UPDATE` - updates existing data in a table
- `DELETE FROM` - deletes existing data from a table
- `SELECT` - extracts data from a database
- `CREATE INDEX` - creates an index (search key)
- `DROP INDEX` - deletes an index
- `GROUP BY` - groups rows that have the same values into summary rows
- `ORDER BY` - sorts the result set in ascending or descending order
- `WHERE` - filters out rows that do not satisfy the specified condition
- `JOIN` - combines rows from two or more tables, based on a related column between them

You will find that many of the data summarisation and manipulation functions you are familiar with in R are also available in SQL. For instance, to get the number of rows in a table, we can perform a `SELECT` with the `COUNT` function:

```sql
SELECT COUNT(*) FROM title_basics;
```

which returns

```text
COUNT(*)
10023440
```

Want to find out how many unique titles are in the database? We can use the `DISTINCT` function:

```sql
SELECT COUNT(DISTINCT primaryTitle) FROM title_basics;
```

returning

```text
COUNT(DISTINCT primaryTitle)
4523070
```

To obtain the equivalent of `dplyr::filter()` in SQL, we use the `WHERE` command. For instance, let's count the number of movies released in 2019:

```sql
SELECT COUNT(*) 
    FROM title_basics 
    WHERE titleType = 'movie' AND startYear = 2019;
```

which returns:

```text
COUNT(*)
18697
```

## Indexing

Indexes are used to speed up data retrieval. They are similar to the indexes in books, which allow you to quickly find a specific page. In SQL, indexes are created using the `CREATE INDEX` command.

For example, to create an index for the `tconst` column in the `title_basics` table, we would do:

```sql
CREATE INDEX tconst_index ON title_basics (tconst);
```

Alternatively, if you already know which columns are best suited for indexing, you can create the index when you create the table:

```sql
CREATE TABLE title_basics (
    tconst VARCHAR(255),
    titleType VARCHAR(255),
    primaryTitle VARCHAR(255),
    originalTitle VARCHAR(255),
    isAdult BOOLEAN,
    startYear INTEGER,
    endYear INTEGER,
    runtimeMinutes INTEGER,
    genres VARCHAR(255),
    INDEX tconst_index (tconst)
);
```

How to decide which columns to index? Indexes are most useful for columns that are frequently used in `WHERE` clauses. For instance, if you often filter the `title_basics` table by `tconst`, then it makes sense to create an index for the `tconst` column.

**PRIMARY KEYS**

A primary key is a column that uniquely identifies each row in a table. It is used to ensure data integrity and to make sure that each row in a table is unique. A primary key is created using the `PRIMARY KEY` constraint.

For example, to create a primary key for the `tconst` column in the `title_basics` table, we would do:

```sql
CREATE TABLE title_basics (
    tconst VARCHAR(255) PRIMARY KEY,
    titleType VARCHAR(255),
    primaryTitle VARCHAR(255),
    originalTitle VARCHAR(255),
    isAdult BOOLEAN,
    startYear INTEGER,
    endYear INTEGER,
    runtimeMinutes INTEGER,
    genres VARCHAR(255)
);
```

::: {.callout-important}

A primary key cannot contain NULL values. If you try to insert a NULL value into a primary key column, you will get an error. And each value in a primary key column must be unique. If you try to insert a duplicate value into a primary key column, you will get an error.

:::

# Part 4. 🔌 Connecting to a database via R

Once you have created a database, you can connect to it using R. We'll use the `DBI` package to connect to a database and the `dbplyr` package to interact with it.

## Connecting to a database

To connect to a database, we use the `dbConnect()` function from the `DBI` package. The `dbConnect()` function takes two arguments:

```r
con <- DBI::dbConnect(drv=RSQLite::SQLite(),
                      "imdb.sqlite3")
```

Then, you can list the tables in the database using the `dbListTables()` function:

```r
DBI::dbListTables(con)
```

should return

```text
[1] "title_basics"
```

## How to run a SQL query in R

To run a SQL query in R, use the `dbSendQuery()` function from the `DBI` package. The `dbSendQuery()` function takes two arguments:

- `con` - a database connection object
- `statement` - a SQL statement

```r
my_query <- 
"SELECT startYear, COUNT(*) as n
FROM title_basics
GROUP BY startYear;"

res <- dbSendQuery(con, my_query)
```

If you look at what's in `res`, you'll see that it's a `DBIResult` object:

```r
res
```

```text
<SQLiteResult>
  SQL  SELECT startYear, COUNT(*) as n
FROM title_basics
GROUP BY startYear;
  ROWS Fetched: 0 [incomplete]
       Changed: 0
```

This means that nothing has been fetched from the database yet. To fetch the results, we use the `dbFetch()` function:

```r
dbFetch(res)
```

This will return a `data.frame` with the results of the query:

```text
    startYear       n
1          NA 1347456
2        1874       1
3        1877       4
4        1878       3
5        1881       2
6        1882       2
7        1883       1
8        1884       1
9        1885       1
10       1887      45
...
```

## The `dbplyr` package

The `dbplyr` package provides a set of functions that allow you to interact with databases using the `dplyr` syntax. This makes it easier to work with databases in R. 

You can use the same syntax you've been using for static files, but instead of using `read_csv()` or `read_tsv()`, you use `tbl()`:

```r
# You must have established the connection before (con)
title_basics_tbl <- tbl(con, "title_basics")
```

::: {.callout-important}

Note that the `tbl()` function does not actually read the data into R. It just creates a reference to the table in the database. The data is only read when you perform an action on the table, such as `collect()` or `show_query()`.
:::

Suppose you want to run the same query as before:

```sql
SELECT startYear, COUNT(*) as n
    FROM title_basics
    GROUP BY startYear;
```

You can do it using the `group_by()` and `summarise()` functions:

```r
title_basics_tbl %>%
    group_by(startYear) %>%
    summarise(n = n())
```

You will see the output in the console, but the data is not actually loaded into R. To load the data into R, you need to use the `collect()` function. The following code will load the data into R and save it as a `tibble`:

```r
summary_df <- 
    title_basics_tbl %>%
    group_by(startYear) %>%
    summarise(n = n()) %>%
    collect()
```

Curious to see the SQL query that was generated? Use the `show_query()` function:

```r
title_basics_tbl %>%
    group_by(startYear) %>%
    summarise(n = n()) %>%
    show_query()
```

```text
<SQL>
SELECT `startYear`, COUNT(*) AS `n`
FROM `title_basics`
GROUP BY `startYear`
```

Now what? You can try to practice your SQL skills by translating the following `dplyr` code into SQL.