Chapter 41: Relational Databases & SQL Fundamentals

Why the Relational Model?

E.F. Codd introduced the relational model in 1970 in his landmark paper A Relational Model of Data for Large Shared Data Banks. His central insight was that data should be described by its content — the values it holds — rather than by pointers or navigational paths. A relation is a set of tuples (rows), each conforming to a fixed set of attributes (columns), where every attribute draws its values from a named domain (data type). The word "table" is the everyday synonym for relation.

A relation has three defining properties:

• No duplicate rows. A relation is a mathematical set, so every row must be uniquely identifiable.
• No ordering of rows. The physical storage order carries no semantic meaning. Ordering is a query-time concern.
• Each cell holds one atomic value. You cannot store a list inside a single cell and still remain in first normal form.

These constraints are not bureaucratic rules; they are what makes querying predictable and optimization possible.

Keys

A candidate key is a minimal set of attributes whose values uniquely identify every row in a relation. "Minimal" means you cannot remove any attribute and still have uniqueness. From the candidate keys you choose one to be the primary key (PK) — the official row identifier. In PostgreSQL, declaring a column PRIMARY KEY creates a unique index and rejects NULLs automatically.

A foreign key (FK) is one or more columns in a "child" table whose values must match a primary key (or unique key) in a "parent" table — or be NULL. This referential integrity constraint is the mechanical enforcement of relationships between entities.

CREATE TABLE customers (
    customer_id  SERIAL PRIMARY KEY,
    email        TEXT    NOT NULL UNIQUE,
    full_name    TEXT    NOT NULL,
    country_code CHAR(2) NOT NULL
);

CREATE TABLE orders (
    order_id    SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
    order_date  DATE    NOT NULL DEFAULT CURRENT_DATE,
    total_usd   NUMERIC(12, 2) NOT NULL CHECK (total_usd >= 0)
);

The REFERENCES clause tells PostgreSQL: every customer<em>id stored in orders must exist in customers. Attempts to insert a row with a nonexistent customer will raise a foreign-key violation error immediately, before any row is committed.

Normalization

Normalization is the process of restructuring a schema to reduce data redundancy and the update anomalies that redundancy causes. The three most important normal forms are:

First Normal Form (1NF)

Every column holds atomic, indivisible values, and there are no repeating groups. A column storing comma-separated tags ("python,sql,ml") violates 1NF. Fix it by creating a separate item</em>tags table with one row per tag.

Second Normal Form (2NF)

A table is in 2NF if it is in 1NF and every non-key attribute is fully functionally dependent on the entire primary key. 2NF violations only arise when the PK is composite. If order<em>id, product</em>id is the PK of an order<em>lines table but product</em>name depends only on product<em>id, move product</em>name to a products table.

Third Normal Form (3NF)

A table is in 3NF if it is in 2NF and there are no transitive dependencies — no non-key column determines another non-key column. If zip<em>code determines city and state, storing all three in a customers table creates a transitive dependency. The fix is a zip</em>codes lookup table.

Why It Matters in Practice

In OLTP (Online Transaction Processing) systems, normalization prevents a single fact from being stored in multiple places, which means an UPDATE only needs to touch one row. In analytical (OLAP) workloads, schemas are often intentionally denormalized (star schema, wide tables) because read performance and query simplicity outweigh update cost. Understanding normalization lets you make that tradeoff deliberately rather than by accident.

A Working Sample Schema

The rest of this chapter uses these four tables. Run the DDL to follow along:

CREATE TABLE products (
    product_id   SERIAL PRIMARY KEY,
    name         TEXT           NOT NULL,
    category     TEXT           NOT NULL,
    price_usd    NUMERIC(10,2)  NOT NULL,
    in_stock     BOOLEAN        NOT NULL DEFAULT TRUE
);

INSERT INTO products (name, category, price_usd) VALUES
  ('Wireless Keyboard', 'Electronics',  49.99),
  ('USB-C Hub',         'Electronics',  29.99),
  ('Standing Desk',     'Furniture',   349.00),
  ('Ergonomic Chair',   'Furniture',   499.00),
  ('Notebook (A5)',     'Stationery',    4.99),
  ('Ballpoint Pens',   'Stationery',    7.49),
  ('Monitor Arm',      'Electronics',  89.95);

Anatomy of a SELECT Statement

