# /// script # requires-python = ">=3.12,<3.15" # dependencies = [ # "marimo[sql]", # "duckdb", # ] # /// """SQL Handbook, Chapter A: What a Database Is. """ import marimo __generated_with = "0.24.0" app = marimo.App(width="medium", sql_output="polars") @app.cell def _(): import duckdb import marimo as mo from urllib.request import urlopen return duckdb, mo, urlopen @app.cell(hide_code=True) def _(mo): mo.md(r""" # What a Database Is Chapter A of the SQL Handbook. Five sections, each one concept. There is no query language in this chapter. Part B starts `SELECT`. What is here is the vocabulary that makes a query mean anything: what a table holds, what makes one row findable, and how two tables know about each other. Every query below runs against the course warehouse, Northwind. You can read this straight through, or open it when you need one thing. Every section ends with questions that have no answers written down, because those are the ones asked out loud in class. To run the code yourself, download the `.py` into your project and open it with `uv run marimo edit`. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Opening the warehouse The two cells below load the 13 parquet files in `data/northwind/` into DuckDB as 13 tables. How that works is Part D, sections 26 and 27, and nothing in this chapter depends on reading it. One name is worth noticing now. The file is `order-details.parquet` and the table is `"Order Details"`, with a space in it, so every mention of it needs double quotes. """) return @app.cell def _(duckdb, mo): # Look for a data/northwind folder here and in every folder above, so the # chapter works wherever you save it. If there is none, read the copy on the # course site instead. _here = mo.notebook_dir() _source = next( (p / "data" / "northwind" for p in [_here, *_here.parents] if (p / "data" / "northwind").is_dir()), "https://oim7510.github.io/data/northwind", ) con = duckdb.connect() _files = { "categories": "categories", "customercustomerdemo": "customercustomerdemo", "customerdemographics": "customerdemographics", "customers": "customers", "employees": "employees", "employeeterritories": "employeeterritories", "Order Details": "order-details", "orders": "orders", "products": "products", "regions": "regions", "shippers": "shippers", "suppliers": "suppliers", "territories": "territories", } for _table, _file in _files.items(): con.execute( f"""CREATE TABLE "{_table}" AS SELECT * FROM '{_source}/{_file}.parquet'""" ) return (con,) @app.cell def _(mo, urlopen): def erd(name): """Return the named diagram from the assets folder, drawn in the page's text color.""" try: _svg = (mo.notebook_dir() / "assets" / f"{name}.svg").read_text() except FileNotFoundError: try: _url = f"https://oim7510.github.io/handbooks/sql/assets/{name}.svg" _svg = urlopen(_url).read().decode("utf-8") except Exception: return mo.md(f"*Diagram `{name}` could not be loaded.*") _style = ( "" ) return mo.Html(f'{_style}
{_svg}
') return (erd,) @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## 1. Tables, rows, columns A relational database keeps everything in tables. A table has named columns and some number of rows, and that is the whole shape of it. The rest of this book is about what you are allowed to claim about tables. ๐Ÿ“– **Used in:** Designing your database """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### A table is columns and rows `shippers` is the smallest table in the warehouse that has anything in it. It has three columns and three rows, and one row is one shipping company. """) return @app.cell def _(con): con.sql("SELECT * FROM shippers") return @app.cell(hide_code=True) def _(mo): mo.md(r""" Three words exist for a row and three for a column, and you will meet all six. | the whole thing | one across | one down | |---|---|---| | table, relation | row, record, tuple | column, field, attribute | `ShipperID`, `CompanyName` and `Phone` are the columns. The line beginning `2` is a row, and it is one company. ๐Ÿ‘‰ **Try it:** find another table in the warehouse with fewer than 10 rows and state, in one sentence, what one row of it means. `regions` and `categories` are both candidates. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Every value in a column has its type `DESCRIBE` asks the database what a table looks like. It answers with one line per column. """) return @app.cell def _(con): con.sql("DESCRIBE shippers") return @app.cell(hide_code=True) def _(mo): mo.md(r""" `ShipperID` is an `INTEGER`, and it is an integer in every row there will ever be. Writing the word `later` into that column is refused at the moment somebody tries, so nothing downstream has to defend against finding it. ### In Excel terms A spreadsheet column holds whatever each cell happens to hold, so one cell says `1,204`, the next says `1204`, a third says `n/a`, and `SUM` quietly skips two of them. A database column has one declared type for all of its rows, which is why a column of prices in a database can be added up without being inspected first. ๐Ÿ‘‰ **Try it:** run `DESCRIBE orders` and find the three columns that hold a date and a time. Then state what `Freight` is a number of. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### What a row stands for `employees` holds nine rows. One row is one person, and the whole table fits on a screen, which makes it the example this chapter keeps coming back to. """) return @app.cell def _(con): con.sql(""" SELECT "EmployeeID", "FirstName", "LastName", "Title", "ReportsTo" FROM employees ORDER BY "EmployeeID" """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" The table has 18 columns and five of them are shown. A photograph and a paragraph of notes are in there too, so a column can hold something you would never add up. ๐Ÿ‘‰ **Try it:** state in one sentence what one row of `orders` is, and then what one row of `"Order Details"` is. The second one is harder and ยง3 is where it gets settled. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### The order of the rows is not part of the table Nothing about a table says which row comes first. The query above ends with `ORDER BY "EmployeeID"`, and that is the only reason the nine came back numbered one to nine. The same query without that line may hand you the same rows in a different order tomorrow, and the database is within its rights. ยง8 is where you ask for an order. ๐Ÿ‘‰ **Try it:** run the employees query again with the `ORDER BY` line deleted, and then with `ORDER BY "LastName"`. Same rows, three arrangements. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### The size of every table `duckdb_tables()` is the database describing itself. """) return @app.cell def _(con): con.sql(""" SELECT table_name, estimated_size FROM duckdb_tables() ORDER BY table_name """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" DuckDB calls that column an estimate, and for a table it has just loaded whole it is the exact count. Checking one of them with `count(*)` takes a second and is the habit to build. `customerdemographics` and `customercustomerdemo` hold zero rows. Somebody designed them, somebody created them, and nobody ever put anything in them. **The trap.** A table existing says nothing about whether the thing it describes was ever recorded. A query that goes through either of those two comes back with no rows while looking entirely correct. ๐Ÿ‘‰ **Try it:** confirm one of the numbers above with `SELECT count(*)` on that table. Then take the three largest tables and state what one row of each means, in one sentence each. ### โœ” Check your understanding 1. `orders` has 16,282 rows. What is one row of it? 2. A colleague sends a spreadsheet where one column holds numbers in some rows and the word `pending` in others. What has to happen before that becomes a database column? 3. Two people run the same query with no `ORDER BY` and compare the first row they get. The two rows differ. What can you conclude? 4. `customerdemographics` has zero rows. What does that tell you about the design, and what does it tell you about the data? """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## 2. Primary keys, compound keys, surrogate keys A table of 16,282 rows is no use until you can name one of them. A **primary key** is the column, or the group of columns, that identifies exactly one row and never repeats. Every table should have one, and somebody decides which column it is. The data never hands you the answer. ๐Ÿ“– **Used in:** Designing your database """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### A primary key is unique and it is never missing `CustomerID` is the primary key of `customers`. Two counts show what that buys. """) return @app.cell def _(con): con.sql(""" SELECT count(*) AS n_rows, count(DISTINCT "CustomerID") AS n_keys FROM customers """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" There are 93 rows and 93 different values in that column. If those two numbers ever came apart, some id would appear twice or some row would have none, and either way the column would not be a key. Only a key guarantees that "the customer with id `VINET`" names exactly one row. Those two counts are the check for any column somebody claims is a key, and they cost one query. ๐Ÿ‘‰ **Try it:** run the same two counts on `employees` using `"EmployeeID"`. Then run them on `customers` using `"Country"`, and explain in one sentence why that column could never be the key. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Choosing which column is the key A `Student` table has to identify one student. The candidates are the student id number, the full name, the email address, the date of birth, the home address, the Social Security number and the driver's license number. Three properties decide it. - **Unique.** Two students must never share the value, and a column that passes this test is a **candidate key**. Full name fails on the first pair of Smiths, and it fails quietly, because nothing about the row looks wrong. - **Stable.** The value must never change. Email addresses and home addresses change all the time, and every row pointing at the old value is then pointing at nothing. - **Simple, and always there.** A date of birth is shared by dozens of people. Not every student holds a driver's license. A Social Security number is a legal liability you would rather keep out of the table entirely. The student id wins because the school invented it for this job, and a key that already exists in the business is a **natural key**. When no such column exists, the next two knowledge points are the two ways out. ๐Ÿ‘‰ **Try it:** think of a list your own team keeps in a spreadsheet. Name the column you would make the primary key, and state which of the three properties you are least sure of. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### When a single column is not enough `employeeterritories` records which employee covers which territory. It has 49 rows and two columns, and neither column on its own identifies a row. """) return @app.cell def _(con): con.sql(""" SELECT count(*) AS n_rows, count(DISTINCT "EmployeeID") AS n_employees, count(DISTINCT ("EmployeeID", "TerritoryID")) AS n_pairs FROM employeeterritories """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" Nine employees are spread across 49 rows, so `EmployeeID` repeats. The pair never repeats. The key of this table is the two columns together, written `PRIMARY KEY (EmployeeID, TerritoryID)`, and that is a **compound key**, also called a **composite key**. The same shape shows up on paper, where you can see it at a glance: | StudentID | CourseID | Grade | |---|---|---| | `123456789` | `ACC7200` | A | | `123456789` | `QTM7800` | B | | `987654321` | `ACC7200` | A- | | `987654321` | `QTM7800` | B+ | Each id appears twice. Each course appears twice. Each pairing appears once. **The trap.** `TerritoryID` on its own also has 49 distinct values in those 49 rows, so a uniqueness check on that one column passes today. It passes because no territory has yet been given to two people. A count over today's rows tells you what happened; a primary key states what the database will refuse tomorrow. ๐Ÿ‘‰ **Try it:** run the same three counts on `"Order Details"`, using `"OrderID"` and the pair `("OrderID", "ProductID")`. Predict all three numbers first. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### An invented key A **surrogate key** is a column the database made up. It carries no meaning at all, and that is the advantage, because nothing that happens in the business can change it. | EnrollmentID | StudentID | CourseID | Grade | |---|---|---|---| | `1` | `123456789` | `ACC7200` | A | | `2` | `123456789` | `QTM7800` | B | | `3` | `987654321` | `ACC7200` | A- | | `4` | `987654321` | `QTM7800` | B+ | `EnrollmentID` is a counter. `orders.OrderID` is the same idea: 10248 is not a fact about that order, it is the number the system handed out when the order arrived. **The trap.** A surrogate key permits the same pairing twice. Nothing stops a fifth row holding `123456789` and `ACC7200` again. The rule that a student takes a course once has to be declared separately, as `UNIQUE (StudentID, CourseID)`, and a design that adds `EnrollmentID` and forgets that line has quietly given up a guarantee it had before. **Every engine spells the counter differently, and DuckDB refuses the common spelling.** SQLite takes this: ```sql CREATE TABLE enrollment (id INTEGER PRIMARY KEY AUTOINCREMENT); ``` DuckDB answers: ``` Parser Error: syntax error at or near "AUTOINCREMENT" ``` DuckDB wants a sequence instead. ยง24 is where the `CREATE TABLE` gets written and where the spelling starts to matter. ๐Ÿ‘‰ **Try it:** run the two counts from earlier on `products` using `"ProductName"`. Then judge whether the product name could be the primary key, and what would have to happen for your answer to change. ### โœ” Check your understanding 1. A colleague says the email address is the primary key of the customer table. What breaks the first time somebody changes their email? 2. `count(*)` and `count(DISTINCT "OrderID")` on `orders` are both 16,282. Does that prove `OrderID` is the primary key? 3. A table has a compound key made of three columns. How many rows can share the same values in the first two of them? 4. What does a surrogate key fail to give you that `UNIQUE (StudentID, CourseID)` gives you? """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## 3. Relationship cardinality: 1:1, 1:M, M:N Tables are kept apart on purpose, and a **relationship** is how one table refers to another. Every relationship is a business rule written down. There are three kinds, and telling them apart is most of what database design is. ๐Ÿ“– **Used in:** Designing your database """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### One to many There is one row on the left and many rows on the right, and each row on the right belongs to exactly one row on the left. A customer places many orders. Each order belongs to one customer. """) return @app.cell def _(erd): erd("erd-one-to-many") return @app.cell(hide_code=True) def _(mo): mo.md(r""" Customer `VINET` is one row in `customers`. Here is how many orders point back at it. """) return @app.cell def _(con): con.sql(""" SELECT count(*) AS vinet_orders FROM orders WHERE "CustomerID" = 'VINET' """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" Those 158 orders each carry the five characters `VINET`. The company name, the address and the phone number sit once in `customers` and are not copied anywhere. This is the reason the two tables are two tables. An address copied onto 158 orders is 158 chances to be wrong. ๐Ÿ‘‰ **Try it:** count the orders belonging to customer `SAVEA` and to customer `ANATR`. Then check that each of those ids appears exactly once in `customers`. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### A table can point at itself `employees.ReportsTo` holds the `EmployeeID` of that person's manager, which puts `employees` on both ends of one relationship and makes it a **self-referencing relationship**. Nine rows fit on a screen, so this is the one relationship in the warehouse you can check entirely by eye. """) return @app.cell def _(con): con.sql(""" SELECT "EmployeeID", "FirstName", "LastName", "Title", "ReportsTo" FROM employees ORDER BY "EmployeeID" """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" Andrew Fuller is employee 2 and his `ReportsTo` is empty, because the Vice President reports to nobody. Five people point at him. Three point at Steven Buchanan, who is employee 5 and points at Fuller in turn. An empty foreign key is a statement in its own right, and here it says the chain stops. ยง9 is where the empty value gets its proper name. ๐Ÿ‘‰ **Try it:** read the nine rows and write out the reporting chain for Anne Dodsworth up to the top. How many names is it? Now do the same for Nancy Davolio. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### One to one There is at most one row on each side. A person holds one passport and a passport belongs to one person. Northwind has no one-to-one relationship anywhere in it, which is ordinary. When two tables line up one for one, the usual answer is a single table with more columns in it. Two cases keep a one-to-one alive. The first is a column so large that you want it out of the way of every query, such as an employee photograph. The second is a group of columns only some people are allowed to read, such as salary, which is easier to control when it sits in a table of its own. ๐Ÿ‘‰ **Try it:** name a one-to-one pair from your own work and state which of those two reasons keeps the two tables apart. If neither applies, explain what merging them would cost. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Many to many Take students and courses. A student takes many courses and a course holds many students. Two obvious designs are available and both of them are wrong. **Bad solution A.** Draw a one to many from students to courses. """) return @app.cell def _(erd): erd("erd-bad-a") return @app.cell(hide_code=True) def _(mo): mo.md(r""" Read the line back from the course end. Each course connects to one student, so every course in the school has exactly one person sitting in it. **Bad solution B.** Draw a one to many from courses to students. """) return @app.cell def _(erd): erd("erd-bad-b") return @app.cell(hide_code=True) def _(mo): mo.md(r""" Read that one back from the student end. Each student connects to one course, so nobody takes two subjects. The two diagrams are mirror images. Turning the line around moves the damage to the other side and never removes it, because the shape is what is wrong. **The answer is a third table**, holding one row for each pairing. """) return @app.cell def _(erd): erd("erd-enrollment") return @app.cell(hide_code=True) def _(mo): mo.md(r""" `ENROLLMENT` has a row for every student-and-course pair that exists. Its key is `(StudentID, CourseID)`, the compound key from ยง2, or the surrogate `EnrollmentID` with `UNIQUE (StudentID, CourseID)` beside it, which is also from ยง2. The one many-to-many has become two one-to-many relationships pointing inward. A table in that position is an **intersection table**, also called a junction table, and it almost always grows columns of its own. `Grade` belongs to the pairing. It is no more a fact about the student than it is a fact about the course. The warehouse holds two of them. - **`employeeterritories`.** It holds nothing but the pair, in 49 rows of two columns, keyed the way ยง2 described. - **`"Order Details"`.** It holds one row per product on one order, and it carries `UnitPrice`, `Quantity` and `Discount`. Those three facts belong to the pairing and to nothing else, because the price on the day is a fact about that line of that order. Order 10248 has three of those rows. """) return @app.cell def _(con): con.sql(""" SELECT * FROM "Order Details" WHERE "OrderID" = 10248 ORDER BY "ProductID" """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" The three lines carry three product ids and no product names. Product 11 is Queso Cabrales, 42 is Singaporean Hokkien Fried Mee and 72 is Mozzarella di Giovanni, and turning those numbers into those names means reaching into `products`, which is a join and belongs to ยง17. The order header, with the customer and the date and the 16.75 of freight, is one row over in `orders`. ๐Ÿ‘‰ **Try it:** name a many-to-many relationship in something you work on. State what its intersection table would be called, and name one column it would carry that belongs to neither of the two sides. ### โœ” Check your understanding 1. Customer `VINET` has 158 orders. How many customers does one of those orders have? 2. Bad solution A and bad solution B are the same mistake. Name the mistake without using the word direction. 3. `Grade` sits on `ENROLLMENT`. What goes wrong if somebody puts it on `STUDENT` instead? 4. `employees.ReportsTo` is empty in one row out of nine. What would you suspect if it were empty in four? """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## 4. Foreign keys and referential integrity A **foreign key** is a column holding another table's primary key. It is the mechanism behind every relationship in ยง3. **Referential integrity** is the promise that a foreign key always points at a row that exists. ๐Ÿ“– **Used in:** Designing your database """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### The foreign key sits on the many side `orders.CustomerID` is a foreign key. It holds a value taken from `customers.CustomerID`, which is that table's primary key. Which side it lands on follows from counting, and there is no choice in it. One customer has many orders, so a customer row has no single order id to hold. One order has one customer, so an order row holds one customer id comfortably. The pointer goes on the side that has one of the other thing. ### In Excel terms A foreign key is the column you would run `VLOOKUP` against. The difference is what happens when the value is missing. `VLOOKUP` puts `#N/A` in the cell and the spreadsheet carries on; a database with the constraint declared refuses to store the row at all. ๐Ÿ‘‰ **Try it:** for each of these pairs, state which table holds the foreign key and what the column is called: `products` and `categories`, `orders` and `employees`, `"Order Details"` and `orders`. Check each answer with `DESCRIBE`. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### What the constraint refuses Once a foreign key is declared, it blocks two things. Both messages below came out of DuckDB, from a two-table example with `ord.customer_id` declared as a foreign key into `customer.id`. The first is an order pointing at a customer who does not exist: ```sql INSERT INTO ord VALUES (99999, 'NOPE', DATE '2016-07-04'); ``` ``` Constraint Error: Violates foreign key constraint because key "id: NOPE" does not exist in the referenced table ``` The second is deleting a customer who still has orders: ```sql DELETE FROM customer WHERE id = 'VINET'; ``` ``` Constraint Error: Violates foreign key constraint because key "customer_id: VINET" is still referenced by a foreign key in a different table ``` A row whose foreign key names a parent that is not there is an **orphan**. Those two refusals are the only two ways an orphan can be created, and the constraint blocks both. ๐Ÿ‘‰ **Try it:** build that two-table example yourself, with `CREATE TABLE customer` and `CREATE TABLE ord`, and reproduce both messages. ยง24 has the syntax if you want it now. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### A parent with no children is allowed The rule runs one way only. Every order must have a customer. Nothing anywhere says a customer must have an order, and nothing says a territory must have an employee. Four territories are covered by nobody. """) return @app.cell def _(con): con.sql(""" SELECT "TerritoryID", "TerritoryDescription" FROM territories WHERE "TerritoryID" NOT IN (SELECT "TerritoryID" FROM employeeterritories) """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" Nobody sells in Columbia, Bentonville, Dallas or Austin, and that is a fact about the sales team. Territories are not the only parents here with no children. Through `ReportsTo` from ยง3, seven of the nine employees have nobody reporting to them. **The trap.** These four are the opposite of an orphan. An orphan is a child with no parent and the constraint forbids it. These are parents with no children, and no constraint has an opinion about them. Reading one as the other turns a true business fact into a bug report, and ยง5 shows where an ERD says which of the two you should expect. ๐Ÿ‘‰ **Try it:** write the same check the other way around, looking for employees who cover no territory. Predict the answer before you run it. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Design against enforcement Everything above describes what a foreign key does once it is **declared**. This warehouse declares nothing at all. It was loaded from parquet files, and a parquet file carries column names and column types and no constraints. """) return @app.cell def _(con): con.sql("DESCRIBE orders") return @app.cell(hide_code=True) def _(mo): mo.md(r""" The `key` column is empty for all 14 columns and the `null` column says `YES` for all 14. There is no primary key, no foreign key and nothing required anywhere in this table, and it would take a row tomorrow whose `CustomerID` is `ZZZZZ`. So the relationships in ยง3 are real, and what holds them up is the process that produced these files. The database is enforcing none of them. Whether they hold is a question you answer by running a query. """) return @app.cell def _(con): con.sql(""" SELECT (SELECT count(*) FROM orders WHERE "CustomerID" NOT IN (SELECT "CustomerID" FROM customers)) AS orders_without_a_customer, (SELECT count(*) FROM "Order Details" WHERE "OrderID" NOT IN (SELECT "OrderID" FROM orders)) AS lines_without_an_order, (SELECT count(*) FROM products WHERE "CategoryID" NOT IN (SELECT "CategoryID" FROM categories)) AS products_without_a_category """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" All three counts are zero. Every order names a customer that exists, every order line names an order that exists, and every product names a category that exists. The design is honored here, and that query is how you know it. **DuckDB checks a declared foreign key and gives you no way to switch the check off.** SQLite does have such a switch, `PRAGMA foreign_keys`, and it is **off by default**, so a SQLite database can accept an orphan and report nothing. In DuckDB the same line is not a setting at all: ```sql PRAGMA foreign_keys = OFF; ``` ``` Catalog Error: unrecognized configuration parameter "foreign_keys" ``` ๐Ÿ‘‰ **Try it:** add a fourth line to the three-zero query, checking `employeeterritories."EmployeeID"` against `employees`. Predict the number, then run it. Then change one table name to something that does not exist and read what happens. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### What happens on a delete is a design decision A design that declares foreign keys also has to answer what a delete does to the rows pointing at the deleted one. There are three answers. | | add an order for a customer who does not exist | delete a customer who has orders | |---|---|---| | **Not enforced** | allowed | allowed | | **Enforced, no cascade** | refused | refused | | **Enforced, cascade** | refused | allowed, and the orders go too | The third row is where somebody removes one customer and 158 orders disappear behind it. The design settled whether that is the intended behavior long before anybody pressed delete. Which of the three you can actually declare depends on the engine, and DuckDB is the strict one: ```sql CREATE TABLE ord ( id INTEGER PRIMARY KEY, customer_id VARCHAR REFERENCES customer(id) ON DELETE CASCADE ); ``` ``` Parser Error: FOREIGN KEY constraints cannot use CASCADE, SET NULL or SET DEFAULT ``` The middle row is the only one DuckDB will write down. ยง24 is where you declare these for real, and where the engine you are running on starts to matter. ๐Ÿ‘‰ **Try it:** take one deletion from your own work, such as removing a customer, an employee or a product. List everything in a system you know that points at the thing being deleted, and state which of the three rows above you would want for each. ### โœ” Check your understanding 1. `orders.CustomerID` is the foreign key and it sits on `orders`. Why can it not sit on `customers` instead? 2. Four territories have no employee. Is that a referential integrity violation? 3. The parquet files declare no constraints. What does that change about the three zeros above, and what does it not change? 4. A design asks for cascade on delete. What do you check before approving it? """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## 5. Reading an ERD An **entity relationship diagram** puts the design of a database on one page: the entities, their attributes, and the lines between them. Four of them appear earlier in this chapter. This section names every mark on the line. ๐Ÿ“– **Used in:** Designing your database """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### What is on the diagram - An **entity** is a thing you keep a table of. It is drawn as a box and it becomes a table, such as `CUSTOMERS` or `ORDERS`. - An **attribute** is one fact about that thing. It is drawn as a line inside the box and it becomes a column, such as `CompanyName` or `OrderDate`. - A **relationship** is a line between two boxes with a word on it that reads as a verb, such as `CUSTOMERS places ORDERS`. An ERD is drawn before any table exists. That is why it says entity where a running database says table. By the time there is a database, the two words mean the same thing. ๐Ÿ‘‰ **Try it:** take three sentences describing any business you know and mark every noun. Sort them into things you would keep a table of and facts about those things. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### The marks at each end of a line The end of a line where it touches a box says how many rows of that box take part. Two symbols do that job. The one nearer the box is the maximum, its **cardinality**, and the one further from the box is the minimum, its **optionality**. - **Two bars.** Exactly one. Written `||`. - **A circle and a bar.** Zero or one. Written `o|`. - **A bar and a crow's foot.** One or more. Written `|{`. - **A circle and a crow's foot.** Zero or more. Written `o{`. The written forms are Mermaid, which is the text format these diagrams were generated from and the one an agent will hand you when you ask it for an ERD. A full line reads `CUSTOMERS ||--o{ ORDERS : places`, and the left end is spelled backwards, so a crow's foot on the left is `}o--`. ๐Ÿ‘‰ **Try it:** using only the four forms above, work out what `TERRITORIES ||--|{ EMPLOYEE_TERRITORIES` would be claiming. Then check whether the four uncovered territories in ยง4 agree with it. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### Reading a line both ways Read it once in each direction, and each time read the marks at the far end. """) return @app.cell def _(erd): erd("erd-one-to-many") return @app.cell(hide_code=True) def _(mo): mo.md(r""" Left to right: one customer places zero or more orders. The end touching `ORDERS` carries a crow's foot for the maximum and a circle for the minimum. Right to left: one order belongs to exactly one customer. The end touching `CUSTOMERS` carries two bars. Both sentences have to be true of the business. A design goes wrong when somebody checked one direction and assumed the other. ๐Ÿ‘‰ **Try it:** read `EMPLOYEES ||--o{ ORDERS : takes` out loud in both directions, then `CATEGORIES ||--o{ PRODUCTS : groups`. Judge whether each sentence is true of a company that sells food. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### The whole warehouse on a page The diagram has seven lines and eight boxes, and it carries everything this chapter has said about Northwind. """) return @app.cell def _(erd): erd("erd-northwind") return @app.cell(hide_code=True) def _(mo): mo.md(r""" These lines read out like this: - **`CATEGORIES ||--o{ PRODUCTS : groups`.** A category groups zero or more products, and each product belongs to exactly one category. - **`ORDERS ||--|{ ORDER_DETAILS : contains`.** An order contains one or more lines, and each line belongs to exactly one order. The bar on the right end is the claim that an order with no lines is not a thing. - **`EMPLOYEES ||--o{ EMPLOYEE_TERRITORIES }o--|| TERRITORIES`.** Two one-to-many relationships meeting at an intersection table, which is the many-to-many from ยง3 drawn the way ยง3 said to draw it. ๐Ÿ‘‰ **Try it:** read the other three lines out loud in both directions. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### The minimum mark is a claim you can test The line into `EMPLOYEE_TERRITORIES` from `TERRITORIES` carries a circle, which says a territory can be covered by nobody. ยง4 found four such territories, so the circle is right and a bar there would have been wrong. The test runs one way. Four rows disprove a bar. No number of rows can prove a circle, because "zero is allowed" is a statement about what the business permits, and today's data is free to be narrower than that. Every customer in this warehouse has placed at least one order, and the line still reads zero or more, because a customer who has not ordered yet is still a customer. ๐Ÿ‘‰ **Try it:** the `ORDERS` to `ORDER_DETAILS` line claims every order has at least one line. Write the query that would disprove it, run it, and state what the answer entitles you to conclude. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" ### PK and FK inside the box An ERD can list the columns. `PK` marks the primary key, `FK` marks a foreign key, and one column can carry both. """) return @app.cell def _(erd): erd("erd-enrollment") return @app.cell(hide_code=True) def _(mo): mo.md(r""" `ENROLLMENT` shows ยง2 and ยง3 at the same time: a surrogate primary key, two foreign keys, and one column of its own. Drawn the other way, `StudentID` and `CourseID` would each be marked `PK FK`, because a compound primary key made of two foreign keys is the same design written differently. ๐Ÿ‘‰ **Try it:** draw `orders`, `"Order Details"` and `products` on paper. Put the columns in the boxes, mark every `PK` and `FK`, and put the right marks on both ends of both lines. Check the columns with `DESCRIBE` before you decide. ### โœ” Check your understanding 1. A line ends in a circle and a crow's foot. What two things does that tell you? 2. You read a relationship in one direction and it is true of the business. Why is that not enough? 3. An ERD claims every order has at least one line. What one query tells you whether the data agrees, and what does a zero result entitle you to conclude? 4. You are handed an ERD with no `PK` or `FK` markings anywhere. What can you still work out from the lines alone? """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## Chapter challenge Nothing here is due. It exists because each section above teaches one thing, and a real design needs all five of them at once. **Design the database for something you already deal with.** Any of these would do: a club's members and its events, a team's projects and the hours booked against them, a shop's suppliers and what it buys from each. Pick something you can describe from memory. Writing the description is the hard part, and the drawing takes 20 minutes once the description is right. **Done looks like** one ERD and one page of prose. The ERD needs four entities or more, at least one many-to-many with its intersection table drawn, a primary key named on every box, and every foreign key marked. The prose gives, for each relationship line, the two sentences from ยง5. **Four decisions are yours, and no drawing tool will make them.** 1. Which of your nouns are entities and which are attributes? An address is a column until the day somebody needs two of them. 2. Natural key or surrogate key, table by table. For each natural key you keep, state what would have to happen for it to break. 3. Which relationship ends carry a circle? Mark the ones you are guessing at. 4. What happens on a delete? Name one row somebody might delete and state what should happen to everything pointing at it. **Going further.** Build it. Write the `CREATE TABLE` statements with real foreign keys, which is ยง24, load 10 rows into each table, then insert a row that violates one of your own constraints and read the message it gives you. After that, hand the ERD to somebody who is not in this course without explaining it, and ask them to describe the business back to you. Write down every question they had to ask, because each one is a place the diagram is not saying what you think it says. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## ๐Ÿค– Ask your agent Two different uses, and the second one is the one this course grades. ### Ask it about what this chapter left out - Normalization has three numbered forms and this chapter never named them. Ask what first, second and third normal form each forbid, and ask for one example table that breaks each. - Ask what an index is, why a primary key usually gets one without being asked, and what having one costs. - Ask how `ON DELETE CASCADE`, `ON DELETE SET NULL` and `ON DELETE RESTRICT` differ, and which of them the engine you are using supports. - Ask how to get an auto-numbered primary key in DuckDB, given that `AUTOINCREMENT` is a syntax error there. - Ask what a star schema is, and why a database built for reporting often breaks the rules in ยง3 deliberately. Test whatever comes back. Run it. ### Watch what it decides without telling you Give it this, exactly: > *"Design the database tables for a food truck that takes catering bookings."* Then read what came back and answer four questions. 1. **How many tables did it invent?** You named two things. 2. **Which keys did it choose, and are they natural or surrogate?** Read the column definitions and ignore the comments above them. 3. **Did it create an intersection table?** If it did, what many-to-many has it decided your business has? 4. **Did it declare foreign keys at all, and did it say anything about deletes?** You asked for one thing and got a dozen decisions, and every one of them is a claim about a business you never described. ๐Ÿ‘‰ **Try it:** send the same sentence twice, in two separate conversations, and put the two table lists side by side. Then send it a third time with one extra sentence about how bookings actually work, and see which of its inventions survive. """) return @app.cell(hide_code=True) def _(mo): mo.md(r""" --- ## Chapter A checklist - [ ] I can name the parts of a table and state what one row of it means - [ ] I can check whether a column is a primary key using two counts - [ ] I can explain when a compound key is needed and when a surrogate key is easier - [ ] I can tell a one-to-many from a many-to-many in a description of a business - [ ] I can explain why a many-to-many needs a third table and what that table holds - [ ] I can state which of two tables holds the foreign key, and why it has to be that one - [ ] I can tell an orphan from a parent with no children - [ ] I can state what this warehouse enforces and what it merely happens to satisfy - [ ] I can read every mark on an ERD line and state the relationship out loud in both directions - [ ] I can name the one relationship end on the ยง5 diagram that the data proves is optional **Part B is one table.** It covers `SELECT`, `WHERE` and `ORDER BY`, and the first questions you get to ask a database. """) return if __name__ == "__main__": app.run()