1. Overview

In lab, we will get practice with with a commonly available and used relational DBMS engine - SQLite. While SQLite is not the most powerful engine, it is lightweight, self contained and in the public domain. Despite this, it still implements most common features of SQL which makes it the most-widely used system out there (a version of it exists on all Android and iOS devices). We will practice using data SQLite by defining a simple relational schema, loading in data, and writing queries.

2. Getting started

Start by creating a week04 directory in your cs44/weeklylabs subdirectory:

# if you have not yet made your cs44/weeklylabs subdirectory do this first:
cd ~/cs44/weeklylabs
mkdir week04
cd week04
pwd
  /home/you/cs44/weeklylabs/week04

Next, we need to change default settings. Luckily, you only need to do this once for the semester if you put in a settings file. Copy over my settings to your home directory:

cp ~soni/public/cs44/.sqliterc ~/

Next, run sqlite3 on the command line with the name of the database you are creating e.g.,:

$ sqlite3 universityDB

This will load the SQLite interface (a command-line prompt). There are many SQLite specific configurations that may be helpful. To get a description of them, type .help.

sqlite> .help

Invoke .show to see the settings (I’ve shown only the ones that should have been set by the copied settings file)

sqlite> .show
     headers: on
        mode: column
colseparator: "$"

If the settings weren’t set properly, you can manually change them:

sqlite> .mode column      #print tables with column formatting
sqlite> .header ON        #print the attribute names at the top of columns
sqlite> .separator '$'    #files containing data will separate fields with a '$'
sqlite> PRAGMA foreign_keys=ON; #enforce foreign key constraints by default

2.1. Warm-up

We will get some practice using SQL syntax. You can copy and paste the code below, but be sure to read and understand the syntax first. Let us create a table for Customers in our database.

2.1.1. Define your schema (DDL)

CREATE TABLE Customer (
    cid  CHAR(10),
    name CHAR(20),
    age  INTEGER,
    addr CHAR(30) );

Note that the white space above is not necessary, it is just to help line up the attributes visually. Run .schema to verify your table is correct. If you make an error, you can always delete the table:

DROP TABLE Customer;

A reminder on types in SQL:

  • INT or INTEGER

  • REAL or FLOAT

  • CHAR(N) fixed length string of size N

  • VARCHAR(N) variable-length of up to size N

  • DATE (‘yyyy-mm-dd’)

  • TIME (‘hh:mm:ss’)

2.1.2. Add tuples (DML)

Now insert tuples of your choosing. For example:

INSERT INTO Customer ( name, cid, age, addr ) VALUES ('Jones', '1234', 36, '102 North St.' );

Add a few more, than try to remove a tuple, e.g.:

DELETE
FROM  Customer
WHERE name = 'Jones';

2.1.3. Query your table (DQL)

To query the tuples in your table, you can run

SELECT *
FROM   Customer;

You can query other types of information; for example, if we want to display only the names of customers with a specific cid:

SELECT C.name
FROM   Customer C
WHERE  C.cid = '1234';

If we want to update a tuple, try:

UPDATE Customer
SET    name = 'Kelemen'
WHERE  cid = '1234';

Can you identify the purpose of each keyword in each of these queries?

2.1.4. Key Constraints

You may have noticed that we didn’t actually define any keys in the above table. Be fault, all fields combine to form the key. If we want to define keys, we can use the keywords PRIMARY KEY or UNIQUE. There are two ways to do this; the following two schemas are equivalent (remember that you can DROP TABLE Customer; to try out these variations):

Named constraints:

CREATE TABLE Customer (
      cid  CHAR(10),
      name CHAR(20),
      age  INTEGER,
      addr CHAR(30),
      CONSTRAINT CustomerKey PRIMARY KEY (cid),
      CONSTRAINT CustomerNA UNIQUE (name, age) );

Unnamed constraints:

CREATE TABLE Customer (
      cid  CHAR(10),
      name CHAR(20),
      age  INTEGER,
      addr CHAR(30),
      PRIMARY KEY (cid),
      UNIQUE (name, age) )

The second presentation is more common, but the first has its uses. It can be used to drop the constraint later, or it can be used by the SQL engine to explain errors.

3. University Database

3.1. Creating Tables

Note: SQLite is case insensitive. We will stick to using all caps for SQL commands because it is more readable.

SQL syntax for CREATE TABLE, INSERT, UPDATE, SELECT, DROP TABLE is identical to that in the book. Just remember that sqlite expects a ; at the end of each SQL command.

