library(DBI)
library(duckdb)
con <- dbConnect(duckdb::duckdb(), "data/books.duckdb", read_only = TRUE)
authors <- dbReadTable(con, "Authors")
books <- dbReadTable(con, "Books")
borrowers <- dbReadTable(con, "Borrowers")
borrowings <- dbReadTable(con, "Borrowings")14 관계형 데이터베이스 기초 개념, Joins와 two-table verbs
관계형 데이터베이스(Relational Database)는 구조화된 데이터를 저장하는 데이터베이스로 보이지는 않지만 우리는 늘 이것을 사용한다. 데이터베이스 전문가가 될 것은 아니라 할지라도 그 개념을 이해하고, 좀 더 나아가 활용할 수 있다면 좋을 것이다. 또한 이 개념을 이해하면 Tidy data 개념을 이해하는데 큰 도움이 된다(실제로 tidy data의 개념은 관계형 데이터베이스의 개념에서 유래되었다).
관계형 데이터베이스는 테이블(table)의 집합으로 이루어진 데이터베이스이다. 각 테이블은 열(column)과 행(row)으로 이루어져 있으며, 각 열은 특정한 속성을 나타내고, 각 행은 개별 데이터 항목을 나타낸다. 즉, 관계형 데이터베이스의 테이블은 다음과 같은 특징이 있다.
- 테이블은 열(column)과 행(row)으로 이루어져 있다.
- 각 열은 특정한 속성을 나타내고, 각 행은 개별 데이터 항목을 나타낸다.
- 테이블은 키(key)를 통해 연결될 수 있다.
키(key)를 통해 테이블들이 서로 어떤 관계를 가지면서 온전한 데이터베이스를 이루기 때문에, 관계형 데이터베이스라고 하는 것이다.
14.1 관계형 데이터베이스 예제: 눈으로 보는 관계형 데이터베이스
관계형 데이터베이스는 Data Normalization이라는 개념을 통해 데이터의 중복을 최소화하고, 데이터의 일관성을 유지한다.
간단한 도서관 도서 대출 시스템을 관리하는 데이터베이스를 가지고 설명한다(이 자료는 DuckDB: Up and Running (Wei-Meng Lee 저) 책에서 발췌했다).
이 데이터베이스는 4개의 테이블로 구성되어 있다.
-
authors: 저자 정보 -
books: 도서 정보 -
borrowers: 대출자 정보 -
borrowings: 대출 정보
다음 코드는 지금은 이해할 필요가 없는데, 설명을 위해서 가상의 데이터베이스를 만들었고, 이것을 R로 불러오는 코드라고만 이해하면 된다.
먼저 저자 정보이다.
도서 정보이다.
| book_id | title | author_id | genre | publication_year |
|---|---|---|---|---|
| 1 | Pride and Prejudice | 1 | Classic | 1813 |
| 2 | Oliver Twist | 2 | Novel | 1837 |
| 3 | Murder on the Orient Express | 3 | Mystery | 1934 |
| 4 | Harry Potter and the Philosopher’s Stone | 4 | Fantasy | 1997 |
| 5 | The Hobbit | 5 | Fantasy | 1937 |
대출자 정보이다.
| borrower_id | name | member_since | |
|---|---|---|---|
| 1 | John Smith | john.smith@example.com | 2022-01-01 |
| 2 | Emma Johnson | emma.johnson@example.com | 2021-12-15 |
| 3 | Michael Brown | michael.brown@example.com | 2022-02-20 |
| 4 | Sophia Wilson | sophia.wilson@example.com | 2022-03-10 |
| 5 | William Taylor | william.taylor@example.com | 2022-04-05 |
| 6 | Jane Doe | jane.doe@example.com | 2022-03-05 |
대출 정보이다.
| borrowing_id | book_id | borrower_id | borrow_date | return_date | status |
|---|---|---|---|---|---|
| 1 | 1 | 1 | 2022-04-10 | 2022-04-25 | Returned |
| 2 | 3 | 2 | 2022-03-20 | NA | On Loan |
| 3 | 4 | 3 | 2022-04-05 | NA | On Loan |
| 4 | 2 | 4 | 2022-04-15 | NA | On Loan |
| 5 | 5 | 5 | 2022-03-30 | 2022-04-20 | Returned |
| 6 | 1 | 3 | 2022-04-26 | NA | On Loan |
이 데이터베이스는 “대출”이라는 사건을 중심으로 보는 것이 좋다. 어떻게 데이터를 찾아가는지 살펴보자. borrowings 테이블에서 5번 대출 이벤트를 읽어 보자.
| borrowing_id | book_id | borrower_id | borrow_date | return_date | status | |
|---|---|---|---|---|---|---|
| 5 | 5 | 5 | 5 | 2022-03-30 | 2022-04-20 | Returned |
- 이 이벤트는
book_id가5인 도서를 대출한 이벤트이다. - 이 도서를 대출한 사람은
borrower_id가5인 사람이다. - 대출 날짜는
2022-03-30이고 반납 날짜는2022-04-01으로 대출 이벤트가 종결되었다.
이 데이터를 찾아가는 방법은 다음과 같다. books 테이블과 authors 테이블에서 책과 그 저자를 확인할 수 있다. 이런 과정은 모두 look-up 프로세스이다.
| book_id | title | author_id | genre | publication_year | |
|---|---|---|---|---|---|
| 5 | 5 | The Hobbit | 5 | Fantasy | 1937 |
대출자 정보를 확인해 보자. borrowers 테이블에서 대출자 정보를 확인할 수 있다.
| borrower_id | name | member_since | ||
|---|---|---|---|---|
| 5 | 5 | William Taylor | william.taylor@example.com | 2022-04-05 |
이번에는 borrowings 테이블에서 1번 대출 이벤트를 읽어 보자.
| borrowing_id | book_id | borrower_id | borrow_date | return_date | status |
|---|---|---|---|---|---|
| 1 | 1 | 1 | 2022-04-10 | 2022-04-25 | Returned |
- 이 이벤트는
book_id가1인 도서를 대출한 이벤트이다. - 이 도서를 대출한 사람은
borrower_id가1인 사람이다. - 대출 날짜는
2022-01-01이고 반납 날짜는2022-01-02으로 대출 이벤트가 종결되었다.
이 데이터를 찾아가는 방법은 다음과 같다. books 테이블과 authors 테이블에서 책과 그 저자를 확인할 수 있다.
| book_id | title | author_id | genre | publication_year |
|---|---|---|---|---|
| 1 | Pride and Prejudice | 1 | Classic | 1813 |
대출자 정보를 확인해 보자. borrowers 테이블에서 대출자 정보를 확인할 수 있다.
| borrower_id | name | member_since | |
|---|---|---|---|
| 1 | John Smith | john.smith@example.com | 2022-01-01 |
14.2 테이블 사이의 관계를 규정하는 키(Key)
앞서 authors, books, borrowers, borrowings 테이블의 첫 열은 author_id, book_id, borrower_id, borrowing_id이다. 이 열은 각 테이블의 행을 고유하게 식별하는 열이다. 이와 같은 열을 기본 키(primary key)라고 한다. 즉, 기본 키는 하나의 테이블에서 고유한 값을 가지고, 다른 행과 구분된다.
기본 키는 어떤 경우에는 고유한 값을 가지는 하나의 열로 구섣되기도 하고, 또 어떤 경우는 여러 개의 열 값들의 조합으로 구성되기도 한다. 이렇게 여러 개의 값으로 구성된 기본 키를 복합 키(composite key)라고 한다.
외래 키(foreign key)는 다른 테이블의 기본 키를 참조하는 열이다. 예를 들어 books 테이블에서 author_id 열은 authors 테이블의 author_id 열을 참조한다. 이렇게 참조 관계를 가지는 열을 외래 키(foreign key)라고 하고, 우리는 그 값을 따라가서 정보를 확인할 수 있다.
| book_id | title | author_id | genre | publication_year |
|---|---|---|---|---|
| 1 | Pride and Prejudice | 1 | Classic | 1813 |
| 2 | Oliver Twist | 2 | Novel | 1837 |
| 3 | Murder on the Orient Express | 3 | Mystery | 1934 |
| 4 | Harry Potter and the Philosopher’s Stone | 4 | Fantasy | 1997 |
| 5 | The Hobbit | 5 | Fantasy | 1937 |
borrowings 테이블은 book_id 열은 books 테이블의 book_id 열을 참조하고, borrower_id 열은 borrowers 테이블의 borrower_id 열을 참조한다. 이 둘 모두 외래 키이다.
| borrowing_id | book_id | borrower_id | borrow_date | return_date | status |
|---|---|---|---|---|---|
| 1 | 1 | 1 | 2022-04-10 | 2022-04-25 | Returned |
| 2 | 3 | 2 | 2022-03-20 | NA | On Loan |
| 3 | 4 | 3 | 2022-04-05 | NA | On Loan |
| 4 | 2 | 4 | 2022-04-15 | NA | On Loan |
| 5 | 5 | 5 | 2022-03-30 | 2022-04-20 | Returned |
| 6 | 1 | 3 | 2022-04-26 | NA | On Loan |
14.3 JOINS과 dplyr two-table verbs
관계형 데이터베이스는 데이터들이 열결된 테이블들에 흩어져 있게 된다. 따라서 필요한 경우 테이블들을 연결하여 원하는 정보를 조회할 수 있는 기능이 필요하다. 이런 기능을 조인(join)이라고 한다. 테이블을 결합하는 논리에 따라 여러 종류의 조인이 있는데, 가장 많이 사용하는 조인만 정리해 보면 다음과 같다.
- inner join: 두 테이블에서 일치하는 값을 기준으로 결합
- 외부 조인(outer join)
- left (outer) join: 왼쪽 테이블의 모든 데이터를 포함하고, 오른쪽 테이블에서 일치하는 값이 있는 경우 결합
- right (outer) join: 오른쪽 테이블의 모든 데이터를 포함하고, 왼쪽 테이블에서 일치하는 값이 있는 경우 결합
- 이것은 왼쪽 조인의 반대이다.
- full (outer) join: 두 테이블의 데이터를 합치는 것
이제 dplyr 패키지에서 제공하는 two-table verbs(joins) 함수를 살펴보자.
먼저 가장 흔히 사용되는 left (outer) join을 살펴보자. 이 함수는 왼쪽 테이블의 모든 데이터를 포함하고, 오른쪽 테이블에서 일치하는 값이 있는 경우 결합한다. 여기서 왼쪽, 오른쪽이라고 하는 말은 조인할 테이블을 좌, 우로 두는 것을 표현한 것이다. 이 left join은 왼쪽 테이블은 그대로 유지하면서, 오른쪽에 있는 테이블의 데이터를 추가하는 것이다.
조인을 이해할 때는 사용자의 의도를 명확하게 하는 것이 중요하다. 예를 들어 다음은 모든 대출 정보를 조회하는 데, 도서 정보를 추가하고 싶다고 하자. 다음은 모든 대출 정보를 가지고 오고, 거기에 매칭되는 도서 정보를 추가한다.
| borrowing_id | book_id | borrower_id | borrow_date | return_date | status |
|---|---|---|---|---|---|
| 1 | 1 | 1 | 2022-04-10 | 2022-04-25 | Returned |
| 2 | 3 | 2 | 2022-03-20 | NA | On Loan |
| 3 | 4 | 3 | 2022-04-05 | NA | On Loan |
| 4 | 2 | 4 | 2022-04-15 | NA | On Loan |
| 5 | 5 | 5 | 2022-03-30 | 2022-04-20 | Returned |
| 6 | 1 | 3 | 2022-04-26 | NA | On Loan |
| book_id | title | author_id | genre | publication_year |
|---|---|---|---|---|
| 1 | Pride and Prejudice | 1 | Classic | 1813 |
| 2 | Oliver Twist | 2 | Novel | 1837 |
| 3 | Murder on the Orient Express | 3 | Mystery | 1934 |
| 4 | Harry Potter and the Philosopher’s Stone | 4 | Fantasy | 1997 |
| 5 | The Hobbit | 5 | Fantasy | 1937 |
borrowing_id book_id borrower_id borrow_date return_date status
1 1 1 1 2022-04-10 2022-04-25 Returned
2 2 3 2 2022-03-20 <NA> On Loan
3 3 4 3 2022-04-05 <NA> On Loan
4 4 2 4 2022-04-15 <NA> On Loan
5 5 5 5 2022-03-30 2022-04-20 Returned
6 6 1 3 2022-04-26 <NA> On Loan
title author_id genre publication_year
1 Pride and Prejudice 1 Classic 1813
2 Murder on the Orient Express 3 Mystery 1934
3 Harry Potter and the Philosopher's Stone 4 Fantasy 1997
4 Oliver Twist 2 Novel 1837
5 The Hobbit 5 Fantasy 1937
6 Pride and Prejudice 1 Classic 1813대출 정보에서 누가 대출을 했는지 알고 싶다고 하자.
| borrowing_id | book_id | borrower_id | borrow_date | return_date | status |
|---|---|---|---|---|---|
| 1 | 1 | 1 | 2022-04-10 | 2022-04-25 | Returned |
| 2 | 3 | 2 | 2022-03-20 | NA | On Loan |
| 3 | 4 | 3 | 2022-04-05 | NA | On Loan |
| 4 | 2 | 4 | 2022-04-15 | NA | On Loan |
| 5 | 5 | 5 | 2022-03-30 | 2022-04-20 | Returned |
| 6 | 1 | 3 | 2022-04-26 | NA | On Loan |
| borrower_id | name | member_since | |
|---|---|---|---|
| 1 | John Smith | john.smith@example.com | 2022-01-01 |
| 2 | Emma Johnson | emma.johnson@example.com | 2021-12-15 |
| 3 | Michael Brown | michael.brown@example.com | 2022-02-20 |
| 4 | Sophia Wilson | sophia.wilson@example.com | 2022-03-10 |
| 5 | William Taylor | william.taylor@example.com | 2022-04-05 |
| 6 | Jane Doe | jane.doe@example.com | 2022-03-05 |
borrowing_id book_id borrower_id borrow_date return_date status
1 1 1 1 2022-04-10 2022-04-25 Returned
2 2 3 2 2022-03-20 <NA> On Loan
3 3 4 3 2022-04-05 <NA> On Loan
4 4 2 4 2022-04-15 <NA> On Loan
5 5 5 5 2022-03-30 2022-04-20 Returned
6 6 1 3 2022-04-26 <NA> On Loan
name email member_since
1 John Smith john.smith@example.com 2022-01-01
2 Emma Johnson emma.johnson@example.com 2021-12-15
3 Michael Brown michael.brown@example.com 2022-02-20
4 Sophia Wilson sophia.wilson@example.com 2022-03-10
5 William Taylor william.taylor@example.com 2022-04-05
6 Michael Brown michael.brown@example.com 2022-02-20만약 이 결과를 가지고 그 도서의 제목 정보도 추가하고 싶을 수 있다. 그러면 다시 연결하면 된다.
borrowings |>
left_join(borrowers, by = c("borrower_id" = "borrower_id")) |>
left_join(books, by = c("book_id" = "book_id")) borrowing_id book_id borrower_id borrow_date return_date status
1 1 1 1 2022-04-10 2022-04-25 Returned
2 2 3 2 2022-03-20 <NA> On Loan
3 3 4 3 2022-04-05 <NA> On Loan
4 4 2 4 2022-04-15 <NA> On Loan
5 5 5 5 2022-03-30 2022-04-20 Returned
6 6 1 3 2022-04-26 <NA> On Loan
name email member_since
1 John Smith john.smith@example.com 2022-01-01
2 Emma Johnson emma.johnson@example.com 2021-12-15
3 Michael Brown michael.brown@example.com 2022-02-20
4 Sophia Wilson sophia.wilson@example.com 2022-03-10
5 William Taylor william.taylor@example.com 2022-04-05
6 Michael Brown michael.brown@example.com 2022-02-20
title author_id genre publication_year
1 Pride and Prejudice 1 Classic 1813
2 Murder on the Orient Express 3 Mystery 1934
3 Harry Potter and the Philosopher's Stone 4 Fantasy 1997
4 Oliver Twist 2 Novel 1837
5 The Hobbit 5 Fantasy 1937
6 Pride and Prejudice 1 Classic 1813이 도서관으 대출자들은 어떤 저자를 좋아하는지 알고 싶을 수 있다. 이런 경우 대출 정보와 대출자 정보를 연결하고, 그 정보를 가지고 도서 정보를 확인하고, 그 정보를 가지고 저자 정보를 확인하면 된다.
borrowings |>
left_join(borrowers, by = c("borrower_id" = "borrower_id")) |>
left_join(books, by = c("book_id" = "book_id")) |>
left_join(authors, by = c("author_id" = "author_id")) borrowing_id book_id borrower_id borrow_date return_date status
1 1 1 1 2022-04-10 2022-04-25 Returned
2 2 3 2 2022-03-20 <NA> On Loan
3 3 4 3 2022-04-05 <NA> On Loan
4 4 2 4 2022-04-15 <NA> On Loan
5 5 5 5 2022-03-30 2022-04-20 Returned
6 6 1 3 2022-04-26 <NA> On Loan
name.x email member_since
1 John Smith john.smith@example.com 2022-01-01
2 Emma Johnson emma.johnson@example.com 2021-12-15
3 Michael Brown michael.brown@example.com 2022-02-20
4 Sophia Wilson sophia.wilson@example.com 2022-03-10
5 William Taylor william.taylor@example.com 2022-04-05
6 Michael Brown michael.brown@example.com 2022-02-20
title author_id genre publication_year
1 Pride and Prejudice 1 Classic 1813
2 Murder on the Orient Express 3 Mystery 1934
3 Harry Potter and the Philosopher's Stone 4 Fantasy 1997
4 Oliver Twist 2 Novel 1837
5 The Hobbit 5 Fantasy 1937
6 Pride and Prejudice 1 Classic 1813
name.y nationality birth_year
1 Jane Austen British 1775
2 Agatha Christie British 1890
3 J.K. Rowling British 1965
4 Charles Dickens British 1812
5 Tolkien British 1892
6 Jane Austen British 1775조인은 테이블은 행의 개수가 많으면 헷갈리기 쉽상이다. 조인의 종류를 아주 간단히 정리해 보면 다음과 같다.
inner_join은 두 테이블에서 일치되는 값이 있을 때 이뤄진다. 다음에서 by = "x"라고 했으므로 x열의 보면 된다. 공통으로 가지고 있는 값은 1이다. 그래서 df1과 df2에서 이 1에 해당되는 행을 찾아서 결합한다.
df1 |>
inner_join(df2, by = "x") |>
knitr::kable()| x | y | a | b |
|---|---|---|---|
| 1 | 2 | 10 | a |
left_join은 왼쪽 테이블의 모든 행을 포함하고, 오른쪽 테이블에서 일치하는 값이 있는 경우 결합한다. 다음에서 by = "x"라고 했으므로 x열의 보면 된다. df1에는 x가 1인 행이 있고, df2에는 x가 1인 행이 있으므로 이 행은 결합된다. 반면 df1에는 x가 2인 행이 있고, df2에는 x가 2인 행이 없으므로 이 행은 결합되지 않지만 left join이므로 왼쪽 테이블의 행은 모두 포함된다. df2에는 여기에 대응하는 값이 없기 때문에 NA로 채워진다.
full_join은 두 테이블의 모든 행을 포함하고, 일치하는 값이 있는 경우 결합한다. 다음에서 by = "x"라고 했으므로 x열의 보면 된다. df1에는 x가 1인 행이 있고, df2에는 x가 1인 행이 있으므로 이 행은 결합된다. 반면 df1에는 x가 2인 행이 있지만 df2에는 없고, df2에는 x가 3인 행이 있지만 df1에는 없다. 이 행들 결합되지 않지만 full join이므로 모두 포함된다. 값이 존재하지 않은 부분은 NA로 채워진다.