Relational Foundations

Understanding SQL and Relational Databases

A foundations guide, from first principles to real-world practice

CHAPTER 1What a Relational Database Actually Is

A relational database stores data as a set of tables. Each table is a grid: columns define what kind of information is stored, rows hold the actual records. The word "relational" does not refer to relationships between tables (a common misconception). It comes from the mathematical term relation, which is what a table formally is: a set of tuples over named attributes.

The model was proposed by E.F. Codd at IBM in 1970, and its core promise is still the reason SQL dominates fifty years later: you describe what data you want, and the database figures out how to get it. This is called declarative querying. You never write "loop over the users file, check each record's city field". You write:

SELECT name FROM users WHERE city = 'Delhi';

The database engine (PostgreSQL, MySQL, SQLite, SQL Server, Oracle) parses this, builds a query plan, decides whether to use an index or scan the whole table, and returns the result. The engine is free to change strategies as the data grows, and your query stays the same.

Three ideas define the relational world, and every chapter of this book expands on one of them:

CHAPTER 2Tables, Rows, Columns, and Types

A table is created with a CREATE TABLE statement that declares every column and its type:

CREATE TABLE users (
    id          SERIAL PRIMARY KEY,
    username    VARCHAR(50) NOT NULL,
    email       VARCHAR(255) NOT NULL UNIQUE,
    is_active   BOOLEAN DEFAULT TRUE,
    balance     NUMERIC(10, 2) DEFAULT 0.00,
    created_at  TIMESTAMP DEFAULT NOW()
);

Read this line by line:

Common types you will meet everywhere

Type What it holds Notes
INTEGER / BIGINT Whole numbers BIGINT for IDs at scale
VARCHAR(n) / TEXT Strings TEXT is unbounded
BOOLEAN true/false MySQL fakes it with TINYINT(1)
NUMERIC(p, s) Exact decimals Always use for money
TIMESTAMP / DATE Time values Prefer TIMESTAMPTZ in Postgres
JSON / JSONB Embedded JSON Postgres can index inside JSONB
UUID Random unique IDs Alternative to serial integers

The important cultural point: in SQL, types are enforced. Inserting the string 'abc' into an INTEGER column is an error, not a silent coercion. The schema is a contract, and the database is the enforcer of that contract.

NULL, the special non-value

NULL means "no value" and behaves strangely on purpose. NULL = NULL is not true, it is NULL. You test for it with IS NULL and IS NOT NULL. Any arithmetic or comparison involving NULL produces NULL. This trips up every beginner at least once, usually inside a WHERE clause that mysteriously drops rows.

CHAPTER 3Keys: How Rows Get Identity

Primary keys

Every well-designed table has a primary key (PK): one column (or a combination) whose value uniquely identifies each row and can never be null. Two common styles:

Foreign keys

A foreign key (FK) is a column in one table that holds the primary key of a row in another table. It is both a pointer and a rule:

CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    user_id     INTEGER NOT NULL REFERENCES users(id),
    total       NUMERIC(10, 2) NOT NULL,
    created_at  TIMESTAMP DEFAULT NOW()
);

REFERENCES users(id) means: every orders.user_id must match an existing users.id. Try to insert an order for user 999 when no such user exists, and the database refuses. Try to delete a user who still has orders, and the database refuses that too (by default). This property is called referential integrity, and it is one of the strongest guarantees relational databases offer. Your data cannot silently rot into a state where orders point at ghosts.

Cascade behavior

You can tell the FK what to do when the parent row disappears:

user_id INTEGER REFERENCES users(id) ON DELETE CASCADE

This single declaration replaces what would otherwise be careful, easy-to-forget cleanup code in the application.

CHAPTER 4Relationships: How Tables Connect

Relational design recognizes three relationship shapes.

One-to-many (the workhorse)

One user has many orders; each order belongs to one user. Implemented with a foreign key on the "many" side, exactly as in the previous chapter. This covers the vast majority of relationships in real schemas.

One-to-one

One user has one profile. Implemented as a separate table whose primary key is also a foreign key to the parent, or with a UNIQUE foreign key. Used to split rarely-accessed or optional data out of a hot table.

Many-to-many (the junction table)

Students take many courses; courses have many students. Neither table can hold the FK, because both sides are "many". The solution is a third table, called a junction table (also association table or join table):

CREATE TABLE enrollments (
    student_id  INTEGER REFERENCES students(id),
    course_id   INTEGER REFERENCES courses(id),
    enrolled_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (student_id, course_id)
);

Each row is one link. The composite primary key prevents duplicate enrollments. Junction tables often grow extra columns of their own (a grade, a timestamp, a role), at which point they quietly become real entities.

Recognizing these three shapes on sight is most of what "reading a schema" means.

CHAPTER 5The SQL Language, Part 1: Reading Data

SELECT is the heart of SQL. Its clauses always appear in this order:

