Session 1 — Databases, tables, and the relational model

You will learn the mental model behind every SQL query: data lives in tables of rows and columns, each table has a primary key that identifies rows, and foreign keys link rows across tables. The course uses one small bookshop schema throughout — authors, books, and sales — which you will learn to read fluently and then query, join, and extend in every later session. You will also see why data is split across tables instead of piled into one list, and what SQL is (and is not).

Plan: Core ideas → worked schema → Quiz.

Time: about 50–60 minutes if you trace every link by hand before checking the quiz.

Session 1 — Databases, tables, and the relational model

The one-paragraph story

Before writing any query, you need the mental picture a database is built around: tables of rows and columns, connected by keys. A relational database stores data in tables where each row is one record and each column is one attribute, and it uses primary keys to identify rows and foreign keys to link rows across tables. SQL — Structured Query Language — is the standard language for asking questions of those tables and for changing what is stored in them. This session builds the vocabulary and the schema you will use in every later session: a small bookshop with three tables (authors, books, sales). By the end you will be able to read a schema, tell a primary key from a foreign key, and explain why we split data across tables instead of piling it into one giant list.

---

What a relational database is

A database is a collection of related data, organised so that it can be queried and updated reliably. A relational database (the kind SQL speaks to) stores that data in tables.

A table is a grid:

• a column (or attribute or field) holds one kind of fact — a name, a price, a date;
• a row (or record) holds one complete instance — one author, one book, one sale;
• a cell holds the value of one column for one row.

Every column has a data type: integers (INTEGER), numbers with decimals (REAL or NUMERIC), text (TEXT or VARCHAR(n)), and dates (DATE). Types matter because they decide what operations make sense — you can multiply two prices, but multiplying two names is meaningless.

Think of a table as a tidy spreadsheet tab: one rule per row, one attribute per column, no hidden structure.

---

The relational model in one example

Here is the bookshop schema used throughout this course. Three tables:

CREATE TABLE authors (
    author_id  INTEGER PRIMARY KEY,
    name       TEXT,
    country    TEXT
);

CREATE TABLE books (
    book_id        INTEGER PRIMARY KEY,
    title          TEXT,
    author_id      INTEGER REFERENCES authors(author_id),
    genre          TEXT,
    price          REAL,
    published_year INTEGER
);

CREATE TABLE sales (
    sale_id   INTEGER PRIMARY KEY,
    book_id   INTEGER REFERENCES books(book_id),
    qty       INTEGER,
    sale_date DATE
);

Read the three tables as three kinds of fact:

• authors — one row per author: (author_id, name, country).
• books — one row per book: (book_id, title, author_id, genre, price, published_year). The author_id column points at the author who wrote the book.
• sales — one row per sale event: (sale_id, book_id, qty, sale_date). The book_id column points at the book sold.

---

Primary keys and foreign keys

A primary key is the column (or column set) that uniquely identifies each row. In authors, author_id is the primary key: no two rows share an author_id, and every row has one. The primary key is how the database keeps track of which record we mean when we talk about "author 2".

A foreign key is a column that holds the primary-key value of a row in another table. In books, the author_id column is a foreign key referencing authors(author_id): it says "this book was written by the author whose author_id equals this value." In sales, book_id is a foreign key referencing books(book_id).

The pair of ideas — primary key on one side, foreign key on the other — is what connects tables. books.author_id links a book to its author; sales.book_id links a sale to its book. Later sessions build JOINs out of exactly these links.

Common confusion. A foreign key is not a duplicate of the whole row — it is just the reference. We do not copy the author's name into every book row; we store the author's author_id and look the name up when we need it. That is the point of splitting data across tables.

---

Why split data into separate tables?

Two reasons dominate. Avoiding repetition: if we stored author names inside every book row, the same name would appear dozens of times, and fixing a typo in one author's name would mean updating every book. Storing the author once and referencing it by id keeps each fact in exactly one place. Keeping one fact per place: the schema makes it structurally impossible to have two authors with the same id (the primary key forbids it), and it makes the links between facts explicit rather than accidental.

The downside is that the interesting question — "who wrote which book, and what did we sell?" — is spread across three tables. Reassembling it is exactly what JOINs (Sessions 5–6) do. The splitting is the design; the joining is the query.

---

What SQL is, and what it is not

SQL is the standard language for:

• querying — SELECT retrieves rows that match a description;
• modifying — INSERT, UPDATE, DELETE change what is stored (Session 11);
• defining — CREATE TABLE and friends set up the schema (Sessions 1 and 12).

