vignette-database-configuration

Vignette on configuring a database and writing queries in R; created as a class project for PSTAT197A in Fall 2022.

Contributors

Jennifer Park, Russell(Senyuan) Liu, Haotian Xia, Haoming Deng, Ankur Malik.

Abstract

In this methods vignette, we explore how to configure a database and write queries in R. First, we go over the required software and packages. Then, we include steps to create a new database, make configurations, and enter new data into an existing database using some sample data. Lastly, we explain how to write queries by introducing some common functions, tables, syntax and the filtering clause in SQL.

Repository contents

1. root directory

1.1 data

1.1.1 day.csv

1.1.2 hour.csv

1.2 scripts

1.2.1 final_script.R

1.2.2 drafts : with different draft made by different people ...

1.3 img

1.3.1 install_1.jpg

1.3.2 install_2.jpg

...

1.4 final_report.qmd

1.5 vignette-database-configuration2.Rproj

1.6 README.md

Reference list

1. MySQL Installation Guide: https://dev.mysql.com/doc/mysql-installation-excerpt/5.7/en/
2. MySQL Server Configuration Guide: https://www.youtube.com/watch?v=nj3nBCwZaqI
3. MySQL Workbench Installation Guide: https://www.youtube.com/watch?v=6FvvWhiZyDY
4. Sample data: https://archive.ics.uci.edu/ml/datasets/Bike+Sharing+Dataset
5. More on RMariaDB: https://rdrr.io/cran/RMariaDB/, https://rmariadb.r-dbi.org/reference/dbconnect-mariadbdriver-method
6. More on dbplyr and JOIN: https://dbplyr.tidyverse.org/reference/join.tbl_sql.html
7. Colburn, Rafe. Special Edition Using SQL. Que, 2000.
8. Röhm, Uwe, et al. "SQL for Data Scientists: Designing SQL Tutorials for Scalable Online Teaching." Proceedings of the VLDB Endowment, vol. 13, no. 12, 2020, pp. 2989--92, https://doi.org/10.14778/3415478.3415526.
9. Guo, Aibo, et al. "ER-SQL: Learning Enhanced Representation for Text-to-SQL Using Table Contents." Neurocomputing (Amsterdam), vol. 465, 2021, pp. 359--70, https://doi.org/10.1016/j.neucom.2021.08.134.
10. Guo, Aibo, et al. "ER-SQL: Learning Enhanced Representation for Text-to-SQL Using Table Contents." Neurocomputing (Amsterdam), vol. 465, 2021, pp. 359--70, https://doi.org/10.1016/j.neucom.2021.08.134.
11. Reese, George., et al. Managing and Using MySQL. 2nd ed., O'Reilly, 2002.\