The SELECT statement is the core of SQL. Its logical evaluation order differs from its written order, and misunderstanding that order is the root cause of many confusing error messages.

The written order is:

SELECT  ...
FROM    ...
WHERE   ...
GROUP BY ...
HAVING  ...
ORDER BY ...
LIMIT   ...

The logical evaluation order is: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. The implication is that column aliases defined in SELECT cannot be referenced in WHERE, because WHERE is evaluated before SELECT. PostgreSQL does allow aliases in ORDER BY as a convenience extension, but not all databases do.

Projections and Column Expressions

A projection selects a subset of columns from a table. Use SELECT <em> during exploration, but name columns explicitly in production code — schema changes silently break </em> queries.

-- All columns (exploration only)
SELECT * FROM products;

-- Named projection
SELECT product_id, name, price_usd FROM products;

Columns can be any valid expression, not just bare column names:

SELECT
    name,
    price_usd,
    price_usd * 1.08               AS price_with_tax,
    UPPER(category)                AS category_upper,
    LENGTH(name)                   AS name_length
FROM products;

Column Aliases

The AS keyword gives a column expression a readable name in the result set. AS is optional — price<em>usd * 1.08 price</em>with_tax also works — but omitting it makes queries harder to read. Alias names that contain spaces or capital letters must be double-quoted: AS "Price (USD)". Single-quoted aliases are a syntax error in standard SQL.

DISTINCT

SELECT DISTINCT eliminates duplicate rows from the result set. It operates on the entire row as returned by the projection, not just the first column.

-- How many unique categories exist?
SELECT DISTINCT category FROM products ORDER BY category;

  category
-------------
 Electronics
 Furniture
 Stationery

DISTINCT is often overused as a band-aid for duplicates that should have been prevented by proper joins or aggregation. If you are surprised to need DISTINCT, investigate whether your join is producing a Cartesian product.

Ordering Results

ORDER BY sorts the result. Specify ASC (default) or DESC per column. You can sort by column name, column alias, or ordinal position (though ordinal position is fragile and discouraged).

SELECT name, category, price_usd
FROM products
ORDER BY category ASC, price_usd DESC;

This sorts alphabetically by category first, then by price descending within each category. NULL values sort last in ascending order and first in descending order by default in PostgreSQL. Use NULLS FIRST or NULLS LAST to override:

ORDER BY price_usd DESC NULLS LAST

LIMIT and OFFSET

LIMIT n returns at most n rows. OFFSET k skips the first k rows. Together they implement pagination:

-- Page 2 of results, 3 rows per page
SELECT product_id, name, price_usd
FROM products
ORDER BY product_id
LIMIT 3 OFFSET 3;

Pitfall: LIMIT without ORDER BY returns a non-deterministic subset. Two identical queries can return different rows if the query planner chooses different access paths. Always pair LIMIT with ORDER BY in production.

Computed Columns and Type Casting

PostgreSQL uses :: for casting and the standard CAST(x AS type) syntax:

SELECT
    name,
    price_usd::INTEGER              AS price_rounded_down,
    CAST(price_usd AS TEXT)         AS price_text,
    ROUND(price_usd, 0)             AS price_nearest_dollar
FROM products;

Be aware that ::INTEGER truncates (rounds toward zero), while ROUND() rounds to nearest. These produce different results for values like 49.7.

The WHERE Clause

WHERE filters the rows returned by a query. Only rows for which the WHERE condition evaluates to TRUE are included in the result. This sounds trivial, but the three-valued logic introduced by NULL makes it subtler than it appears.

Comparison Operators

PostgreSQL supports the standard comparison operators: =, <> (or !=), <, <=, >, >=. These work on numbers, strings, and dates:

-- Products priced below $50
SELECT name, price_usd FROM products WHERE price_usd < 50;

-- Electronics category only
SELECT name, price_usd FROM products WHERE category = 'Electronics';

-- Not furniture
SELECT name, category FROM products WHERE category <> 'Furniture';

Logical Operators: AND, OR, NOT

Combine conditions with AND, OR, and NOT. AND has higher precedence than OR — always use parentheses when mixing them to make intent explicit:

-- Electronics OR items under $10 (parentheses clarify grouping)
SELECT name, category, price_usd
FROM products
WHERE category = 'Electronics'
   OR price_usd < 10;