A SQL statement reads like a sentence: SELECT what, FROM where, WHERE under which condition. You do not write how to find the rows (that is the database engine's job); you write what you want. This declarative style is the opposite of writing a loop: you describe the result, and the engine decides the steps.

Terminology worth pinning down now: a query is a SELECT statement; a result set is the table-like grid it returns (it may be a subset of rows, a subset of columns, or both); and rows in the result set are what you count, check, and sort in every drill that follows.

---

A picture of the schema

flowchart LR
    A["authors\nauthor_id PK\nname\ncountry"] --> B["books\nauthor_id FK\n(title belongs to author)"]
    B --> S["sales\nbook_id FK\n(sale refers to book)"]

Read the arrows as "references": each book references an author, each sale references a book. The three tables plus these links are the whole world of this course.

---

Pitfalls to watch for

• A primary key identifies a row; a foreign key references a row. They are two halves of one link.
• Columns have types. Mixing types in a drill (adding a text value to a price) is an error the engine would reject.
• Rows are unordered unless you say otherwise. SQL gives no guarantee about row order without an ORDER BY (Session 2) — never assume "the order I saw them in".
• A table is a set of facts, not a report. The human-readable report ("author names with their countries") is produced by queries, not stored as a fourth table.

Session 1 — Worked examples

Example A — Reading a schema

Here is the bookshop schema (all three tables) with a few rows filled in.

authors

| author_id | name | country | |-----------|------|---------| | 1 | Alina Voss | Germany | | 2 | Mateo Rios | Argentina | | 3 | Priya Anand | India | | 4 | Jonas Weber | Germany |

books

| book_id | title | author_id | genre | price | published_year | |---------|-------|-----------|-------|-------|----------------| | 10 | The Glass Harbour | 1 | fiction | 24.99 | 2019 | | 11 | Static Echoes | 1 | fiction | 19.50 | 2021 | | 12 | River Clock | 2 | mystery | 22.00 | 2018 | | 13 | Salt Roads | 3 | history | 31.00 | 2020 | | 14 | North of Noon | 3 | history | 27.50 | 2017 | | 15 | Prairie Circuit | 4 | mystery | 18.00 | 2022 |

sales

| sale_id | book_id | qty | sale_date | |---------|---------|-----|-----------| | 100 | 10 | 2 | 2024-01-05 | | 101 | 12 | 1 | 2024-01-05 | | 102 | 10 | 3 | 2024-01-12 | | 103 | 14 | 1 | 2024-01-12 | | 104 | 13 | 2 | 2024-01-19 |

Answer these:

1. What is the primary key of books? → book_id. Each book has a distinct book_id, and no row lacks one.
2. Which column in sales is a foreign key, and where does it point? → book_id, pointing at books(book_id).
3. Which author wrote book 13? → Look up book_id = 13 in books: author_id = 3, which is Priya Anand. Notice we did not store her name in books — we stored the reference 3 and looked up the name in authors.
4. How many sales rows reference book 10? → Two (sale_id 100 and 102). One book can be sold many times; that is why sales has its own primary key.

---

Example B — Why repetition is the enemy

Imagine a badly designed schema where books stored the author's name directly:

-- Anti-pattern: do not do this
CREATE TABLE books_bad (
    book_id  INTEGER PRIMARY KEY,
    title    TEXT,
    author_name TEXT   -- repeated in every row
);

Book 10 and book 11 would both carry 'Alina Voss'. Now suppose her name changes (say, she marries and hyphenates). Fixing the bad design means updating every row that contains the string; in the good design you update one row in authors (where author_id = 1) and every query picks up the change automatically. This is the concrete payoff of storing each fact in exactly one place.

---

Example C — Tracing a link across three tables

Trace the chain: "Which country did the author of the best-selling book 10 come from?"

1. Find book 10 in books → author_id = 1.
2. Find author 1 in authors → country = 'Germany'.

Three tables, two lookups, one answer. Every join in Sessions 5–6 is this same chain of lookups, done in bulk and expressed in one SQL statement instead of by hand.

---

Example D — Types in action

Book 12 has price = 22.00 and qty = 1 in sale_id 101. The product 22.00 * 1 is a valid REAL computation (you will write exactly this in Session 3). By contrast, 'Germany' * 2 is nonsense — text has no multiplication. When you see a type error in a future drill, the fix is almost always "convert the value or use the correct column", never "multiply the text".