INFOMDWR – Lab 0: Know Your Tools

Introduction

During this lab, you will get familiar with the most important tools that will be used during the course. These tools include (SQLite), (DB Browser), (Google Colab) for Python and (RStudio) for R.

SQLite and DB Browser

In this course, we will use SQLite and its graphical interface DB Browser to create and query DBMSs. During this lab, you will install and use the DB Browser or SQLite3. As an example for a database, we will use the Chinook database. To download the .sql file, open the Link and look for Chinook_Sqlite.sql. Right click on the link and Save Link As ... to download the file into a location of your choice.

SQLite3

To create a database using sqlite3:

$ sqlite3 Chinook

This command will create a file Chinook.db in the working directory. To check if the database has been created correctly, you can run .databasesfrom the bash of sqlite3 as follows:

sqlite3> .databases 

If the database has been created correctly, you should see the following output

main: /Users/.../Chinook r/w

Now, you can import the tables from the file Chinook_Sqlite.sql using:

sqlite3> .read Chinook_Sqlite.sql

It will take a few seconds to import the database. After that, you can list the tables that exist in the database using:

sqlite3> .tables

If the database was imported correctly, you should see the following list of tables: [Album, Employee, InvoiceLine, PlaylistTrack, Artist, Genre, MediaType, Track, Customer, Invoice, Playlist]

DB Browser

In DB Browser, you can use the graphical interface to create the Chinook database. To import the database from the file Chinook_Sqlite.sql, open the File menu and select import > Database from SQL file .... Change to the location where you saved the file Chinook_Sqlite.sql and select it. You will be asked if you want to create a new databse or not. Since we have created a new database, you select No. If the database is imported correctly, you will see the number of tables changed from 0 to 11.

Now, we need to get familiar with executing a simple SQL query that returns the names of the tables in the database. Unlike using sqlite3, we should use the Execute SQL tool. When selecting the Execute SQL, you will see a text box. Type the query:

SELECT name FROM sqlite_schema
WHERE type ='table' AND 
      name NOT LIKE 'sqlite_%';

After executing the query, you should see a table with the names of the 11 tables in the database.

Google Colab for Python

For simplicity, we will use Google Colab for running the Python programs during the course. If you are familiar with other Integrated Development Environment such as Anaconda, PyCharm or Spyder, then use that IDE. Be careful that specific libraries may run only under a specific version of Python.

Google Colab

We will start by creating a new new notebook and checking the version of Python that is already installed. We run the command:

!python --version

If we need to install a different version of Python (e.g. 3.7), we can use:

!apt-get install python3.7

Installing more Libraries

Most of the important libraries that we may need are already installed in the Google Colab environment. However, if you would like to install additional libraries, you can use the pip command. For example, to install py_stringmatching, you can use the command:

!pip install py_stringmatching

Note: when running system commands in Google colab, we use ! before the command (this is common for running system commands in any notebook environment).

Running simple Python code

Run the following Python code and explain what the code is doing:

t, f = True, False
print(t and f) 
print(t or f)  
print(not t)   

R & RStudio

We will be using the most popular development environment for R: RStudio. In the exercises below (and the readings for this lab from R4DS) you will set up your computer for doing the R practicals in later parts of this course.

R & RStudio

Installing R & RStudio

Install R and RStudio as per the instructions in the syllabus here.

Installing additional packages

Open RStudio, find the console window (the REPL) and type the following code:

2 + 2

This should return 4.

If you are not sure where to execute code, use the following figure to identify the console: