Chapter 1

Chapter 1 — Relational Database Concepts

Before writing a single line of SQL, it helps to understand what a relational database actually is and why it's structured the way it is. This chapter covers the core ideas — tables, rows, columns, keys, and relationships — and introduces the bookshop database that we'll build on throughout this entire course.

Course database. Every chapter in this course uses the same bookshop database. We'll build it up gradually — this chapter creates the schema, and subsequent chapters fill it with data and query it. By the end of the course you'll know it well, which makes the SQL examples easy to follow.

1. What Is a Relational Database?

A relational database stores data in tables — a structure you can think of as a spreadsheet with strict rules. Each table holds one type of thing (books, customers, orders), and the tables are linked together using relationships. That linking is the "relational" part.

Here's a simple example — a table of books:

book_id

title

author

genre

price

The Midnight Library

Matt Haig

Fiction

£8.99

Dune

Frank Herbert

Sci-Fi

£9.99

Sapiens

Yuval Noah Harari

Non-fiction

£10.99

Each horizontal line is a row (also called a record or tuple). Each vertical column holds one piece of information. Every row in this table describes exactly one book — no more, no less.

Table

A named collection of related data, organised into rows and columns. A database contains many tables. Think of it as one sheet in a spreadsheet — but with strict rules about what data is allowed.

Row (Record)

A single entry in a table — one book, one customer, one order. Every row in a table has the same set of columns, but different values.

Column (Field)

One attribute of the thing being described — the book's title, its price, its genre. Every column has a name and a data type (text, number, date) that constrains what values it can hold.

Cell

The intersection of a row and a column — a single value. The cell at row 2, column "title" contains "Dune". Cells can also be NULL, meaning the value is unknown or not applicable.

2. Primary Keys

Every table needs a way to uniquely identify each row. That's what a primary key does. In the books table above, book_id is the primary key — no two books share the same ID, and every book has one.

Primary keys have three rules:

Unique — no two rows can have the same primary key value.
Not NULL — every row must have a value; it can't be empty.
Stable — once set, the key shouldn't change. Book 2 is always book 2.

Use surrogate keys, not natural keys. It's tempting to use something that's "naturally unique" — an ISBN for a book, a passport number for a person — as a primary key. These are called natural keys. The problem is that natural keys can change (a book gets a new edition and a new ISBN), may not exist for all rows, or may be longer than needed. A simple auto-incrementing integer (book_id = 1, 2, 3…) called a surrogate key is almost always the better choice.

In MySQL, the most common pattern is an integer column that increments automatically:

-- AUTO_INCREMENT creates a new unique ID for every inserted row CREATE TABLE books ( book_id INT NOT NULL AUTO_INCREMENT, title VARCHAR(255) NOT NULL, price DECIMAL(6,2) NOT NULL, PRIMARY KEY (book_id) );

You don't insert the book_id — MySQL generates it automatically when you add a new row. The first book gets ID 1, the next gets ID 2, and so on, forever upwards.

3. Relationships & Foreign Keys

A single flat table can only get you so far. The power of a relational database comes from linking tables together. Consider the author problem: if we store the author's name inside the books table, we duplicate it for every book they've written — and if we need to correct a spelling, we'd have to update every row.

The solution is to put authors in their own table and link to them with a foreign key:

author_id

first_name

last_name

Matt

Haig

Frank

Herbert

Yuval Noah

Harari

book_id

title

author_id ↗

genre

price

The Midnight Library

Fiction

8.99

Dune

Sci-Fi

9.99

Sapiens

Non-fiction

10.99

The author_id column in the books table is a foreign key — it refers to the primary key of the authors table. MySQL can enforce this relationship: if you try to insert a book with author_id = 99 and there's no author with ID 99, MySQL will reject the insert. This is called referential integrity.

One-to-Many

The most common relationship. One author can write many books, but each book has one author. The "many" side (books) holds the foreign key pointing back to the "one" side (authors).

Many-to-Many

A customer can order many books; a book can be ordered by many customers. This requires a third junction table (e.g. order_items) that links both sides. Covered in the Advanced course.

One-to-One

Rare — used to split a table for security or performance reasons. Example: a customers table and a separate customer_payment_details table, linked 1:1.

Referential Integrity

MySQL's guarantee that foreign key values always point to a real row. You can't delete an author that still has books, and you can't add a book pointing to a non-existent author.

4. How SQL Works

SQL (Structured Query Language, pronounced "sequel" or "S-Q-L") is the language you use to talk to a relational database. You write a statement describing what data you want — not how to find it. The database engine figures out the how.

You write a SQL statement

e.g. SELECT title, price FROM books WHERE price < 10.00;

MySQL parses and validates it

Checks syntax is correct, that the tables and columns you named exist, and that you have permission to access them.

The query optimiser builds an execution plan

