{ "cells": [ { "cell_type": "raw", "metadata": {}, "source": [ "---\n", "title: \"Notebook 9: Databases — Where Data Lives When the Program Stops\"\n", "subtitle: \"COMP 1150 — Computer Science Concepts\"\n", "author: \"Brendan Shea, PhD\"\n", "date: last-modified\n", "---" ] }, { "cell_type": "markdown", "metadata": { "colab_header": true }, "source": [ "\n", "[![Open In Colab](https://colab.research.google.com/assets/colab-badge.svg)](https://colab.research.google.com/github/brendanpshea/computing_concepts_python/blob/main/v2/notebooks/COMP1150_NB09_Databases.ipynb) \n", "[Download .ipynb](https://raw.githubusercontent.com/brendanpshea/computing_concepts_python/main/v2/notebooks/COMP1150_NB09_Databases.ipynb) · [View on GitHub](https://github.com/brendanpshea/computing_concepts_python/blob/main/v2/notebooks/COMP1150_NB09_Databases.ipynb)\n" ] }, { "cell_type": "markdown", "id": "af3bd28f", "metadata": {}, "source": [ "## Learning Outcomes\n", "\n", "By the end of this notebook, you will be able to:\n", "\n", "- Explain what a database is and why programs use one instead of variables, spreadsheets, or plain files\n", "- Describe the relational model: tables, rows, columns, primary keys, and foreign keys\n", "- Write basic SQL: `CREATE TABLE`, `INSERT`, `SELECT` with `WHERE` and `ORDER BY`, `JOIN`, and `GROUP BY`\n", "- Explain what an integrity constraint protects against, and why a database that says \"no\" is a feature\n", "- Distinguish relational from document (non-relational) data models, and argue which fits a given problem\n", "\n", "*Maps to course LOs: 9*" ] }, { "cell_type": "markdown", "id": "26d6c7b1", "metadata": {}, "source": [ "## The Problem: Batch 88 Goes Missing\n", "\n", "The **Marchpane Confectionery Works** is famous for candies that shouldn't be possible: Gloomberry Fizzers, Thundermint Bars, Whispering Toffee that hums when you unwrap it. Its founder, **Cordelia Marchpane**, is a brilliant inventor — and, until this week, a terrible record-keeper.\n", "\n", "Every fact about the factory lives in one enormous spreadsheet started by Cordelia's grandmother. Candies, recipes, batches, shop orders — all of it, in one grid, edited by whoever grabs the laptop first.\n", "\n", "This week, three things went wrong at once:\n", "\n", "1. A clerk renamed \"Gloomberry Fizzer\" to \"Gloomberry Fizzers\" in row 40 — but not in rows 87, 210, or 384. Now searches for the candy find *some* of its records.\n", "2. The Puddleton Sweet Shoppe received 400 boxes of Thundermint Bars. They ordered Whispering Toffee.\n", "3. Batch 88 failed a quality check, and **nobody can say which recipe version it used** — the batch row just says \"the usual one.\"" ] }, { "cell_type": "markdown", "id": "c7956585", "metadata": {}, "source": [ "Cordelia's floor manager, **Hazel Nougat**, delivers the verdict: *\"The problem isn't that we made mistakes. Everyone makes mistakes. The problem is that nothing stopped us.\"*\n", "\n", "The spreadsheet will accept anything: a duplicated candy, a misspelled name, an order pointing at a product that doesn't exist. It has no rules, no memory of who changed what, and no way for two people to work safely at once.\n", "\n", "There's one more problem, and it's the deepest one. Every program you've written in this course so far had the same quiet flaw: **when the program stopped, its data vanished.** Every list, every dictionary, every object — gone the moment the cell finished. A factory can't work that way. Data has to *outlive* the program that created it.\n", "\n", "The tool that fixes all of this — the rules, the sharing, and the surviving — is a **database**. That's what this notebook is about." ] }, { "cell_type": "markdown", "id": "760a95d7", "metadata": {}, "source": [ "### The Roadmap\n", "\n", "Here's where we're going:\n", "\n", "1. **Why a database?** What the spreadsheet can't do, and the idea of the *relational model*.\n", "2. **Talking to a database** — a language called SQL, and how to create and fill tables.\n", "3. **Asking questions** — `SELECT`, the query that does most of the world's data work.\n", "4. **Two tables are better than one** — `JOIN`, `GROUP BY`, and the safety rules that would have saved Batch 88.\n", "5. **When rows don't fit** — document databases, for data that refuses to sit in neat columns.\n", "\n", "At the end, you'll design and build a small database of your own, with an AI assistant doing the typing and you doing the thinking." ] }, { "cell_type": "markdown", "id": "b9309182", "metadata": {}, "source": [ "## 1. Why a Database? From One Big Grid to Tables\n", "\n", "A **database** is an organized collection of data, managed by a program whose whole job is to keep that data correct, shared, and permanent. That program is called a **database management system** (DBMS). The one we'll use is **SQLite** — small, free, and built into Python, but a genuine database used in phones, browsers, and airplanes.\n", "\n", "The most successful way to organize a database — dominant for fifty years — is the **relational model**. Its core move sounds almost too simple:\n", "\n", "**Give each *kind* of thing its own table.**\n", "\n", "Candies are one kind of thing, so they get a `candies` table. Batches are a different kind of thing: `batches` table. Shop orders: `shop_orders` table. Cordelia's grandmother crammed all three into one grid, and that's precisely why every disaster happened." ] }, { "cell_type": "markdown", "id": "d84d41b7", "metadata": {}, "source": [ "### The Vocabulary of Tables\n", "\n", "Four words carry the whole relational model:\n", "\n", "- A **table** holds every record of one kind of thing (all the candies, and *only* candies).\n", "- A **row** is one of those things (one candy: Thundermint Bar).\n", "- A **column** is one fact recorded about every row (every candy has a `price_pence`).\n", "- The **primary key** is a column holding a unique, unchanging ID for each row — a name tag that never lies.\n", "\n", "That last one is the fix for the Gloomberry disaster. Names get misspelled, renamed, and duplicated. So a table never uses a name to identify anything. Candy #3 is candy #3 forever, even if its name changes in exactly one place: its own row." ] }, { "cell_type": "markdown", "id": "45bfb94e", "metadata": {}, "source": [ "### Picture It: The Factory's Tables\n", "\n", "The diagram below shows the three tables we'll build for the Marchpane Works, and how they connect. Each box is a table; each line inside it is a column. The arrows show rows in one table pointing at rows in another — a batch *belongs to* a candy, and so does an order." ] }, { "cell_type": "code", "execution_count": 1, "id": "a0f149a8", "metadata": { "cellView": "form", "jupyter": { "source_hidden": true } }, "outputs": [ { "data": { "image/svg+xml": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "candies\n", "\n", "candies\n", "candy_id 🔑\n", "name\n", "department\n", "price_pence\n", "sugar_grams\n", "\n", "\n", "\n", "\n", "batches\n", "\n", "batches\n", "batch_id 🔑\n", "candy_id ↗\n", "batch_size\n", "quality_score\n", "made_on\n", "\n", "\n", "\n", "\n", "batches:candy_id->candies:candy_id\n", "\n", "\n", "belongs to\n", "\n", "\n", "\n", "orders\n", "\n", "shop_orders\n", "order_id 🔑\n", "candy_id ↗\n", "shop_name\n", "boxes\n", "\n", "\n", "\n", "\n", "orders:candy_id->candies:candy_id\n", "\n", "\n", "belongs to\n", "\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#| echo: false\n", "#| fig-alt: \"Entity-relationship diagram: the batches table and the orders table each reference candy_id in the candies table as a foreign key.\"\n", "#@title 📊 Diagram: the factory's tables (click to show code)\n", "import graphviz\n", "\n", "er = graphviz.Digraph()\n", "er.attr(rankdir=\"LR\", bgcolor=\"transparent\")\n", "er.attr(\"node\", shape=\"none\", fontname=\"Helvetica\")\n", "\n", "def table_node(name, title, rows):\n", " body = \"\".join(\n", " f'{r}' for r in rows\n", " )\n", " label = ('<'\n", " + f''\n", " + body + \"
{title}
>\")\n", " er.node(name, label)\n", "\n", "table_node(\"candies\", \"candies\",\n", " [\"candy_id 🔑\", \"name\", \"department\", \"price_pence\", \"sugar_grams\"])\n", "table_node(\"batches\", \"batches\",\n", " [\"batch_id 🔑\", \"candy_id ↗\", \"batch_size\", \"quality_score\", \"made_on\"])\n", "table_node(\"orders\", \"shop_orders\",\n", " [\"order_id 🔑\", \"candy_id ↗\", \"shop_name\", \"boxes\"])\n", "\n", "er.edge(\"batches:candy_id\", \"candies:candy_id\", label=\"belongs to\")\n", "er.edge(\"orders:candy_id\", \"candies:candy_id\", label=\"belongs to\")\n", "er" ] }, { "cell_type": "markdown", "id": "60847cfe", "metadata": {}, "source": [ "**Reading it:** each box is a table and each line is a column. The 🔑 marks the primary key — the row's permanent ID. The arrows (↗) show that `batches` and `shop_orders` don't store a candy's *name*; they store its `candy_id`, pointing back at exactly one row in `candies`. Rename the candy there, and every batch and order follows automatically." ] }, { "cell_type": "markdown", "id": "d64c2b0b", "metadata": {}, "source": [ "### 💭 Think About It — You Used Five Databases Before Breakfast\n", "\n", "Your phone's contacts, your text messages, your streaming history, your school's registration system, the till at the coffee shop — every one is a database.\n", "\n", "Pick one system you used today. What do you think its tables are? What would go wrong if it were one big spreadsheet that anyone could edit?" ] }, { "cell_type": "markdown", "id": "6435be4d", "metadata": {}, "source": [ "## 2. Talking to a Database: SQL\n", "\n", "Databases speak their own language: **SQL** (\"ess-cue-ell,\" or \"sequel\"), short for *Structured Query Language*. It's been the standard since the 1970s, and it's wonderfully unlike Python: instead of telling the computer *how* to do something step by step, you describe *what* you want, and the database figures out how.\n", "\n", "In this notebook we'll write SQL directly in code cells, using a small tool called **jupysql**. Any cell that starts with the line `%%sql` speaks SQL instead of Python. That first line is called a *cell magic* — think of it as a sign on the door saying which language is spoken inside.\n", "\n", "The next cell installs the tool. It takes a few seconds and only needs to run once per session." ] }, { "cell_type": "code", "execution_count": 2, "id": "c0b52db8", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Note: you may need to restart the kernel to use updated packages.\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\n", "[notice] A new release of pip is available: 25.2 -> 26.1.2\n", "[notice] To update, run: pythonw.exe -m pip install --upgrade pip\n" ] } ], "source": [ "%pip install jupysql --quiet" ] }, { "cell_type": "markdown", "id": "17ed05f3", "metadata": {}, "source": [ "### Connecting to a Database\n", "\n", "With the tool installed, two lines get us ready. The general pattern is:\n", "\n", "```\n", "%load_ext sql\n", "%sql sqlite:///file_name.db\n", "```\n", "\n", "The first line switches on SQL support. The second connects to a database *file* — and here's a quiet superpower: if the file doesn't exist, SQLite simply creates it. The next cell connects us to `candy_factory.db`, the file where everything we build will live — and where it will **still be** after the program stops." ] }, { "cell_type": "code", "execution_count": 3, "id": "eea7070a", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Connecting to 'sqlite:///candy_factory.db'" ], "text/plain": [ "Connecting to 'sqlite:///candy_factory.db'" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%load_ext sql\n", "%config SqlMagic.displaylimit = 20\n", "%sql sqlite:///candy_factory.db" ] }, { "cell_type": "markdown", "id": "83be44b4", "metadata": {}, "source": [ "### Making a Table: CREATE TABLE\n", "\n", "The first SQL statement to learn creates a table. The general shape is:\n", "\n", "```\n", "CREATE TABLE table_name (\n", " column_name TYPE,\n", " column_name TYPE,\n", " ...\n", ");\n", "```\n", "\n", "You name the table, then list each column with its **data type**. SQLite's everyday types are `INTEGER` (whole numbers), `REAL` (decimals), and `TEXT` (strings). Adding `PRIMARY KEY` after a column marks it as the table's permanent ID.\n", "\n", "The next cell builds Cordelia's `candies` table. (The `DROP TABLE IF EXISTS` line first deletes any old copy, so you can safely re-run the cell.)" ] }, { "cell_type": "code", "execution_count": 4, "id": "d8438d8e", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'sqlite:///candy_factory.db'" ], "text/plain": [ "Running query in 'sqlite:///candy_factory.db'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
" ], "text/plain": [ "++\n", "||\n", "++\n", "++" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "DROP TABLE IF EXISTS candies;\n", "\n", "CREATE TABLE candies (\n", " candy_id INTEGER PRIMARY KEY,\n", " name TEXT,\n", " department TEXT,\n", " price_pence INTEGER,\n", " sugar_grams REAL\n", ");" ] }, { "cell_type": "markdown", "id": "568cdf57", "metadata": {}, "source": [ "### Understanding the Code\n", "\n", "- `%%sql` on the first line means: this whole cell is SQL, not Python.\n", "- `CREATE TABLE candies ( ... )` declares the table and its five columns.\n", "- `candy_id INTEGER PRIMARY KEY` — every candy gets a unique whole-number ID. This is the name tag that never lies.\n", "- Statements end with a semicolon `;` — SQL's version of a full stop.\n", "\n", "The table exists, but it's empty — a labeled filing cabinet with no files. Let's fix that." ] }, { "cell_type": "markdown", "id": "b3522d34", "metadata": {}, "source": [ "### Filling It: INSERT\n", "\n", "Adding a row uses `INSERT`. The general shape:\n", "\n", "```\n", "INSERT INTO table_name (column_1, column_2, ...)\n", "VALUES (value_1, value_2, ...);\n", "```\n", "\n", "You name the columns, then supply one value for each, in the same order. Text values go in single quotes: `'Thundermint Bar'`. The next cell stocks the factory's catalog." ] }, { "cell_type": "code", "execution_count": 5, "id": "b5bfdc87", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'sqlite:///candy_factory.db'" ], "text/plain": [ "Running query in 'sqlite:///candy_factory.db'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "8 rows affected." ], "text/plain": [ "8 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
" ], "text/plain": [ "++\n", "||\n", "++\n", "++" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "INSERT INTO candies (candy_id, name, department, price_pence, sugar_grams) VALUES\n", " (1, 'Gloomberry Fizzer', 'Fizzworks', 120, 22.0),\n", " (2, 'Thundermint Bar', 'Chocolate', 250, 31.5),\n", " (3, 'Whispering Toffee', 'Toffee Hall', 180, 27.0),\n", " (4, 'Marchpane Original', 'Marzipanery', 300, 18.5),\n", " (5, 'Puddle Drops', 'Fizzworks', 90, 12.0),\n", " (6, 'Candied Thistle', 'Experimental', 210, 8.0),\n", " (7, 'Hummingbird Nougat', 'Toffee Hall', 260, 24.5),\n", " (8, 'Midnight Sherbet', 'Fizzworks', 140, 19.0);" ] }, { "cell_type": "markdown", "id": "4d1eb736", "metadata": {}, "source": [ "Eight candies, one `INSERT`. (Listing several rows after one `VALUES`, separated by commas, is a handy shortcut.)\n", "\n", "But did it work? Time to meet the most important word in SQL. The next cell asks the database to show us everything in the table." ] }, { "cell_type": "code", "execution_count": 6, "id": "6455ee59", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'sqlite:///candy_factory.db'" ], "text/plain": [ "Running query in 'sqlite:///candy_factory.db'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
candy_idnamedepartmentprice_pencesugar_grams
1Gloomberry FizzerFizzworks12022.0
2Thundermint BarChocolate25031.5
3Whispering ToffeeToffee Hall18027.0
4Marchpane OriginalMarzipanery30018.5
5Puddle DropsFizzworks9012.0
6Candied ThistleExperimental2108.0
7Hummingbird NougatToffee Hall26024.5
8Midnight SherbetFizzworks14019.0
" ], "text/plain": [ "+----------+--------------------+--------------+-------------+-------------+\n", "| candy_id | name | department | price_pence | sugar_grams |\n", "+----------+--------------------+--------------+-------------+-------------+\n", "| 1 | Gloomberry Fizzer | Fizzworks | 120 | 22.0 |\n", "| 2 | Thundermint Bar | Chocolate | 250 | 31.5 |\n", "| 3 | Whispering Toffee | Toffee Hall | 180 | 27.0 |\n", "| 4 | Marchpane Original | Marzipanery | 300 | 18.5 |\n", "| 5 | Puddle Drops | Fizzworks | 90 | 12.0 |\n", "| 6 | Candied Thistle | Experimental | 210 | 8.0 |\n", "| 7 | Hummingbird Nougat | Toffee Hall | 260 | 24.5 |\n", "| 8 | Midnight Sherbet | Fizzworks | 140 | 19.0 |\n", "+----------+--------------------+--------------+-------------+-------------+" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT * FROM candies;" ] }, { "cell_type": "markdown", "id": "5038474e", "metadata": {}, "source": [ "There's the whole catalog, back out of the database as a clean table. `SELECT * FROM candies` means \"give me every column (`*`) of every row in `candies`.\"\n", "\n", "Notice what you did *not* have to write: no loop, no print formatting, no file handling. You described what you wanted; the database did the rest. The whole next section is about asking sharper questions than \"everything, please.\"" ] }, { "cell_type": "markdown", "id": "80cdefd0", "metadata": {}, "source": [ "### ✏️ Your Turn — Cordelia's Ingredient Stores\n", "\n", "Cordelia wants a table for raw ingredients. It needs: an `ingredient_id` (whole number, primary key), a `name` (text), `stock_kg` (a decimal — how much is in the storeroom), and `cost_pence_per_kg` (a whole number).\n", "\n", "Write the `CREATE TABLE` statement, then `INSERT` at least three ingredients. Invent something suitably strange — lifting syrup, powdered thunder, gloomberries." ] }, { "cell_type": "code", "execution_count": 7, "id": "e54c562b", "metadata": {}, "outputs": [], "source": [ "#| eval: false\n", "%%sql\n", "-- TODO: create the ingredients table (4 columns, described above)\n", "\n", "\n", "-- TODO: insert at least three ingredients" ] }, { "cell_type": "markdown", "id": "2e390b51", "metadata": {}, "source": [ "## 3. Asking Questions: Barnaby and the Tasting Bureau\n", "\n", "**Barnaby Brittle** runs the factory's Tasting Bureau, and he lives inside one SQL word: `SELECT`. Creating tables happens once; *querying* them happens thousands of times a day, everywhere. A request for data is called a **query**.\n", "\n", "The full shape of a basic query:\n", "\n", "```\n", "SELECT column_1, column_2\n", "FROM table_name\n", "WHERE condition;\n", "```\n", "\n", "- `SELECT` — *which columns* you want (or `*` for all of them).\n", "- `FROM` — *which table* to look in.\n", "- `WHERE` — *which rows* qualify. Leave it off, and you get every row.\n", "\n", "Barnaby rarely needs every column. The next cell pulls just names and prices." ] }, { "cell_type": "code", "execution_count": 8, "id": "35103d36", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'sqlite:///candy_factory.db'" ], "text/plain": [ "Running query in 'sqlite:///candy_factory.db'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
nameprice_pence
Gloomberry Fizzer120
Thundermint Bar250
Whispering Toffee180
Marchpane Original300
Puddle Drops90
Candied Thistle210
Hummingbird Nougat260
Midnight Sherbet140
" ], "text/plain": [ "+--------------------+-------------+\n", "| name | price_pence |\n", "+--------------------+-------------+\n", "| Gloomberry Fizzer | 120 |\n", "| Thundermint Bar | 250 |\n", "| Whispering Toffee | 180 |\n", "| Marchpane Original | 300 |\n", "| Puddle Drops | 90 |\n", "| Candied Thistle | 210 |\n", "| Hummingbird Nougat | 260 |\n", "| Midnight Sherbet | 140 |\n", "+--------------------+-------------+" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT name, price_pence\n", "FROM candies;" ] }, { "cell_type": "markdown", "id": "ad0c89bb", "metadata": {}, "source": [ "Two columns instead of five — `SELECT` lets you take only what you need.\n", "\n", "Filtering *rows* is where queries get powerful. A `WHERE` condition works like the boolean tests you've written in Python: `=`, `<`, `>`, combined with `AND` and `OR`. (One trap for Python speakers: SQL tests equality with a single `=`, not `==`.)\n", "\n", "Barnaby has been asked for the *lighter* end of the catalog. The next cell finds every candy under 20 grams of sugar." ] }, { "cell_type": "code", "execution_count": 9, "id": "29a6edf7", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'sqlite:///candy_factory.db'" ], "text/plain": [ "Running query in 'sqlite:///candy_factory.db'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namesugar_grams
Marchpane Original18.5
Puddle Drops12.0
Candied Thistle8.0
Midnight Sherbet19.0
" ], "text/plain": [ "+--------------------+-------------+\n", "| name | sugar_grams |\n", "+--------------------+-------------+\n", "| Marchpane Original | 18.5 |\n", "| Puddle Drops | 12.0 |\n", "| Candied Thistle | 8.0 |\n", "| Midnight Sherbet | 19.0 |\n", "+--------------------+-------------+" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT name, sugar_grams\n", "FROM candies\n", "WHERE sugar_grams < 20;" ] }, { "cell_type": "markdown", "id": "86097008", "metadata": {}, "source": [ "Four rows survive the filter. The database checked the condition against every candy and kept only the ones where it was true — exactly like an `if` inside a loop, except you never wrote the loop.\n", "\n", "Conditions combine just like in Python. `WHERE department = 'Fizzworks' AND price_pence < 100` finds cheap fizzy candies. `OR` and `NOT` work too." ] }, { "cell_type": "markdown", "id": "e10a9796", "metadata": {}, "source": [ "### 🔮 Predict Before You Run\n", "\n", "The next cell asks for candies where `department = 'Fizzworks' AND sugar_grams < 20`.\n", "\n", "Look back at the full catalog printout above. **Before you run the cell**, write down which candies you expect — and how many rows that is." ] }, { "cell_type": "code", "execution_count": 10, "id": "4e16feca", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'sqlite:///candy_factory.db'" ], "text/plain": [ "Running query in 'sqlite:///candy_factory.db'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namedepartmentsugar_grams
Puddle DropsFizzworks12.0
Midnight SherbetFizzworks19.0
" ], "text/plain": [ "+------------------+------------+-------------+\n", "| name | department | sugar_grams |\n", "+------------------+------------+-------------+\n", "| Puddle Drops | Fizzworks | 12.0 |\n", "| Midnight Sherbet | Fizzworks | 19.0 |\n", "+------------------+------------+-------------+" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT name, department, sugar_grams\n", "FROM candies\n", "WHERE department = 'Fizzworks' AND sugar_grams < 20;" ] }, { "cell_type": "markdown", "id": "bdbaffd6", "metadata": {}, "source": [ "Did you predict both? Gloomberry Fizzer is in Fizzworks but has 22 grams of sugar — the `AND` requires *both* conditions, so it's out.\n", "\n", "One more tool and Barnaby's kit is complete: **sorting**. Add these to the end of a query:\n", "\n", "```\n", "ORDER BY column_name DESC\n", "LIMIT n;\n", "```\n", "\n", "`ORDER BY` sorts the results by a column (`DESC` for descending, highest first; leave it off for ascending). `LIMIT` keeps only the first *n* rows. Together they answer \"top three\" questions — like the next cell: the factory's three most expensive candies." ] }, { "cell_type": "code", "execution_count": 11, "id": "68c50c51", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'sqlite:///candy_factory.db'" ], "text/plain": [ "Running query in 'sqlite:///candy_factory.db'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
nameprice_pence
Marchpane Original300
Hummingbird Nougat260
Thundermint Bar250
" ], "text/plain": [ "+--------------------+-------------+\n", "| name | price_pence |\n", "+--------------------+-------------+\n", "| Marchpane Original | 300 |\n", "| Hummingbird Nougat | 260 |\n", "| Thundermint Bar | 250 |\n", "+--------------------+-------------+" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT name, price_pence\n", "FROM candies\n", "ORDER BY price_pence DESC\n", "LIMIT 3;" ] }, { "cell_type": "markdown", "id": "5f8eda70", "metadata": {}, "source": [ "Sorted, trimmed, done. Notice the pattern of everything in this section: you *describe the result* — these columns, rows matching this, in this order, only this many — and the database works out the steps. That's SQL's whole personality." ] }, { "cell_type": "markdown", "id": "20e88832", "metadata": {}, "source": [ "### ✏️ Your Turn — Barnaby's Shortlist\n", "\n", "The Tasting Bureau needs a shortlist for a \"light and affordable\" gift box: every candy **under 25 grams of sugar**, showing `name`, `price_pence`, and `sugar_grams`, **sorted from cheapest to most expensive**.\n", "\n", "One query does it. (Remember: ascending is the default sort.)" ] }, { "cell_type": "code", "execution_count": 12, "id": "a6ca3854", "metadata": {}, "outputs": [], "source": [ "#| eval: false\n", "%%sql\n", "-- TODO: Barnaby's shortlist — under 25g sugar, cheapest first\n", "SELECT" ] }, { "cell_type": "markdown", "id": "1ea4b7fd", "metadata": {}, "source": [ "## 4. Two Tables Are Better Than One\n", "\n", "Back to the week's disasters. The renamed Gloomberry Fizzer broke the spreadsheet because the candy's *name* was copied into hundreds of rows. The relational fix: other tables store the candy's **primary key**, never its name.\n", "\n", "When a column in one table holds the primary key of another table's row, it's called a **foreign key**. A batch doesn't say \"the usual one\" — it says `candy_id = 3`, which points at exactly one row in `candies`, forever.\n", "\n", "The next cell builds the factory's two remaining tables. Watch the `FOREIGN KEY` lines — and the `PRAGMA` at the top, which switches on SQLite's enforcement of them." ] }, { "cell_type": "code", "execution_count": 13, "id": "a1166a6d", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'sqlite:///candy_factory.db'" ], "text/plain": [ "Running query in 'sqlite:///candy_factory.db'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
" ], "text/plain": [ "++\n", "||\n", "++\n", "++" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "PRAGMA foreign_keys = ON;\n", "\n", "DROP TABLE IF EXISTS batches;\n", "CREATE TABLE batches (\n", " batch_id INTEGER PRIMARY KEY,\n", " candy_id INTEGER,\n", " batch_size INTEGER,\n", " quality_score REAL,\n", " made_on TEXT,\n", " FOREIGN KEY (candy_id) REFERENCES candies (candy_id)\n", ");\n", "\n", "DROP TABLE IF EXISTS shop_orders;\n", "CREATE TABLE shop_orders (\n", " order_id INTEGER PRIMARY KEY,\n", " candy_id INTEGER,\n", " shop_name TEXT,\n", " boxes INTEGER,\n", " FOREIGN KEY (candy_id) REFERENCES candies (candy_id)\n", ");" ] }, { "cell_type": "markdown", "id": "b44b4f18", "metadata": {}, "source": [ "### Understanding the Code\n", "\n", "- Each table gets its own primary key (`batch_id`, `order_id`).\n", "- `candy_id INTEGER` — the foreign key column. It holds *numbers*, not names.\n", "- `FOREIGN KEY (candy_id) REFERENCES candies (candy_id)` — the rule: any value in this column **must** exist as a primary key in `candies`.\n", "- `PRAGMA foreign_keys = ON` — SQLite politely ignores that rule unless you switch enforcement on. We want it on. We'll see why shortly.\n", "\n", "Now some data: recent batches off the line, and the week's shop orders." ] }, { "cell_type": "code", "execution_count": 14, "id": "70bdbffc", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'sqlite:///candy_factory.db'" ], "text/plain": [ "Running query in 'sqlite:///candy_factory.db'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "5 rows affected." ], "text/plain": [ "5 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "6 rows affected." ], "text/plain": [ "6 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
" ], "text/plain": [ "++\n", "||\n", "++\n", "++" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "INSERT INTO batches (batch_id, candy_id, batch_size, quality_score, made_on) VALUES\n", " (86, 2, 500, 9.1, '2026-06-29'),\n", " (87, 5, 800, 8.4, '2026-06-30'),\n", " (88, 1, 650, 5.2, '2026-07-01'),\n", " (89, 3, 400, 9.6, '2026-07-01'),\n", " (90, 1, 700, 8.9, '2026-07-02');\n", "\n", "INSERT INTO shop_orders (order_id, candy_id, shop_name, boxes) VALUES\n", " (501, 3, 'Puddleton Sweet Shoppe', 400),\n", " (502, 2, 'Puddleton Sweet Shoppe', 120),\n", " (503, 1, 'The Sugared Quill', 250),\n", " (504, 4, 'The Sugared Quill', 60),\n", " (505, 2, 'Grimble & Daughters', 300),\n", " (506, 5, 'Grimble & Daughters', 450);" ] }, { "cell_type": "markdown", "id": "33d831a4", "metadata": {}, "source": [ "### Putting Them Back Together: JOIN\n", "\n", "Look at `batches`: it's all ID numbers. Efficient — but Hazel can't hand Cordelia a report that says \"candy 1 had a bad day.\" We need each batch *with its candy's name*. Recombining tables is what `JOIN` does:\n", "\n", "```\n", "SELECT columns\n", "FROM table_a\n", "JOIN table_b ON table_a.key_column = table_b.key_column;\n", "```\n", "\n", "The `ON` clause says how rows match up: a batch and a candy belong together when their `candy_id` values are equal. Because both tables have a column called `candy_id`, we write the table name in front — `batches.candy_id` — to say which one we mean.\n", "\n", "The next cell produces Hazel's readable batch report." ] }, { "cell_type": "code", "execution_count": 15, "id": "efd1e364", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'sqlite:///candy_factory.db'" ], "text/plain": [ "Running query in 'sqlite:///candy_factory.db'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
batch_idnamequality_score
88Gloomberry Fizzer5.2
87Puddle Drops8.4
90Gloomberry Fizzer8.9
86Thundermint Bar9.1
89Whispering Toffee9.6
" ], "text/plain": [ "+----------+-------------------+---------------+\n", "| batch_id | name | quality_score |\n", "+----------+-------------------+---------------+\n", "| 88 | Gloomberry Fizzer | 5.2 |\n", "| 87 | Puddle Drops | 8.4 |\n", "| 90 | Gloomberry Fizzer | 8.9 |\n", "| 86 | Thundermint Bar | 9.1 |\n", "| 89 | Whispering Toffee | 9.6 |\n", "+----------+-------------------+---------------+" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT batches.batch_id, candies.name, batches.quality_score\n", "FROM batches\n", "JOIN candies ON batches.candy_id = candies.candy_id\n", "ORDER BY batches.quality_score;" ] }, { "cell_type": "markdown", "id": "83527e1d", "metadata": {}, "source": [ "Every batch, with its candy's *name* — even though `batches` never stores a name. The database matched each batch's `candy_id` to the right row of `candies` and stitched the columns together on the fly.\n", "\n", "And there's the week's mystery solved in one line of output: **Batch 88, Gloomberry Fizzer, quality 5.2.** The ID pointed at exactly one candy. No \"the usual one\" possible.\n", "\n", "This is the deal the relational model offers: store every fact **once**, in the table where it belongs — then use `JOIN` to recombine facts whenever a question needs them." ] }, { "cell_type": "markdown", "id": "10027284", "metadata": {}, "source": [ "### Ledger's Question: GROUP BY\n", "\n", "**Ledger Pettigrew** in accounts doesn't want rows; he wants *totals*. Which candy earned the most this week? That takes two new tools:\n", "\n", "```\n", "SELECT column, SUM(expression)\n", "FROM ...\n", "GROUP BY column;\n", "```\n", "\n", "`GROUP BY` gathers rows into buckets — one bucket per candy. An **aggregate function** then boils each bucket down to one number: `SUM()` adds, `COUNT()` counts, `AVG()` averages.\n", "\n", "The next cell joins orders to candies, groups by candy, and totals up the money." ] }, { "cell_type": "code", "execution_count": 16, "id": "37474ca3", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'sqlite:///candy_factory.db'" ], "text/plain": [ "Running query in 'sqlite:///candy_factory.db'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
nametotal_boxesrevenue_pence
Thundermint Bar420105000
Whispering Toffee40072000
Puddle Drops45040500
Gloomberry Fizzer25030000
Marchpane Original6018000
" ], "text/plain": [ "+--------------------+-------------+---------------+\n", "| name | total_boxes | revenue_pence |\n", "+--------------------+-------------+---------------+\n", "| Thundermint Bar | 420 | 105000 |\n", "| Whispering Toffee | 400 | 72000 |\n", "| Puddle Drops | 450 | 40500 |\n", "| Gloomberry Fizzer | 250 | 30000 |\n", "| Marchpane Original | 60 | 18000 |\n", "+--------------------+-------------+---------------+" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT candies.name,\n", " SUM(shop_orders.boxes) AS total_boxes,\n", " SUM(shop_orders.boxes * candies.price_pence) AS revenue_pence\n", "FROM shop_orders\n", "JOIN candies ON shop_orders.candy_id = candies.candy_id\n", "GROUP BY candies.name\n", "ORDER BY revenue_pence DESC;" ] }, { "cell_type": "markdown", "id": "5b853f85", "metadata": {}, "source": [ "One row per candy, not per order — that's `GROUP BY` at work. The `AS` keyword just gives a calculated column a readable name.\n", "\n", "Ledger's entire quarterly report is queries like this one. So is a streaming service's \"most played,\" a hospital's \"visits per patient,\" and a game's leaderboard. `JOIN` + `GROUP BY` is most of what \"data analysis\" means in practice." ] }, { "cell_type": "markdown", "id": "51143ef2", "metadata": {}, "source": [ "### Hazel's Safety Rule\n", "\n", "One disaster remains: the Puddleton shipment — an order pointing at the wrong product. The spreadsheet accepted it silently. What does the database do?\n", "\n", "A rule like `FOREIGN KEY` is an **integrity constraint**: a promise about the data that the database itself enforces. Not a warning. A refusal.\n", "\n", "The next cell tries to log a batch for candy #999 — a candy that does not exist. It's marked not to run automatically, because it is *designed to fail*. Run it yourself and read the error." ] }, { "cell_type": "code", "execution_count": 17, "id": "a3c3661d", "metadata": {}, "outputs": [], "source": [ "#| eval: false\n", "%%sql\n", "-- This INSERT is designed to FAIL. Run it and read the error message.\n", "INSERT INTO batches (batch_id, candy_id, batch_size, quality_score, made_on)\n", "VALUES (91, 999, 500, 7.0, '2026-07-03');" ] }, { "cell_type": "markdown", "id": "47f06869", "metadata": {}, "source": [ "You should see an error ending in **`FOREIGN KEY constraint failed`**.\n", "\n", "Hazel's verdict on the spreadsheet was: *\"nothing stopped us.\"* This is what stopping us looks like. The database checked `candies` for a candy #999, found nothing, and rejected the row — no matter who typed it, no matter how busy the factory was.\n", "\n", "That's the deep difference between a spreadsheet and a database. A spreadsheet stores what you typed. A database enforces what must be *true*." ] }, { "cell_type": "markdown", "id": "7c079263", "metadata": {}, "source": [ "### ✏️ Your Turn — Hazel's Quality Watchlist\n", "\n", "Hazel wants a watchlist: the **candy name**, **batch ID**, and **quality score** for every batch scoring **below 9.0**, worst first.\n", "\n", "You'll need a `JOIN` (the names live in `candies`, the scores in `batches`), a `WHERE`, and an `ORDER BY`. Use the batch report query above as your model." ] }, { "cell_type": "code", "execution_count": 18, "id": "9326c95d", "metadata": {}, "outputs": [], "source": [ "#| eval: false\n", "%%sql\n", "-- TODO: Hazel's watchlist — name, batch_id, quality_score for batches under 9.0, worst first\n", "SELECT" ] }, { "cell_type": "markdown", "id": "58b083be", "metadata": {}, "source": [ "### 💭 Think About It — The Machine That Says No\n", "\n", "The foreign-key error *prevented* a user from doing what they asked. Usually we call that a bug. Here it's the whole point.\n", "\n", "Where else should software refuse an instruction, even from an authorized user? Where would a hard \"no\" do more harm than good? Who should get to decide which rules are unbreakable?" ] }, { "cell_type": "markdown", "id": "0967d8e2", "metadata": {}, "source": [ "## 5. When Rows Don't Fit: Juniper's Flavor Dreams\n", "\n", "**Juniper Quist** runs Experimental Flavors, and her data problem is different in kind. The factory invites the public to submit \"flavor dreams\" — descriptions of candies they wish existed. The submissions are chaos:\n", "\n", "- One dreamer specifies a texture, a color, and a sound.\n", "- Another lists fourteen allergies and a childhood memory.\n", "- Another writes only: *\"make it taste like a thunderstorm.\"*\n", "\n", "Try designing a table for that. What are the columns? `texture`? `memory`? `sound_on_unwrapping`? Every submission would fill three columns and leave forty empty — and next week someone invents a field you never imagined. Rigid columns, which *saved* the factory in sections 1–4, are exactly wrong here." ] }, { "cell_type": "markdown", "id": "35c372ba", "metadata": {}, "source": [ "### The Document Model\n", "\n", "The alternative: store each submission as a **document** — a free-form bundle of labeled data, usually written as **JSON** (JavaScript Object Notation). JSON should look eerily familiar; it's essentially a Python dictionary in text form:\n", "\n", "```\n", "{\"from\": \"a dreamer\", \"wants\": [\"fizzy\", \"purple\"], \"loudness\": 3}\n", "```\n", "\n", "A **document database** stores one document per record and lets every document have a *different shape*. Famous examples include MongoDB; these systems are often grouped under the banner **NoSQL** (\"not only SQL\").\n", "\n", "We don't need new software to try the idea — SQLite can store JSON in an ordinary `TEXT` column and query inside it. The next cell files three wildly different dreams." ] }, { "cell_type": "code", "execution_count": 19, "id": "74fc2464", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'sqlite:///candy_factory.db'" ], "text/plain": [ "Running query in 'sqlite:///candy_factory.db'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "3 rows affected." ], "text/plain": [ "3 rows affected." ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
" ], "text/plain": [ "++\n", "||\n", "++\n", "++" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "DROP TABLE IF EXISTS flavor_dreams;\n", "CREATE TABLE flavor_dreams (\n", " dream_id INTEGER PRIMARY KEY,\n", " dream TEXT\n", ");\n", "\n", "INSERT INTO flavor_dreams (dream_id, dream) VALUES\n", " (1, '{\"from\": \"Milo, age 9\", \"taste\": \"thunderstorm\", \"loudness\": 5}'),\n", " (2, '{\"from\": \"Prof. Anemone\", \"texture\": \"cloud\", \"color\": \"in-between blue\",\n", " \"allergies\": [\"nuts\", \"regret\"]}'),\n", " (3, '{\"from\": \"anonymous\", \"taste\": \"the last day of school\",\n", " \"must_hum\": true, \"budget_pence\": 200}');" ] }, { "cell_type": "markdown", "id": "ac1a67c9", "metadata": {}, "source": [ "Three rows, three completely different shapes — and the table didn't complain, because to the table each dream is just one text value. No empty columns, no `ALTER TABLE` every time a dreamer invents a new field.\n", "\n", "Can we still *query* inside them? SQLite provides `json_extract(column, '$.field')`, which reaches into a document and pulls out one labeled value (the `$` means \"start at the top of the document\"). The next cell pulls out who each dream is from, and its taste — if it has one." ] }, { "cell_type": "code", "execution_count": 20, "id": "bc1ddb34", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'sqlite:///candy_factory.db'" ], "text/plain": [ "Running query in 'sqlite:///candy_factory.db'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
dream_iddreamertaste
1Milo, age 9thunderstorm
2Prof. AnemoneNone
3anonymousthe last day of school
" ], "text/plain": [ "+----------+---------------+------------------------+\n", "| dream_id | dreamer | taste |\n", "+----------+---------------+------------------------+\n", "| 1 | Milo, age 9 | thunderstorm |\n", "| 2 | Prof. Anemone | None |\n", "| 3 | anonymous | the last day of school |\n", "+----------+---------------+------------------------+" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT dream_id,\n", " json_extract(dream, '$.from') AS dreamer,\n", " json_extract(dream, '$.taste') AS taste\n", "FROM flavor_dreams;" ] }, { "cell_type": "markdown", "id": "8013f584", "metadata": {}, "source": [ "Notice Prof. Anemone's `taste` comes back as `None` — her dream simply doesn't have that field, and the document model shrugs: fine.\n", "\n", "That shrug is the whole trade-off. Compare the two models honestly:\n", "\n", "| | Relational (tables) | Document (JSON) |\n", "|---|---|---|\n", "| Every record has the same shape? | Yes — enforced | No — each document differs |\n", "| Rules like foreign keys? | Strong, automatic | Weak or manual |\n", "| Combining data (`JOIN`)? | Excellent | Awkward |\n", "| Handles surprise fields? | Painfully (`ALTER TABLE`) | Effortlessly |\n", "| Best when… | the shape is known and shared | the shape is varied and evolving |\n", "\n", ": Relational tables compared with JSON documents on structure, rules, and change.\n", "\n", "The rule of thumb: **batches and bank accounts want tables; dreams and profiles want documents.** Real systems — including this factory — often use both, side by side." ] }, { "cell_type": "markdown", "id": "067be6ba", "metadata": {}, "source": [ "### ✏️ Your Turn — Pick the Right Model\n", "\n", "For each scenario, choose **relational** or **document** — and give one sentence of *why*, using the table above. There's a defensible case on both sides for at least one of them.\n", "\n", "1. A bank's ledger of account transfers.\n", "2. Player save-files for a video game, where every update adds new abilities and items.\n", "3. A clinic's records of patients, visits, and prescriptions.\n", "4. An online shop's product catalog: books, gloves, kayaks — each with different attributes.\n", "\n", "No code for this one — write your four answers in the cell below or on paper." ] }, { "cell_type": "markdown", "id": "4b4510c4", "metadata": {}, "source": [ "*(Write your answers here — double-click this cell to edit it.)*\n", "\n", "1.\n", "2.\n", "3.\n", "4." ] }, { "cell_type": "markdown", "id": "9fd11aa1", "metadata": {}, "source": [ "### 💭 Think About It — Queryable Isn't the Same as Fair Game\n", "\n", "Juniper realizes she can run one query and read every dream ever submitted — including names, allergies, and childhood memories people typed in without much thought.\n", "\n", "Databases make data *easy to ask about*. Does being able to query something give you the right to? Who should be allowed to run which queries at the factory — and who decides?" ] }, { "cell_type": "markdown", "id": "535ee6b9", "metadata": {}, "source": [ "## 6. How a *Program* Talks to the Database\n", "\n", "The `%%sql` magic is a tool for humans in notebooks. Real applications — a shop website, the factory's ordering app — are programs, and they run SQL through an ordinary library. Python ships with one: `sqlite3`.\n", "\n", "The pattern is only three moves: **connect** to the database file, **execute** a query, **fetch** the results. The next cell is plain Python, reading the same `candy_factory.db` we've been building all along." ] }, { "cell_type": "code", "execution_count": 21, "id": "4bb5e530", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Marchpane Original: 300p\n", "Hummingbird Nougat: 260p\n", "Thundermint Bar: 250p\n" ] } ], "source": [ "import sqlite3\n", "\n", "connection = sqlite3.connect(\"candy_factory.db\")\n", "rows = connection.execute(\n", " \"SELECT name, price_pence FROM candies ORDER BY price_pence DESC LIMIT 3\"\n", ").fetchall()\n", "connection.close()\n", "\n", "for name, price in rows:\n", " print(f\"{name}: {price}p\")" ] }, { "cell_type": "markdown", "id": "8f8f7e54", "metadata": {}, "source": [ "Same SQL, same answers — the language doesn't change, only who's speaking it. The query went in as a string; the results came back as a plain Python list you can loop over.\n", "\n", "Hold onto this pattern. Next notebook, we'll build a small **web application**, and this is exactly how it will work: a browser asks a question, a few lines of Python run a query, and the database — the permanent, rule-enforcing memory underneath everything — supplies the answer." ] }, { "cell_type": "markdown", "id": "3564d895", "metadata": {}, "source": [ "## SQL Quick Reference: The Card on the Wall\n", "\n", "Cordelia keeps a laminated card taped beside the factory terminal — every command the Marchpane Works actually uses, with a working example of each. This is that card.\n", "\n", "Two bits of official vocabulary make the card easier to organize. Commands that shape the *tables themselves* are called **DDL** (*data definition language*). Commands that change or read the *rows inside them* are called **DML** (*data manipulation language*). You've been using both all along — the card just sorts them.\n", "\n", "Every example below runs against this notebook's own tables. Paste any of them into a `%%sql` cell above and experiment." ] }, { "cell_type": "markdown", "id": "ca479dea", "metadata": {}, "source": [ "### Defining Structure (DDL)\n", "\n", "These commands create and reshape tables — the filing cabinets, not the files.\n", "\n", "| Keyword | What it does | Example |\n", "|---|---|---|\n", "| `CREATE TABLE` | makes a new table with named, typed columns | `CREATE TABLE staff (staff_id INTEGER PRIMARY KEY, name TEXT);` |\n", "| `DROP TABLE IF EXISTS` | deletes a table and everything in it | `DROP TABLE IF EXISTS staff;` |\n", "| `ALTER TABLE ... ADD COLUMN` | adds a column to an existing table | `ALTER TABLE candies ADD COLUMN is_seasonal INTEGER;` |\n", "\n", ": SQL keywords for defining structure (DDL), with an example of each.\n", "\n", "`ALTER TABLE` is the command Juniper's flavor dreams were rescuing us from: with rigid columns, every newly invented field means altering the table for *every* row." ] }, { "cell_type": "markdown", "id": "7091a77f", "metadata": {}, "source": [ "### Changing Data (DML)\n", "\n", "These commands add, edit, and remove rows.\n", "\n", "| Keyword | What it does | Example |\n", "|---|---|---|\n", "| `INSERT INTO ... VALUES` | adds new rows | `INSERT INTO candies (candy_id, name, department, price_pence, sugar_grams) VALUES (9, 'Fizzing Humbug', 'Fizzworks', 150, 21.0);` |\n", "| `UPDATE ... SET ... WHERE` | edits matching rows | `UPDATE candies SET price_pence = 130 WHERE name = 'Gloomberry Fizzer';` |\n", "| `DELETE FROM ... WHERE` | removes matching rows | `DELETE FROM candies WHERE candy_id = 9;` |\n", "\n", ": SQL keywords for changing data (DML), with an example of each.\n", "\n", "⚠️ **The classic disaster:** on `UPDATE` and `DELETE`, the `WHERE` clause chooses *which* rows. Forget it, and you change — or delete — **every row in the table**. Databases do exactly what you ask. Ask carefully." ] }, { "cell_type": "markdown", "id": "8aeb8170", "metadata": {}, "source": [ "### Asking Questions (Queries)\n", "\n", "The `SELECT` family — most of the SQL anyone writes, most days.\n", "\n", "| Keyword | What it does | Example |\n", "|---|---|---|\n", "| `SELECT ... FROM` | choose columns from a table | `SELECT name, price_pence FROM candies;` |\n", "| `WHERE` | keep only matching rows | `SELECT name FROM candies WHERE sugar_grams < 20;` |\n", "| `ORDER BY ... DESC` | sort results (DESC = highest first) | `SELECT name, price_pence FROM candies ORDER BY price_pence DESC;` |\n", "| `LIMIT` | keep only the first *n* results | `SELECT name FROM candies ORDER BY price_pence DESC LIMIT 3;` |\n", "| `JOIN ... ON` | recombine two tables by matching keys | `SELECT candies.name, batches.quality_score FROM batches JOIN candies ON batches.candy_id = candies.candy_id;` |\n", "| `GROUP BY` | gather rows into buckets for totals | `SELECT candy_id, COUNT(*) FROM batches GROUP BY candy_id;` |\n", "| `COUNT / SUM / AVG` | boil a bucket down to one number | `SELECT AVG(quality_score) FROM batches;` |\n", "| `AS` | rename a result column | `SELECT name, price_pence AS price FROM candies;` |\n", "\n", ": SQL keywords for querying data, with an example of each.\n" ] }, { "cell_type": "markdown", "id": "72786527", "metadata": {}, "source": [ "### Types and Rules\n", "\n", "The building blocks inside a `CREATE TABLE`.\n", "\n", "| Keyword | What it does | Example |\n", "|---|---|---|\n", "| `INTEGER` / `REAL` / `TEXT` | column types: whole numbers / decimals / text | `sugar_grams REAL` |\n", "| `PRIMARY KEY` | this column is the row's unique, permanent ID | `candy_id INTEGER PRIMARY KEY` |\n", "| `FOREIGN KEY ... REFERENCES` | values here must exist as a key in another table | `FOREIGN KEY (candy_id) REFERENCES candies (candy_id)` |\n", "| `NOT NULL` | this column may never be left empty | `name TEXT NOT NULL` |\n", "| `PRAGMA foreign_keys = ON` | tells SQLite to actually enforce foreign keys | `PRAGMA foreign_keys = ON;` |\n", "\n", ": SQL column types and constraints, with an example of each.\n", "\n", "**Not covered here, but you'll meet them:** `DISTINCT` (drop duplicate results), `HAVING` (a `WHERE` for groups), and `CREATE INDEX` (make lookups on a column faster). When you see them in the wild, you'll know they're friends.\n", "\n", "Keep the card handy — the capstone below is where you'll want it." ] }, { "cell_type": "markdown", "id": "ab291875", "metadata": {}, "source": [ "## ✏️ Capstone — The Sweetshop Database\n", "\n", "Time to build your own. You'll design a small database and have an AI assistant (Gemini, Claude, or ChatGPT) write the SQL — while *you* make every design decision and verify every result.\n", "\n", "**The default theme:** you've opened a small sweetshop that stocks Marchpane candies. You need three tables: your products, your suppliers, and your sales. **Or reskin it entirely** — a record shop, your game collection, a plant nursery, a recipe box. Any theme with 2–3 kinds of connected things works.\n", "\n", "### Step 0 — Design First (before touching the AI)\n", "\n", "In the cell below, write your design as plain text:\n", "\n", "- Your 2–3 tables, and what one *row* of each represents\n", "- The columns of each table, with types\n", "- Where the primary keys are, and which column is a **foreign key** pointing at which table" ] }, { "cell_type": "markdown", "id": "a9a8f0cc", "metadata": {}, "source": [ "*(Double-click and write your design here.)*\n", "\n", "**Table 1:** ...\n", "\n", "**Table 2:** ...\n", "\n", "**Table 3:** ..." ] }, { "cell_type": "markdown", "id": "162182fe", "metadata": {}, "source": [ "### Step 1 — Build It *(prompt #1)*\n", "\n", "Turn your design into a prompt. A skeleton to fill in:\n", "\n", "> I'm learning SQL with SQLite in a Jupyter notebook, using `%%sql` cell magic.\n", "> Write `CREATE TABLE` statements for these tables: **[paste your design]**.\n", "> Include `PRIMARY KEY` and `FOREIGN KEY` constraints, and start with `DROP TABLE IF EXISTS` lines.\n", "> Then write `INSERT` statements adding **[5–10]** realistic rows per table, themed around **[your theme]**.\n", "> SQL only, one code block, no explanations.\n", "\n", "Paste the AI's SQL into the cell below and run it. If it errors, read the message — fix it yourself or ask the AI, but *you* decide what changes." ] }, { "cell_type": "code", "execution_count": 22, "id": "90c33d21", "metadata": {}, "outputs": [], "source": [ "#| eval: false\n", "%%sql\n", "-- ✏️ Paste your AI-built CREATE TABLE and INSERT statements here, then run." ] }, { "cell_type": "markdown", "id": "b4086d95", "metadata": {}, "source": [ "### Step 2 — Interrogate It *(prompt #2)*\n", "\n", "Ask the AI for **five queries** against your schema — require at least: one `WHERE` filter, one `ORDER BY ... LIMIT`, one `JOIN`, and one `GROUP BY` with `SUM` or `COUNT`.\n", "\n", "**Then verify like Barnaby would:** before running each query, look at your inserted data and predict the answer. Run it. If a result surprises you, figure out whether the query is wrong or your prediction was — one of those happens a lot with AI-written `JOIN`s." ] }, { "cell_type": "code", "execution_count": 23, "id": "e18c9c8d", "metadata": {}, "outputs": [], "source": [ "#| eval: false\n", "%%sql\n", "-- ✏️ Paste and run your five queries here (one at a time is fine)." ] }, { "cell_type": "markdown", "id": "1736b77f", "metadata": {}, "source": [ "### Step 3 — Break It, Then Reflect\n", "\n", "Two final tests:\n", "\n", "1. **Try to break your own rules.** Write an `INSERT` that violates one of your foreign keys (like candy #999). Confirm the database refuses it. If it *doesn't*, find out why — did the AI forget the constraint, or is `PRAGMA foreign_keys = ON` missing?\n", "2. **Reflect** in the cell below, 2–3 sentences: What did the AI get wrong or almost-wrong? What did you catch by predicting results first?\n", "\n", "*AI is a fast first draft. You verify.*" ] }, { "cell_type": "code", "execution_count": 24, "id": "ac89d64b", "metadata": {}, "outputs": [], "source": [ "#| eval: false\n", "%%sql\n", "-- ✏️ Your constraint-breaking INSERT goes here. It SHOULD fail." ] }, { "cell_type": "markdown", "id": "8a663ee1", "metadata": {}, "source": [ "*(Your 2–3 sentence reflection — double-click to edit.)*" ] }, { "cell_type": "markdown", "id": "1d3d0f31", "metadata": {}, "source": [ "## Key Terms\n", "\n", "- **Aggregate function** — A function like `SUM()`, `COUNT()`, or `AVG()` that boils a group of rows down to one number.\n", "- **Column** — One fact recorded about every row in a table, such as `price_pence`.\n", "- **Database** — An organized, permanent collection of data managed by software that keeps it correct and shareable.\n", "- **DBMS (database management system)** — The program that manages a database and enforces its rules; SQLite is one.\n", "- **DDL (data definition language)** — The SQL commands that shape tables themselves: `CREATE TABLE`, `DROP TABLE`, `ALTER TABLE`.\n", "- **DML (data manipulation language)** — The SQL commands that change or read the rows: `INSERT`, `UPDATE`, `DELETE`, `SELECT`.\n", "- **Document database** — A database that stores free-form documents (usually JSON), letting every record have a different shape.\n", "- **Foreign key** — A column holding the primary key of a row in another table, linking the two.\n", "- **Integrity constraint** — A rule about the data (like a foreign key) that the database itself enforces by refusing bad changes.\n", "- **JOIN** — The SQL operation that recombines rows from two tables by matching key values.\n", "- **JSON** — A text format for labeled, nested data; looks and works much like a Python dictionary.\n", "- **NoSQL** — An umbrella term for non-relational databases, including document stores.\n", "- **Primary key** — A column whose value uniquely and permanently identifies each row in its table.\n", "- **Query** — A request for data, written in SQL with `SELECT`.\n", "- **Relational model** — The design that organizes data into tables of rows and columns, one table per kind of thing.\n", "- **Row** — One record in a table: one candy, one batch, one order.\n", "- **SQL** — Structured Query Language, the standard language for creating, filling, and querying databases.\n", "- **Table** — A named grid holding every record of one kind of thing.\n" ] }, { "cell_type": "markdown", "id": "6d306c08", "metadata": {}, "source": [ "## Summary\n", "\n", "A database gives data three things a running program can't: permanence, shared access, and enforced rules. The relational model organizes data as tables — one per kind of thing — where every row has a permanent primary key, and other tables point at it with foreign keys instead of copying names. SQL is the language for all of it: `CREATE TABLE` and `INSERT` to build, `SELECT` with `WHERE`, `ORDER BY`, `JOIN`, and `GROUP BY` to ask questions. Integrity constraints are the database *refusing* to store what must not be true — the safety rule the Marchpane spreadsheet never had. And when data won't sit in fixed columns, document databases trade those rules away for flexibility. Knowing *which trade to make* is the real skill." ] }, { "cell_type": "markdown", "id": "f50047a0", "metadata": {}, "source": [ "## What's Next\n", "\n", "Your database can answer questions — but only for someone sitting at this notebook. Next time we go bigger: operating systems, networks, and the web, ending with your first **web application**. A browser anywhere in the world will send a request, and a few lines of Python will answer it — by querying a database, exactly the way you did today." ] }, { "cell_type": "markdown", "id": "dd9c425d", "metadata": {}, "source": [ "*COMP 1150 — Computer Science Concepts · Brendan Shea, PhD*\n", "*Content licensed under [CC BY 4.0](https://creativecommons.org/licenses/by/4.0/).*" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.13.9" } }, "nbformat": 4, "nbformat_minor": 5 }