Databases and persistence

The Relational Model

SQL Basics, DDL, and DML

Querying Data

Advanced Concepts

Sqlite

DBeaver

SQL Scripts

Connecting with Javascript

Appendix: Glossary of Database Terms

Resources

Practice

Assignment

Core program

Part 1 — Build a database from scratch

In this section you will design and create your own database without a script to help you. Use either the SQLite CLI or DBeaver.

Exercise 1: The pets database

A local vet wants to keep track of their clients and the pets they bring in. Create a new empty database called pets.db and write the DDL to create the following two tables:

owners

Column Type Constraints
id INTEGER PRIMARY KEY AUTOINCREMENT
first_name TEXT NOT NULL
last_name TEXT NOT NULL
email TEXT UNIQUE

pets

Column Type Constraints
id INTEGER PRIMARY KEY AUTOINCREMENT
name TEXT NOT NULL
species TEXT NOT NULL
breed TEXT
age INTEGER
owner_id INTEGER NOT NULL, FOREIGN KEY → owners

Once both tables exist, insert at least 3 owners and 6 pets, making sure at least two owners have more than one pet.

Check: run .tables in the CLI (or look in the Database Navigator in DBeaver) to confirm both tables were created. Then run SELECT * FROM pets; to see your data.

Exercise 2: Add a visits table

The vet also wants to record when each pet came in for a check-up. Add a third table to pets.db:

visits

Column Type Constraints
id INTEGER PRIMARY KEY AUTOINCREMENT
visit_date TEXT NOT NULL
reason TEXT
pet_id INTEGER NOT NULL, FOREIGN KEY → pets

Insert at least 4 visits across different pets.

Think: why is there no direct link between visits and owners? How would you find all visits for a given owner?

Part 2 — Load a SQL script

Exercise 3: Set up the music library

Download music_library.sql (music_library.sql) from the course materials. Create a new empty database called music_library.db and load the script into it using either the SQLite CLI or DBeaver.

Once it is loaded, run the following query to verify everything is in place:

SELECT al.title, al.release_year, ar.name AS artist
FROM albums al
JOIN artists ar ON ar.id = al.artist_id
ORDER BY al.release_year
LIMIT 10;

You should see 10 albums with their artist names, ordered by year.

Part 3 — Querying data

Use books_library.db (books_library.sql ) for exercises 4–8, and music_library.db (music_library.sql ) ffor exercises 9–10. Write and run each query in DBeaver or the SQLite CLI.

Exercise 4: Basic filtering

Write queries to find:

  1. All authors born after 1950.
  2. All books published between 1960 and 1990 (inclusive).
  3. All books in the 'Horror' genre.
  4. All authors with the nationality 'British'.

Exercise 5: Sorting and limiting

  1. Get the 5 most recently published books (title and year only).
  2. Get the 5 oldest books.
  3. List all authors sorted alphabetically by last name.

Exercise 6: JOINs

  1. Show every book with the author’s full name alongside it (combine first_name and last_name into one column called author).
  2. List all books by J.R.R. Tolkien, ordered by published_year.
  3. Find all books published after 2000, showing title, year, and author name.

Exercise 7: Music library — basic queries

Switch to music_library.db and write queries to find:

  1. All albums released in the 1990s.
  2. All albums by Radiohead.
  3. All albums in the 'Hip-Hop' genre, showing album title and artist name.

Part 4 — Modifying data

All exercises in this section use books_library.db.

Exercise 8: Insert

  1. Add yourself as a new author (use your real name, or make one up). Pick a nationality and birth year.
  2. Insert two books for your new author — they can be real books, imaginary ones, or your own.
  3. Verify they appear by querying for all books by your author.

Exercise 9: Update

  1. Someone mis-categorised "Good Omens" — change its genre from 'Comedy Fantasy' to 'Fantasy'.
  2. Update the nationality of one author of your choice (pick something plausible).
  3. Change the published_year of one of the books you inserted in Exercise 11.

Check: after each UPDATE, run a SELECT to confirm the change took effect.

Exercise 10: Delete

  1. Delete one of the books you added in Exercise 11.
  2. Now delete the author you added — but first check: what happens if the author still has books in the database? How would you handle that?

Think: what order do you need to delete in, and why?

Part 5 — JavaScript

For all JavaScript exercises, create a new project folder, install better-sqlite3, and copy in books_library.db.