Relational Model & SQL Basics

The library that never loses a card 📚

Picture a big library with 100,000 books and no catalogue. Someone asks: "Which books were published after 1990, best-rated first?" You would have to walk every shelf.

Now give the librarian a card catalogue. Every kind of thing gets its own drawer (books, members, loans). Every drawer holds identical cards, and every card has the same fields: title, author, year. Each card also has a unique number, so "card 42 in the members drawer" always means the same person.

That is a relational database:

• a table is a drawer (books)
• a row is one card (one book)
• a column is one field on the card (title, year)
• a primary key is the unique number on the card
• SQL (Structured Query Language) is how you ask the librarian questions

What you'll learn

• How tables, rows, columns, primary keys and foreign keys fit together
• The everyday verbs: SELECT, INSERT, UPDATE, DELETE
• Filtering and sorting with WHERE, ORDER BY and LIMIT
• NULL, the "unknown" value that behaves unlike any other
• Constraints that make the database refuse bad data for you

In the labs, the database is SQLite (it ships inside Python's standard library). The program creates a tiny in-memory database, and you write the SQL inside a Python string.

Tables, keys and types

A table has a fixed list of columns, each with a name and a type (INTEGER, TEXT, REAL, ...). Every row supplies one value per column.

CREATE TABLE books (
  id     INTEGER PRIMARY KEY,
  title  TEXT NOT NULL,
  author TEXT NOT NULL,
  year   INTEGER NOT NULL,
  rating REAL
);

INSERT INTO books (title, author, year, rating)
VALUES ('Dune', 'Frank Herbert', 1965, 4.5);

• The primary key identifies exactly one row. Other tables point at a row by storing its key.
• A foreign key is a column whose value must match a key in another table. loans.book_id should always match some books.id. This is what makes the model relational: tables are linked by values, not by copying data.
• The order of rows in a table is not part of the model. If you want a specific order, you must ask for it.

SQLite is flexible about column types (it treats them as hints). Other databases such as PostgreSQL are strict. Declare the types you mean anyway; it documents your intent and keeps your SQL portable.

Asking questions: SELECT

SELECT title, year          -- which columns to show
FROM books                  -- which table
WHERE year >= 1980          -- keep only matching rows
ORDER BY year DESC, title   -- sort (DESC = high to low)
LIMIT 5;                    -- stop after 5 rows

You write the clauses in this order, but think of the database working in a different order: first FROM (pick the table), then WHERE (drop rows), then SELECT (choose columns), then ORDER BY, then LIMIT. Run the demo below to see that pipeline written as plain Python.

Filtering well: WHERE, NULL and parameters

Inside WHERE you can combine tests with AND, OR, NOT, and use =, <>, <, >=, BETWEEN a AND b, IN (...) and LIKE 'Du%' (% means "anything").

NULL means "unknown", not zero and not empty text. Any comparison with an unknown is unknown, and WHERE keeps a row only when the test is true:

SELECT * FROM books WHERE rating = NULL;    -- always returns nothing!
SELECT * FROM books WHERE rating IS NULL;   -- correct
SELECT * FROM books WHERE rating < 4;       -- also skips rows where rating is NULL

Never glue user input into SQL text. Pass values separately as parameters (? or :name), and the database treats them as data, never as SQL. This is the foundation of protection against SQL injection, which a later lesson covers:

db.execute("SELECT title FROM books WHERE year >= ?", (1990,))
db.execute("SELECT title FROM books WHERE year >= :y LIMIT :n", {"y": 1990, "n": 5})

Sorting. ORDER BY takes several keys. ORDER BY rating DESC, title sorts by rating, and breaks ties alphabetically. Without ORDER BY, the order of the result is not guaranteed, even if it looks stable today. LIMIT without ORDER BY gives you "some rows".

Your turn: query the library

In the lab below, all the plumbing is written. You only write the SELECT.

Changing data: INSERT, UPDATE, DELETE

INSERT INTO products (name, stock, price_cents) VALUES ('Desk Lamp', 5, 2499);

UPDATE products SET stock = stock + 10 WHERE name = 'Stapler';

DELETE FROM products WHERE discontinued = 1;

Together with SELECT these four verbs are called CRUD (Create, Read, Update, Delete).

The most expensive typo in SQL: UPDATE or DELETE without a WHERE changes every row. Habit to build: write the WHERE first, test it as a SELECT, and only then turn it into an UPDATE or DELETE.

Two details worth knowing:

• In SET stock = stock + 10 the right side is computed from the current value of the row. The database does the arithmetic, so you do not read the value into your program first.
• Every statement reports how many rows it touched (cursor.rowcount in Python's sqlite3). Checking it is a cheap way to notice a WHERE that matched nothing, or too much.

Money is stored here as integer cents (price_cents), not as REAL. Binary floating point cannot represent values like 0.10 exactly, and rounding errors in money are a classic bug.

Your turn: run a stock-take

Constraints: let the database say no

Validation in your application code is a good first line of defense, but it can be bypassed or forgotten: another service, a script, a bug. Constraints live in the database and apply to every writer.

CREATE TABLE students (
  id    INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,                       -- required and no duplicates
  age   INTEGER CHECK (age BETWEEN 16 AND 100),     -- any condition you can express
  team  TEXT DEFAULT 'unassigned'                   -- used when the column is omitted
);

• NOT NULL: the value may not be missing.
• UNIQUE: no two rows share this value. (NULLs do not count as equal to each other, so most databases allow several NULLs in a UNIQUE column.)
• CHECK (condition): reject any row where the condition is false.
• PRIMARY KEY: identifies each row. It must be unique and is how other tables point at the row.
• FOREIGN KEY: team_id INTEGER REFERENCES teams(id) means the value must exist in teams. SQLite only enforces foreign keys after PRAGMA foreign_keys = ON, once per connection. Other databases enforce them always.

When a statement breaks a constraint, the whole statement fails with an error (Python raises sqlite3.IntegrityError) and nothing is written. Your program can catch it and report something useful to the user.

Your turn: design the guard

Recap and what to practice next

• A relational database stores data in tables of rows and columns; primary keys identify rows and foreign keys link tables.
• SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT reads data; INSERT, UPDATE and DELETE change it. Always scope changes with WHERE.
• NULL is unknown: use IS NULL, and remember that comparisons with it never come out true.
• Pass values as parameters, never by gluing strings.
• Constraints (NOT NULL, UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY) keep bad data out no matter who writes it.

Practice next: invent a small domain you know (a gym, a football league, a recipe box). Write its CREATE TABLE statements with constraints, insert ten rows and ask five different questions with SELECT. In the next lesson you will learn how to decide which tables a domain needs.