SELECT columns
FROM table
WHERE row_filter
GROUP BY grouping
HAVING group_filter
ORDER BY sorting
LIMIT count OFFSET skip;

A few examples, from trivial to useful:

-- Everything (fine for exploring, avoid in application code)
SELECT * FROM users;

-- Specific columns, filtered and sorted
SELECT username, email
FROM users
WHERE is_active = TRUE AND created_at > '2026-01-01'
ORDER BY created_at DESC
LIMIT 20;

-- Pattern matching and set membership
SELECT * FROM users WHERE email LIKE '%@iitd.ac.in';
SELECT * FROM orders WHERE status IN ('pending', 'processing');

-- Ranges and null checks
SELECT * FROM orders WHERE total BETWEEN 100 AND 500;
SELECT * FROM users WHERE deleted_at IS NULL;

Key operators to have in your fingers: =, <> (not equal), <, >, AND, OR, NOT, IN, BETWEEN, LIKE (with % as wildcard), IS NULL.

Logical execution order

The clauses are written in one order but evaluated in another: FROM, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY, then LIMIT. This explains a classic beginner error: you cannot use a column alias defined in SELECT inside WHERE, because WHERE runs first.

Subqueries

A query can nest inside another:

SELECT username FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

Subqueries are readable for small cases; joins (next chapter) usually scale better.

CHAPTER 6Joins: Combining Tables

Normalization splits facts across tables. Joins reassemble them. A join matches rows from two tables based on a condition, almost always PK = FK.

SELECT users.username, orders.total, orders.created_at
FROM users
JOIN orders ON orders.user_id = users.id
WHERE orders.total > 500;

The join family

The one to internalize deeply is LEFT JOIN, because "show me all X, even the ones without Y" is an everyday business question:

-- Users who have never ordered anything
SELECT users.username
FROM users
LEFT JOIN orders ON orders.user_id = users.id
WHERE orders.id IS NULL;

Joining through a junction table

Many-to-many queries chain two joins:

SELECT students.name, courses.title
FROM students
JOIN enrollments ON enrollments.student_id = students.id
JOIN courses ON courses.id = enrollments.course_id;

Joins are the feature MongoDB and other document stores deliberately de-emphasize, which is why understanding them clarifies both worlds: SQL keeps data apart and joins at read time; document databases pre-join data at write time by embedding.

CHAPTER 7Aggregation: Asking Questions of Groups

Aggregate functions collapse many rows into one value: COUNT, SUM, AVG, MIN, MAX.

SELECT COUNT(*) FROM orders;
SELECT AVG(total) FROM orders WHERE status = 'completed';

GROUP BY runs aggregates per group instead of over the whole table:

-- Revenue per user
SELECT user_id, COUNT(*) AS order_count, SUM(total) AS revenue
FROM orders
GROUP BY user_id
ORDER BY revenue DESC;

Every column in the SELECT must either be inside an aggregate function or appear in GROUP BY. This rule feels bureaucratic until you realize it prevents ambiguous results.

HAVING filters groups after aggregation, the way WHERE filters rows before it:

-- Only users with more than 5 orders
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5;

Combined with joins, this is the reporting engine of the business world:

SELECT users.username, SUM(orders.total) AS lifetime_value
FROM users
JOIN orders ON orders.user_id = users.id
GROUP BY users.username
HAVING SUM(orders.total) > 10000
ORDER BY lifetime_value DESC
LIMIT 10;

CHAPTER 8The SQL Language, Part 2: Writing Data

-- Insert
INSERT INTO users (username, email) VALUES ('lakshay', 'l@example.com');

-- Insert multiple rows
INSERT INTO users (username, email) VALUES
    ('a', 'a@x.com'),
    ('b', 'b@x.com');

-- Update
UPDATE users SET is_active = FALSE WHERE last_login < '2025-01-01';

-- Delete
DELETE FROM orders WHERE status = 'cancelled';

Two safety habits that separate professionals from horror stories:

  1. Never run UPDATE or DELETE without a WHERE clause unless you truly mean every row. UPDATE users SET is_active = FALSE; deactivates every user in the system, instantly, with no confirmation prompt.
  2. Test destructive statements as a SELECT first. Write SELECT * FROM orders WHERE status = 'cancelled', eyeball the rows, then change the verb to DELETE.

Modern engines also support upserts, insert-or-update in one statement:

INSERT INTO settings (key, value) VALUES ('theme', 'dark')
ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value;

CHAPTER 9Constraints: The Database as a Guardian

Constraints are rules attached to the schema that the engine enforces on every write, from every application, forever. This is the philosophical core of relational databases: the database defends its own correctness, rather than trusting each application to behave.

Why this matters in practice: a bug in one code path cannot create a negative price, an order without a customer, or two accounts with the same email. The invalid write is rejected at the last line of defense. Systems that move to schemaless databases lose this safety net and must rebuild it in application code, which is precisely why constraint awareness matters when comparing SQL with MongoDB.

