The SQL Book/Chapter 1/1.1

1.1 The Relational Model & SQL Architecture

How relational databases structure information into mathematical relations (tables), tuples (rows), and attributes (columns).

Conceptual Deep Dive

In 1970, Edgar F. Codd revolutionized data processing by introducing the Relational Model. Unlike hierarchical or document storage where data is nested in unpredictable trees, relational systems organize information into strict mathematical relations (known in SQL as tables).

SQL (Structured Query Language) is a declarative language. In procedural languages like Rust, Python, or C++, you specify the exact control flow: 'initialize an array, loop through indices, filter matches, and append to a list.' In SQL, you describe the desired state of the output, and the database query planner (Cost-Based Optimizer) calculates the most efficient way to fetch, join, and filter the data using B-Trees, hash joins, or table scans.

Every relation satisfies three core invariants:

First Normal Form (1NF): Each cell contains atomic, scalar values (no nested lists or unstructured blobs without strict typing).
Primary Key Identity: Every tuple possesses an immutable unique identifier that distinguishes it from every other entity.
Referential Integrity: Foreign keys establish verifiable mathematical mappings between distinct relations.

Syntax Specification

SQL Syntax:DDLDMLClausesFunctionsTypesStrings

-- Basic anatomy of a relational query
SELECT column1, column2, expression AS alias
FROM primary_table
JOIN related_table ON primary_table.id = related_table.foreign_id
WHERE filter_condition
ORDER BY sort_column ASC;

Key Takeaways

Declarative paradigm: You describe what data you need, not how the CPU iterates over disk pages.
Mathematical relations: Tables are unordered sets of tuples where columns represent typed attributes.
The query planner optimizes index traversal and execution plans under the hood.

Common Gotchas & Pitfalls

Tables are sets, not lists: Rows have no inherent order unless an explicit ORDER BY clause is provided.
Assuming sequential evaluation: SQL clauses are not executed in the order they are written (e.g. FROM and WHERE happen before SELECT).

⚙️ Under the Hood: SQLite Query Planner Internals

The SQLite query parser creates an Abstract Syntax Tree (AST), passes it to the code generator, and generates a bytecode VDBE (Virtual Database Engine) instruction sequence that runs against B-Tree disk pages.

Multiple Interactive Examples (3 Scenarios)

Each example includes input tables, formatted SQL code, execution breakdown, output tables, and live SQLite WASM execution.

Example 1 of 3Basic

Basic Table Inspection & Attribute Selection

Problem to Solve:

Retrieve the student identifier, full name, and grade level from the student body registry.

📋 Input Data (Source Table Before Query)

Table: studentsCore student registry with academic standing

id	name	age	grade	favorite_subject	gpa
1	Emma Watson	16	11th	Computer Science	3.9
2	Lucas Silva	15	10th	Math	3.7
3	Sophia Chen	17	12th	Physics	4
4	Liam Miller	16	11th	Art	3.4
5	Aria Patel	15	10th	Biology	3.8

💻 SQL Query

SQLite WASM

SELECT id, name, grade
FROM students;

⚙️ What Is Getting Done Under The Hood:

Accesses the 'students' relation from the SQLite master catalog.
Extracts only the attributes 'id', 'name', and 'grade', discarding 'age', 'favorite_subject', and 'gpa'.
Streams the tuples to the output cursor without sorting overhead.

📊 Output Result Table

5 row(s) returned

3 columns projected across all 5 registered students:

id	name	grade
1	Emma Watson	11th
2	Lucas Silva	10th
3	Sophia Chen	12th
4	Liam Miller	11th
5	Aria Patel	10th

Pro Tip: Using explicit column names instead of SELECT * saves bandwidth and prevents schema breaking changes in client code.

Example 2 of 3Analytical

Inspecting Database System Catalog & Master Table

Problem to Solve:

Discover all active relational tables in the database along with their underlying schema DDL definitions.

💻 SQL Query

SQLite WASM

SELECT type, name, tbl_name
FROM sqlite_master
WHERE type = 'table' AND name NOT LIKE 'sqlite_%'
ORDER BY name ASC;

⚙️ What Is Getting Done Under The Hood:

Queries the root schema metadata table 'sqlite_master'.
Filters for relational tables while excluding internal engine storage tables (prefixed with sqlite_).
Orders the table names alphabetically.

📊 Output Result Table

4 row(s) returned

Catalog listing of all user tables in the active SQLite database:

type	name	tbl_name
table	courses	courses
table	enrollments	enrollments
table	products	products
table	students	students

Pro Tip: In PostgreSQL this corresponds to pg_catalog.pg_tables or INFORMATION_SCHEMA.TABLES.

Example 3 of 3Edge Case

Schema Column Introspection via PRAGMA

Problem to Solve:

Inspect the column types, default values, and primary key constraints of the courses table.

📋 Input Data (Source Table Before Query)

Table: coursesAcademic course catalog

course_id	course_name	teacher	credits	department
101	Intro to Python	Dr. Turing	4	Computer Science
102	Calculus I	Prof. Newton	4	Mathematics
103	World History	Ms. Earhart	3	Humanities
104	Creative Writing	Mr. Poe	2	Arts

💻 SQL Query

SQLite WASM

PRAGMA table_info(courses);

⚙️ What Is Getting Done Under The Hood:

Invokes the SQLite PRAGMA introspection command on the 'courses' table.
Returns column index (cid), column name, affinity type, notnull flag, default value, and primary key index (pk).
Confirms that course_id is designated as primary key (pk = 1).

📊 Output Result Table

5 row(s) returned

Metadata description of each column in the courses schema:

cid	name	type	notnull	dflt_value	pk
0	course_id	INTEGER	0	NULL	1
1	course_name	TEXT	1	NULL	0
2	teacher	TEXT	0	NULL	0
3	credits	INTEGER	0	NULL	0
4	department	TEXT	0	NULL	0

Pro Tip: Explaino-Structura and the Supabase Studio panel use this exact PRAGMA query to render the live UI schema editor.

Press ← or → arrow keys to turn pages