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
# 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 ~/
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
.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
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:
CHAR(N)fixed length string of size N
VARCHAR(N)variable-length of up to size N
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):
CREATE TABLE Customer ( cid CHAR(10), name CHAR(20), age INTEGER, addr CHAR(30), CONSTRAINT CustomerKey PRIMARY KEY (cid), CONSTRAINT CustomerNA UNIQUE (name, age) );
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
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)
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 (
while you can use
TEXTas a type for unspecified length, it is good practice to prescribe length bounds on these fields —
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)
targetField is the attribute in this relation that (e.g., studentID)
that will take on a value from the attribute
sourceField (id) in
When done, use the commands
.table to see all tables created and
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
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?
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
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
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:
Note: if you need to start over, you can always quit sqlite and delete your previous database:
sqlite> .exit $ rm restaurant.db
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.
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.dbdatabase. You could also run
INSERT INTOSQL statements to add values in one at a time.
SELECTqueries to list the relation instances from each relation to verify that you have correctly added your data.
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.
Copy and Paste the output of your final query to the Gradescope assignment.