Chapter 1: Relational Databases & SQL

---

Metadata Card

Dimension	Value
Difficulty	(Beginner)
Prerequisites	Vol 4 Chapter 1 (Layering), any programming basics
Keywords	Database, SQL, DDL, DML, SELECT, JOIN, Transaction
Code Language	SQL (primary) / Java (diff window)

---

Your Progress

You stand in the foyer of the Data Fortress. Master Chen hands you a thick ledger, the cover bearing three words: Relational Database.

"You've accumulated quite a bit on your journey," Master Chen says. "Code, configs, logs, user data, transaction records — all stuffed in your pocket. CSV files, JSON folders, ArrayList in memory. Like a pocket full of odds and ends."

"Your pocket isn't enough anymore, is it?" He opens the ledger to the first page, which shows a table — rows and columns, neatly organized.

"This is called a relation. Not a relationship between people — a relationship between data. You put all your data into tables, each with a fixed structure (columns), each row being a record. Then you talk to it using a language called SQL. Tell it what you want, not how to get it."

Why Not Just Use Files?

When managing vault inventory with CSV files, problems arise:

• Concurrent writes: Two admins open the same file; last save wins — one's changes lost
• Crash recovery: Power fails mid-write; half the data is lost
• Query efficiency: Finding "all weapons worth > 200" requires reading the entire file
• Data consistency: Changing item type AND value requires two file writes; if one fails, data is inconsistent

Databases solve all these: concurrency control, crash recovery, declarative queries, transactional guarantees.

SQL — Structured Query Language

Not a programming language — it's a declarative DSL: You say what you want, not how to get it.

-- File approach: you write loops, sorting, filtering
-- Database approach: you just say what you want
SELECT name, value FROM vault_items WHERE item_type = 'Weapon' AND value > 200;

Installing SQLite

# Check if installed
sqlite3 --version

# If not:
# macOS: brew install sqlite3
# Ubuntu/Debian: sudo apt install sqlite3

CREATE TABLE

sqlite3 fortress.db

CREATE TABLE vault_items (
  item_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  item_type TEXT NOT NULL,
  value INTEGER NOT NULL,
  discovered_date TEXT
);

.tables

INSERT — Adding Data

INSERT INTO vault_items VALUES (1, 'Flame Sword', 'Weapon', 1200, '2024-01-15');
INSERT INTO vault_items (item_id, name, item_type, value, discovered_date)
VALUES (2, 'Healing Potion', 'Potion', 80, '2024-03-01');

SELECT — Querying Data

SELECT * FROM vault_items;
SELECT name, value FROM vault_items WHERE item_type = 'Weapon';
SELECT name, value FROM vault_items WHERE item_type = 'Weapon' AND value > 1000;
SELECT name, value FROM vault_items ORDER BY value DESC LIMIT 3;
SELECT name FROM vault_items WHERE name LIKE '%Sword%';

UPDATE & DELETE

UPDATE vault_items SET value = 1500 WHERE name = 'Flame Sword';
DELETE FROM vault_items WHERE item_id = 5;

JOIN — Connecting Tables

SELECT a.name, q.quest_name
FROM adventurers a
JOIN quests q ON a.adventurer_id = q.adventurer_id;

GROUP BY & Aggregation

SELECT item_type, COUNT(*), AVG(value), MAX(value)
FROM vault_items
GROUP BY item_type;

Transactions

BEGIN TRANSACTION;
UPDATE vault SET balance = balance - 500 WHERE owner = 'Alice';
UPDATE vault SET balance = balance + 500 WHERE owner = 'Bob';
COMMIT;

---

Traveler's Notes

Databases are the warehouses of the digital world. From a simple adventurers table, you'll build an entire storage system. The key insight: databases don't just store data — they manage it, protect it, and help you find exactly what you need, when you need it. Start with SQLite, graduate to PostgreSQL, but always keep the relational model's clarity in mind.