Database systems

Requirements

Workstation: check workstation setup for more details.

Exercises:

1. Sql I - manual / slides

   • introduction
   • SFW queries (select-from-where)
   • insert
   • delete
   • explain
   • first join

2. Sql II - manual

   • joins (inner, outer, ...)
   • foreign keys
   • 1:n and 1:1 and m:n
   • SQL indexes
   • Group by
   • explain

3. Python + SQL - manual

   • Writing scripts that work with MySQL
   • Introduction to ORM with peewee
   • Danger of using ORM

4. Mongodb and redis - manual

   • Mongodb as an example for the noSQL database
   • Redis - cache and fast key/value storage

5. Google BigQuery and Google Cloud SQL - manual

6. Exam

References

• Missing Semester and 15 min better bash
• Stanford Advanced SQL
• Principles of Data-Intensive Systems
• Data Management and Data Systems
• Latency Numbers Every Programmer Should Know

Workstation setup

Windows

Notice: After the second exercise the latest, please switch to Ubuntu.

Editor:

• atom or vscode

Mysql:

• https://dev.mysql.com/doc/refman/8.0/en/windows-installation.html
• https://dev.mysql.com/downloads/workbench/

Recommended: use chocolatey to install these tools.

Linux Ubuntu

Editor:

# install atom
sudo snap install atom --classic

Docker for running databases:

# install docker
sudo su

apt-get update ;
apt-get install -qq apt-transport-https ca-certificates curl software-properties-common ;
curl -fsSL https://download.docker.com/linux/ubuntu/gpg | apt-key add - ;
add-apt-repository 'deb [arch=amd64] https://download.docker.com/linux/ubuntu '$(lsb_release -cs)' stable' ;
apt-get update ;
apt-get install -qq docker-ce ;

# do not forget to exit
exit

# as a normal user
sudo docker ps

Mysql workbench:

sudo apt update
sudo snap install mysql-workbench-community
snap connect mysql-workbench-community:password-manager-service

MacOS

Please install https://brew.sh/ first.

Atom:

brew install atom

Docker (howto):

brew install --cask docker

Mysql workbench:

brew install --cask mysqlworkbench