Lab: SQL DDL

In this lab, you will set up SQLite on your computer, and you will implement a relational schema using SQL’s Data Definition Language (DDL).

Instructions

You should work on this assignment individually. It is important that all students get SQLite installed on their devices! However, please ask the instructor or classmates for help if you get stuck.

Task 1: Install SQLite

First, check whether you already have SQLite installed by trying to run SQLite from the command line. Notice that the command is named sqlite3:

pstory@Gray-MBP ~> sqlite3 --help
Usage: sqlite3 [OPTIONS] FILENAME [SQL]
FILENAME is the name of an SQLite database. A new database is created
if the file does not previously exist.
OPTIONS include:
   -A ARGS...           run ".archive ARGS" and exit
   -append              append the database to the end of the file
   -ascii               set output mode to 'ascii'
   -bail                stop after hitting an error
...

If you get an error, then you need to install SQLite.

You can either download SQLite directly from the developers, or install it through a package manager. For example, it is available form MacPorts and Homebrew on MacOS, and from apt on Linux.

SQLite Meta Commands

You may find these SQLite meta commands helpful:

• .help View documentation for all meta commands
• .tables List names of tables
• .schema TABLE Show the CREATE statements for the TABLE
• .read FILE Read input from FILE

SQL Documentation

In addition to the course slides, you may find these resources helpful:

• SQLite CREATE TABLE Reference
• SQLite INSERT Reference
• PostgreSQL Data Types
• SQLite Data Types

Task 2: Create Tables

You will implement a simple database for recording the cities and states in the United States. Each state’s primary key is its two-character abbreviation (e.g., MA). Each city has a compound primary key, consisting of the city’s name and its state’s abbreviation (e.g., Worcester, MA).

Create usa.db with this command:

pstory@Gray-MBP ~> sqlite3 usa.db

Next, create the city and state tables. Use appropriate data types for each column, and set primary and foreign key constraints as appropriate.

Note: Use lowercase for all table and column names.

Note: Use the STRICT keyword when creating tables. Otherwise, SQLite won’t check most constraints. For example, by default SQLite allows NULL primary keys and doesn’t enforce data types. Spend a few minutes reviewing SQLite’s documentation for STRICT tables.

Task 3: Insert Records

Before inserting records, configure SQLite to enforce foreign keys. Most databases do this by default! Within the SQLite CLI, run:

PRAGMA foreign_keys = ON;

Next, write SQL to insert these records:

Finally, test that your schema is enforcing constraints correctly by attempting to insert invalid data.

Task 4: Install a SQLite GUI

Install DB Browser for SQLite, or an equivalent tool.

Next, use it to view the database you created.

Note: The latest stable release of DB Browser does not support the STRICT keyword (the latest stable release is from 2021). To open your database, you should use the latest nightly build, which does support the STRICT keyword.

Submit

Upload schema.sql and records.sql to Gradescope:

• schema.sql should contain your CREATE TABLE statements
• records.sql should contain your INSERT statements

For full credit, your schema should enforce all constraints as described. Also, your insert statements should add the data pictured above.