🗓️ Week 03 – Day 01: Intro to databases

When to move from files to databases

Author

Dr Jon Cardoso-Silva

Published

24 July 2023

🌱 Today’s Learning Objectives

Fundamentals of databases: tables, primary and foreign keys
When to use databases over files
Basic SQL queries (SELECT, GROUP BY, ARRANGE, etc.)
Concepts of noSQL databases

👨‍🏫 Lecture Notes

🎥 Looking for lecture recordings? You can only find those on Moodle.

You can download the lecture material as a Quarto Markdown file by clicking the button below.

🎥 Looking for lecture recordings? You can only find those on Moodle.

📖 Recommended readings & Revision

Read more about supported DBMSs and the DBI package (Wondrasek, Brunner, and Müller 2020)
Why use dbplyr instead of pure SQL? (Wickham, Girlich, and Ruiz 2023)
When should I use SQLite?
SQLite is faster than your filesystem
Slow SQLite querying? Read this blogpost about SQLite performance tuning
What are the limits of SQLite?

References

Wickham, Hadley, Maximilian Girlich, and Edgar Ruiz. 2023. “Writing SQL with Dbplyr: Why You Might Use Dbplyr Instead of Writing SQL Yourself.” Tutorial. Dbplyr Package in R. https://dbplyr.tidyverse.org/articles/sql.html.

Wondrasek, James, Katharina Brunner, and Krill Müller. 2020. “Introduction to DBI.” Software. DBI Package in R. https://dbi.r-dbi.org/articles/dbi.