Advanced Database Concepts

In this lesson

• Table design
• Foreign keys & CASCADE
• Backup & restore
• SQL injection

In this lesson we look at what we can do with a database once it exists. We shape a table, keep it safe with backups, and ask it questions by sorting and counting. We finish with one important safety idea called SQL injection.

Note. For the activities below, we use one database called bookshop and one main table called books. We build it up together, one step at a time, so we are always working with something familiar. Later we add one small partner table so we can see how two tables link. Everything runs in MySQL Workbench.

Each idea comes with a short explanation, the SQL we run, and a small activity to try before moving on. Take your time, and feel free to run every example yourself.

Part 1 · Shaping a tableFrom an empty database to a real table

Six small steps, each one building on the last. The same table follows us through the rest of the lesson.

1 · Create the database

Before we can make any tables, we need somewhere to keep them. CREATE DATABASE tells MySQL to start a fresh, empty space with the name we give it. We run this in MySQL Workbench, then click the refresh icon on the Schemas panel to see our new bookshop appear.

CREATE DATABASE bookshop;

Create a database called bookshop in MySQL Workbench. Then double-click it in the Schemas panel so it becomes your active database (its name turns bold).

2 · Create a table

A table is just a grid of rows and columns, a bit like a spreadsheet with rules. Every column needs a name and a data type. We use INT for whole numbers, VARCHAR(100) for short text (100 is the longest it can hold), and DECIMAL for money. For now we make price an INT on purpose, and we fix that in step 4.

USE bookshop;

CREATE TABLE books (
  id     INT,
  title  VARCHAR(100),
  author VARCHAR(100),
  price  INT
);

Inside bookshop, create a table called books with four columns: id (INT), title (VARCHAR 100), author (VARCHAR 100) and price (INT).

3 · Add a new column

Tables are not set in stone. With ALTER TABLE ADD COLUMN we can add a new field at any time, and none of the data we already have is lost. Let us say the shop now wants to keep track of how many copies of each book are in stock.

ALTER TABLE books ADD COLUMN stock_count INT;

Add a new column called stock_count (INT) to the books table.

📍 Where does the new column go?

By default a new column lands at the very end of the table. If we want it somewhere else, we add AFTER a column name, or FIRST to put it at the front.

id · title · author · price · stock_count

With no position given, the new column goes to the very end. This is the default.

ALTER TABLE books ADD COLUMN pages INT AFTER title;

id · title · pages · author · price

AFTER title drops the new column in right after the title column.

ALTER TABLE books ADD COLUMN sku INT FIRST;

sku · id · title · author · price

FIRST moves the new column to the very front of the table.

Position is just about the order the columns are listed in. It does not change any of the data inside them.

4 · Change a column's data type

Right now price is an INT, so it can only hold whole numbers. But a book costs $19.99, not $19. MODIFY COLUMN lets us change the type of a column we already have. DECIMAL(6,2) means up to 6 digits in total, with 2 of them after the decimal point, which is perfect for prices.

ALTER TABLE books MODIFY COLUMN price DECIMAL(6,2);

Change the price column from INT to DECIMAL(6,2) so it can hold cents.

⚠️ Watch out: changing a type can change the data

Changing a column type is not always free. Sometimes the values inside change too. Here is what happens in three common cases. Green means the value is kept, red means it changes.

Decimals are rounded to the nearest whole number, so the cents are lost for good. Values that were already whole survive unchanged.

The value is kept, but it is now text. Sorting changes too, so "100" can come before "20".

Clean number text converts back fine. Anything that is not a number turns into 0.

The lesson: always back up before a big type change, so we can restore if a value is lost. That is exactly what the backup section is for.

5 · Make a column the primary key

A primary key is the column that gives every row its own identity. No two rows can share the same value, and it can never be left blank. id is the obvious choice here, because every book gets its own number and nothing else has to be unique.

ALTER TABLE books ADD PRIMARY KEY (id);

Make id the primary key of the books table.

6 · Make a column auto-increment

Typing an id by hand for every new book is slow and easy to get wrong. AUTO_INCREMENT asks MySQL to do the counting for us. When we add a book without giving an id, MySQL fills in the next free number by itself (1, 2, 3 and so on). In MySQL a column has to be a key before it can auto-increment, which is why we did step 5 first.

ALTER TABLE books MODIFY COLUMN id INT AUTO_INCREMENT;

Make id AUTO_INCREMENT, then add a new book without giving it an id. Leave id out of the column list and watch MySQL fill in the number for you.

Before we sort and count, our table needs a few real rows to work with. We run this INSERT once, and then we have five books to play with for the rest of the lesson.

INSERT INTO books (title, author, price, stock_count) VALUES
('Atomic Habits',            'James Clear',     24.99, 12),
('Sapiens',                  'Yuval N. Harari', 29.50,  7),
('The Pragmatic Programmer', 'David Thomas',    42.00,  3),
('Educated',                 'Tara Westover',   18.75, 20),
('Deep Work',                'Cal Newport',     22.00,  9);

This is what books now holds

Part 2 · Linking tablesForeign keys, and what CASCADE does

Real databases have many tables, and they point at each other. A foreign key is a column that points to a row in another table. CASCADE (and its relatives) decide what happens to that link when the row it points to is deleted or changed.

Note. We add one small partner table called reviews, where each review points to a book. Every demo below reuses these exact same two tables — only the rule changes, so it is easier to see what each one does.

Here each review has a book_id that points to a book in books — in plain English, "this review belongs to that book." The one tricky question is: what should happen to a review if the book it belongs to gets deleted, or gets a new id? We answer that question with a rule, set right when we create the table.

