Databases with R (part 3)

.title[
# Databases with R (part 3)
]
.subtitle[
## <a href="https://pos.it/databases-24" class="uri">https://pos.it/databases-24</a>
]
.author[
### Kirill Müller
]
.author[
### cynkra GmbH
]
.date[
### August 12, 2024
]

---

.fansi.fansi-error {
    color: #D70040;
}

pre {
 white-space: pre-wrap;       /* css-3 */
 white-space: -moz-pre-wrap;  /* Mozilla, since 1999 */
 white-space: -pre-wrap;      /* Opera 4-6 */
 white-space: -o-pre-wrap;    /* Opera 7 */
 word-wrap: break-word;       /* Internet Explorer 5.5+ */
 margin-bottom: 0px;
}

.reg-margin-bottom {
  margin-bottom: 25px;
}

.remark-slide-content {
  padding-top: 0px;
  padding-bottom: 0px;
}
.remark-slide-scaler {
  overflow-y: auto;
}

.font17 {
    font-size: 17px;
}
.font14 {
    font-size: 14px;
}

.script-number-font {
  color: inherit !important;
  opacity: 0.9 !important;
  transform: scale(1.8);
  transform-origin: top left;
}

.script-number {
  color: inherit !important;
  opacity: 0.9 !important;
  position: absolute;
  top: 12px;
  right: 20px;
  transform: scale(1.8);
  transform-origin: top right;
}

.exercise-timer {
  position: fixed;
  bottom: 12px;
  left: 20px;
  transform: scale(1.8);
  transform-origin: bottom left;
}

.exercise-timer img {
  max-width: none; /* Prevent the image from being constrained by the container */
}

</style>

# Learning goals

---

---

background-image: url("data:image/png;base64,#images/31.webp")
background-size: 40%
background-position: 100% 100%

# Extract, Transform, Load

<div class="script-number-font"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_31.R"><title>Script: db_31.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_31.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_31.R</text></g></svg></div>

``` r
library(tidyverse)
library(DBI)
```
]

---

background-image: url("data:image/png;base64,#images/31-frame.webp")
background-size: 40%
background-position: 100% 100%

# Extract, Transform, Load

``` r
library(tidyverse)
library(DBI)
```
]

---

# Extract: Raw data

From arbitrary source: CSV, Excel, web API, other database, ...

``` r
pixar_films_raw <- pixarfilms::pixar_films
pixar_films_raw
```

<PRE class="fansi fansi-output"><CODE># A tibble: 27 × 5
 number film release_date run_time film_rating
 &lt;chr&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; 
1 1 Toy Story 1995-11-22 81 G 
2 2 A Bug&#039;s Life 1998-11-25 95 G 
3 3 Toy Story 2 1999-11-24 92 G 
4 4 Monsters, Inc. 2001-11-02 92 G 
5 5 Finding Nemo 2003-05-30 100 G 
6 6 The Incredibles 2004-11-05 115 PG 
# ℹ 21 more rows
</CODE></PRE>

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_31.R"><title>Script: db_31.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_31.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_31.R</text></g></svg></div>

---

background-image: url("data:image/png;base64,#images/magic-hat-and-wand-nath-r.svg")
background-size: 25%
background-position: 100% 100%

# Transform: Derived data

- Fix type of `number` column
- Extract `franchise` and `sequel` columns

``` r
pixar_films_clean
```

<PRE class="fansi fansi-output"><CODE># A tibble: 27 × 7
 number film franchise sequel release_date run_time film_rating
 &lt;int&gt; &lt;chr&gt; &lt;chr&gt; &lt;int&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; 
1 1 Toy Story Toy Story 1 1995-11-22 81 G 
2 2 A Bug&#039;s Life A Bug&#039;s Life NA 1998-11-25 95 G 
3 3 Toy Story 2 Toy Story 2 1999-11-24 92 G 
4 4 Monsters, Inc. Monsters, Inc. NA 2001-11-02 92 G 
5 5 Finding Nemo Finding Nemo NA 2003-05-30 100 G 
6 6 The Incredibles The Incredibles NA 2004-11-05 115 PG 
# ℹ 21 more rows
</CODE></PRE>