-- Electronics AND under $50
SELECT name, price_usd
FROM products
WHERE category = 'Electronics'
  AND price_usd < 50;

BETWEEN, IN, LIKE

BETWEEN a AND b is inclusive on both ends — equivalent to >= a AND <= b.

SELECT name, price_usd FROM products
WHERE price_usd BETWEEN 20 AND 100;

IN (list) is shorthand for multiple OR-equality conditions:

SELECT name FROM products
WHERE category IN ('Electronics', 'Furniture');

LIKE matches string patterns. % matches any sequence of characters; <em> matches exactly one character. Use ILIKE in PostgreSQL for case-insensitive matching:

-- Names containing "keyboard" (case-insensitive)
SELECT name FROM products WHERE name ILIKE '%keyboard%';

-- Names starting with exactly 5 characters then a space
SELECT name FROM products WHERE name LIKE '_____ %';

Three-Valued Logic and NULL

NULL in SQL does not mean zero, empty string, or false. NULL means unknown or inapplicable. This single fact is responsible for a disproportionate share of SQL bugs in production systems.

SQL uses three-valued logic: every predicate evaluates to TRUE, FALSE, or UNKNOWN. Any comparison involving NULL evaluates to UNKNOWN:

Because WHERE only admits TRUE rows, UNKNOWN rows are silently excluded. The following query returns zero rows even if NULLs exist in price</em>usd:

-- WRONG: never matches NULL prices
SELECT * FROM products WHERE price_usd = NULL;

-- CORRECT: use IS NULL
SELECT * FROM products WHERE price_usd IS NULL;

-- CORRECT: non-NULL prices
SELECT * FROM products WHERE price_usd IS NOT NULL;

NULL Propagation in Expressions

NULL propagates through arithmetic and string expressions: NULL + 1 is NULL, NULL || 'hello' is NULL (in PostgreSQL). The COALESCE function is the standard tool for substituting a default value:

-- Return 0.00 if discount_usd is NULL
SELECT name, COALESCE(discount_usd, 0.00) AS discount FROM products;

NULLIF(a, b) returns NULL when a = b, and a otherwise — useful for avoiding division-by-zero:

-- Avoid division by zero: return NULL instead
SELECT revenue / NULLIF(units_sold, 0) AS avg_revenue_per_unit
FROM sales;

Common NULL Pitfalls

• COUNT() vs COUNT(col): COUNT(</em>) counts all rows; COUNT(col) counts non-NULL values. They can differ significantly.
• NOT IN with NULLs: If a subquery in NOT IN returns even one NULL, the entire NOT IN evaluates to UNKNOWN for every row, silently returning zero rows. Prefer NOT EXISTS or filter NULLs explicitly.
• Aggregates ignore NULLs: AVG(col) averages only the non-NULL rows. If this differs from averaging-over-all-rows (treating NULL as zero), the results will be wrong.

-- Demonstration: COUNT(*) vs COUNT(column)
-- (Add a NULL discount for illustration)
ALTER TABLE products ADD COLUMN discount_usd NUMERIC(6,2);
UPDATE products SET discount_usd = 5.00 WHERE product_id IN (1, 3);

SELECT
    COUNT(*)             AS total_rows,
    COUNT(discount_usd)  AS rows_with_discount,
    AVG(discount_usd)    AS avg_discount_nonnull
FROM products;
-- total_rows=7, rows_with_discount=2, avg_discount_nonnull=5.00

SELECT
    name,
    LOWER(name)                        AS name_lower,
    UPPER(category)                    AS cat_upper,
    LENGTH(name)                       AS name_len,
    TRIM('  hello  ')                  AS trimmed,
    SUBSTRING(name FROM 1 FOR 8)       AS first_8,
    REPLACE(name, ' ', '_')            AS name_slug,
    POSITION('Desk' IN name)           AS desk_pos
FROM products
WHERE category = 'Furniture';

SELECT
    price_usd,
    ROUND(price_usd, 1)   AS rounded_1dp,
    CEIL(price_usd)       AS ceiling,
    FLOOR(price_usd)      AS floor,
    ABS(-price_usd)       AS absolute,
    MOD(product_id, 2)    AS parity        -- 0 = even, 1 = odd
FROM products;