CREATE TABLE reviews (
  id      INT PRIMARY KEY,
  book_id INT,
  comment VARCHAR(200),
  FOREIGN KEY (book_id) REFERENCES books(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

Delete a book, and all of its reviews are deleted with it, automatically. The delete cascades down to the linked rows.

Delete a book, and its reviews are kept — but their book_id resets to NULL, meaning "we no longer know which book this was."

ON DELETE / UPDATE SET DEFAULT

Reset the foreign key to a default value chosen up front, like a placeholder book_id. Not supported by MySQL.

Change a book's id, and every review that points to it updates to match, so no review is left pointing at the wrong book.

MySQL refuses to delete or renumber a book while reviews still point to it, to avoid leaving broken links.

Note. Three small scenarios below, each with the same click-and-see setup: pick a rule, then try the action, and watch the two tables react.

🗑️ Scenario 1 — what happens when you delete a book?

reviews (book_id points to books.id)

✏️ Scenario 2 — what happens when a book's id changes?

🧩 Scenario 3 — populating a default value instead

Some databases (PostgreSQL, SQL Server) offer a third reaction: ON DELETE SET DEFAULT. Instead of deleting the reviews (CASCADE) or blanking them out (SET NULL), the book_id resets to a default value chosen up front — here, a placeholder book with id = 0 called "(Unknown book)" that already sits in the table. Delete book #2, and its reviews quietly re-point at that placeholder instead of disappearing or breaking. MySQL's InnoDB does not support this: if you write ON DELETE SET DEFAULT, MySQL refuses to create the table at all. SET NULL is the closest MySQL gets to this idea.

Part 3 · Backup & restoreNever lose a database again

A backup is a safety net. We learn to make one, and prove it works by restoring from it.

A backup is just a saved copy of our whole database. Every table and every row is written out into a single file of plain SQL commands, such as CREATE TABLE and INSERT INTO. If our database is ever deleted or damaged, or we make a change we cannot undo, we open that file and restore everything exactly as it was.

💾 Backing up in MySQL Workbench

1. Open the Server menu → Data Export
2. Tick the bookshop schema
3. Choose Export to Self-Contained File and pick where to save it
4. Click Start Export

This gives us one .sql file, a complete snapshot of the database that we can keep safe.

♻️ Restoring in MySQL Workbench

1. Open the Server menu → Data Import
2. Choose Import from Self-Contained File and select the .sql file we saved
3. Under Default Target Schema, choose or create bookshop
4. Click Start Import

MySQL runs every command in the file again and rebuilds the database from scratch.

The same thing on the command line

Those buttons in Workbench are really running these two commands for us. It is handy to recognise them if you ever see them written down.

mysqldump -u root -p bookshop > bookshop_backup.sql

mysql -u root -p bookshop < bookshop_backup.sql

✏️ Your turn: lose it, then bring it back

1. Export bookshop to a self-contained file.
2. Right-click the bookshop schema and choose Drop Schema to delete it. This is safe, because we have a backup.
3. Use Data Import to bring it back from the file we saved.
4. Run SELECT * FROM books; to check that all of our books returned.

Part 4 · Sorting resultsORDER BY: putting rows in the order we want

We keep the same books table and the same data. ORDER BY only changes the order the rows come back in. We add ASC to go low to high, or DESC to go high to low, after the column name.

Note. We keep using the same books table we created and filled earlier. Nothing new to set up.

The query we are running

SELECT * FROM books ORDER BY price ASC;

Notice that only the order of the rows changes. The books themselves stay exactly the same.

Write a query that lists every book from cheapest to most expensive.

Write a query that lists every book title in reverse alphabetical order, from Z to A.

Part 5 · Counting rowsCOUNT: answering how many

COUNT tells us how many rows match, and nothing more. On its own, COUNT(*) counts every row. Add a WHERE and it counts only the rows we care about.

Note. Same books table again. Pick a filter below and watch the total change.

SELECT COUNT(*) FROM books;

The rows that match stay bright, and COUNT simply adds them up. Faded rows are left out of the total.

Write a query that counts how many books cost more than $20.

Stretch goal: count how many books we have for each author. (Hint: GROUP BY gathers matching rows together first, and then COUNT runs on each group.)

Part 6 · A safety topicSQL injection, in plain English

We do not need to write any code to understand this. We just need to see it happen once.

Many websites build a database command by pasting whatever we type straight into a sentence. A login form might build something like find the user named (whatever was typed). Most of the time that is fine. But if the site never checks what we typed, we could type something that is not a name at all. It could be a piece of a database command, and the database cannot tell the difference, so it simply runs it. That isSQL injection: slipping a command into a box that was only meant to hold a word.

What the database actually receives

SELECT * FROM users
WHERE username = '…'
  AND password = '…';

Everything here runs in your browser only. There is no real database and no real login, and nothing is sent anywhere.

How real systems stay safe

Never paste text into a command

We send whatever the user typed as a separate value, never as part of the command itself. That is exactly what the safe version above does.

A username box has no reason to accept quote marks or the word OR, so we can reject them before they cause trouble.

Give each account the least it needs

A login page never needs to delete tables, so we do not give it that power, even if something slips through.

Quick referenceEvery query from this lesson, in one place

Bookmark this. We will want it again during the practical lab.

• Add a column in a position
• Link tables, cascade on delete
• Make a column auto-increment

One database, one table, and a handful of small commands. We can now shape it, protect it, sort it and count it. The habit that matters most is the last one: we never trust text typed into a box, and we never paste it straight into a command.

Database Management Systems · Master of Business Informatics