CHAPTER 10Transactions and ACID

A transaction groups several statements into one all-or-nothing unit:

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

If anything fails between BEGIN and COMMIT (a constraint violation, a crash, a ROLLBACK), neither update happens. Money is never half-transferred.

The famous ACID acronym describes what transactions guarantee:

ACID transactions across multiple tables are effortless and cheap in SQL. This is a genuine differentiator: many NoSQL systems either lack multi-document transactions or make them expensive and conditional. Any workload where partial writes are unacceptable (payments, inventory, bookings) leans on this heavily.

CHAPTER 11Indexes: Why Queries Are Fast (or Slow)

Without an index, SELECT * FROM users WHERE email = 'x@y.com' reads every row in the table: a full table scan, O(n). An index is a separate sorted structure (almost always a B-tree) that maps column values to row locations, making lookups O(log n):

CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);

Facts worth knowing:

CHAPTER 12Normalization: Designing Good Schemas

Normalization is the discipline of storing each fact exactly once. The classic normal forms, in plain language:

The practical payoff: update anomalies disappear. If a customer's city is stored in one place, changing it is one UPDATE of one row. Stored in a thousand order rows, it requires a mass update and will eventually drift into inconsistency.

The practical cost: normalized data is spread across tables, so reads need joins. This is the eternal tradeoff, and it explains denormalization: deliberately duplicating data (a cached order_count on the user row, a snapshot of the product price on the order line) to make hot reads cheaper, accepting the burden of keeping copies in sync. Mature schemas are normalized by default and denormalized surgically, with reasons written down.

CHAPTER 13Schema Migrations: Evolving a Live Database

The schema is code-adjacent: it changes as the product changes. But unlike code, you cannot just redeploy it, because the database is full of data that must survive the change. Migrations solve this: small, versioned, ordered scripts that transform the schema step by step.

The raw SQL for schema change is DDL (Data Definition Language):

ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users DROP COLUMN legacy_flag;
ALTER TABLE users RENAME COLUMN username TO handle;

In practice nobody runs these by hand. A migration tool keeps a folder of scripts and a bookkeeping table inside the database recording which have run. In the Python world the standard tool is Alembic (paired with SQLAlchemy). A project using it has:

alembic.ini
alembic/
    env.py
    versions/
        20260114_a1b2c3_create_users.py
        20260201_d4e5f6_add_orders.py

Each version file has an upgrade() function applying the change and a downgrade() reversing it:

def upgrade():
    op.add_column('users', sa.Column('phone', sa.String(20)))

def downgrade():
    op.drop_column('users', 'phone')

Running alembic upgrade head applies all pending migrations in order, on your laptop, in CI, and in production, guaranteeing every environment has the same schema. Migrations are the operational heartbeat of a SQL codebase: the full history of the database's shape, in version control, reviewable in pull requests. When a project abandons SQL entirely, this whole apparatus becomes dead weight and should be removed, and its absence is one of the clearest signs a codebase no longer depends on a relational store.

CHAPTER 14ORMs: Talking to SQL from Python

An ORM (Object-Relational Mapper) maps tables to classes and rows to objects, so application code manipulates objects and the ORM emits SQL. The dominant Python ORM is SQLAlchemy; Django has its own built-in ORM.

A SQLAlchemy model:

from sqlalchemy import Column, Integer, String, ForeignKey, Numeric
from sqlalchemy.orm import declarative_base, relationship

Base = declarative_base()

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    username = Column(String(50), nullable=False)
    email = Column(String(255), unique=True, nullable=False)
    orders = relationship("Order", back_populates="user")

class Order(Base):
    __tablename__ = "orders"
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
    total = Column(Numeric(10, 2), nullable=False)
    user = relationship("User", back_populates="orders")

And the plumbing that connects it to a real database, which in a FastAPI app usually looks like:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine("postgresql://user:pass@localhost/mydb")
SessionLocal = sessionmaker(bind=engine)

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

Usage reads like Python but executes as SQL:

user = db.query(User).filter(User.email == "l@x.com").first()
user.orders  # lazy-loads via a JOIN or second query
db.add(Order(user_id=user.id, total=499.00))
db.commit()

Two ideas to understand about ORMs:

An ORM does not hide SQL so much as generate it; strong developers read the generated SQL when things get slow.

CHAPTER 15Strengths, Limits, and When SQL Is the Right Tool

Where SQL excels:

Where it strains:

The mature position is not "SQL vs NoSQL" as a religion but as a fit question: what shape is the data, what guarantees does the business need, what queries will be asked? Relational databases remain the default answer for most transactional applications, and the burden of proof sits on the alternative. Understanding why (constraints, transactions, joins, normalization) is exactly what this book has tried to build.

End of guide.