SELECT
    CURRENT_DATE                            AS today,
    CURRENT_TIMESTAMP                       AS now,
    EXTRACT(YEAR FROM CURRENT_DATE)         AS current_year,
    EXTRACT(DOW  FROM CURRENT_DATE)         AS day_of_week,  -- 0=Sun
    CURRENT_DATE - INTERVAL '30 days'       AS thirty_ago,
    DATE_TRUNC('month', CURRENT_TIMESTAMP)  AS month_start,
    AGE('2030-01-01'::DATE, CURRENT_DATE)   AS time_to_2030;

-- Searched CASE (most flexible)
SELECT name, price_usd,
    CASE
        WHEN price_usd < 20   THEN 'budget'
        WHEN price_usd < 100  THEN 'mid-range'
        ELSE                       'premium'
    END AS price_tier
FROM products;

-- Simple CASE (equality checks only)
SELECT name, category,
    CASE category
        WHEN 'Electronics' THEN 'Tech'
        WHEN 'Furniture'   THEN 'Office'
        ELSE                    'Other'
    END AS category_group
FROM products;

SELECT
    COUNT(*)                    AS total_products,
    COUNT(DISTINCT category)    AS num_categories,
    SUM(price_usd)              AS total_list_value,
    AVG(price_usd)              AS avg_price,
    MIN(price_usd)              AS cheapest,
    MAX(price_usd)              AS most_expensive
FROM products;

SELECT
    category,
    COUNT(*)          AS num_products,
    ROUND(AVG(price_usd), 2) AS avg_price,
    MIN(price_usd)    AS min_price,
    MAX(price_usd)    AS max_price
FROM products
GROUP BY category
ORDER BY avg_price DESC;

  category   | num_products | avg_price | min_price | max_price
-------------+--------------+-----------+-----------+-----------
 Furniture   |            2 |    424.00 |    349.00 |    499.00
 Electronics |            3 |     56.64 |     29.99 |     89.95
 Stationery  |            2 |      6.24 |      4.99 |      7.49

-- Only categories with more than 1 product AND avg price > $30
SELECT
    category,
    COUNT(*)                    AS num_products,
    ROUND(AVG(price_usd), 2)    AS avg_price
FROM products
GROUP BY category
HAVING COUNT(*) > 1
   AND AVG(price_usd) > 30
ORDER BY avg_price DESC;

Rule of thumb: WHERE filters rows before grouping; HAVING filters groups after aggregation. For best performance, push as much filtering as possible into WHERE so fewer rows enter the aggregation step.