---

background-image: url("data:image/png;base64,#images/magic-hat-and-wand-nath-r.svg")
background-size: 25%
background-position: 100% 100%

# Create target database

Ask your DBA!

---

# Load: Write table to the database

``` r
con_rw <- dbConnect(duckdb::duckdb(), dbdir = "pixar.duckdb")
dbWriteTable(con_rw, "pixar_films", pixar_films_clean)
dbExecute(con_rw, "CREATE UNIQUE INDEX pixarfilms_pk ON pixar_films (film)")
```

```
[1] 0
```

``` r
nrow(dbReadTable(con_rw, "pixar_films"))
```

```
[1] 27
```

``` r
dbDisconnect(con_rw)
```

## Caveat: schemas, catalogs, ...

```r
dbWriteTable(con_rw, SQL("datasets.movies.pixar_films"), pixar_films_clean)
```

---

# Reload: Overwrite table in the database

``` r
con_rw <- dbConnect(duckdb::duckdb(), dbdir = "pixar.duckdb")
dbExecute(con_rw, "TRUNCATE TABLE pixar_films")
```

```
[1] 27
```

``` r
dbAppendTable(con_rw, "pixar_films", pixar_films_clean)
nrow(dbReadTable(con_rw, "pixar_films"))
```

```
[1] 27
```

``` r
dbDisconnect(con_rw)
```

---

# Consume: share the file, open it

``` r
con_ro <- dbConnect(duckdb::duckdb(), dbdir = "pixar.duckdb", read_only = TRUE)
my_pixar_films <- tbl(con_ro, "pixar_films")
my_pixar_films
```

<PRE class="fansi fansi-output"><CODE># Source: table&lt;pixar_films&gt; [?? x 7]
# Database: DuckDB v1.0.0
 number film franchise sequel release_date run_time film_rating
 &lt;int&gt; &lt;chr&gt; &lt;chr&gt; &lt;int&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; 
1 1 Toy Story Toy Story 1 1995-11-22 81 G 
2 2 A Bug&#039;s Life A Bug&#039;s Life NA 1998-11-25 95 G 
3 3 Toy Story 2 Toy Story 2 1999-11-24 92 G 
4 4 Monsters, Inc. Monsters, Inc. NA 2001-11-02 92 G 
5 5 Finding Nemo Finding Nemo NA 2003-05-30 100 G 
6 6 The Incredibles The Incredibles NA 2004-11-05 115 PG 
# ℹ more rows
</CODE></PRE>

---

background-image: url("data:image/png;base64,#images/31-frame.webp")
background-size: 40%
background-position: 100% 100%

# Exercises: Extract, Transform, Load

1. Adapt the ETL workflow to convert the `run_time` column to a duration.
   - Hint: Use `mutate()` with `hms::hms(minutes = ...)` .
2. Re-run the workflow.

<div class="exercise-timer"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="84" height="20" role="img" aria-label="Timer: 05:00"><title>Timer: 05:00</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="84" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="41" height="20" fill="#555"/><rect x="41" width="43" height="20" fill="#007ec6"/><rect width="84" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="215" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="310">Timer</text><text x="215" y="140" transform="scale(.1)" fill="#fff" textLength="310">Timer</text><text aria-hidden="true" x="615" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">05:00</text><text x="615" y="140" transform="scale(.1)" fill="#fff" textLength="330">05:00</text></g></svg></div>

---

# Learning goals

---

background-size: 40%
background-position: 100% 100%

# Remote

<div class="script-number-font"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_32.R"><title>Script: db_32.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_32.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_32.R</text></g></svg></div>

``` r
library(DBI)
library(dm)
```
]

---

# <https://relational.fel.cvut.cz/dataset/CORA>

---

# Connection arguments

``` r
con <- dbConnect(
 RMariaDB::MariaDB(),
 dbname = "CORA",
 username = "guest",
 password = "ctu-relational",
 host = "relational.fel.cvut.cz"
)
con
```

```
<MariaDBConnection>
 Connection: guest@relational.fel.cvut.cz<CORA>[18932] via TCP/IP
```

