Database Systems

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. We will get some practice using SQL syntax in SQLite to create, and delete relations, to insert and update tuples. and a couple simple SQL queries along the way. You can copy and paste the SQL code below, but be sure to read and understand the syntax first.

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 ~newhall/public/cs44/.sqliterc ~/.

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

$ sqlite3 customerDB

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

You can also add setting to your .sqliterc file that is read by sqlite3 on start-up to configure itself. In another terminal, cat out the .sqliterc file you copied over to see the config options that it defines:

cat ~/.sqliterc

.mode column
.header ON
.separator '$'
PRAGMA foreign_keys=ON;

Define your schema (DDL)

Let’s create a relation (a table) for Customers in our database.

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’)

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 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?

Key Constraints

You may have noticed that we didn’t actually define any keys in the above table. Be default, 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.

Try:

drop table Customer and list schema to see it is dropped
```
sqlite> .schema
```
now re-create Customer table you just dropped adding in some key constraints (you can cut and paste CREATE TABLE Customer command from above)
add a few records ('INSERT`)
try to add a record that violates a constraint and see what happens

SQLite tends to be less aggressive about enforcing constraints, so note that you may see behavior that differs from what we discussed in lecture.

Exit and Reload

You can exit and reload databases. Note that the file is saved locally so you’ll have to be in the same directory in which you created the file. After adding a few relations to Customer, try this out. When you reload try running some commands to see the saved relation.

sqlite> .exit

$ sqlite3 customerDB
SQLite version 3.45.1 2024-01-30 16:01:20
Enter ".help" for usage hints.
sqlite> .table
Customer
sqlite> .schema
...

Importing data

Most SQL engines have some built-in command to bulk-load entries from some file on disk. I have provided an example file with some customer data in it for you.

$ ls ~newhall/public/cs44/sqlitetutor/customer_start

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

$ less ~newhall/public/cs44/sqlitetutor/customer_start

To import, use the following command:

sqlite> .import 'filename' Table

For example (note: you cannot use ~ shorthand for /home/ here):

sqlite> .import '/home/newhall/public/cs44/sqlitetutor/customer_start' Customer

Then try a query to list out all Customers, and you should see some newly added Customer tuples listed at the end:

sqlite> SELECT * FROM Customer;

cid   name     age  addr
----  -------  ---  ----------------
...
1111  Mo       22   33 N. South St.
1122  Peter    44   100 S. North St.
2222  Jo       55   343 W. East St.
2323  Flo      34   345 E. West St.

Exit and Reload again

Try exiting and reloading again to see that the tuples you added with the .import command were saved to your sqlite customerDB file. Note that the file is saved locally so you’ll have to be in the same directory in which you created the file.

sqlite> .exit
$ sqlite3 customerDB
SQLite version 3.45.1 2024-01-30 16:01:20
Enter ".help" for usage hints.
sqlite> .table
...
sqlite> SELECT * FROM Customer;
...

Practice with multiple tables

This will be a part of your next lab assignment, so keep a copy of the CREATE commands you use to create tables (you can copy them into a text file for now), and do not delete the database file you create for this part after you are done running though this example.

Let’s try an example with a University database that contains a few tables. Start by running sqlite with the name of the database file you want to create:

$ sqlite3 universityDB

Create Relations

First, 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:

use the field names exactly as they are given in the scheme above in your SQL CREATE TABLE commands
the primary key is highlighted
age and all of the id values are INTEGER
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)
```
For example:
```
FOREIGN KEY (studentID) REFERENCES Student(id)
```

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

sqlite> .table
Class     Enrolled  Faculty   Student

Import Data

Next, use sqlite .import command to bulk load your relations with some tuple values. I provided four files, one each for the tables you just defined, that you can use for this. These files are located in my public directory:

$ ls ~newhall/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 ~newhall/public/cs44/universityDB/student_start

To import, use the following command:

sqlite> .import 'filename' Table

For example:

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

Load all four tables, and then print out the values in each table using an SQL query like the following example:

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/universityDB/student_start

Exit and Reload

Try exiting and reloading your universityDB. You can re-run SQL queries to print out all tuples in each relation, and use the .table and .schema squlite command to list meta data about the DB.

sqlite> .exit
$ sqlite3 universityDB
SQLite version 3.45.1 2024-01-30 16:01:20
Enter ".help" for usage hints.
Enter SQL statements terminated with a ";"
sqlite> .table
Class     Enrolled  Faculty   Student

Resources

SQL

SQLite tutorial from in class week 4
SQL Tutorial from sqlzoo.net

CS44 SQLite tutorial

Overview

Getting started

Define your schema (DDL)

Add tuples (DML)

Query your table (DQL)

Key Constraints

Exit and Reload

Importing data

Exit and Reload again

Practice with multiple tables

Create Relations

Import Data

Exit and Reload

Resources

SQL