MySQL decides the most efficient way to retrieve the data — which indexes to use, what order to check conditions in. You don't control this directly.

MySQL executes the plan and returns a result set

The matching rows are returned to your client (MySQL Workbench, phpMyAdmin, your PHP script, etc.) as a table of results.

SQL is divided into sub-languages by what kind of statement you're writing:

DQL — Data Query Language

SELECT — reading data. The vast majority of SQL you'll write. Covered in Chapters 2, 3, 8, and 9.

DML — Data Manipulation Language

INSERT, UPDATE, DELETE — creating, modifying, and removing rows. Covered in Chapter 5.

DDL — Data Definition Language

CREATE, ALTER, DROP — defining and changing the structure of tables. Covered in Chapter 6.

TCL — Transaction Control Language

BEGIN, COMMIT, ROLLBACK — grouping statements into atomic units of work. Covered in Chapter 10.

5. The Bookshop Database

Throughout this course we'll work with a fictional online bookshop. It's simple enough to understand at a glance but realistic enough to demonstrate every concept properly. Here are the four core tables:

authors

author_id INT PK

first_name VARCHAR(100) NN

last_name VARCHAR(100) NN

nationality VARCHAR(60)

birth_year YEAR

books

book_id INT PK

author_id INT FK

title VARCHAR(255) NN

genre VARCHAR(60)

published_year YEAR

price DECIMAL(6,2) NN

stock INT

customers

customer_id INT PK

first_name VARCHAR(100) NN

last_name VARCHAR(100) NN

email VARCHAR(150) NN

joined_date DATE

city VARCHAR(80)

orders

order_id INT PK

customer_id INT FK

book_id INT FK

order_date DATETIME NN

quantity INT NN

total_price DECIMAL(8,2) NN

■ PK = Primary Key · ■ FK = Foreign Key · ■ NN = Not Null

The relationships: one author → many books; one customer → many orders; one book → many orders. The orders table sits at the centre, linking customers to books they've purchased.

6. Creating the Bookshop Database

Let's build it. Run these statements in MySQL Workbench, phpMyAdmin, or the MySQL command-line client to create the database and its four tables:

-- Create the database and switch to it CREATE DATABASE bookshop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE bookshop;

-- Authors table (no foreign keys — it's the "parent" table) CREATE TABLE authors ( author_id INT NOT NULL AUTO_INCREMENT, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, nationality VARCHAR(60), birth_year YEAR, PRIMARY KEY (author_id) );

-- Books table (references authors) CREATE TABLE books ( book_id INT NOT NULL AUTO_INCREMENT, author_id INT NOT NULL, title VARCHAR(255) NOT NULL, genre VARCHAR(60), published_year YEAR, price DECIMAL(6,2) NOT NULL, stock INT DEFAULT 0, PRIMARY KEY (book_id), FOREIGN KEY (author_id) REFERENCES authors(author_id) );

-- Customers table (no foreign keys) CREATE TABLE customers ( customer_id INT NOT NULL AUTO_INCREMENT, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, email VARCHAR(150) NOT NULL UNIQUE, joined_date DATE, city VARCHAR(80), PRIMARY KEY (customer_id) );

-- Orders table (references both customers and books) CREATE TABLE orders ( order_id INT NOT NULL AUTO_INCREMENT, customer_id INT NOT NULL, book_id INT NOT NULL, order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, quantity INT NOT NULL DEFAULT 1, total_price DECIMAL(8,2) NOT NULL, PRIMARY KEY (order_id), FOREIGN KEY (customer_id) REFERENCES customers(customer_id), FOREIGN KEY (book_id) REFERENCES books(book_id) );

Create tables in the right order. Because books references authors, you must create authors first. Similarly, orders references both customers and books, so it must be created last. If you create a table before its parent exists, MySQL will return an error: Cannot add foreign key constraint.

Verify the structure

Chapter Summary

Concept	What it means
Table	Named collection of rows and columns describing one type of thing.
Row / Record	A single entry in a table — one book, one customer, one order.
Column / Field	One attribute of each row, with a fixed name and data type.
Primary Key	A column (or combination) that uniquely identifies each row. Never NULL, never duplicate, never changes.
Foreign Key	A column that references the primary key of another table, creating a relationship.
Referential Integrity	MySQL's guarantee that foreign key values always point to a real, existing row.
AUTO_INCREMENT	MySQL automatically assigns the next available integer when a row is inserted.
SQL sub-languages	DQL (SELECT), DML (INSERT/UPDATE/DELETE), DDL (CREATE/ALTER/DROP), TCL (BEGIN/COMMIT/ROLLBACK).
bookshop schema	authors → books → orders ← customers. Used throughout the entire course.

Next: Chapter 2 — SELECT Basics. The bookshop database is built. Now let's query it — choosing columns, filtering rows with WHERE, and combining conditions with AND, OR, and NOT.