Problem Set 4

Course: COSC 460 Databases Fall 2018
Instructor: Michael Hay
Assignment: Problem set 4
Due dates: Mon, Sep 24 at 01:20 PM

We will be using SQLite for this problem set. SQLite is lightweight database management system that is extremely widely used because of its simplicity. There is plenty of documentation on it here and other tutorials that are decent.

Using DataGrip

You may also wish to use DataGrip. DataGrip is made by the same folks as IntelliJ and it provides a user-friendly interface for querying. It is freely available using the same academic license as IntelliJ. My suggestion is to first create a database using SQLite and then connect to it using DataGrip. To connect to an existing SQLite database:

Go to File > Data Sources…
Click on the “+” in the top left corner to add a data source and select “SQLite” data source. (Do not click on SQLite in the section labeled “Drivers.”)
In the pane on the right, there is a “File: “ box. Choose the existing sqlite db file.
Click “Test Connection”. If it says something like “Download missing drivers” at the bottom, go ahead and do that. Then click OK.

Main Instructions

These instructions are written for a Mac OS (linux will be nearly identical, and Windows probably annoyingly different). Sqlite is installed by default on all macs and is available on the machines in the lab, which are available during open hours.

Download these files and save them to a directory. Let’s call this directory SQL_DIRECTORY.
- load_data.sql.
- student.sql.

Both of these files are plain text files that can be opened with any text editor (such as Atom).

Open Terminal. Change the current directory to the SQL_DIRECTORY.
```
 $ cd SQL_DIRECTORY
 $ ls
 load_data.sql   student.sql
```
To get started, do this:
```
 $ sqlite3 mydatabase.db
 SQLite version 3.8.5 2014-08-15 22:37:57
 Enter ".help" for usage hints.
 sqlite>
```
In the above, we called the database mydatabase.db but you can call it whatever you want. This command will cause a file named mydatabase.db to be created in the current directory.
At the prompt, populate the database by having sqlite read the load_data.sql file, like so:
```
 sqlite> .read load_data.sql
```

Check that the data was loaded: the .tables command lists the tables and .schema describes the schema for each table.

 sqlite> .tables
 Eats       Frequents  Person     Serves
 sqlite> .schema
 CREATE TABLE Person(name text, age int, gender text);
 CREATE TABLE Frequents(name text, pizzeria text);
 CREATE TABLE Eats(name text, pizza text);
 CREATE TABLE Serves(pizzeria text, pizza text, price decimal);

Open student.sql in a text editor. You will see some documentation indicating a series of sql statements you are supposed to write. For the first query, write this: SELECT * From Person;. You can execute your queries by saving your work, then returning to sqlite, and typing:
```
 sqlite> .read student.sql
 Amy|16|female
 Ben|21|male
 Cal|33|male
 ...
```
Make some changes to how sqlite formats the output.
```
 sqlite> .header ON
 sqlite> .mode column
```

And try again:

 sqlite> .read student.sql
 name        age         gender
 ----------  ----------  ----------
 Amy         16          female
 Ben         21          male
 Cal         33          male
 ...

Continue to edit student.sql, revising your answer for the first query and filling in answers for the rest of them.
Once you have written answers for all of the queries, upload student.sql to Gradescope. I wrote a program that will read in your submission and attempt to parse out each query and compare it with the solution. If you receive errors, double check that each query has a completely blank line following it. The autograder may not check every query – to encourage you to carefully debug your own work, some results will only be available after the due date.