``` r
dbListTables(con)
```

```
[1] "paper"   "content" "cites"  
```

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_32.R"><title>Script: db_32.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_32.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_32.R</text></g></svg></div>

---

# Connection arguments

Ask your colleagues or your DBA!

## Documentation

<https://dbi.r-dbi.org/reference/dbConnect.html>

## Avoid hard-coded credentials

```r
con <- dbConnect(
 RMariaDB::MariaDB(),
 dbname = "CORA",
 username = "guest",
 password = keyring::key_get(...),
 host = "relational.fel.cvut.cz"
)
```

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_32.R"><title>Script: db_32.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_32.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_32.R</text></g></svg></div>

---

# Handling many tables with dm

``` r
dm <- dm_from_con(con)
```

```
Keys could not be queried.
```

``` r
dm
```

<PRE class="fansi fansi-output"><CODE>── Table source ──────────────────────────────────────────────────────
src: mysql [guest@relational.fel.cvut.cz:3306/CORA]
── Metadata ──────────────────────────────────────────────────────────
Tables: `paper`, `content`, `cites`
Columns: 6
Primary keys: 0
Foreign keys: 0
</CODE></PRE>

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_32.R"><title>Script: db_32.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_32.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_32.R</text></g></svg></div>

---

# Handling many tables with dm

``` r
dm |>
  dm_nrow()
```

```
  paper content   cites 
   2708   49216    5429 
```
]

``` r
dm$paper
```

<PRE class="fansi fansi-output"><CODE># Source: table&lt;`CORA`.`paper`&gt; [?? x 2]
# Database: mysql [guest@relational.fel.cvut.cz:3306/CORA]
 paper_id class_label 
 &lt;int&gt; &lt;chr&gt; 
1 35 Genetic_Algorithms 
2 40 Genetic_Algorithms 
3 114 Reinforcement_Learning
4 117 Reinforcement_Learning
5 128 Reinforcement_Learning
6 130 Reinforcement_Learning
# ℹ more rows
</CODE></PRE>
]

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_32.R"><title>Script: db_32.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_32.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_32.R</text></g></svg></div>

---

background-size: 40%
background-position: 100% 100%

# Exercises: Remote

.pull-left[
1. Find another database on <https://relational.fel.cvut.cz/> to connect to.
2. Connect to that database.
3. Explore the tables of that database.

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_32.R"><title>Script: db_32.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_32.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_32.R</text></g></svg></div>

---

# Learning goals

---

background-image: url("data:image/png;base64,#images/33.webp")
background-size: 40%
background-position: 100% 100%

# Data model basics

.pull-left[
- Compound object for multiple tables
- Keys, relationships, constraints
- Zooming
- Flattening

<div class="script-number-font"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_33.R"><title>Script: db_33.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_33.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_33.R</text></g></svg></div>

``` r
library(tidyverse)
library(dm)
```
]

---

background-image: url("data:image/png;base64,#images/33-frame.webp")
background-size: 40%
background-position: 100% 100%

# Data model basics

.pull-left[
- Compound object for multiple tables
- Keys, relationships, constraints
- Zooming
- Flattening

<div class="script-number-font"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_33.R"><title>Script: db_33.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_33.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_33.R</text></g></svg></div>

``` r
library(tidyverse)
library(dm)
```
]

---

# Data model objects

``` r
pixar_dm <- dm_pixarfilms()
pixar_dm
```

<PRE class="fansi fansi-output"><CODE>── Metadata ────────────────────────
Tables: `pixar_films`, `pixar_people`, `academy`, `box_office`, `genres`, `public_response`
Columns: 23
Primary keys: 5
Foreign keys: 5
</CODE></PRE>

``` r
pixar_dm |>
  dm_draw()
```
]