We will create four relations to simulate course enrollments using the following schema:

  • Student(id, name, major, level, age)

  • Class(name, time, room, facultyID)

  • Enrolled(studentID, className)

  • Faculty(id, name, dept)

Note the above about the schema:

  • the primary key is highlighted

  • age and all of the id values are integers

  • all other fields should be set as strings (CHAR, TEXT, or VARCHAR)

  • while you can use TEXT as a type for unspecified length, it is good practice to prescribe length bounds on these fields — CHAR(N) or VARCHAR(N) are recommended. For example, you can specify a length of 20 for names and department, 10 for majors and time, and 5 for level and room.

  • Be sure to identify the foreign keys in the above relations — studentID, className, and facultyID.

As a reminder, foreign keys are specified in the schema as:

FOREIGN KEY (targetField) REFERENCES sourceRelation(sourceField)

where targetField is the attribute in this relation that (e.g., studentID) that will take on a value from the attribute sourceField (id) in sourceRelation (Student).

When done, use the commands .table to see all tables created and .schema to review your full schema.

sqlite> .table
Class     Enrolled  Faculty   Student

3.2. Importing data

Most SQL engines have some built-in command to bulk-load entries from some file on disk. I have provided four files, one each for the tables you just defined. These files are located in my public space:

$ ls ~soni/public/cs44/universityDB/
class_start  enrolled_start  faculty_start  student_start

First, view the contents of these files (outside of SQLite) to make sure you understand what \"raw\" data looks.

$ less ~soni/public/cs44/universityDB/student_start

To import, use the following command:

sqlite> .import 'filename' Table

For example:

sqlite> .import '/home/soni/public/cs44/universityDB/student_start' Student

Load all four tables, and then print out the values using a simple query:

sqlite> SELECT * FROM Student;
id          name        major       level       age
----------  ----------  ----------  ----------  ----------
1111        Mo          CS          SO          20
1122        Peter       English     JR          20
1234        Tyler       Math        FR          18
2222        Jo          Math        SR          21
2323        Jo          Math        JR          22
3333        Tanya       Math        JR          21
4444        Malik       CS          JR          20
4545        Sarah       English     SR          21
5555        Chris       Math        FR          19
6666        Charles     English     FR          18
6767        Sarah       Math        FR          18
7777        Josh        CS          SO          19
7878        Heather     Math        SR          22
8888        Elmo        CS          SO          20
9999        Jo          Math        FR          19

Do your results match the original file?

cat /home/newhall/public/cs44/week04/student_start

3.3. Extensions

Try out new queries that update data. Try and violate foreign key constraints by inserting new tuples or updating existing ones. SQLite tends to be less aggressive about enforcing these so note anything that differs from what we discussed in lecture.

3.4. Exit and Reload

At this point, you should be able to exit and reload the database. All contents are saved in the file you originally invoked when running sqlite (i.e., universityDB). Note that the file is saved locally so you’ll have to be in the same folder you created the file.

sqlite> .exit
$ sqlite3 universityDB
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .table
Class     Enrolled  Faculty   Student

4. Homework

Work on this portion with your homework group and submit the result in Gradescope.

Building off the SQLite tutorial we you will construct and submit restaurant.db, a database modeling the restaurant industry.

First, create your database:

sqlite3 restaurant.db

Note: if you need to start over, you can always quit sqlite and delete your previous database:

sqlite> .exit
$ rm restaurant.db

  1. Create a new database with following relations, keys in bold (make reasonable guesses as the types of different fields):

    • Customer(id, name, budget)

    • Restaurant(id, name, address)

    • DinesAt(custid, restid, date)

      Note that there are two references in these relations to connect customer ids and restaurant ids to the DinesAt relation.

      List the schema in sqlite to verify that you have correctly created these relations. Copy and paste your SQL statements to the appropriate Gradescope problem.

  2. Add instances to your database. Add 8 Customer instances, 3 Restaurant instances, and make sure that each customer you add dines at some restaurant. You may choose any values for the relation instance attributes that you’d like, so long as they satisfy the given constraints.

    I recommend that you use bulk loading from files for this part. Create an ascii file for each relation, each containing relation instances. Then try bulk loading them into your restaurant.db database. You could also run INSERT INTO SQL statements to add values in one at a time.

    Run SELECT queries to list the relation instances from each relation to verify that you have correctly added your data.

  3. Run the following query on your resulting database:

    SELECT C.name, R.name
FROM  Customer C, Restaurant R, DinesAt D
WHERE C.id = D.custid AND R.id = D.restid;

    It should produce a result relation consisting of the name of each customer and the name of the restaurant in which they dine.

  4. Copy and Paste the output of your final query to the Gradescope assignment.