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 .sqlite file, open the Link and look for Chinook_Sqlite.sqlite and Chinook_Sqlite.sql. Right click on the link and Save Link As ... to download the file into a location of your choice.

SQLite3 (on MACOS)

For MAC users, sqlite is builtin command. To run sqlite just open your terminal and change the directory to your current working directory where you saved the databse. To create a database, type the following command in the terminal:

$ 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

Note: make sure that you downloaded Chinook_Sqlite.sql and it is in the current working directory. Otherwise, you should specify the path to the .sql file.

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]

However, if you have downloaded Chinook_Sqlite.sqlite, you cannot use .read. Instead, use .open because Chinook_Sqlite.sqlite is a binary database file and not SQL code (plain text).

Browsing the database in Chinook_Sqlite.sqlite: The file Chinook_Sqlite.sqlite contains the whole database so you can open it directly using:

sqlite3 Chinook_Sqlite.sqlite

then you can see the database and perform basic queries on it.

SQLite3 (on Windows)

For MS-Windows user, download the file sqlite-tools-win-x64-3460100.zipfrom (SQLite) and store it a specific location (let us say ’infomdwr/lab0/).

Which file to download?

Extract the downloaded file and the change the directory name to sqlite (as in the fingure).