.pull-right[


<svg width="315pt" height="330pt"
 viewBox="-100.00 0.00 315.00 330.00" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink">
<g id="graph0" class="graph" transform="scale(1 1) rotate(0) translate(4 326)">
<title>%0</title>
<g id="a_graph0"><a xlink:title="Data Model">
<polygon fill="#ffffff" stroke="transparent" points="-4,4 -4,-326 211,-326 211,4 -4,4"/>
</a>
</g>

<g id="node1" class="node">
<title>academy</title>
<polygon fill="#ed7d31" stroke="transparent" points="1.5,-301 1.5,-321 100.5,-321 100.5,-301 1.5,-301"/>
<text text-anchor="start" x="26.1255" y="-306.4" font-family="Times,serif" font-size="14.00" fill="#ffffff">academy</text>
<polygon fill="#fbe5d5" stroke="transparent" points="1.5,-281 1.5,-301 100.5,-301 100.5,-281 1.5,-281"/>
<text text-anchor="start" x="3.5" y="-286.4" font-family="Times,serif" font-size="14.00" fill="#444444">film</text>
<polygon fill="#fbe5d5" stroke="transparent" points="1.5,-261 1.5,-281 100.5,-281 100.5,-261 1.5,-261"/>
<text text-anchor="start" x="3.183" y="-267.4" font-family="Times,serif" text-decoration="underline" font-size="14.00" fill="#444444">film, award_type</text>
<polygon fill="none" stroke="#9e5320" stroke-opacity="0.666667" points="0,-260 0,-322 101,-322 101,-260 0,-260"/>
</g>

<g id="node4" class="node">
<title>pixar_films</title>
<polygon fill="#5b9bd5" stroke="transparent" points="138,-161 138,-181 206,-181 206,-161 138,-161"/>
<text text-anchor="start" x="139.7286" y="-166.4" font-family="Times,serif" font-size="14.00" fill="#ffffff">pixar_films</text>
<polygon fill="#deebf6" stroke="transparent" points="138,-141 138,-161 206,-161 206,-141 138,-141"/>
<text text-anchor="start" x="140" y="-147.4" font-family="Times,serif" text-decoration="underline" font-size="14.00" fill="#444444">film</text>
<polygon fill="none" stroke="#3c678e" stroke-opacity="0.666667" points="137,-140 137,-182 207,-182 207,-140 137,-140"/>
</g>

<g id="edge2" class="edge">
<title>academy:film&#45;&gt;pixar_films:film</title>
<path fill="none" stroke="#555555" d="M100.5,-291C161.0155,-291 84.073,-167.4399 127.9432,-152.4775"/>
<polygon fill="#555555" stroke="#555555" points="128.615,-155.9165 138,-151 127.5974,-148.9909 128.615,-155.9165"/>
</g>

<g id="node2" class="node">
<title>box_office</title>
<polygon fill="#ed7d31" stroke="transparent" points="18.5,-221 18.5,-241 82.5,-241 82.5,-221 18.5,-221"/>
<text text-anchor="start" x="20.1795" y="-226.4" font-family="Times,serif" font-size="14.00" fill="#ffffff">box_office</text>
<polygon fill="#fbe5d5" stroke="transparent" points="18.5,-201 18.5,-221 82.5,-221 82.5,-201 18.5,-201"/>
<text text-anchor="start" x="20.5" y="-207.4" font-family="Times,serif" text-decoration="underline" font-size="14.00" fill="#444444">film</text>
<polygon fill="none" stroke="#9e5320" stroke-opacity="0.666667" points="17.5,-200 17.5,-242 83.5,-242 83.5,-200 17.5,-200"/>
</g>

<g id="edge3" class="edge">
<title>box_office:film&#45;&gt;pixar_films:film</title>
<path fill="none" stroke="#555555" d="M82.5,-211C114.9945,-211 104.6973,-162.9888 128.1654,-152.8615"/>
<polygon fill="#555555" stroke="#555555" points="128.8254,-156.2988 138,-151 127.5235,-149.4209 128.8254,-156.2988"/>
</g>

<g id="node3" class="node">
<title>genres</title>
<polygon fill="#ed7d31" stroke="transparent" points="18.5,-161 18.5,-181 83.5,-181 83.5,-161 18.5,-161"/>
<text text-anchor="start" x="32.7328" y="-166.4" font-family="Times,serif" font-size="14.00" fill="#ffffff">genres</text>
<polygon fill="#fbe5d5" stroke="transparent" points="18.5,-141 18.5,-161 83.5,-161 83.5,-141 18.5,-141"/>
<text text-anchor="start" x="20.5" y="-146.4" font-family="Times,serif" font-size="14.00" fill="#444444">film</text>
<polygon fill="#fbe5d5" stroke="transparent" points="18.5,-121 18.5,-141 83.5,-141 83.5,-121 18.5,-121"/>
<text text-anchor="start" x="20.2889" y="-127.4" font-family="Times,serif" text-decoration="underline" font-size="14.00" fill="#444444">film, genre</text>
<polygon fill="none" stroke="#9e5320" stroke-opacity="0.666667" points="17,-120 17,-182 84,-182 84,-120 17,-120"/>
</g>

<g id="edge4" class="edge">
<title>genres:film&#45;&gt;pixar_films:film</title>
<path fill="none" stroke="#555555" d="M83.5,-151C103.9375,-151 111.4419,-151 127.8378,-151"/>
<polygon fill="#555555" stroke="#555555" points="128,-154.5001 138,-151 128,-147.5001 128,-154.5001"/>
</g>

<g id="node5" class="node">
<title>pixar_people</title>
<polygon fill="#70ad47" stroke="transparent" points="12.5,-81 12.5,-101 89.5,-101 89.5,-81 12.5,-81"/>
<text text-anchor="start" x="14.4572" y="-86.4" font-family="Times,serif" font-size="14.00" fill="#ffffff">pixar_people</text>
<polygon fill="#e2eeda" stroke="transparent" points="12.5,-61 12.5,-81 89.5,-81 89.5,-61 12.5,-61"/>
<text text-anchor="start" x="14.5" y="-66.4" font-family="Times,serif" font-size="14.00" fill="#444444">film</text>
<polygon fill="none" stroke="#4a732f" stroke-opacity="0.666667" points="11,-60 11,-102 90,-102 90,-60 11,-60"/>
</g>

<g id="edge1" class="edge">
<title>pixar_people:film&#45;&gt;pixar_films:film</title>
<path fill="none" stroke="#555555" d="M89.5,-71C127.1813,-71 102.2491,-136.7031 128.1904,-149.0225"/>
<polygon fill="#555555" stroke="#555555" points="127.5055,-152.4547 138,-151 128.8889,-145.5928 127.5055,-152.4547"/>
</g>

<g id="node6" class="node">
<title>public_response</title>
<polygon fill="#ed7d31" stroke="transparent" points="3.5,-21 3.5,-41 97.5,-41 97.5,-21 3.5,-21"/>
<text text-anchor="start" x="5.0126" y="-26.4" font-family="Times,serif" font-size="14.00" fill="#ffffff">public_response</text>
<polygon fill="#fbe5d5" stroke="transparent" points="3.5,-1 3.5,-21 97.5,-21 97.5,-1 3.5,-1"/>
<text text-anchor="start" x="5.5" y="-7.4" font-family="Times,serif" text-decoration="underline" font-size="14.00" fill="#444444">film</text>
<polygon fill="none" stroke="#9e5320" stroke-opacity="0.666667" points="2.5,0 2.5,-42 98.5,-42 98.5,0 2.5,0"/>
</g>

<g id="edge5" class="edge">
<title>public_response:film&#45;&gt;pixar_films:film</title>
<path fill="none" stroke="#555555" d="M97.5,-11C158.3517,-11 83.4456,-134.5601 127.8579,-149.5225"/>
<polygon fill="#555555" stroke="#555555" points="127.5999,-153.0217 138,-151 128.6091,-146.0949 127.5999,-153.0217"/>
</g>
</g>
</svg>

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_33.R"><title>Script: db_33.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_33.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_33.R</text></g></svg></div>

---

# Data model objects

Use like a named list.

``` r
names(pixar_dm)
```

```
[1] "pixar_films"     "pixar_people"    "academy"         "box_office"     
[5] "genres"          "public_response"
```

``` r
pixar_dm$pixar_films
```

<PRE class="fansi fansi-output"><CODE># A tibble: 27 × 5
 number film release_…¹ run_t…² film_…³
 &lt;chr&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; 
1 1 Toy Story 1995-11-22 81 G 
2 2 A Bug&#039;s L… 1998-11-25 95 G 
3 3 Toy Story… 1999-11-24 92 G 
4 4 Monsters,… 2001-11-02 92 G 
5 5 Finding N… 2003-05-30 100 G 
6 6 The Incre… 2004-11-05 115 PG 
# ℹ 21 more rows
# ℹ abbreviated names: ¹release_date, …
</CODE></PRE>
]

``` r
pixar_dm$academy
```

<PRE class="fansi fansi-output"><CODE># A tibble: 80 × 3
 film award_type status 
 &lt;chr&gt; &lt;chr&gt; &lt;chr&gt; 
1 Toy Story Animated Feature Award not yet…
2 Toy Story Original Screenplay Nominated 
3 Toy Story Adapted Screenplay Ineligible 
4 Toy Story Original Score Nominated 
5 Toy Story Original Song Nominated 
6 Toy Story Other Won Special A…
# ℹ 74 more rows
</CODE></PRE>
]

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_33.R"><title>Script: db_33.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_33.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_33.R</text></g></svg></div>

---

# Primary keys

Column(s) that uniquely identify rows in a table.

``` r
any(duplicated(pixar_dm$pixar_films$film))
```

```
[1] FALSE
```

``` r
check_key(pixar_dm$pixar_films, film)
```
]

``` r
any(duplicated(pixar_dm$academy[c("film", "award_type")]))
```

```
[1] FALSE
```

``` r
check_key(pixar_dm$academy, film, award_type)
check_key(pixar_dm$academy, film)
```

<PRE class="fansi fansi-error"><CODE>Error in `abort_not_unique_key()`:
! (`film`) not a unique key of `pixar_dm$academy`.
</CODE></PRE>
]

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_33.R"><title>Script: db_33.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_33.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_33.R</text></g></svg></div>

