CS44: Database Management Systems

SQL Tutorial

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.

Getting started

Create a directory for our workspace. For example:

cd ~/cs44/
mkdir exercises
mkdir week04
cd exercises
mkdir week04
cd week04

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

Of interest to us are the following:

By default, query results do not print out column headers (names of attributes), nor do they print in evenly-spaced columnst (the default uses a delimiter e.g., "ValA\|ValB"). We want to fix that by setting the headers ON and the default mode to column format:
```
  sqlite> .mode column
  sqlite> .header ON
```
We will be using the .import command to quickly insert a number of tuples. We need to define the separator for fields in our text file to the dollar sign $:
```
  sqlite> .separator '$'
```
Invoke .show to confirm the changes.
```
  sqlite> .show
```
SQLite does not enforce foreign key constraints by default. Run the following command to fix this:
```
  sqlite> PRAGMA foreign_keys=ON;
```

Warm-up

First, I will give you all the commands to work in SQLite. In the section below, I will ask you to apply your understanding to a different problem. Let us create a table for Customers in our database:

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
CHAR(N) fixed length string of size N
VARCHAR(N) variable-length of up to size N
REAL or FLOAT
DATE (‘yyyy-mm-dd’)
TIME (‘hh:mm:ss’)

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';

Query your table (DQL)

To query the tuples in your table, you can run

SELECT *
FROM   Customer;

You can run report various types of information instead; 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?

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:

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. If you don’t want to have to remember the exact names of the fields for Customer that form the key~~, you can reference the named constraint; that is, we can state REFERENCES CustomerKey~~. EDIT: this actually doesn’t work as I originally explained. You can name constraints, but it is so for reasons other then indicating foreign key references. It can be used to drop the constraint later, or it can be used by the SQL engine to explain errors.

University Database

Creating Tables

Note: SQLite is case insensitive. We will stick to using all caps for SQL commands because a) it is more readable and b) it gives the impression that you are screaming instructions to the DBMS.

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.

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

sqlite> .table
Class     Enrolled  Faculty   Student

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

The delimiters could be commas instead of $, but this avoids issues e.g., department names with commas in them.

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

Extensions

Try out new queries that update data. Also, be sure to try and violate foreign key constraints.

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.

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