```sql SELECT category, COUNT() AS total, COUNT(*) FILTER (WHERE priceusd < 50) AS budgetcount, ROUND(AVG(priceusd) FILTER (WHERE instock), 2) AS avginstockprice FROM products GROUP BY category;

Data Definition Language (DDL)

DDL statements define and modify the structure of database objects. They include CREATE, ALTER, DROP, and TRUNCATE. Unlike DML statements, DDL changes in PostgreSQL are auto-committed — there is no implicit transaction wrapper you can roll back, so be careful.

CREATE TABLE

CREATE TABLE employees (
    employee_id  SERIAL         PRIMARY KEY,
    first_name   TEXT           NOT NULL,
    last_name    TEXT           NOT NULL,
    email        TEXT           NOT NULL UNIQUE,
    hire_date    DATE           NOT NULL DEFAULT CURRENT_DATE,
    salary_usd   NUMERIC(12, 2) NOT NULL CHECK (salary_usd > 0),
    department   TEXT,
    manager_id   INTEGER        REFERENCES employees(employee_id)  -- self-referential FK
);

Key column-level constraints:

• NOT NULL — rejects NULL values at insert/update time.
• UNIQUE — enforces uniqueness; unlike PRIMARY KEY, it allows NULLs (multiple NULLs are permitted in PostgreSQL because NULL != NULL).
• CHECK (expr) — the row is rejected if expr evaluates to FALSE. Note: CHECK does not reject NULLs — a NULL salary passes CHECK (salary<em>usd &gt; 0) because NULL > 0 is UNKNOWN, not FALSE.
• DEFAULT expr — the value used when a column is omitted from an INSERT.
• SERIAL — shorthand for an integer column with a sequence-backed default (equivalent to INTEGER GENERATED ALWAYS AS IDENTITY in modern SQL).

ALTER TABLE

ALTER TABLE modifies an existing table. Common operations:

-- Add a column
ALTER TABLE employees
    ADD COLUMN termination_date DATE;

-- Set a default on an existing column
ALTER TABLE employees
    ALTER COLUMN department SET DEFAULT 'General';

-- Add a table-level check constraint
ALTER TABLE employees
    ADD CONSTRAINT chk_termination
    CHECK (termination_date IS NULL OR termination_date > hire_date);

-- Rename a column (non-destructive)
ALTER TABLE employees
    RENAME COLUMN last_name TO surname;

-- Drop a column (destructive — data is lost)
ALTER TABLE employees
    DROP COLUMN termination_date;

Adding a column with NOT NULL and no default to a large table rewrites the entire table in older PostgreSQL versions. In PG 11+, adding a column with a non-volatile default is instant because the default is stored in the catalog, not backfilled row by row.

DROP TABLE

-- Drop if it exists (idempotent)
DROP TABLE IF EXISTS employees CASCADE;

CASCADE drops all objects that depend on the table (foreign keys referencing it, views built on it). Without CASCADE, PostgreSQL refuses to drop a table that other objects depend on, which is the safer default.

TRUNCATE table</em>name removes all rows from a table very fast (it does not scan rows) but, unlike DELETE, cannot be filtered. Use it to reset a table during testing.

Data Manipulation Language (DML)

DML modifies the data inside tables. DML statements run within transactions and can be rolled back.

INSERT

-- Single row
INSERT INTO employees (first_name, last_name, email, salary_usd, department)
VALUES ('Ada', 'Lovelace', 'ada@example.com', 95000.00, 'Engineering');

-- Multiple rows in one statement (much faster than one-by-one)
INSERT INTO employees (first_name, last_name, email, salary_usd, department)
VALUES
    ('Grace', 'Hopper',  'grace@example.com', 105000.00, 'Engineering'),
    ('Alan',  'Turing',  'alan@example.com',  115000.00, 'Research'),
    ('John',  'McCarthy','jmc@example.com',    98000.00, 'Research');

-- Insert from a query result
INSERT INTO employees_archive
SELECT * FROM employees WHERE hire_date < '2020-01-01';

INSERT ... ON CONFLICT is PostgreSQL's upsert mechanism:

INSERT INTO employees (email, first_name, last_name, salary_usd)
VALUES ('ada@example.com', 'Ada', 'Lovelace', 100000.00)
ON CONFLICT (email) DO UPDATE
    SET salary_usd = EXCLUDED.salary_usd;

EXCLUDED refers to the row that was rejected due to conflict. This pattern — insert if new, update if exists — is extremely common in ETL pipelines.

UPDATE

-- Give all Engineering employees a 10% raise
UPDATE employees
SET salary_usd = salary_usd * 1.10
WHERE department = 'Engineering';

-- Update multiple columns at once
UPDATE employees
SET
    department   = 'Applied Research',
    salary_usd   = 120000.00
WHERE email = 'alan@example.com';

Always include a WHERE clause in UPDATE. An UPDATE without WHERE modifies every row in the table. Most teams run SELECT ... WHERE <condition> to preview the affected rows before executing the UPDATE.

DELETE

-- Delete specific rows
DELETE FROM employees WHERE department = 'General';

-- Delete with a RETURNING clause (get the deleted rows back)
DELETE FROM employees
WHERE hire_date < CURRENT_DATE - INTERVAL '10 years'
RETURNING employee_id, email;

RETURNING is a PostgreSQL extension that makes a DML statement return the affected rows, saving a separate SELECT query. It works with INSERT, UPDATE, and DELETE.

Transactions

Wrap multi-statement operations in an explicit transaction to guarantee atomicity:

BEGIN;

UPDATE employees SET salary_usd = salary_usd * 1.05
WHERE department = 'Research';

INSERT INTO salary_audit (employee_id, changed_at, delta_pct)
SELECT employee_id, NOW(), 5.0
FROM employees WHERE department = 'Research';

COMMIT;  -- or ROLLBACK to undo both statements

PostgreSQL wraps every single statement in an implicit transaction, so a failed statement does not partially commit. Explicit BEGIN ... COMMIT blocks are necessary when you need multiple statements to succeed or fail together.