---

# Foreign keys

``` r
all(pixar_dm$academy$film %in% pixar_dm$pixar_films$film)
```

```
[1] TRUE
```

``` r
check_subset(
  pixar_dm$academy, film, 
  pixar_dm$pixar_films, film)
```
]

``` r
check_subset(
  pixar_dm$pixar_films, 
  film, pixar_dm$academy, film)
```

<PRE class="fansi fansi-output"><CODE># A tibble: 4 × 1
 film 
 &lt;chr&gt; 
1 Luca 
2 Turning Red
3 Lightyear 
4 NA 
</CODE></PRE><PRE class="fansi fansi-error"><CODE>Error in `abort_not_subset_of()`:
! Column (`film`) of table `pixar_dm$pixar_films` contains values (see examples above) that are not present in column (`film`) of table `pixar_dm$academy`.
</CODE></PRE>
]

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_33.R"><title>Script: db_33.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_33.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_33.R</text></g></svg></div>

---

# Constraints

Properties of primary and foreign keys can be checked.

``` r
pixar_dm |>
  dm_examine_constraints()
```

<PRE class="fansi fansi-message"><CODE>! Unsatisfied constraints:
</CODE></PRE><PRE class="fansi fansi-output"><CODE>• Table `pixar_films`: primary key `film`: has 1 missing values
</CODE></PRE>

``` r
dm_pixarfilms(consistent = TRUE) |>
  dm_examine_constraints()
```

<PRE class="fansi fansi-message"><CODE>ℹ All constraints satisfied.
</CODE></PRE>

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_33.R"><title>Script: db_33.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_33.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_33.R</text></g></svg></div>

---

# Constraints

An example from another dataset.

``` r
dm_nycflights13() |>
  dm_examine_constraints()
```

<PRE class="fansi fansi-message"><CODE>! Unsatisfied constraints:
</CODE></PRE><PRE class="fansi fansi-output"><CODE>• Table `flights`: foreign key `tailnum` into table `planes`: values of `flights$tailnum` not in `planes$tailnum`: N725MQ (6), N537MQ (5), N722MQ (5), N730MQ (5), N736MQ (5), …
</CODE></PRE>

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_33.R"><title>Script: db_33.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_33.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_33.R</text></g></svg></div>

---

# Zooming

Focusing on one table in a dm object.
Allows applying data transformations on that table inside a dm object.

``` r
pixar_dm |>
  dm_zoom_to(academy)
```

<PRE class="fansi fansi-output"><CODE># Zoomed table: academy
# A tibble: 80 × 3
 film award_type status 
 &lt;chr&gt; &lt;chr&gt; &lt;chr&gt; 
1 Toy Story Animated Feature Award not yet…
2 Toy Story Original Screenplay Nominated 
3 Toy Story Adapted Screenplay Ineligible 
4 Toy Story Original Score Nominated 
5 Toy Story Original Song Nominated 
6 Toy Story Other Won Special A…
# ℹ 74 more rows
</CODE></PRE>
]

``` r
pixar_dm |>
  dm_zoom_to(academy) |>
  left_join(
    pixar_films, select = release_date)
```

<PRE class="fansi fansi-output"><CODE># Zoomed table: academy
# A tibble: 80 × 4
 film award_type status release_…¹
 &lt;chr&gt; &lt;chr&gt; &lt;chr&gt; &lt;date&gt; 
1 Toy Story Animated Feature Award… 1995-11-22
2 Toy Story Original Screen… Nomin… 1995-11-22
3 Toy Story Adapted Screenp… Ineli… 1995-11-22
4 Toy Story Original Score Nomin… 1995-11-22
5 Toy Story Original Song Nomin… 1995-11-22
6 Toy Story Other Won S… 1995-11-22
# ℹ 74 more rows
# ℹ abbreviated name: ¹release_date
</CODE></PRE>
]

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_33.R"><title>Script: db_33.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_33.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_33.R</text></g></svg></div>

---

# Flattening

Join a table to all related tables.

``` r
pixar_dm |>
  dm_flatten_to_tbl(academy)
```

<PRE class="fansi fansi-output"><CODE># A tibble: 80 × 7
 film award_type status number release_…¹ run_t…² film_…³
 &lt;chr&gt; &lt;chr&gt; &lt;chr&gt; &lt;chr&gt; &lt;date&gt; &lt;dbl&gt; &lt;chr&gt; 
1 Toy Story Animated Feature Award not yet introduced 1 1995-11-22 81 G 
2 Toy Story Original Screenplay Nominated 1 1995-11-22 81 G 
3 Toy Story Adapted Screenplay Ineligible 1 1995-11-22 81 G 
4 Toy Story Original Score Nominated 1 1995-11-22 81 G 
5 Toy Story Original Song Nominated 1 1995-11-22 81 G 
6 Toy Story Other Won Special Achievement 1 1995-11-22 81 G 
# ℹ 74 more rows
# ℹ abbreviated names: ¹release_date, ²run_time, ³film_rating
</CODE></PRE>

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_33.R"><title>Script: db_33.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_33.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_33.R</text></g></svg></div>

---

# Flattening

``` r
dm_nycflights13() |>
  dm_select(weather, -year, -month, -day, -hour) |>
  dm_flatten_to_tbl(flights)
```

```
Renaming ambiguous columns: %>%
  dm_rename(flights, year.flights = year) %>%
  dm_rename(airlines, name.airlines = name) %>%
  dm_rename(airports, name.airports = name) %>%
  dm_rename(planes, year.planes = year)
```

<PRE class="fansi fansi-output"><CODE># A tibble: 1,761 × 44
 year.flights month day dep_time sched…¹ dep_d…² arr_t…³ sched…⁴ arr_d…⁵ carrier flight
 &lt;int&gt; &lt;int&gt; &lt;int&gt; &lt;int&gt; &lt;int&gt; &lt;dbl&gt; &lt;int&gt; &lt;int&gt; &lt;dbl&gt; &lt;chr&gt; &lt;int&gt;
1 2013 1 10 3 2359 4 426 437 -11 B6 727
2 2013 1 10 16 2359 17 447 444 3 B6 739
3 2013 1 10 450 500 -10 634 648 -14 US 1117
4 2013 1 10 520 525 -5 813 820 -7 UA 1018
5 2013 1 10 530 530 0 824 829 -5 UA 404
6 2013 1 10 531 540 -9 832 850 -18 AA 1141
# ℹ 1,755 more rows
# ℹ abbreviated names: ¹sched_dep_time, ²dep_delay, ³arr_time, ⁴sched_arr_time, …
</CODE></PRE>

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_33.R"><title>Script: db_33.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_33.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_33.R</text></g></svg></div>

---

# Copying to the database

``` r
con_rw <- dbConnect(
 duckdb::duckdb(), 
 dbdir = "pixar-copy.duckdb")

dm_pixarfilms_db <- copy_dm_to(
 con_rw,
 dm_pixarfilms(),
 set_key_constraints = FALSE,
 temporary = FALSE
)
```

```
Note: method with signature 'DBIConnection#Id' chosen for function 'dbExistsTable',
 target signature 'duckdb_connection#Id'.
 "duckdb_connection#ANY" would also be valid
```
]

``` r
dm_pixarfilms_db
```

<PRE class="fansi fansi-output"><CODE>── Table source ────────────────────
src: DuckDB v1.0.0
── Metadata ────────────────────────
Tables: `pixar_films`, `pixar_people`, `academy`, `box_office`, `genres`, `public_response`
Columns: 23
Primary keys: 5
Foreign keys: 5
</CODE></PRE>

``` r
dbDisconnect(con_rw)
```

Append with `dm_rows_append()`
]

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_33.R"><title>Script: db_33.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_33.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_33.R</text></g></svg></div>

---

# Exercises: Data model basics

.pull-left[
1. Explore <https://dm.cynkra.com> and the built-in data models
 `dm_nycflights13()` and `dm_pixarfilms()`
2. Given the table structure at the right, create a dm object.
 - Hint: Use the `dm()` function to create a dm object from scratch
3. Draw the dm object
4. Color the tables
5. Deploy the data model to a DuckDB database
]

.pull-right[
<img src="data:image/png;base64,#images/conf.svg" width="100%" height="100%" style="display: block; margin: auto;" />
]

<div class="exercise-timer"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="84" height="20" role="img" aria-label="Timer: 15:00"><title>Timer: 15:00</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="84" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="41" height="20" fill="#555"/><rect x="41" width="43" height="20" fill="#007ec6"/><rect width="84" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="215" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="310">Timer</text><text x="215" y="140" transform="scale(.1)" fill="#fff" textLength="310">Timer</text><text aria-hidden="true" x="615" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">15:00</text><text x="615" y="140" transform="scale(.1)" fill="#fff" textLength="330">15:00</text></g></svg></div>

<div class="script-number"><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" width="100" height="20" role="img" aria-label="Script: db_33.R"><title>Script: db_33.R</title><linearGradient id="s" x2="0" y2="100%"><stop offset="0" stop-color="#bbb" stop-opacity=".1"/><stop offset="1" stop-opacity=".1"/></linearGradient><clipPath id="r"><rect width="100" height="20" rx="3" fill="#fff"/></clipPath><g clip-path="url(#r)"><rect width="43" height="20" fill="#555"/><rect x="43" width="57" height="20" fill="#fe7d37"/><rect width="100" height="20" fill="url(#s)"/></g><g fill="#fff" text-anchor="middle" font-family="Verdana,Geneva,DejaVu Sans,sans-serif" text-rendering="geometricPrecision" font-size="110"><text aria-hidden="true" x="225" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="330">Script</text><text x="225" y="140" transform="scale(.1)" fill="#fff" textLength="330">Script</text><text aria-hidden="true" x="705" y="150" fill="#010101" fill-opacity=".3" transform="scale(.1)" textLength="470">db_33.R</text><text x="705" y="140" transform="scale(.1)" fill="#fff" textLength="470">db_33.R</text></g></svg></div>

---

# Learning done!

---

# Tools learned

---

# https://posit-conf-2024.github.io/databases/part-4.html