--- title: 'Data Modeling and Relational Theory' source: 'https://academia.sh/en/courses/relational-theory' language: en updated: '2026-08-23T07:00:46+00:00' license: 'CC BY-SA 4.0' lessons: 14 outcomes: - 'Defines the core concepts of the relational model' - 'Turns a domain into a normalized schema' - 'Expresses integrity constraints in the schema' --- # Data Modeling and Relational Theory An introductory course that builds the move from data kept in a file to a relational schema, key and integrity constraints, the logic of null values, normalization, and the choice between data processing approaches. ## The Relational Model The rationale for the move from a file to a database, the relation–row–column–domain concepts, key types, integrity constraints, the three-valued logic of null values, and data type selection. ### What Is a Database Source: https://academia.sh/en/courses/relational-theory/what-is-a-database The search, concurrency, and integrity problems created by keeping data in a file, and which of these a database management system solves and with what guarantees. The Data Structures course established how data is organized in memory and how that organization determines processing cost: arrays, hash tables, binary search trees. Introduction to Linux and Shell Programming taught filtering, cutting, and counting that same data while it sat on disk in a text file. The Node.js Runtime course read a file, parsed it, and used it inside a program. All three courses shared one assumption: data is either in memory or in a single file, and exactly one program reaches it. This curriculum removes that assumption. Its question is: what problems appear once data is kept in a file, and which of these does a database solve? A single domain runs through the whole course — a library's loan records: books, members, branches, and loan transactions. However well a library catalog's interface is designed, the interface answers wrongly if the data behind it is modeled wrongly. #### The Record in the File Suppose the library keeps its loan transactions in a comma-separated text file. Shell tools are enough to work with that file: ```sh cat > loan.csv <<'CSV' loan_no,member_name,member_email,book_title,book_author,branch,pickup_date,return_date 1001,Alice Kane,alice.kane@example.test,Lost Time,Elena Marsh,Central,2025-03-02,2025-03-16 1002,Marcus Reyes,marcus.reyes@example.test,Lost Time,Elena Marsh,Central,2025-03-04, 1003,Alice Kane,alice.kane@example.test,Sea Lighthouses,Kevin Ashford,Bahcelievler,2025-03-05,2025-03-19 1004,Alice Kane,alice.kane@example.test,Lost Time,Elena Marsh,central,2025-03-20, 1005,Sylvia Renner,sylvia.renner@example.test,Sea Lighthouses,Kevin Ashford,Central,2025-03-21, 1006,Marcus Reyes,marcus.reyes@example.test,Silent Garden,Elena Marsh,Bahcelievler,2025-03-22,2025-04-01 CSV awk -F, 'NR > 1 && $8 == "" { print $2, $4 }' loan.csv ``` ``` Marcus Reyes Lost Time Alice Kane Lost Time Sylvia Renner Sea Lighthouses ``` The list of unreturned books came out. While the file is small, this is enough. Problems begin not when the file grows, but when **the question changes** and **a second program starts reaching the file**. #### Writing the Question Consider the question "how many times was each book loaned, and by how many distinct members?" With shell tools this means several passes, a sort, and a counter held by hand. Once the same data sits in a database, the question is written without saying how the answer is computed: ```sh cat > loan.csv <<'CSV' loan_no,member_name,member_email,book_title,book_author,branch,pickup_date,return_date 1001,Alice Kane,alice.kane@example.test,Lost Time,Elena Marsh,Central,2025-03-02,2025-03-16 1002,Marcus Reyes,marcus.reyes@example.test,Lost Time,Elena Marsh,Central,2025-03-04, 1003,Alice Kane,alice.kane@example.test,Sea Lighthouses,Kevin Ashford,Bahcelievler,2025-03-05,2025-03-19 1004,Alice Kane,alice.kane@example.test,Lost Time,Elena Marsh,central,2025-03-20, 1005,Sylvia Renner,sylvia.renner@example.test,Sea Lighthouses,Kevin Ashford,Central,2025-03-21, 1006,Marcus Reyes,marcus.reyes@example.test,Silent Garden,Elena Marsh,Bahcelievler,2025-03-22,2025-04-01 CSV sqlite3 :memory: <<'SQL' .mode csv .import loan.csv loan_raw .headers on .mode box SELECT book_title, COUNT(*) AS loan_count, COUNT(DISTINCT member_email) AS member_count FROM loan_raw GROUP BY book_title ORDER BY loan_count DESC; SQL ``` ``` ┌─────────────────┬────────────┬──────────────┐ │ book_title │ loan_count │ member_count │ ├─────────────────┼────────────┼──────────────┤ │ Lost Time │ 3 │ 2 │ │ Sea Lighthouses │ 2 │ 2 │ │ Silent Garden │ 1 │ 1 │ └─────────────────┴────────────┴──────────────┘ ``` The difference is not one of syntax alone. The shell solution writes **how** the answer is computed; the second writes **what** is wanted. This is called **declarative querying**: the desired result is defined, and the system decides in what order and by what method the result is reached. The same question is written the same way whether the table holds a thousand rows or ten million; what changes is the execution plan the system chooses. #### Two Writers at Once The real break appears when two programs write to the file at the same time. Lending a copy takes three steps: read the state, confirm the copy is free, add the record. What happens when two counters hand out the same copy at once? ```sh cat > checkout.mjs <<'JS' import { readFile, writeFile } from "node:fs/promises"; import { setTimeout as delay } from "node:timers/promises"; const file = "state.json"; const member = process.argv[2]; const text = await readFile(file, "utf8"); const state = JSON.parse(text); if (state.on_loan.includes(7)) { console.log(`${member}: copy 7 already on loan`); } else { await delay(50); // stand-in for disk and network latency state.on_loan.push(7); state.records.push({ copy_no: 7, member }); await writeFile(file, JSON.stringify(state)); console.log(`${member}: copy 7 given out`); } JS echo '{"on_loan": [], "records": []}' > state.json node checkout.mjs alice & node checkout.mjs marcus & wait cat state.json ``` ``` marcus: copy 7 given out alice: copy 7 given out {"on_loan":[7],"records":[{"copy_no":7,"member":"marcus"}]} ``` The order of the two lines and which record remains in the file change from run to run; two things stay fixed. First: both processes report that the operation succeeded, meaning one copy was given to two members. Second: exactly one record remains in the file — the one that wrote last has also erased the record the earlier write had added. This is called a **lost update**. The wait between the read and the write was inserted to make the delay deterministic. In a real system that delay comes from disk access, a network round trip, or the operating system switching processes; removing the wait does not remove the problem, it only makes it appear more rarely. #### The Constraint That Was Never Written There are other things the file does not say. Record `1004` writes the branch name as `central`, while the others write `Central`; shell tools count these as two separate branches. The same member name repeats across three rows — if the member's email address changes, all three rows need correcting, and forgetting one leaves the file contradicting itself. No row carries the rule "a copy can be on loan to only one member at a time"; that rule lives only in the minds of the programs that write to the file. In a database management system, the rule is stored together with the data, and the engine rejects any violation: ```sh sqlite3 :memory: <<'SQL' CREATE TABLE loan ( loan_no INTEGER PRIMARY KEY, copy_no INTEGER NOT NULL, member_no INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT ); CREATE UNIQUE INDEX loan_open ON loan (copy_no) WHERE return_date IS NULL; INSERT INTO loan VALUES (1001, 7, 41, '2025-03-02', NULL); INSERT INTO loan VALUES (1002, 7, 52, '2025-03-04', NULL); SQL ``` ``` Runtime error near line 10: UNIQUE constraint failed: loan.copy_no (19) ``` The shape of the error message varies by engine; what does not vary is that the second insert never takes effect. The rule is now enforced not by application code, but by the system that stores the data itself. This distinction is the axis of the course: the difference between expressing a rule **in the schema** and repeating it in every program that writes. #### What a Database Management System Provides A **database management system** is software that stores data and governs access to it. The guarantees it provides fall under four headings: - **Declarative access.** The query states what is wanted; the system chooses the access path, index use, and join order. - **Integrity.** Constraints written into the schema are checked on every write attempt, regardless of which program is writing. - **Concurrency control.** Transactions running at the same time do not see each other's half-finished work; the lost update is the engine's responsibility to prevent. - **Durability.** Work reported as complete stays in the data even if the system crashes at that instant; work left unfinished is rolled back as if it never happened. The last two guarantees together give the concept of a transaction, defined in the course's final topic. Integrity is the subject of the lessons immediately ahead. #### Where a File Is the Right Choice A database is not the answer to every problem. Configuration files, logs with a single writer, raw data downloaded for a one-time analysis, text that belongs under version control — these are right to leave in a file. The distinguishing measure is not size but three questions: are there multiple simultaneous writers to the data, are there rules that must be enforced on the data, and is a half-written record acceptable? If the answer to all three is "no," a file is enough. #### Summary - File-based storage falls short not when data grows, but when the question diversifies and a second writer appears. - Declarative querying separates the desired result from the method of computing it; the execution decision belongs to the system. - Two processes performing a concurrent read–modify–write on the same file produce a lost update, and both report success. - A constraint written into the schema applies to every program that reaches the data; a check repeated in application code applies only to the program that wrote it. - A database management system's four guarantees are declarative access, integrity, concurrency control, and durability. #### Next Step This lesson showed that rules need to be stored together with the data, but it did not define what the rules are written on. The rows in `loan.csv` and the rows in a database differ by more than comma usage: under one of them stands a mathematical structure, under the other only a formatting convention. The next lesson defines that structure — the concepts of relation, row, column, and domain — and shows why a table is not an ordered list. ### The Relational Model Source: https://academia.sh/en/courses/relational-theory/relational-model The definition of relation, row, column, and domain; where row order and row repetition stand in the model, and where a SQL table departs from a relation. The previous lesson ended by saying there is a difference deeper than formatting between the rows of a text file and the rows of a database. This lesson names that difference: what mathematical structure stands under a table, and which of the details visible when looking at a table does that structure say **do not belong to the data**? The answer collects into a single concept. In the relational model, data is held in something that looks like a table but is not one: a **relation**. Every rule in this lesson is a direct consequence of the relation's definition, which comes from set theory. #### Domain The set of values a column may take is called that column's **domain**. A branch's province draws from a text domain, a book's publication year from an integer domain, and a book's loanability from a two-element truth domain. A domain is narrower than a type. The publication year's type is integer; its domain might be "the integers between 1450 and 2100." A type says how a value is stored, a domain which values are meaningful. In the schema, a domain is written as a **constraint**: ```sh sqlite3 :memory: <<'SQL' CREATE TABLE book ( isbn TEXT NOT NULL, title TEXT NOT NULL, publication_year INTEGER NOT NULL CHECK (publication_year BETWEEN 1450 AND 2100) ); INSERT INTO book VALUES ('978-975-0000-01-1', 'Lost Time', 2019); INSERT INTO book VALUES ('978-975-0000-02-8', 'Sea Lighthouses', 20199); SQL ``` ``` Runtime error near line 7: CHECK constraint failed: publication_year BETWEEN 1450 AND 2100 (19) ``` A slip of the finger entered the year as `20199`, and the engine turns it back. Not defining the domain in the schema means expecting every program that writes to the data to perform this check on its own. #### Relation Given $n$ domains $D_1, D_2, \dots, D_n$, their **Cartesian product** is the set of all $n$-tuples whose components are each drawn from the corresponding domain. A relation is a subset of that product: $$ R \subseteq D_1 \times D_2 \times \dots \times D_n $$ The Cartesian product gives "everything that could be written"; a relation selects, from among those, "what is actually true." If the branch relation holds the triple `('CEN', 'Central', 'Ankara')`, this states that a branch coded CEN, named Central, in Ankara exists in this library. Triples absent from the relation are considered false. A table is a list of facts. A relation has two parts. The **heading** is the set of attribute-name-to-domain pairings and does not change over time. The **body** is the set of rows matching the heading and changes on every insert, delete, and update. The number of attributes in the heading is called the relation's **degree**; the number of rows in the body is called its **cardinality**. #### Row and Column Each element of the body is a **row**, or in the theoretical term, a **tuple**. A row is a mapping from attribute names to values: the `branch_code` attribute maps to `'CEN'`, the `name` attribute maps to `'Central'`. Each item in the heading is a **column** and corresponds to an **attribute**. A column has a name and a domain, not a position number. In the `branch` relation, there is no such concept as "the second column" — there is a `name` column. Two consequences follow from this, and both are commonly forgotten in practice. #### Row Order Does Not Belong to the Data The body is a set, and the elements of a set are unordered. Whatever order a query returns rows in, that order comes not from the data but from the access path the engine chooses. Adding an index to the same table, without changing a single row, is enough to change the order of the result: ```sh sqlite3 :memory: <<'SQL' .headers on .mode box CREATE TABLE branch (branch_code TEXT, name TEXT, city TEXT); INSERT INTO branch VALUES ('CEN', 'Central', 'Ankara'), ('BHC', 'Bahcelievler', 'Ankara'), ('KDK', 'Kadikoy', 'Istanbul'); SELECT branch_code, name FROM branch; CREATE INDEX branch_name ON branch (name, branch_code); SELECT branch_code, name FROM branch; SQL ``` ``` ┌─────────────┬──────────────┐ │ branch_code │ name │ ├─────────────┼──────────────┤ │ CEN │ Central │ │ BHC │ Bahcelievler │ │ KDK │ Kadikoy │ └─────────────┴──────────────┘ ┌─────────────┬──────────────┐ │ branch_code │ name │ ├─────────────┼──────────────┤ │ BHC │ Bahcelievler │ │ CEN │ Central │ │ KDK │ Kadikoy │ └─────────────┴──────────────┘ ``` The two queries are identical, the data is identical, the order differs. The second query now reads not the table but the index kept ordered by name. Which access path gets chosen depends on the engine; as a rule, an ordered result must be requested explicitly. A program that leaves the order unrequested has left its correctness to chance. #### Repeated Rows and the Multiset In set theory, an element either belongs to a set or does not; it cannot be present twice. By definition, a relation holds no repeated row. A SQL table departs from a relation on exactly this point: if no key is defined, the same row can be written twice. ```sh sqlite3 :memory: <<'SQL' .headers on .mode box CREATE TABLE branch (branch_code TEXT, name TEXT, city TEXT); INSERT INTO branch VALUES ('CEN', 'Central', 'Ankara'); INSERT INTO branch VALUES ('CEN', 'Central', 'Ankara'); SELECT COUNT(*) AS row_count, COUNT(DISTINCT branch_code) AS distinct_code FROM branch; SQL ``` ``` ┌───────────┬───────────────┐ │ row_count │ distinct_code │ ├───────────┼───────────────┤ │ 2 │ 1 │ └───────────┴───────────────┘ ``` A SQL table is not a set but a **multiset**: the same value can be present more than once. This is not a deficiency of the model but a deliberate departure from the theory — eliminating repeats requires sorting or hashing, and that costs something. The consequence is that a table behaving like a relation does not happen on its own; it must be requested in the schema, and defining a key is that request. Keys are the subject of the next lesson. #### Relation Variable and Schema The `branch` table above holds a value that changes over time: three rows today, four tomorrow. The theory names this distinction. A **relation variable** is a name, and that name's value at any instant is a relation. `CREATE TABLE` creates a relation variable; `INSERT` changes its value. From here, two separate planes of database work follow. The **schema** states the headings of relation variables and the constraints placed on them; it rarely changes, and changing it requires a migration. The **instance** is the totality of values at a given instant; it changes constantly. A design error lives in the schema, a data error lives in the instance; fixing the former is more expensive than fixing the latter. #### The Indivisibility of a Value The model's final rule is that where a row and a column meet, there must be **a single value**. Writing a branch's phone numbers as one piece of text, `'0312-000-0001, 0312-000-0002'`, means squeezing a list into one place. The system treats that text as indivisible: no query can be written to find the second number inside it, removing one number turns into text editing, and no constraint can be placed on a number. The remedy is moving the multivalued fact into a separate relation — each phone number in its own row, together with the branch code. This rule will be formalized under the name first normal form in the Normalization topic; for now it stands as a requirement of the model. #### Summary - A relation is a subset of the Cartesian product of attribute domains; its heading is fixed, its body is variable. - A domain is a column's set of meaningful values and is narrower than a type; it is written into the schema as a constraint. - Because the body is a set, row order does not belong to the data; an ordered result must be requested explicitly. - A SQL table accepts repeated rows because it is a multiset; behaving like a relation depends on a key being defined in the schema. - The schema states relation variables and their constraints; the instance states the values at a given moment. #### Next Step This lesson arrived at the same place twice: rows need to be distinguishable from one another, and that distinguishing does not happen unless the schema says so. The next lesson defines the column set that identifies a row on its own, discusses which one is chosen when several candidates exist, and builds the key that binds one relation's rows to another relation's rows. ### Keys Source: https://academia.sh/en/courses/relational-theory/keys Definitions of superkey, candidate key, primary key, alternate key, and foreign key; the choice between a natural and a surrogate key, and the criteria for choosing a key. The previous lesson arrived at the same point twice: rows need to be distinguishable from one another, and that distinguishing does not happen unless the schema says so. Left unstated, a SQL table is a multiset that accepts the same row twice. This lesson's question is: how is the column set that identifies a row on its own defined, which one is chosen when several candidates exist, and how is one relation's row bound to another relation's row? #### Superkey and Candidate Key Call $H$ the set of attributes in a relation's heading. A subset $K \subseteq H$ is a **superkey** if no two distinct rows carry the same values on $K$ in any valid value of the relation. In other words, the values on $K$ determine the row. This definition is too generous: adding a redundant column to a superkey leaves it a superkey still. If `member_no` determines the row in the member relation, so does `(member_no, name)`. Reducing away the redundancy gives a **candidate key**: a superkey none of whose proper subsets is itself a superkey. A candidate key is an irreducible determiner; a relation's candidate keys may differ in size. A relation can have more than one candidate key. In the member relation, both the member number and the email address determine a row on their own: ```sh sqlite3 :memory: <<'SQL' CREATE TABLE member ( member_no INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE, name TEXT NOT NULL, registered_at TEXT NOT NULL ); INSERT INTO member VALUES (41, 'alice.kane@example.test', 'Alice Kane', '2024-09-12'); INSERT INTO member VALUES (52, 'alice.kane@example.test', 'Alice K. Kane', '2025-01-08'); SQL ``` ``` Runtime error near line 8: UNIQUE constraint failed: member.email (19) ``` The second insert is rejected even though it carries a different member number, because the email address is also a candidate key and that address is already in use. Being a candidate key comes not from the absence of repetition in today's data, but from the rule that **repetition can never occur**. This distinction matters: a column that does not currently repeat in the data can be mistaken for a key. The right question is not "does it repeat right now" but "can it possibly repeat." In a library setting the member name is a good example of this — that it does not repeat today does not mean two members named Alice Kane cannot exist. #### Primary and Alternate Key One of the candidate keys is chosen as the relation's official identity; this is called the **primary key**. Candidate keys not chosen are called **alternate keys**, and they too are written into the schema as uniqueness constraints — the `UNIQUE` above does exactly that. Choosing a primary key is a matter of preference, and it also affects the engine's behavior: references made to rows from other relations are built on the primary key, and an index generally falls on it. ```sh sqlite3 :memory: <<'SQL' CREATE TABLE member ( member_no INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE, name TEXT NOT NULL ); INSERT INTO member VALUES (41, 'alice.kane@example.test', 'Alice Kane'); INSERT INTO member VALUES (41, 'marcus.reyes@example.test', 'Marcus Reyes'); SQL ``` ``` Runtime error near line 7: UNIQUE constraint failed: member.member_no (19) ``` #### Composite Key A key need not consist of a single column. A key formed by several columns together is called a **composite key**. In the library, a book's physical copy — from here on, a **copy** — is determined by three things: which book, which branch, which copy number at that branch. ```sh sqlite3 :memory: <<'SQL' CREATE TABLE copy ( isbn TEXT NOT NULL, branch_code TEXT NOT NULL, copy_no INTEGER NOT NULL, PRIMARY KEY (isbn, branch_code, copy_no) ); INSERT INTO copy VALUES ('978-975-0000-01-1', 'CEN', 1), ('978-975-0000-01-1', 'CEN', 2), ('978-975-0000-01-1', 'BHC', 1); INSERT INTO copy VALUES ('978-975-0000-01-1', 'BHC', 1); SQL ``` ``` Runtime error near line 10: UNIQUE constraint failed: copy.isbn, copy.branch_code, copy.copy_no (19) ``` The first and second copies of the same book at the Central branch and the first copy at the Bahcelievler branch are separate rows; the fourth insert is rejected because it collides with the third. A composite key's column order is immaterial to keyness — a set is a set — but it matters to index behavior; that distinction belongs to courses covering the engine's internals. #### Natural and Surrogate Key A key can come from the domain's own data or be generated for the purpose of identification. One that comes from the domain's own data is called a **natural key**: a book's ISBN, a branch code, a member's email address. One generated purely to distinguish rows is called a **surrogate key**: a loan transaction's sequence number, for instance. ```sh sqlite3 :memory: <<'SQL' .headers on .mode box CREATE TABLE loan ( loan_no INTEGER PRIMARY KEY, member_no INTEGER NOT NULL, pickup_date TEXT NOT NULL ); INSERT INTO loan (member_no, pickup_date) VALUES (41, '2025-03-02'), (52, '2025-03-04'); SELECT * FROM loan; SQL ``` ``` ┌─────────┬───────────┬─────────────┐ │ loan_no │ member_no │ pickup_date │ ├─────────┼───────────┼─────────────┤ │ 1 │ 41 │ 2025-03-02 │ │ 2 │ 52 │ 2025-03-04 │ └─────────┴───────────┴─────────────┘ ``` The insert statement gave no value for the `loan_no` column; the values were generated by the system. **How the identity column is generated varies by engine**: some engines define a separate sequence-generator object, some declare it in the column definition, some use the table's hidden row number. That generated values will advance without gaps is a guarantee in no engine; the number consumed by a rolled-back transaction does not come back. Reading a surrogate key as "which record number" is therefore a mistake. A natural key makes the data self-explanatory and needs no extra column; against that, it can change if the domain's rules change. A surrogate key is stable and narrow; against that, it carries no meaning and does not by itself guarantee uniqueness — even in a relation carrying a surrogate key, the natural candidate key still needs to be written with `UNIQUE`, or the same book could be entered twice under two different numbers. #### Foreign Key Relations are bound to one another by a **foreign key**: the values of a column set in one relation must be found among the candidate key values of another relation. ```sh sqlite3 :memory: <<'SQL' PRAGMA foreign_keys = ON; CREATE TABLE member ( member_no INTEGER PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE loan ( loan_no INTEGER PRIMARY KEY, member_no INTEGER NOT NULL REFERENCES member (member_no), pickup_date TEXT NOT NULL ); INSERT INTO member VALUES (41, 'Alice Kane'); INSERT INTO loan VALUES (1001, 41, '2025-03-02'); INSERT INTO loan VALUES (1002, 77, '2025-03-04'); SQL ``` ``` Runtime error near line 13: FOREIGN KEY constraint failed (19) ``` Member `41` exists in the table, so the first loan record passes; member `77` does not exist, so the second is rejected. The foreign key is the sole mechanism the relational model uses to bind relations together — rows are bound not by pointer but by **value**. This is a fundamental break from the linked lists of the Data Structures course: because the binding rests on a key value rather than a memory address, it keeps its validity when the database is closed and reopened, moved to another machine, or restored from a backup. #### Criteria for Choosing a Key Four criteria are useful when choosing a primary key among candidate keys. - **Stability.** The key's value should not change, since every row that references it carries that value. An email address is a poor primary key for this reason and a good alternate key. - **Narrowness.** A key repeats in every relation that references it and takes up space in indexes. A single integer, in place of a three-column text composite, makes a clear difference as the number of references grows. - **Indivisibility.** A key carrying information that can be parsed out — a string combining a branch code and a year, say — breaks when that rule changes. - **Privacy.** A key appears in addresses and links exposed to the outside. Making a personal value such as a national identity number a primary key also means exposing it. #### Summary - A superkey is any column set that determines the row; a candidate key is a superkey none of whose proper subsets is itself a superkey. - One of the candidate keys is chosen as the primary key; the rest are written as alternate keys with a uniqueness constraint. - Keyness comes not from today's data but from the rule placed on the data; "it does not currently repeat" is not proof of keyness. - A natural key comes from the data and can change; a surrogate key is generated and does not change, though it does not remove the need for the natural candidate key's uniqueness. - A foreign key binds rows by value rather than by address, so it keeps its validity across migration and restoration. #### Next Step This lesson defined the foreign key and rejected one violation of it, but it did not name the rule behind that rejection: why can a loan record not be written for a member who does not exist, and what should happen if that member's record is later deleted? The next lesson defines the two integrity rules keys rest on — entity integrity and referential integrity — and shows the behaviors the system can choose on deletion and update. ### Integrity Constraints Source: https://academia.sh/en/courses/relational-theory/integrity-constraints The definitions of domain, entity, and referential integrity; a foreign key's delete and update actions; the timing of constraint checking and the case for writing a constraint into the schema. The previous lesson rejected a foreign key violation but did not name the rule behind it. This lesson's question is: what rules must a database satisfy at every instant, how are these rules written into the schema, and which behaviors can the system choose when a delete arrives that would break one? Constraints fall into three layers. **Domain integrity** states which values may be written to a column — type, `NOT NULL`, and `CHECK` are its tools and were seen in the previous lesson. **Entity integrity** requires that every row's identity be determined. **Referential integrity** requires that a reference from one relation to another never point nowhere. A fourth layer is domain-specific business rules; these too are written into the schema wherever possible. #### Entity Integrity The rule is one sentence: **no column making up the primary key may take a null value.** Its justification follows directly from the key's definition. A key's job is to distinguish a row; a null value means "unknown." A row whose identity is unknown is a row that cannot be referenced. That this rule stands in the standard does not mean every engine enforces it with the same strictness: ```sh sqlite3 :memory: <<'SQL' .headers on .mode box CREATE TABLE branch (branch_code TEXT PRIMARY KEY, name TEXT NOT NULL); INSERT INTO branch VALUES (NULL, 'Unnamed Branch'); SELECT COUNT(*) AS row_count FROM branch; SQL ``` ``` ┌───────────┐ │ row_count │ ├───────────┤ │ 1 │ └───────────┘ ``` A row with a null primary key entered the table. **This behavior varies by engine**; some reject the same statement outright. A portable schema with a clear intent writes the rule itself: ```sh sqlite3 :memory: <<'SQL' CREATE TABLE branch (branch_code TEXT PRIMARY KEY NOT NULL, name TEXT NOT NULL); INSERT INTO branch VALUES (NULL, 'Unnamed Branch'); SQL ``` ``` Runtime error near line 2: NOT NULL constraint failed: branch.branch_code (19) ``` The general principle that follows is one of the course's recurring themes: writing a rule explicitly into the schema, rather than assuming the engine already enforces it, improves both portability and readability. #### Referential Integrity The rule is this: a foreign key's value is either null, or a row **exists** in the referenced relation carrying that value. There is no third possibility; a row whose target cannot be found is called an **orphan record**. Whether this rule is enforced also varies by engine. In the command-line tool used here, the check is off by default and needs to be turned on per session: ```sh sqlite3 :memory: <<'SQL' .headers on .mode box PRAGMA foreign_keys; CREATE TABLE member (member_no INTEGER PRIMARY KEY, name TEXT NOT NULL); CREATE TABLE loan ( loan_no INTEGER PRIMARY KEY, member_no INTEGER NOT NULL REFERENCES member (member_no) ); INSERT INTO loan VALUES (1001, 77); SELECT COUNT(*) AS orphan_record FROM loan; SQL ``` ``` ┌──────────────┐ │ foreign_keys │ ├──────────────┤ │ 0 │ └──────────────┘ ┌───────────────┐ │ orphan_record │ ├───────────────┤ │ 1 │ └───────────────┘ ``` `REFERENCES` is written into the schema, the member table is empty, and yet a loan record went in anyway. Because the check was off, the declaration carried only documentary value. The blocks for the rest of this lesson begin with the line that turns the check on. #### Delete and Update Actions Referential integrity can break at two moments: when the referenced row is deleted, and when the referenced key value is updated. The schema chooses a behavior for each of these two moments. - **`NO ACTION` / `RESTRICT`** — the operation is rejected. This is the standard's default behavior; the difference between the two lies in the timing of the check. - **`CASCADE`** — the delete or update propagates to the referencing rows. - **`SET NULL`** — the referencing row's foreign key is cleared; the column must accept a null value. - **`SET DEFAULT`** — the referencing row's foreign key is set to the column's default value; that value must exist at the target. The choice is not arbitrary; it follows the domain's meaning. When a member is deleted, leaving their penalty records in place is meaningless — these records are dependent on the member and cannot exist on their own, so `CASCADE`. When a branch closes, the member's record should not be deleted; the member is left without a branch, so `SET NULL`. A member's loan history, however, should not be deleted — a past record is a fact — so the delete is rejected there. ```sh sqlite3 :memory: <<'SQL' .headers on .mode box .nullvalue (null) PRAGMA foreign_keys = ON; CREATE TABLE branch (branch_code TEXT PRIMARY KEY NOT NULL, name TEXT NOT NULL); CREATE TABLE member ( member_no INTEGER PRIMARY KEY, name TEXT NOT NULL, branch_code TEXT REFERENCES branch (branch_code) ON DELETE SET NULL ); CREATE TABLE penalty ( penalty_no INTEGER PRIMARY KEY, member_no INTEGER NOT NULL REFERENCES member (member_no) ON DELETE CASCADE, amount INTEGER NOT NULL ); INSERT INTO branch VALUES ('CEN', 'Central'), ('BHC', 'Bahcelievler'); INSERT INTO member VALUES (41, 'Alice Kane', 'CEN'), (52, 'Marcus Reyes', 'BHC'); INSERT INTO penalty VALUES (9001, 52, 30), (9002, 52, 15); DELETE FROM member WHERE member_no = 52; DELETE FROM branch WHERE branch_code = 'CEN'; SELECT (SELECT COUNT(*) FROM member) AS member_rows, (SELECT COUNT(*) FROM penalty) AS penalty_rows, (SELECT branch_code FROM member WHERE member_no = 41) AS member41_branch; SQL ``` ``` ┌─────────────┬──────────────┬─────────────────┐ │ member_rows │ penalty_rows │ member41_branch │ ├─────────────┼──────────────┼─────────────────┤ │ 1 │ 0 │ (null) │ └─────────────┴──────────────┴─────────────────┘ ``` A single member was deleted, and the two penalty records bound to them went too; a branch was deleted, and the member bound to it was left without a branch. The `.nullvalue` setting exists only to make the null value visible in the output — without it, a null value and an empty string cannot be told apart on screen. The default behavior is rejection: ```sh sqlite3 :memory: <<'SQL' PRAGMA foreign_keys = ON; CREATE TABLE member (member_no INTEGER PRIMARY KEY, name TEXT NOT NULL); CREATE TABLE loan ( loan_no INTEGER PRIMARY KEY, member_no INTEGER NOT NULL REFERENCES member (member_no) ); INSERT INTO member VALUES (41, 'Alice Kane'); INSERT INTO loan VALUES (1001, 41); DELETE FROM member WHERE member_no = 41; SQL ``` ``` Runtime error near line 9: FOREIGN KEY constraint failed (19) ``` Care is needed with `CASCADE`: propagation chains. A statement that deletes a member also deletes their penalty records, any payment records bound to those, and the continuation of that chain. The number of rows a delete removes can be far larger than the person writing the statement expects. #### When a Constraint Is Checked Constraints are checked by default at the end of every statement. This can prevent some valid states from being temporarily constructed. If two relations reference each other — every branch has a manager and every manager works at a branch — then whichever gets inserted first, the other side does not yet exist at the moment of insertion. The remedy is to postpone the check to the end of the transaction; this is called a **deferred constraint**. Under deferred checking, intermediate states may break the rule, as long as the rule holds once the transaction completes. **Whether this capability exists and how it is declared varies by engine**; where it does not exist, one end of the mutual reference must accept a null value and be filled in over two steps. #### Where to Write a Constraint Not every rule fits the schema. The rule "a member may hold at most five copies on loan at once" cannot be checked by looking at a single row; it requires a count. Rules like this are written either with a trigger or in the application layer. The criterion is this: if a rule holds for **every** path that reaches the data and can be expressed over a single row or row set, it is written into the schema. A constraint in the schema is enforced independent of which program is writing, in which language it is written, and whether the data is touched by hand or through a bulk migration. A check in the application layer covers only writes that pass through that application; a second service, a maintenance script, or a manually run statement bypasses it. A constraint's second function is documentation. Someone reading the schema learns from a line that says `ON DELETE CASCADE` that a penalty record depends on a member. The same fact, scattered through application code, goes unread. #### Summary - Entity integrity means the primary key cannot take a null value; a row of unknown identity cannot be referenced. - Referential integrity requires a foreign key to be either null or matched at the target; a violation produces an orphan record. - Writing a constraint explicitly into the schema, rather than assuming the engine enforces it by default, improves portability and readability. - Delete and update actions — reject, propagate, clear, reset to default — are chosen according to the domain's meaning; propagation chains and can delete more rows than expected. - Rules that bind every write path and can be expressed at the row level belong in the schema; those requiring a count or history belong in the application layer. #### Next Step This lesson said "or it is null" twice: a foreign key may be null, `SET NULL` clears a column. What a null value means was left undefined. The next lesson shows that a null value is not a value, what comparisons do with it, and what traps adding a third result to the true–false pair opens up in query writing. ### Null Values Source: https://academia.sh/en/courses/relational-theory/null-values Why null is not a value, the truth table of three-valued logic, how filters and NOT IN behave with nulls, how a uniqueness constraint treats a null, and ways to avoid null values. The previous lesson said "or it is null" twice: a foreign key may be null, a `SET NULL` action clears a column. What a null value means was left undefined. This lesson's question is: what is a null value, what do comparisons made against it return, and what traps does adding a third result to the true–false pair open in query writing? #### A Null Value Is Not a Value **Null** is the marker showing that no value is present in a column. It is not zero, not an empty string, and not a special value named "none." Its meaning reads as "that information does not exist for this row," and it represents two separate situations under a single marker: unknown information (a member's phone number exists but was not recorded) and inapplicable information (a loan transaction's return date has not yet come into existence). That these two situations share the same marker is one of the oldest criticisms leveled at the relational model. Because it is not a value, a null value cannot be compared against a value. The result of the comparison is neither true nor false: ```sh sqlite3 :memory: <<'SQL' .headers on .mode box .nullvalue (null) SELECT NULL = NULL AS is_equal, NULL <> NULL AS is_different, NULL IS NULL AS is_null, (NULL = NULL) IS NULL AS result_is_null; SQL ``` ``` ┌──────────┬──────────────┬─────────┬────────────────┐ │ is_equal │ is_different │ is_null │ result_is_null │ ├──────────┼──────────────┼─────────┼────────────────┤ │ (null) │ (null) │ 1 │ 1 │ └──────────┴──────────────┴─────────┴────────────────┘ ``` `NULL = NULL` is not true; `NULL <> NULL` is not true either. Both return a third result. The only correct way to test for a null value is the `IS NULL` and `IS NOT NULL` operators — these are not comparisons but state checks, and they always return true or false. #### The Third Truth Value The name of the third result a comparison can produce is **unknown**, and the name of the system built around it is **three-valued logic**. The `AND` and `OR` operators are extended to cover this third value as well. There is no need to memorize the table; having it computed is enough: ```sh sqlite3 :memory: <<'SQL' .headers on .mode box .nullvalue (null) WITH d(label, v) AS (VALUES ('true', 1), ('false', 0), ('unknown', NULL)) SELECT a.label AS left_side, b.label AS right_side, a.v AND b.v AS both_true, a.v OR b.v AS either_true FROM d a, d b; SQL ``` ``` ┌───────────┬────────────┬───────────┬─────────────┐ │ left_side │ right_side │ both_true │ either_true │ ├───────────┼────────────┼───────────┼─────────────┤ │ true │ true │ 1 │ 1 │ │ true │ false │ 0 │ 1 │ │ true │ unknown │ (null) │ 1 │ │ false │ true │ 0 │ 1 │ │ false │ false │ 0 │ 0 │ │ false │ unknown │ 0 │ (null) │ │ unknown │ true │ (null) │ 1 │ │ unknown │ false │ 0 │ (null) │ │ unknown │ unknown │ (null) │ (null) │ └───────────┴────────────┴───────────┴─────────────┘ ``` The rule to read from the table is this: if one side settles the result, the unknown does not propagate. `false AND unknown` is false, because if one side is false the result is false no matter the other side. `true OR unknown` is true, for the same reason. In every other case, the unknown carries through to the result. This is the logical-level counterpart of the short-circuit evaluation seen in the Programming Fundamentals course. #### The Set a Filter Splits `WHERE` passes only the rows that yield **true**; false and unknown are filtered out together. The visible consequence is that two conditions assumed to be each other's negation do not, together, cover every row: ```sh sqlite3 :memory: <<'SQL' .headers on .mode box CREATE TABLE loan (loan_no INTEGER PRIMARY KEY, member_no INTEGER, return_date TEXT); INSERT INTO loan VALUES (1001, 41, '2025-03-16'), (1002, 52, NULL), (1003, 41, '2025-03-19'), (1004, 41, NULL); SELECT (SELECT COUNT(*) FROM loan) AS total, (SELECT COUNT(*) FROM loan WHERE return_date = '2025-03-16') AS equal_count, (SELECT COUNT(*) FROM loan WHERE return_date <> '2025-03-16') AS different_count, (SELECT COUNT(*) FROM loan WHERE return_date IS NULL) AS null_count; SQL ``` ``` ┌───────┬─────────────┬─────────────────┬────────────┐ │ total │ equal_count │ different_count │ null_count │ ├───────┼─────────────┼─────────────────┼────────────┤ │ 4 │ 1 │ 1 │ 2 │ └───────┴─────────────┴─────────────────┴────────────┘ ``` There are four rows; "equals" returns one row and "does not equal" returns one row. Their sum does not reach four, because two rows pass neither filter. The two-valued-logic habit of "a condition or its negation covers the whole set" does not hold in a column that holds null values. If full coverage is wanted, the condition must be written for it explicitly. #### The `NOT IN` Trap The most expensive consequence of the same rule shows up in subqueries. The phrasing `x NOT IN (a, b, c)` is equivalent to `x <> a AND x <> b AND x <> c`. If the list contains even a single null value, that comparison returns unknown, the `AND` chain falls into unknown, and **no row** passes: ```sh sqlite3 :memory: <<'SQL' .headers on .mode box CREATE TABLE member (member_no INTEGER PRIMARY KEY, name TEXT NOT NULL); CREATE TABLE penalty (penalty_no INTEGER PRIMARY KEY, member_no INTEGER); INSERT INTO member VALUES (41, 'Alice Kane'), (52, 'Marcus Reyes'), (63, 'Sylvia Renner'); INSERT INTO penalty VALUES (9001, 52), (9002, NULL); SELECT 'NOT IN, raw subquery' AS phrasing, COUNT(*) AS rows_returned FROM member WHERE member_no NOT IN (SELECT member_no FROM penalty) UNION ALL SELECT 'NOT IN, nulls filtered', COUNT(*) FROM member WHERE member_no NOT IN (SELECT member_no FROM penalty WHERE member_no IS NOT NULL) UNION ALL SELECT 'NOT EXISTS', COUNT(*) FROM member m WHERE NOT EXISTS (SELECT 1 FROM penalty p WHERE p.member_no = m.member_no); SQL ``` ``` ┌────────────────────────┬───────────────┐ │ phrasing │ rows_returned │ ├────────────────────────┼───────────────┤ │ NOT IN, raw subquery │ 0 │ │ NOT IN, nulls filtered │ 2 │ │ NOT EXISTS │ 2 │ └────────────────────────┴───────────────┘ ``` The correct answer is two: two members have no penalty. The first phrasing returns zero, and it does this without raising an error — the query is valid, the result is silently wrong. This is the most commonly encountered null-value trap, and it has two defenses: either the null values are filtered out inside the subquery, or the `NOT EXISTS` phrasing is chosen. The second is preferred, because its correctness does not depend on the subquery's content. #### Uniqueness and Null Values A uniqueness constraint follows the same logic. Because two null values are not considered equal to one another, a unique column can hold more than one null value: ```sh sqlite3 :memory: <<'SQL' .headers on .mode box CREATE TABLE member ( member_no INTEGER PRIMARY KEY, name TEXT NOT NULL, national_id TEXT UNIQUE ); INSERT INTO member VALUES (41, 'Alice Kane', NULL); INSERT INTO member VALUES (52, 'Marcus Reyes', NULL); INSERT INTO member VALUES (63, 'Sylvia Renner', NULL); SELECT COUNT(*) AS row_count FROM member; SQL ``` ``` ┌───────────┐ │ row_count │ ├───────────┤ │ 3 │ └───────────┘ ``` All three rows passed. This behavior conforms to the standard, but **it can vary by engine**; some systems allow only a single null value in a unique column. The practical consequence of this ambiguity is that if a column is meant to be both unique and required, `UNIQUE` alone is not enough — `NOT NULL` must be added alongside it. The reverse also holds: a uniqueness constraint cannot be used to limit the number of null values. #### Aggregate Functions Aggregate functions do not count null values into their totals. `COUNT(*)` counts rows, `COUNT(column)` counts the rows where that column holds a value; `SUM` and `AVG` skip null values: ```sh sqlite3 :memory: <<'SQL' .headers on .mode box .nullvalue (null) CREATE TABLE penalty (penalty_no INTEGER PRIMARY KEY, amount INTEGER); INSERT INTO penalty VALUES (9001, 30), (9002, NULL), (9003, 10); SELECT COUNT(*) AS row_count, COUNT(amount) AS filled_amount, SUM(amount) AS total, AVG(amount) AS average FROM penalty; SQL ``` ``` ┌───────────┬───────────────┬───────┬─────────┐ │ row_count │ filled_amount │ total │ average │ ├───────────┼───────────────┼───────┼─────────┤ │ 3 │ 2 │ 40 │ 20.0 │ └───────────┴───────────────┴───────┴─────────┘ ``` The average is the average of two values, not of three rows. If a null value should count as zero, that must be written explicitly; skipping is what the function does on its own. Whether null values sort first or last is left unspecified by the standard and **varies by engine**; if a specific position is wanted, it must be declared explicitly in the sort expression. #### Avoiding Null Values A null value is a tool, not a goal. It shows up unnecessarily in three common situations, and all three have a modeling counterpart. - **Columns set aside for a multivalued fact.** The `phone_1`, `phone_2`, `phone_3` columns sit null in most rows for two of the three. The counterpart is moving phone numbers into a separate relation. - **Columns meaningful only for some rows.** An end date specific to a fixed-term membership sits null for members with no fixed term. The counterpart is keeping the subtype in a separate relation. - **An event that has not yet occurred.** A null return date means the loan transaction is still in progress. This is the right use of a null value — there is no other marker to announce that the event has not occurred. The general criterion is this: a null value is in its place when it states a **condition**; it calls for fixing the schema when it is papering over incomplete modeling. #### Summary - A null value is not a value; it is distinct from zero and from an empty string, and it is tested only with `IS NULL`. - Comparisons produce a third result; `WHERE` passes only what is true, so a condition and its negation do not together cover every row. - A single null value in a `NOT IN` subquery silently empties the result; `NOT EXISTS` does not carry this sensitivity. - A uniqueness constraint treats null values as distinct from one another; if a value is required, `NOT NULL` must be written separately. - Aggregate functions skip null values; an average divides by the count of values, not the count of rows. #### Next Step This lesson showed the consequences of a column being allowed to hold a null value; what the column **holds** remains unaddressed. Dates were written as text and amounts as integers throughout the course, and these choices were never justified. The next lesson takes up number, text, date, and binary types; it discusses why a monetary amount is not kept in a floating-point number, what setting a text comparison depends on, and whether large binary data belongs in a database at all. ### Data Types Source: https://academia.sh/en/courses/relational-theory/data-types The choice among integer, decimal, and floating-point numbers; text length and collation; storing dates alongside a time zone; binary data, truth values, and enumeration patterns. The previous five lessons built the schema: relations, keys, constraints, and the logic of null values. What a column **holds** was passed over without justification — dates were written as text, amounts as integers. This lesson's question is: by what criteria is a column's type chosen, and where does the cost of the wrong choice show up? A type choice determines three things at once: which values can be stored, how comparison and sorting are carried out, and how many bytes are spent per row. The first two affect correctness, the third affects cost; the wrong choice is usually noticed through the first two. #### Numbers and Money The How Computers Work course established that a floating-point number works with binary fractions and that most decimal fractions cannot be represented exactly. That fact holds in a database too, and it does not show up on screen: ```sh sqlite3 :memory: <<'SQL' .headers on .mode box SELECT 0.1 + 0.2 AS sum_result, 0.1 + 0.2 = 0.3 AS point_three_equal, 10 + 20 = 30 AS cents_equal; SQL ``` ``` ┌────────────┬───────────────────┬─────────────┐ │ sum_result │ point_three_equal │ cents_equal │ ├────────────┼───────────────────┼─────────────┤ │ 0.3 │ 0 │ 1 │ └────────────┴───────────────────┴─────────────┘ ``` The sum prints as `0.3` on screen but is not `0.3`. Display rounds; comparison does not. If a late fee is held in this type, the query testing whether the amount paid equals the amount owed sometimes returns the wrong answer, and the error is below a cent — no one looking at the log can see it. There are three options. **Integer** types are exact; for money they are used by choosing the smallest unit — an amount is stored as an integer count of cents and divided by a hundred for display. **Decimal** types (`NUMERIC`, `DECIMAL`) offer exact decimal arithmetic to a fixed number of digits and are the standard choice for a monetary column. **Floating-point** types are for values that are already approximate by nature — measurements, ratios, scientific magnitudes. The rule is: a counted quantity takes an integer or decimal type, a measured quantity takes a floating-point type. Money is counted. A second criterion for an integer type is width. A narrow type saves space but brings the overflow limit closer; choosing a two-byte type for a member number means no more than sixty-five thousand members can ever be recorded. How overflow behaves — wrapping or raising an error — varies by engine; choosing the limit generously from the start is cheaper than changing the type later. #### Enforcement of a Type That a type is declared does not necessarily mean it is honored: ```sh sqlite3 :memory: <<'SQL' .headers on .mode box CREATE TABLE book (isbn TEXT PRIMARY KEY, publication_year INTEGER NOT NULL); INSERT INTO book VALUES ('978-975-0000-01-1', 'twenty nineteen'); SELECT publication_year, typeof(publication_year) AS stored_type FROM book; SQL ``` ``` ┌──────────────────┬─────────────┐ │ publication_year │ stored_type │ ├──────────────────┼─────────────┤ │ twenty nineteen │ text │ └──────────────────┴─────────────┘ ``` The column is declared as an integer; the value stored is text. **How binding a type declaration is varies by engine**: some engines attempt a conversion and reject it on failure, some accept the value as given. The portable defense is again the constraint — the domain definition bounds the value independent of the type: ```sh sqlite3 :memory: <<'SQL' CREATE TABLE book ( isbn TEXT PRIMARY KEY NOT NULL, publication_year INTEGER NOT NULL CHECK (publication_year BETWEEN 1450 AND 2100) ); INSERT INTO book VALUES ('978-975-0000-01-1', 'twenty nineteen'); SQL ``` ``` Runtime error near line 5: CHECK constraint failed: publication_year BETWEEN 1450 AND 2100 (19) ``` #### Text and Collation Text types split along two axes. **Length**: a fixed-length type uses the same space in every row and pads short values with spaces; a variable-length type stores only what is written. Fixed length belongs only to genuinely fixed data — a two-letter country code, a three-character branch code. **Limit**: a length limit is a domain constraint; the rule that a branch code is three characters is not expressed by an unbounded text column. The real subtlety is in comparison. Whether two pieces of text are considered equal, and which comes first, depends on **collation**: ```sh sqlite3 :memory: <<'SQL' .headers on .mode box SELECT 'ayse' = 'AYSE' AS binary_comparison, 'ayse' = 'AYSE' COLLATE NOCASE AS collated_comparison, 'ayşe' = 'AYŞE' COLLATE NOCASE AS turkish_lettered; SQL ``` ``` ┌───────────────────┬─────────────────────┬──────────────────┐ │ binary_comparison │ collated_comparison │ turkish_lettered │ ├───────────────────┼─────────────────────┼──────────────────┤ │ 0 │ 1 │ 0 │ └───────────────────┴─────────────────────┴──────────────────┘ ``` The default comparison is byte for byte: `ayse` and `AYSE` differ. Choosing a case-insensitive collation equates the two, but `ayşe` and `AYŞE` still do not equate — this collation covers only the base Latin letters. **Collation names and their coverage vary by engine**; a schema working with Turkish text needs a language-aware collation chosen explicitly. The normalization problem from the Character Encodings lesson resurfaces here: two visually identical strings do not count as equal when their code point sequences differ. #### Date and Time Three separate concepts exist for date and time: date alone, time alone, and the timestamp that joins the two. The timestamp carries one further distinction — is the value tied to a time zone, or is it local wall-clock time? A library's opening hour, "09:00," is a local rule; the instant a loan transaction occurs is a universal point and must be stored together with its time zone. In environments without a dedicated date type, text in ISO 8601 format is used. The valuable property of this format is that lexicographic order equals chronological order: ```sh sqlite3 :memory: <<'SQL' .headers on .mode box .nullvalue (null) CREATE TABLE loan (loan_no INTEGER PRIMARY KEY, pickup_date TEXT NOT NULL, return_date TEXT); INSERT INTO loan VALUES (1001, '2025-03-02', '2025-03-16'), (1002, '2025-03-04', '2025-04-01'), (1003, '2025-12-30', NULL); SELECT loan_no, pickup_date, julianday(return_date) - julianday(pickup_date) AS days, pickup_date > '2025-03-03' AS after_march_third FROM loan ORDER BY pickup_date; SQL ``` ``` ┌─────────┬─────────────┬────────┬───────────────────┐ │ loan_no │ pickup_date │ days │ after_march_third │ ├─────────┼─────────────┼────────┼───────────────────┤ │ 1001 │ 2025-03-02 │ 14.0 │ 0 │ │ 1002 │ 2025-03-04 │ 28.0 │ 1 │ │ 1003 │ 2025-12-30 │ (null) │ 1 │ └─────────┴─────────────┴────────┴───────────────────┘ ``` Sorting and range comparison work correctly on the text. Arithmetic, however, requires date functions, and **the names of these functions vary by engine**; a function that converts to a day count was used here to compute the day difference. This is the cost of the text representation: there is no type checking, `'2025-13-45'` can be written, and every operation requires parsing. Where an engine has a dedicated date type, that type is chosen. Using a zeroed-out date or a placeholder like `'0000-00-00'` is a workaround for dodging the null-value discussion from the previous lesson, and it requires that placeholder to be filtered out of every query. A null value is used for an unknown date. #### Truth Values, Binary Data, and Enumeration Three more common cases remain. - **Truth value.** In engines without a dedicated type, a single-digit integer column is used, paired with a `CHECK (column IN (0, 1))` constraint. Without the constraint, the column can take a third value, and there is no way to trace which code wrote it. - **Binary data.** Data such as a cover image or a scanned document can be stored in binary types. The criterion is size: binary data that is small and meaningful together with the row can stay in the column; for large objects, keeping them in a file system or object store and storing only the address in the database lowers backup size and row-read cost. The cost of this is that consistency between the two storage locations now falls to the application. - **Enumeration.** A loan transaction's status — open, overdue, closed — is a bounded value set. There are two counterparts: a `CHECK ... IN (...)` constraint on a text column, or a foreign key to a separate relation that holds the values as rows. The second turns adding a new value from a schema change into a data change, and it allows attaching extra information — a display name, a sort order — to the value. #### Summary - Counted quantities are stored with an integer or decimal type, measured quantities with a floating-point type; money is counted. - A floating-point sum can display correctly on screen while an equality comparison still returns the wrong result. - How binding a type declaration is varies by engine; a domain's real limit, once written as a constraint, holds in every engine. - Text equality and ordering depend on collation; case insensitivity does not extend to Turkish letters on its own. - In the absence of a dedicated date type, ISO 8601 text is used; sorting works correctly, and arithmetic requires an engine-specific function. #### Next Step The Relational Model topic concludes here: the relation was defined, keys were chosen, constraints were written, the logic of null values was established, and columns were given types. One question remains — which relation should a column belong to? In the course's first lesson, the member name repeated across three rows in the `loan.csv` file, and an email change required editing three places. The next topic opens with the concept that explains where that repetition comes from: functional dependency. ## Normalization The concept of functional dependency, first, second, and third normal forms, Boyce–Codd and beyond, the measured trade-off of denormalization, and common schema design patterns. ### Functional Dependency Source: https://academia.sh/en/courses/relational-theory/functional-dependency The definition and notation of functional dependency, dependency arising from meaning rather than from data, the types of dependency, Armstrong's axioms, and finding keys through attribute closure. The Relational Model topic built the schema but left one question unanswered: which column belongs in which relation? In the `loan.csv` file from the course's first lesson, the member name repeated across three rows, and an email change required a correction in three places. This lesson's question is: where does that repetition come from, and how is it named formally? The answer lies in a single concept. If the value of one set of columns determines the value of another set of columns, a dependency exists between them; repetition arises when that dependency is placed in the wrong location. #### Definition and Notation Let $H$ be the attribute set of relation $R$, and let $X, Y \subseteq H$. If, in every valid value of $R$, two rows carrying the same values on $X$ also carry the same values on $Y$, the attributes of $Y$ are said to be **functionally dependent** on $X$, written: $$ X \to Y $$ Read as "$X$ determines $Y$." A few examples from the library domain: - $\text{member no} \to \text{member name}, \text{member email}$ — once the member number is known, the member's name and address are known. - $\text{ISBN} \to \text{book title}, \text{book author}$ - $\text{branch code} \to \text{branch name}$ - $\text{loan no} \to$ every other attribute. The last line is the superkey definition from the previous topic, restated in the language of dependency: $K$ is a superkey if and only if $K \to H$. A candidate key is the smallest set with this property. The concept of a key is a special case of the concept of dependency. #### Dependency Comes From Meaning The critical point is this: a functional dependency is not read off today's data. Today's data can only **refute** a dependency; it cannot confirm one. Dependency comes from the rule of the domain. Counting the difference on an example is the fastest way to eliminate dependency candidates: ```js const headers = "loan_no member_no member_name isbn book_title branch_code branch_name".split(" "); const data = [ "1001 41 Alice_Kane 975-01 Lost_Time CEN Central", "1002 52 Marcus_Reyes 975-01 Lost_Time CEN Central", "1003 41 Alice_Kane 975-02 Sea_Lighthouses BHC Bahcelievler", "1004 41 Alice_Kane 975-01 Lost_Time CEN Central", "1005 63 Alice_Kane 975-02 Sea_Lighthouses CEN Central", "1006 52 Marcus_Reyes 975-03 Silent_Garden BHC Bahcelievler", ].map((s) => Object.fromEntries(s.split(" ").map((v, i) => [headers[i], v]))); function countViolations(left, right) { const groups = new Map(); for (const row of data) { const key = left.map((a) => row[a]).join("|"); const value = right.map((a) => row[a]).join("|"); if (groups.has(key)) groups.get(key).add(value); else groups.set(key, new Set([value])); } return [...groups.values()].filter((d) => d.size > 1).length; } const candidates = [ [["loan_no"], ["member_no"]], [["member_no"], ["member_name"]], [["member_name"], ["member_no"]], [["isbn"], ["book_title"]], [["book_title"], ["isbn"]], [["branch_code"], ["branch_name"]], [["member_no"], ["isbn"]], ]; for (const [left, right] of candidates) { const violations = countViolations(left, right); const status = violations === 0 ? "consistent with the data" : `violated in ${violations} group${violations === 1 ? "" : "s"}`; console.log(`${left.join(",")} -> ${right.join(",")}`.padEnd(30), status); } ``` ``` loan_no -> member_no consistent with the data member_no -> member_name consistent with the data member_name -> member_no violated in 1 group isbn -> book_title consistent with the data book_title -> isbn consistent with the data branch_code -> branch_name consistent with the data member_no -> isbn violated in 2 groups ``` The checker groups rows by their left-side values and counts a violation whenever a group holds more than one right-side value. `member_name -> member_no` shows one violation: two different members share the same name. `member_no -> isbn` shows two violations: the same member has borrowed more than one book. The real lesson lies in the rows marked "consistent with the data." The dependency `book_title -> isbn` is not refuted across these six rows, but it does not hold — two different books can carry the same title, and the schema breaks the day that record is entered. A checker is an elimination tool: finding a violation means the dependency **definitely** does not hold; finding none means it has only **not yet** been refuted. The rule of the domain decides. #### Types of Dependency Four distinctions will be used in defining the normal forms. **Trivial dependency**: when $Y \subseteq X$, $X \to Y$ always holds. $\text{member no}, \text{member name} \to \text{member name}$ carries no information. **Full dependency**: if $X \to Y$ holds and no proper subset of $X$ determines $Y$, the dependency is full. **Partial dependency**: if $X$ is a composite key and $Y$ depends on a proper subset of $X$, the dependency is partial. If the loan record's key were $(\text{member no}, \text{ISBN})$, then $\text{member no} \to \text{member name}$ would be a partial dependency: the member name depends on half the key, not on the whole of it. **Transitive dependency**: when $X \to Y$ and $Y \to Z$ hold, $Z$ depends on $X$ through $Y$. In the loan record this is the chain $\text{loan no} \to \text{branch code} \to \text{branch name}$; the branch name does not depend on the loan directly, but through the branch code. Partial and transitive dependencies both cause the same fact to repeat across multiple rows — the branch name is rewritten in every loan made from that branch. The next lesson's topic is removing these two dependencies. #### Armstrong's Axioms Dependencies can be derived from one another. Three axioms suffice to produce every dependency that follows from a known set of dependencies. - **Reflexivity**: if $Y \subseteq X$, then $X \to Y$. - **Augmentation**: if $X \to Y$, then for every $Z$, $X \cup Z \to Y \cup Z$. - **Transitivity**: if $X \to Y$ and $Y \to Z$, then $X \to Z$. Three useful rules follow from these: **union** (if $X \to Y$ and $X \to Z$, then $X \to Y \cup Z$), **decomposition** (if $X \to Y \cup Z$, then $X \to Y$ and $X \to Z$), and **pseudotransitivity**. The decomposition rule licenses splitting a dependency with several attributes on the right side into single-attribute pieces; analysis is usually carried out this way. The axiom set has two properties: it is **sound** (every derived dependency actually holds) and **complete** (every dependency that holds can be derived). This is why normal-form checks can be carried out mechanically rather than by hand intuition. #### Attribute Closure Instead of deriving by hand, a set is computed. The **attribute closure** of a set $X$, written $X^+$, is the set of all attributes that can be determined from $X$ using the given dependencies. The computation is iterative: the right side of every dependency whose left side already lies within $X^+$ is added to the set, and this continues until the set stops growing. Closure answers two questions in a single step. Does $X \to Y$ hold? Yes, if $Y \subseteq X^+$. Is $X$ a superkey? Yes, if $X^+$ covers every attribute. ```js const attributes = "loan_no member_no member_name isbn book_title branch_code branch_name".split(" "); const dependencies = [ [["loan_no"], ["member_no", "isbn", "branch_code"]], [["member_no"], ["member_name"]], [["isbn"], ["book_title"]], [["branch_code"], ["branch_name"]], ]; function closure(start) { const result = new Set(start); let grew = true; while (grew) { grew = false; for (const [left, right] of dependencies) { if (left.every((a) => result.has(a))) { for (const a of right) if (result.has(a) === false) { result.add(a); grew = true; } } } } return [...result]; } for (const set of [["loan_no"], ["member_no"], ["member_no", "isbn"]]) { const k = closure(set); console.log(`{${set.join(",")}}+ = {${k.join(",")}}`); console.log(` -> ${k.length === attributes.length ? "superkey" : "not a superkey"}`); } ``` ``` {loan_no}+ = {loan_no,member_no,isbn,branch_code,member_name,book_title,branch_name} -> superkey {member_no}+ = {member_no,member_name} -> not a superkey {member_no,isbn}+ = {member_no,isbn,member_name,book_title} -> not a superkey ``` The loan number alone reaches every attribute, so it is a superkey; since no proper subset of it has this property, it is also a candidate key. The member number and the ISBN together still cannot reach the branch information — because the branch depends on the loan, not on the pair of member and book. Closure computation is the transitive-closure idea from the Data Structures course, applied to dependencies instead of graphs: instead of the set of nodes reachable from a node, it finds the set of attributes that can be determined from a set of attributes. #### What a Dependency Looks Like in the Schema A dependency shows up in the schema in one of two ways. If its left side is a candidate key, the dependency is already enforced by the key constraint — because the key is unique, each value occurs once, and the right side is written once for that row. If the left side is not a key, the dependency cannot be written into the schema; the engine has no way of knowing about it, and no constraint can be built to prevent it from being violated. From this comes normalization's one-sentence justification: **splitting relations so that the left side of every functional dependency is a key** ensures dependencies are enforced by the schema. The next lesson defines this splitting process step by step. #### Summary - $X \to Y$ is the rule that two rows matching on $X$ also match on $Y$; a superkey is the left side of a dependency whose right side is every attribute. - Dependency comes from the rule of the domain; a data sample can only refute it, never confirm it. - Partial dependency is built on part of a composite key, transitive dependency through an intermediate attribute; both cause rows to repeat. - Armstrong's axioms are sound and complete, so dependency inference can be carried out mechanically. - Attribute closure answers, in a single computation, both whether a dependency holds and whether a set is a superkey. #### Next Step This lesson named the source of repetition: dependencies whose left side is not a key. The next lesson defines the steps for removing them. First normal form requires the indivisibility of values, second normal form requires removing partial dependency, third normal form requires removing transitive dependency. At each step the same question will be asked: which update anomaly does this split eliminate? ### Normal Forms Source: https://academia.sh/en/courses/relational-theory/normal-forms The three types of update anomaly; the definitions of first, second, and third normal form; and query output showing which anomaly each step removes. The previous lesson named the source of repetition: functional dependencies whose left side is not a key. This lesson's question is: in what order are these dependencies removed, and what concrete problem does each step eliminate? Normalization's criterion is not aesthetic. The criterion is **update anomalies**: situations, caused by a relation's structure, that leave data inconsistent. There are three kinds. - **Insertion anomaly**: recording one fact requires also knowing an unrelated fact. A branch that has never lent out a book cannot be written into a table that stores loan records. - **Deletion anomaly**: deleting one fact makes a second fact disappear too. When a book's only loan record is deleted, the book itself drops out of the records. - **Modification anomaly**: because the same fact is written in multiple rows, a change must be applied to all of them; skipping one leaves the data self-contradictory. Normal forms are definitions that eliminate, step by step, the structures that produce these three anomalies. #### First Normal Form **First normal form** requires a single, indivisible value at every row–column intersection. No lists, nested structures, or repeating column groups are allowed. The cost of this rule is concrete. When phone numbers are stored comma-separated in a single text column, searching for one number reduces to string matching, and string matching produces false hits: ```sh sqlite3 :memory: <<'SQL' .headers on .mode box CREATE TABLE member_flat (member_no INTEGER PRIMARY KEY, name TEXT, phones TEXT); INSERT INTO member_flat VALUES (41, 'Alice Kane', '0312-555-0101,0312-555-0102'), (52, 'Marcus Reyes', '0312-555-0110'), (63, 'Sylvia Renner', '0312-555-011'); CREATE TABLE member_phone (member_no INTEGER, phone TEXT, PRIMARY KEY (member_no, phone)); INSERT INTO member_phone VALUES (41, '0312-555-0101'), (41, '0312-555-0102'), (52, '0312-555-0110'), (63, '0312-555-011'); SELECT 'list in one column' AS format, COUNT(*) AS found FROM member_flat WHERE phones LIKE '%0312-555-011%' UNION ALL SELECT 'separate relation', COUNT(*) FROM member_phone WHERE phone = '0312-555-011'; SQL ``` ``` ┌────────────────────┬───────┐ │ format │ found │ ├────────────────────┼───────┤ │ list in one column │ 2 │ │ separate relation │ 1 │ └────────────────────┴───────┘ ``` The number being searched for was `0312-555-011`, and it had a single owner. The string search also pulled in the record `0312-555-0110`, which contains that number as a prefix. When numbers are held in a separate relation, comparison becomes equality and the false match disappears; in addition, a constraint can be written on the number, its uniqueness can be checked, and a single number can be deleted. The same rule applies to the "repeating column group" form. Columns `phone_1`, `phone_2`, `phone_3` are nothing but a list written out horizontally; a fourth number requires a schema change. #### Second Normal Form **Second normal form** defines a relation that satisfies first normal form and in which **no non-key attribute is dependent on a proper subset of a candidate key**. In short, no partial dependency exists. The definition only has meaning for relations with a composite key; a single-column key has no proper subset. The relation holding the quantity of each book per branch has the key `(isbn, branch_code)`. The book's title depends only on the ISBN, and the branch's name depends only on the branch code — both are partial dependencies: ```sh sqlite3 :memory: <<'SQL' .headers on .mode box CREATE TABLE stock_flat ( isbn TEXT NOT NULL, branch_code TEXT NOT NULL, quantity INTEGER NOT NULL, book_title TEXT NOT NULL, branch_name TEXT NOT NULL, PRIMARY KEY (isbn, branch_code) ); INSERT INTO stock_flat VALUES ('975-01', 'CEN', 3, 'Lost Time', 'Central'), ('975-01', 'BHC', 1, 'Lost Time', 'Bahcelievler'), ('975-02', 'CEN', 2, 'Sea Lighthouses', 'Central'), ('975-02', 'BHC', 4, 'Sea Lighthouses', 'Bahcelievler'), ('975-03', 'BHC', 1, 'Silent Garden', 'Bahcelievler'); UPDATE stock_flat SET branch_name = 'Bahcelievler Branch' WHERE branch_code = 'BHC' AND isbn = '975-01'; SELECT branch_code, COUNT(*) AS rows, COUNT(DISTINCT branch_name) AS distinct_names FROM stock_flat GROUP BY branch_code; SQL ``` ``` ┌─────────────┬──────┬────────────────┐ │ branch_code │ rows │ distinct_names │ ├─────────────┼──────┼────────────────┤ │ BHC │ 3 │ 2 │ │ CEN │ 2 │ 1 │ └─────────────┴──────┴────────────────┘ ``` The update changing the branch name was applied to a single row, and the branch is now on record under two different names. The engine cannot prevent this, because the rule "the same branch code carries the same name" is not written in the schema — nor can it be, since the left side of the dependency is not a key. Decomposition makes the left side of the partial dependency the key of its own relation: the book information moves to the `book` relation, the branch information to the `branch` relation, and `stock` holds only the key together with the `quantity` column, which is fully dependent on it. ```sh sqlite3 :memory: <<'SQL' .headers on .mode box PRAGMA foreign_keys = ON; CREATE TABLE book (isbn TEXT PRIMARY KEY NOT NULL, title TEXT NOT NULL); CREATE TABLE branch (branch_code TEXT PRIMARY KEY NOT NULL, name TEXT NOT NULL); CREATE TABLE stock ( isbn TEXT NOT NULL REFERENCES book (isbn), branch_code TEXT NOT NULL REFERENCES branch (branch_code), quantity INTEGER NOT NULL, PRIMARY KEY (isbn, branch_code) ); INSERT INTO book VALUES ('975-01', 'Lost Time'), ('975-02', 'Sea Lighthouses'), ('975-03', 'Silent Garden'); INSERT INTO branch VALUES ('CEN', 'Central'), ('BHC', 'Bahcelievler'), ('KDK', 'Kadikoy'); INSERT INTO stock VALUES ('975-01', 'CEN', 3), ('975-01', 'BHC', 1), ('975-02', 'CEN', 2), ('975-02', 'BHC', 4), ('975-03', 'BHC', 1); UPDATE branch SET name = 'Bahcelievler Branch' WHERE branch_code = 'BHC'; SELECT changes() AS updated_rows, (SELECT COUNT(*) FROM branch WHERE branch_code = 'KDK') AS branch_without_stock; SQL ``` ``` ┌──────────────┬──────────────────────┐ │ updated_rows │ branch_without_stock │ ├──────────────┼──────────────────────┤ │ 1 │ 1 │ └──────────────┴──────────────────────┘ ``` Because the branch name is now held in a single row, one update suffices and inconsistency cannot arise. The same decomposition also removes the insertion anomaly: the Kadikoy branch, which has no books at all, is on record — in the flat table this row could not have been written, because the ISBN part of the key would have been left empty. The name of the function that returns the number of changed rows varies by engine. #### Third Normal Form **Third normal form** defines a relation that satisfies second normal form and in which **no non-key attribute is dependent on another non-key attribute**. In short, no transitive dependency exists. In the loan record the key is a single column, so no partial dependency exists; there is, however, the chain `loan_no → member_no → member_name, member_email`. The member information does not depend on the loan directly, but through the member number: ```sh sqlite3 :memory: <<'SQL' .headers on .mode box CREATE TABLE loan_flat ( loan_no INTEGER PRIMARY KEY, member_no INTEGER NOT NULL, member_name TEXT NOT NULL, member_email TEXT NOT NULL, isbn TEXT NOT NULL, book_title TEXT NOT NULL, checkout_date TEXT NOT NULL ); INSERT INTO loan_flat VALUES (1001, 41, 'Alice Kane', 'alice@example.test', '975-01', 'Lost Time', '2025-03-02'), (1002, 52, 'Marcus Reyes', 'marco@example.test', '975-01', 'Lost Time', '2025-03-04'), (1003, 41, 'Alice Kane', 'alice@example.test', '975-02', 'Sea Lighthouses', '2025-03-05'), (1004, 41, 'Alice Kane', 'alice@example.test', '975-01', 'Lost Time', '2025-03-20'), (1005, 52, 'Marcus Reyes', 'marco@example.test', '975-03', 'Silent Garden', '2025-03-22'); UPDATE loan_flat SET member_email = 'alice.kane@example.test' WHERE loan_no = 1001; DELETE FROM loan_flat WHERE loan_no = 1005; SELECT (SELECT COUNT(DISTINCT member_email) FROM loan_flat WHERE member_no = 41) AS member41_email, (SELECT COUNT(*) FROM loan_flat WHERE isbn = '975-03') AS book975_03; SQL ``` ``` ┌────────────────┬────────────┐ │ member41_email │ book975_03 │ ├────────────────┼────────────┤ │ 2 │ 0 │ └────────────────┴────────────┘ ``` Two anomalies appear at once. An email address corrected on one row left the same member appearing to have two different addresses. The book whose only loan record was deleted disappeared from the database entirely — its title had been written only in that row. Decomposition makes the middle of the transitive chain the key of its own relation: ```sh sqlite3 :memory: <<'SQL' .headers on .mode box PRAGMA foreign_keys = ON; CREATE TABLE member (member_no INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL); CREATE TABLE book (isbn TEXT PRIMARY KEY NOT NULL, title TEXT NOT NULL); CREATE TABLE loan ( loan_no INTEGER PRIMARY KEY, member_no INTEGER NOT NULL REFERENCES member (member_no), isbn TEXT NOT NULL REFERENCES book (isbn), checkout_date TEXT NOT NULL ); INSERT INTO member VALUES (41, 'Alice Kane', 'alice@example.test'), (52, 'Marcus Reyes', 'marco@example.test'); INSERT INTO book VALUES ('975-01', 'Lost Time'), ('975-02', 'Sea Lighthouses'), ('975-03', 'Silent Garden'); INSERT INTO loan VALUES (1001, 41, '975-01', '2025-03-02'), (1002, 52, '975-01', '2025-03-04'), (1003, 41, '975-02', '2025-03-05'), (1004, 41, '975-01', '2025-03-20'), (1005, 52, '975-03', '2025-03-22'); UPDATE member SET email = 'alice.kane@example.test' WHERE member_no = 41; DELETE FROM loan WHERE loan_no = 1005; SELECT (SELECT COUNT(DISTINCT email) FROM member WHERE member_no = 41) AS member41_email, (SELECT COUNT(*) FROM book WHERE isbn = '975-03') AS book975_03; SQL ``` ``` ┌────────────────┬────────────┐ │ member41_email │ book975_03 │ ├────────────────┼────────────┤ │ 1 │ 1 │ └────────────────┴────────────┘ ``` The same two statements were run; the result changed. Because the email is held in one place only, a single address remains; because the book record is independent of the loan record, it was unaffected by the deletion. Neither was achieved by how the query was written, but by the structure of the schema. #### The Two Conditions for Decomposition Splitting a relation is not always safe. Two conditions are required. **Lossless join**: when the split relations are joined back together, the original rows must be recovered exactly, with no extra rows created. The condition is that the common attribute set must be a superkey of at least one of the parts. All the decompositions above satisfy this, because the split was always made along the left side of a dependency. **Dependency preservation**: the original dependencies must be checkable separately on the parts. A dependency that is not preserved can only be tested by joining the parts back together — that is, it cannot be written as a constraint. Decompositions up to third normal form can satisfy both at once. The next lesson's topic is the case where these two conditions come apart. #### Summary - Normalization's criterion is the elimination of insertion, deletion, and modification anomalies. - First normal form requires a single indivisible value in every cell; a column holding a list reduces search to string matching and produces false matches. - Second normal form removes partial dependency, third normal form removes transitive dependency; both split along the left side of a dependency. - In a normalized schema, a fact is written in exactly one place; an update applies to a single row, and inconsistency cannot arise. - A decomposition must satisfy lossless join and dependency preservation; the join is lossless when the common attribute set is a superkey of one of the parts. #### Next Step Third normal form focuses on non-key attributes. But what if the right side of a dependency is part of a key? By definition that attribute is not a non-key attribute, and third normal form is not considered violated — yet the repetition continues. The next lesson closes this gap by defining Boyce–Codd normal form, shows a case where lossless join and dependency preservation cannot be satisfied together, and moves on to fourth normal form through multivalued dependency. ### Advanced Normal Forms Source: https://academia.sh/en/courses/relational-theory/advanced-normal-forms The definition of Boyce–Codd normal form and how it differs from third normal form, a decomposition that loses dependency preservation, multivalued dependency and fourth normal form, and normalization's stopping point. Third normal form focuses on non-key attributes: it requires that they depend neither on part of a key nor on another non-key attribute. But what if the right side of a dependency is part of a key? By definition that attribute is not a non-key attribute, so third normal form is not considered violated — yet the same fact keeps repeating across multiple rows. This lesson's question is: how is this gap closed, and what does closing it cost? #### Boyce–Codd Normal Form **Boyce–Codd normal form** sets a single, stricter condition: **the left side of every non-trivial functional dependency must be a superkey.** The "non-key attribute" exemption of third normal form is removed; whatever the attribute, the set that determines it must be a key. The two definitions diverge exactly where candidate keys overlap. Consider the relation that records which shelf a book stands on in the library. The rule is: a book stands on a single shelf within each branch, and each shelf belongs to a single branch. Two candidate keys follow from this — `(isbn, branch_code)` and `(isbn, shelf)` — along with a dependency: `shelf → branch_code`. This relation is in third normal form: `branch_code` is not counted as a non-key attribute because it is part of a candidate key. But `shelf` is not a superkey, so it violates the Boyce–Codd condition. The result is an inconsistency the schema cannot enforce: ```sh sqlite3 :memory: <<'SQL' .headers on .mode box CREATE TABLE placement ( isbn TEXT NOT NULL, branch_code TEXT NOT NULL, shelf TEXT NOT NULL, PRIMARY KEY (isbn, branch_code), UNIQUE (isbn, shelf) ); INSERT INTO placement VALUES ('975-01', 'CEN', 'R12'), ('975-02', 'CEN', 'R12'), ('975-03', 'BHC', 'R07'); INSERT INTO placement VALUES ('975-04', 'BHC', 'R12'); SELECT shelf, COUNT(DISTINCT branch_code) AS distinct_branches FROM placement GROUP BY shelf; SQL ``` ``` ┌───────┬───────────────────┐ │ shelf │ distinct_branches │ ├───────┼───────────────────┤ │ R07 │ 1 │ │ R12 │ 2 │ └───────┴───────────────────┘ ``` Both candidate keys are written into the schema and neither has been violated; even so, shelf R12 appears in two separate branches. Because the left side of the dependency `shelf → branch_code` is not a key, that rule cannot be written into the schema — the engine has no way of knowing about it. And the shelf's branch is rewritten for every book placed on that shelf — repetition continues. #### Decomposition and the Loss of Dependency Preservation Boyce–Codd decomposition makes the left side of the offending dependency the key of a new relation: `shelf(shelf, branch_code)` and `book_shelf(isbn, shelf)`. ```sh sqlite3 :memory: <<'SQL' .headers on .mode box PRAGMA foreign_keys = ON; CREATE TABLE shelf (shelf TEXT PRIMARY KEY NOT NULL, branch_code TEXT NOT NULL); CREATE TABLE book_shelf ( isbn TEXT NOT NULL, shelf TEXT NOT NULL REFERENCES shelf (shelf), PRIMARY KEY (isbn, shelf) ); INSERT INTO shelf VALUES ('R12', 'CEN'), ('R07', 'BHC'), ('R21', 'CEN'); INSERT INTO book_shelf VALUES ('975-01', 'R12'), ('975-02', 'R12'), ('975-03', 'R07'); INSERT INTO book_shelf VALUES ('975-01', 'R21'); SELECT (SELECT COUNT(DISTINCT branch_code) FROM shelf WHERE shelf = 'R12') AS r12_branches, (SELECT COUNT(*) FROM book_shelf b JOIN shelf s ON s.shelf = b.shelf WHERE b.isbn = '975-01' AND s.branch_code = 'CEN') AS book975_01_mrk; SQL ``` ``` ┌──────────────┬────────────────┐ │ r12_branches │ book975_01_mrk │ ├──────────────┼────────────────┤ │ 1 │ 2 │ └──────────────┴────────────────┘ ``` The first column shows the gain: the shelf now belongs to a single branch, and this rule is enforced by a key. The second column shows the cost: book `975-01` now stands on two separate shelves in the Central branch. Before decomposition this row could not have been written, because `(isbn, branch_code)` was the key. What was lost is the dependency `(isbn, branch_code) → shelf`. This dependency can no longer be expressed in either relation; testing it requires joining the two together. This is called **loss of dependency preservation**, and it is the known limitation of Boyce–Codd normal form: every relation can be decomposed losslessly into Boyce–Codd form, but preserving dependency while doing so is not always possible. The decision is left to the designer at this point. The choice depends on which rule matters more to enforce in the schema: that a shelf belongs to a single branch, or that a book stands on a single shelf per branch. Staying in third normal form to preserve the second rule is also a valid decision — normal forms are not a ladder but a list of trade-offs. #### Multivalued Dependency Boyce–Codd normal form exhausts functional dependencies, but they are not the only source of repetition. If a member has several interests and several phone numbers, and the two sets are independent of each other, holding all three in a single relation produces a Cartesian product. This is called a **multivalued dependency**. The relation is in Boyce–Codd form — the key consists of every attribute, so no non-trivial functional dependency exists. Yet every new phone number requires adding as many rows as the member has interests: ```sh sqlite3 :memory: <<'SQL' .headers on .mode box CREATE TABLE member_combined (member_no INTEGER, interest TEXT, phone TEXT, PRIMARY KEY (member_no, interest, phone)); INSERT INTO member_combined VALUES (41, 'history', '0312-555-0101'), (41, 'history', '0312-555-0102'), (41, 'history', '0312-555-0103'), (41, 'poetry', '0312-555-0101'), (41, 'poetry', '0312-555-0102'), (41, 'poetry', '0312-555-0103'); CREATE TABLE member_interest (member_no INTEGER, interest TEXT, PRIMARY KEY (member_no, interest)); CREATE TABLE member_phone (member_no INTEGER, phone TEXT, PRIMARY KEY (member_no, phone)); INSERT INTO member_interest VALUES (41, 'history'), (41, 'poetry'); INSERT INTO member_phone VALUES (41, '0312-555-0101'), (41, '0312-555-0102'), (41, '0312-555-0103'); INSERT INTO member_interest VALUES (41, 'travel'); INSERT INTO member_combined VALUES (41, 'travel', '0312-555-0101'); SELECT (SELECT COUNT(*) FROM member_combined) AS combined_rows, (SELECT COUNT(*) FROM member_interest) + (SELECT COUNT(*) FROM member_phone) AS separate_rows, (SELECT COUNT(*) FROM member_interest i JOIN member_phone p USING (member_no)) AS joined; SQL ``` ``` ┌───────────────┬───────────────┬────────┐ │ combined_rows │ separate_rows │ joined │ ├───────────────┼───────────────┼────────┤ │ 7 │ 6 │ 9 │ └───────────────┴───────────────┴────────┘ ``` When the third interest was added, a single row was written, and the combined relation stayed at seven rows; for consistency it should have had nine. A silent inconsistency was created: the member with the `travel` interest appears to have only one phone number. The separate relations held six rows in total, and the join correctly produced nine. The difference is that the row count grows as $m + n$ rather than $m \times n$. **Fourth normal form** requires the left side of every non-trivial multivalued dependency to be a superkey. Its practical counterpart is a plain rule: two independent multivalued facts are not held in the same relation. #### Further Forms **Fifth normal form** deals with cases where a relation can only be decomposed losslessly into three or more parts rather than two; the constraint that produces this case is called a **join dependency**. It is rare in real schemas and typically arises when a three-way relationship actually consists of three pairwise relationships. **Domain-key normal form** states the criterion in its most general form: every constraint on the relation must be derivable solely from domain definitions and keys. A relation that satisfies this condition can have no update anomaly. It is valuable as a theoretical goal; it is not reachable for every relation, and whether it has been reached cannot be tested in general. #### Where to Stop In practice, most designs stop at third normal form or Boyce–Codd form. The deciding criterion is not the number of the form but two questions: which anomalies can actually arise, and can the constraint that prevents them be written into the schema? The order to follow is also fixed. First, the rules of the domain — that is, the functional and multivalued dependencies — are written down. Then the candidate keys are found through closure computation. Then dependencies whose left side is not a key are sought out; each one turns into either a decomposition or a deliberate exception. The exception decision must remain in writing, because to the next designer that structure will look like a mistake. #### Summary - Boyce–Codd normal form requires the left side of every non-trivial dependency to be a superkey; it removes the non-key exemption found in third normal form. - The two forms diverge only in relations where candidate keys overlap; a schema left in third normal form can harbor a dependency that cannot be enforced. - Every relation can be decomposed losslessly into Boyce–Codd form, but dependency preservation cannot always be achieved; which rule gets enforced in the schema is a design decision. - When two independent multivalued facts are held in the same relation, the row count grows multiplicatively and a missing row produces a silent inconsistency; fourth normal form separates them. - Normal forms are not a ladder but a list of trade-offs; the stopping point is chosen according to the anomalies that can actually arise. #### Next Step Every step so far has moved in the same direction: remove repetition, split the relation, enforce the rule in the schema. This has an unpaid cost — every split relation must be joined back together at read time. The next lesson measures this cost and takes up the opposite decision: under what condition is deliberately putting repetition back defensible, and what write cost is taken on in exchange? ### Denormalization Source: https://academia.sh/en/courses/relational-theory/denormalization Measuring the read cost of a normalized schema, the write cost of denormalization, the requirement that a derived column cover every write path, and the criteria for a denormalization decision. Every step of normalization moved in the same direction: remove repetition, split the relation, enforce the rule in the schema. The cost mentioned at the end of the previous lesson is measured here — every split relation must be joined back together at read time. This lesson's question is: how is that cost counted, and under what condition is putting repetition back deliberately defensible? **Denormalization** is deliberately adding repetition to a normalized schema for read performance. Two words in the definition are decisive: **deliberately** (by measurement, not by mistake) and **to a normalized schema** (never normalizing at all is not denormalization; it is an incomplete design). #### The Cost of Reading Displaying a loan list on screen requires the member name, the book title, and the branch name. In a normalized schema this information sits in four separate relations. The number of relations the engine accesses while executing the query can be counted from the plan output: ```sh sqlite3 :memory: <<'SQL' CREATE TABLE branch (branch_code TEXT PRIMARY KEY NOT NULL, name TEXT NOT NULL); CREATE TABLE member (member_no INTEGER PRIMARY KEY, name TEXT NOT NULL); CREATE TABLE book (isbn TEXT PRIMARY KEY NOT NULL, title TEXT NOT NULL); CREATE TABLE loan ( loan_no INTEGER PRIMARY KEY, member_no INTEGER NOT NULL REFERENCES member (member_no), isbn TEXT NOT NULL REFERENCES book (isbn), branch_code TEXT NOT NULL REFERENCES branch (branch_code), checkout_date TEXT NOT NULL ); CREATE TABLE loan_summary ( loan_no INTEGER PRIMARY KEY, member_name TEXT NOT NULL, book_title TEXT NOT NULL, branch_name TEXT NOT NULL, checkout_date TEXT NOT NULL ); EXPLAIN QUERY PLAN SELECT u.name, k.title, s.name, o.checkout_date FROM loan o JOIN member u ON u.member_no = o.member_no JOIN book k ON k.isbn = o.isbn JOIN branch s ON s.branch_code = o.branch_code WHERE o.checkout_date >= '2025-03-01'; EXPLAIN QUERY PLAN SELECT member_name, book_title, branch_name, checkout_date FROM loan_summary WHERE checkout_date >= '2025-03-01'; SQL ``` ``` QUERY PLAN |--SCAN o |--SEARCH u USING INTEGER PRIMARY KEY (rowid=?) |--SEARCH k USING INDEX sqlite_autoindex_book_1 (isbn=?) `--SEARCH s USING INDEX sqlite_autoindex_branch_1 (branch_code=?) QUERY PLAN `--SCAN loan_summary ``` The first plan has four access steps, the second has one. **The format of the plan output and the way the plan is requested vary by engine**; what is counted is independent of format — how many relations the query touches, and by what path it accesses each one. The loan table is scanned, and the remaining three are accessed one row at a time through a key. That is three extra lookups per scan; on a million-row scan, three million lookups. The cost of joins is not always this obvious. Access through a key is cheap, and small relations stay in memory. Moving to denormalization without measuring is the decision this lesson does not defend. #### The Cost of Writing When repetition is put back, the cost moves. When a branch's name changes, a normalized schema updates one row; a summary table updates every row belonging to that branch: ```sh sqlite3 :memory: <<'SQL' .headers on .mode box CREATE TABLE branch (branch_code TEXT PRIMARY KEY NOT NULL, name TEXT NOT NULL); CREATE TABLE loan_summary (loan_no INTEGER PRIMARY KEY, branch_code TEXT NOT NULL, branch_name TEXT NOT NULL); INSERT INTO branch VALUES ('CEN', 'Central'), ('BHC', 'Bahcelievler'); INSERT INTO loan_summary VALUES (1001, 'CEN', 'Central'), (1002, 'CEN', 'Central'), (1003, 'BHC', 'Bahcelievler'), (1004, 'CEN', 'Central'), (1005, 'CEN', 'Central'); UPDATE branch SET name = 'Central Branch' WHERE branch_code = 'CEN'; SELECT changes() AS source_rows; UPDATE loan_summary SET branch_name = 'Central Branch' WHERE branch_code = 'CEN'; SELECT changes() AS summary_rows; INSERT INTO loan_summary VALUES (1006, 'CEN', 'Central'); SELECT branch_code, COUNT(DISTINCT branch_name) AS distinct_names FROM loan_summary GROUP BY branch_code; SQL ``` ``` ┌─────────────┐ │ source_rows │ ├─────────────┤ │ 1 │ └─────────────┘ ┌──────────────┐ │ summary_rows │ ├──────────────┤ │ 4 │ └──────────────┘ ┌─────────────┬────────────────┐ │ branch_code │ distinct_names │ ├─────────────┼────────────────┤ │ BHC │ 1 │ │ CEN │ 2 │ └─────────────┴────────────────┘ ``` Four rows were updated instead of one — a measurable and acceptable cost. The real danger is in the final statement: a new row was inserted into the summary table under the old name, and the branch again appears under two names. The cost of denormalization is not the update cost, it is **the obligation of every write path to fill in the copy correctly**. #### Derived Columns and Every Write Path The same problem appears more sharply with derived values. Holding a member's open-loan count in a column instead of counting it on every query moves the cost of counting to write time. Consistency can be maintained with triggers — as long as every write path is covered: ```sh sqlite3 :memory: <<'SQL' .headers on .mode box CREATE TABLE member (member_no INTEGER PRIMARY KEY, name TEXT NOT NULL, open_loans INTEGER NOT NULL DEFAULT 0); CREATE TABLE loan (loan_no INTEGER PRIMARY KEY, member_no INTEGER NOT NULL, return_date TEXT); INSERT INTO member VALUES (41, 'Alice Kane', 0), (52, 'Marcus Reyes', 0); CREATE TRIGGER loan_inserted AFTER INSERT ON loan BEGIN UPDATE member SET open_loans = open_loans + 1 WHERE member_no = NEW.member_no; END; CREATE TRIGGER loan_returned AFTER UPDATE OF return_date ON loan WHEN OLD.return_date IS NULL AND NEW.return_date IS NOT NULL BEGIN UPDATE member SET open_loans = open_loans - 1 WHERE member_no = NEW.member_no; END; INSERT INTO loan VALUES (1001, 41, NULL), (1002, 41, NULL), (1003, 52, NULL); UPDATE loan SET return_date = '2025-03-16' WHERE loan_no = 1002; SELECT u.member_no, u.open_loans AS counter, (SELECT COUNT(*) FROM loan o WHERE o.member_no = u.member_no AND o.return_date IS NULL) AS actual FROM member u; DELETE FROM loan WHERE loan_no = 1001; SELECT u.member_no, u.open_loans AS counter, (SELECT COUNT(*) FROM loan o WHERE o.member_no = u.member_no AND o.return_date IS NULL) AS actual FROM member u; SQL ``` ``` ┌───────────┬─────────┬────────┐ │ member_no │ counter │ actual │ ├───────────┼─────────┼────────┤ │ 41 │ 1 │ 1 │ │ 52 │ 1 │ 1 │ └───────────┴─────────┴────────┘ ┌───────────┬─────────┬────────┐ │ member_no │ counter │ actual │ ├───────────┼─────────┼────────┤ │ 41 │ 1 │ 0 │ │ 52 │ 1 │ 1 │ └───────────┴─────────┴────────┘ ``` The first result is correct because the insertion and return paths are covered. No trigger was written for the deletion path; when a loan record is deleted, the counter stays where it was and diverges from the value actually counted. This is the rule for derived columns: **every path that changes the source must be counted** — insertion, deletion, updates, bulk migrations, and manually run maintenance statements included. If one path is skipped, the column silently drifts into being wrong, and when the wrongness began does not appear in the records. This is why derived values need a second requirement: an audit job that recomputes from the source and reports the difference. The second query above is exactly that. #### Forms of Denormalization Five patterns appear in practice, and their costs differ. - **Copied column.** A frequently read attribute is copied into the referencing relation. Cheap if the source rarely changes. - **Derived total.** A count, a sum, or a most-recent date is kept in a column. This is the example above; every write path must be covered. - **Summary relation.** A precomputed relation is kept for reporting and refreshed at set intervals. It sets up an explicit trade-off between freshness and cost. - **Pre-joined relation.** The result of a frequently used join is stored. - **View.** The join is moved behind a view; because the result is not stored, no repetition is added — only the query text becomes simpler. Whether a stored form of the result exists, and how it is refreshed, **varies by engine**. The last item matters: a view alone is not denormalization. The read cost stays the same; only the repeated query text disappears. #### Decision Criteria Denormalization is a last-resort tool. What is tried before it is well established: fixing the query itself, adding the necessary indexes, cutting unnecessary column reads, using pagination. These leave the data in one place; denormalization does not. Four questions are asked for the decision. What is the ratio of reads to writes — if reads do not dominate, the gain is small and the cost is large. How often does the copied data change — frequently changing data constantly invalidates the copy. Is stale data acceptable — this is the most critical answer to give for a summary relation. And are all the paths that fill the copy known — if not, the decision should be deferred. The decision itself must also remain in writing. Someone reading the schema later may mistake the repetition for a design error and try to "fix" it; keeping a record of which measurement made the repetition necessary prevents that. #### Summary - Denormalization is adding repetition to a normalized schema based on measurement; never normalizing at all is not denormalization. - The read cost is measured by counting, from plan output, how many relations a query accesses and by what path. - When repetition is added, the cost moves to write time: the number of updated rows grows, and every write path must fill the copy correctly. - A derived column's consistency silently breaks when one of the paths that changes its source is skipped; an audit job that recomputes from the source is required. - Query fixes, index additions, and pagination are tried before denormalization; the decision is left in writing along with its justification. #### Next Step The final lesson of the normalization topic moves past individual rules to look at recurring design situations: how one-to-many and many-to-many relationships are built, how tree-shaped data is held in a relational schema, how a row is versioned when history must be preserved, and which common patterns are actually anti-patterns — tables that store every row as a key–value pair lead this list. ### Schema Design Patterns Source: https://academia.sh/en/courses/relational-theory/schema-design-patterns Translating relationship cardinalities into a schema, storing hierarchical data, subtype and time-dimension patterns, and common anti-patterns led by the key-value table. The previous lessons gave rule after rule: find the dependencies, split the relation, put repetition back only by measurement. In practice, the same modeling situations recur again and again, and each has an established counterpart. This lesson's question is: which situation translates into which schema structure, and which common structures are actually the problem themselves? #### Relationship Cardinalities How a relationship between two relations translates into a schema depends on cardinality. A **one-to-many** relationship is built by putting the foreign key on the "many" side. A member has several loan records; the `member_no` column sits in the loan relation. The reverse direction — holding the loan number in the member relation — means squeezing a list into a single cell, and it breaks first normal form. A **one-to-one** relationship is rare and usually rests on one of two reasons: separating a sparsely populated part of a row, or keeping columns whose access depends on a different authorization level apart. The foreign key is placed on one of the two sides and backed by a uniqueness constraint. A **many-to-many** relationship cannot be expressed directly; a **junction table** is placed between the two. Its key is a composite key made up of the keys of both sides: ```sh sqlite3 :memory: <<'SQL' .headers on .mode box PRAGMA foreign_keys = ON; CREATE TABLE book (isbn TEXT PRIMARY KEY NOT NULL, title TEXT NOT NULL); CREATE TABLE author (author_no INTEGER PRIMARY KEY, name TEXT NOT NULL); CREATE TABLE book_author ( isbn TEXT NOT NULL REFERENCES book (isbn), author_no INTEGER NOT NULL REFERENCES author (author_no), position INTEGER NOT NULL, PRIMARY KEY (isbn, author_no) ); INSERT INTO book VALUES ('975-01', 'Lost Time'), ('975-02', 'Sea Lighthouses'); INSERT INTO author VALUES (7, 'Elena Marsh'), (8, 'Kevin Ashford'); INSERT INTO book_author VALUES ('975-01', 7, 1), ('975-01', 8, 2), ('975-02', 8, 1); SELECT k.title, COUNT(*) AS author_count FROM book k JOIN book_author ky ON ky.isbn = k.isbn GROUP BY k.isbn ORDER BY k.isbn; SQL ``` ``` ┌─────────────────┬──────────────┐ │ title │ author_count │ ├─────────────────┼──────────────┤ │ Lost Time │ 2 │ │ Sea Lighthouses │ 1 │ └─────────────────┴──────────────┘ ``` A junction table often carries more than a plain mapping. The `position` column here holds the order in which the authors appear on the cover — an attribute that belongs to the relationship itself and fits no other relation. The loan transaction is really the same kind of thing: it carries the checkout and return dates that belong to the relationship between a member and a copy. #### Hierarchical Data Book subjects form a tree: fiction under literature, historical fiction under fiction. The simplest counterpart is a relation referencing its own key — this is called an **adjacency list**. Walking the tree requires a recursive query: ```sh sqlite3 :memory: <<'SQL' .headers on .mode box PRAGMA foreign_keys = ON; CREATE TABLE subject ( subject_no INTEGER PRIMARY KEY, name TEXT NOT NULL, parent_subject INTEGER REFERENCES subject (subject_no) ); INSERT INTO subject VALUES (1, 'Literature', NULL), (2, 'Fiction', 1), (3, 'Poetry', 1), (4, 'Historical Fiction', 2), (5, 'Science', NULL); WITH RECURSIVE branch(subject_no, name, depth, path) AS ( SELECT subject_no, name, 0, name FROM subject WHERE parent_subject IS NULL UNION ALL SELECT k.subject_no, k.name, d.depth + 1, d.path || ' / ' || k.name FROM subject k JOIN branch d ON k.parent_subject = d.subject_no ) SELECT depth, path FROM branch ORDER BY path; SQL ``` ``` ┌───────┬───────────────────────────────────────────┐ │ depth │ path │ ├───────┼───────────────────────────────────────────┤ │ 0 │ Literature │ │ 1 │ Literature / Fiction │ │ 2 │ Literature / Fiction / Historical Fiction │ │ 1 │ Literature / Poetry │ │ 0 │ Science │ └───────┴───────────────────────────────────────────┘ ``` The query starts at the root and unfolds the tree by adding one level at each step. This is the same idea as breadth-first search from the Data Structures course; the difference is that the queue is held inside the query engine. An adjacency list is cheap to write and expensive to read: retrieving a node's entire subtree takes as many steps as its depth. Three common counterparts exist. **Path enumeration** stores, in each row, the path from the root as a string; a subtree query reduces to a prefix match, but moving a node updates the entire subtree. **Nested set** gives each node a left and a right number; reads are very fast, but an insertion shifts almost the whole table. **Closure table** stores every ancestor–descendant pair as a separate row; reads and writes are balanced, at the cost of a larger row count. The choice follows the ratio of reads to writes. #### Subtype and Time The **subtype** situation arises from types of a shared parent concept that carry different attributes: library materials can be a book, a periodical, or an audio recording; they have common fields and separate ones. Three counterparts are used. Holding everything in a single relation — placing every type's columns side by side — produces a large number of null values. A separate relation per type makes common queries harder. Keeping common attributes in a parent relation and separate attributes in a subtype relation per type is the balanced counterpart; the subtype relation's primary key is also a foreign key to the parent relation. The **time dimension** hides two separate requirements. The first is the interval over which a fact is valid: between which dates a membership fee held which amount. Its counterpart is adding a start and end date to the row and constraining the intervals not to overlap. The second is storing a row's past states: every change writes a new version row, and the current row is marked with a flag or an empty end date. Mixing the two produces a schema that gets neither the history right nor the current state cheaply. #### Anti-Patterns Some structures appear to solve the problem while eliminating its constraints. The most common is the key-value table, which holds columns as rows: ```sh sqlite3 :memory: <<'SQL' .headers on .mode box .nullvalue (empty) CREATE TABLE book_attribute ( isbn TEXT NOT NULL, attribute TEXT NOT NULL, value TEXT NOT NULL, PRIMARY KEY (isbn, attribute) ); INSERT INTO book_attribute VALUES ('975-01', 'title', 'Lost Time'), ('975-01', 'publication_year', '2019'), ('975-02', 'title', 'Sea Lighthouses'), ('975-02', 'publication_year', 'twenty twenty'), ('975-03', 'title', 'Silent Garden'); SELECT b.value AS title, y.value AS publication_year FROM book_attribute b LEFT JOIN book_attribute y ON y.isbn = b.isbn AND y.attribute = 'publication_year' WHERE b.attribute = 'title' ORDER BY b.isbn; SQL ``` ``` ┌─────────────────┬──────────────────┐ │ title │ publication_year │ ├─────────────────┼──────────────────┤ │ Lost Time │ 2019 │ │ Sea Lighthouses │ twenty twenty │ │ Silent Garden │ (empty) │ └─────────────────┴──────────────────┘ ``` Three losses show up in the same output. No type or domain constraint can be written for the publication year, because the `value` column is shared by every attribute — `twenty twenty` has been entered. Required-ness cannot be enforced; the third book has no year at all, and there is no place for a `NOT NULL` to prevent it. And reading two attributes requires joining the table with itself; as the number of attributes grows, so does the number of joins. This structure has a narrow legitimate use: attributes that are genuinely unknown in advance, defined by the user, and carry no expected constraint. When it is chosen to avoid a schema change, every guarantee the database provides is given back. Three anti-patterns appear more often. A **polymorphic foreign key** tries to reference several relations with a single column — the target relation's name is held in a separate column, and referential integrity becomes unenforceable. A **numbered column name** — `phone_1`, `phone_2` — is first normal form written out horizontally. A **magic value** places a placeholder such as `-1` or `'1900-01-01'` instead of a null; every query must filter it out, and when the filter is forgotten it leaks into totals. #### Summary - A one-to-many relationship is built with a foreign key, a many-to-many relationship with a junction table that has a composite key; the junction table carries the relationship's own attributes. - For a hierarchy, an adjacency list is cheap to write and expensive to read; path enumeration, nested set, and closure table shift this balance in different directions. - Subtypes are modeled by keeping common attributes in the parent relation and separate attributes in a subtype relation per type. - Validity interval and version history are separate requirements; trying to serve both with a single structure breaks both. - A key-value table loses type, domain, and required-ness constraints all at once and adds a join per attribute; its legitimate use is limited to attributes that are not known in advance. #### Next Step The normalization topic concludes here: how to build the schema, how to split it, and under which conditions to deliberately merge it back have all been established. The remaining question is what kind of workload this schema will run under. A system that records individual loan transactions and a system that produces annual loan statistics want the same data, but not the same access pattern. The next topic begins with this distinction. ## Data Processing Approaches The separation of transactional and analytical workloads, row- and column-oriented storage, the ACID properties, and the criteria for choosing between relational and non-relational systems. ### Transactional and Analytical Workloads Source: https://academia.sh/en/courses/relational-theory/transactional-and-analytical-workloads The difference in access pattern between transactional and analytical workloads, row- and column-oriented storage's effect on the bytes read, the covering index, and the star schema. The Normalization topic determined how the schema would be built. The remaining question is what kind of workload this schema runs under. A system that records individual loan transactions and a system that produces an annual loan statistic want the same data, but not the same access pattern. This lesson's question is: how are the two workload shapes separated, and what does that separation change in the storage layout? #### Two Workload Shapes A **transactional workload** is the daily work at the circulation desk: fetch a member's record by key, insert a loan row, update a return row. Its traits are these: each request touches a small number of rows, the full set of a row's columns is requested, latency is measured in milliseconds, and a large number of requests run at the same time. The write rate is high. An **analytical workload** is report generation: how many loans went out at which branch in which month, what the sum of overdue penalties is, how a given subject has shifted over the years. Its traits are the reverse: each request touches millions of rows, it asks for only a few of a row's columns, latency is measured in seconds, the number of concurrent requests is low, and writes are almost absent. These two workloads share the same data but call for opposite design decisions. A transactional workload gains from a normalized schema, narrow rows, and access by key. An analytical workload gains from wide scans, pre-joined structures, and reading only the columns it needs. #### Row and Column Orientation The difference takes concrete shape in the storage layout. **Row-oriented storage** keeps all of a row's columns next to each other on disk. Reading a row is a single block read — ideal for a transactional workload. Aggregating a single column, on the other hand, requires reading every column next to it as well. **Column-oriented storage** does the reverse: all the values of the same column sit next to each other. Aggregating a column reads only that column; fetching an entire row, on the other hand, requires a separate read from every column. The difference is measurable. In a row-oriented engine, the way to emulate column orientation is to keep a single column in its own relation: ```sh mkdir -p workload && cd workload && rm -f row.db column.db sqlite3 row.db <<'SQL' PRAGMA page_size = 4096; CREATE TABLE loan ( loan_no INTEGER PRIMARY KEY, member_no INTEGER NOT NULL, isbn TEXT NOT NULL, branch_code TEXT NOT NULL, checkout_date TEXT NOT NULL, return_date TEXT NOT NULL, overdue_days INTEGER NOT NULL, penalty_cents INTEGER NOT NULL, channel TEXT NOT NULL, note_text TEXT NOT NULL ); WITH RECURSIVE counter(n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM counter WHERE n < 200000 ) INSERT INTO loan (member_no, isbn, branch_code, checkout_date, return_date, overdue_days, penalty_cents, channel, note_text) SELECT n % 5000, '978-975-' || (n % 900), 'CEN', '2025-03-02', '2025-03-16', n % 7, (n % 7) * 250, 'counter', 'record note field ' || n FROM counter; SQL sqlite3 column.db "PRAGMA page_size = 4096; CREATE TABLE penalty_cents (value INTEGER NOT NULL);" sqlite3 row.db "ATTACH 'column.db' AS s; INSERT INTO s.penalty_cents SELECT penalty_cents FROM loan;" echo "row.db $(wc -c < row.db | tr -d ' ') bytes" echo "column.db $(wc -c < column.db | tr -d ' ') bytes" sqlite3 row.db "SELECT SUM(penalty_cents) FROM loan;" sqlite3 column.db "SELECT SUM(value) FROM penalty_cents;" ``` ``` row.db 17428480 bytes column.db 1957888 bytes 149999250 149999250 ``` The two files carry the same two hundred thousand values and yield the same total. Computing the penalty total requires scanning 17.4 million bytes in the row-oriented layout; 2 million bytes in the column layout. Because the page size is set explicitly in the block, the measurement is reproducible; **the byte counts depend on the page size and the engine's row encoding**, while the ratio is set by the number of columns and their widths. #### The Share of Compression Column orientation's second gain is in compression. Because the values of the same column share a type and are often similar to each other, ratios unreachable in a row-oriented layout are captured. Three methods are common: **dictionary encoding** maps repeated values to small integers — a few bits suffice for a branch code column; **run-length encoding** stores consecutive identical values in a sorted column as a single pair; **bit packing** writes narrow-range integers without aligning them to full byte boundaries. Compression does not save space alone. Because fewer bytes are read, the scan also speeds up, and some operations can run on the encoded form without decoding the values. #### The Row-Oriented Engine's Counterpart A row-oriented engine's closest counterpart to this gain is the **covering index**: an index that contains every column the query needs. The engine produces the answer from the index without ever touching the table. ```sh sqlite3 :memory: <<'SQL' CREATE TABLE loan ( loan_no INTEGER PRIMARY KEY, member_no INTEGER NOT NULL, branch_code TEXT NOT NULL, note_text TEXT NOT NULL, penalty_cents INTEGER NOT NULL ); EXPLAIN QUERY PLAN SELECT branch_code, SUM(penalty_cents) FROM loan GROUP BY branch_code; CREATE INDEX loan_penalty ON loan (branch_code, penalty_cents); EXPLAIN QUERY PLAN SELECT branch_code, SUM(penalty_cents) FROM loan GROUP BY branch_code; SQL ``` ``` QUERY PLAN |--SCAN loan `--USE TEMP B-TREE FOR GROUP BY QUERY PLAN `--SCAN loan USING COVERING INDEX loan_penalty ``` Once the index is added, the plan changes in two ways: the scan runs on the index instead of the table — meaning only two columns are read — and no temporary structure is needed for the grouping, because the index is already sorted by branch code. A covering index approaches column orientation for a narrow query; it does not scale to a widening set of queries, since each one would need its own index. #### The Analytical Schema An analytical workload also changes the schema's shape. A **star schema** places a **fact table** at the center and **dimension tables** around it. In the library example, the fact table is loan events; the dimensions are member, book, branch, and calendar. A fact table's row carries the measurable quantities and the keys leading to the dimensions: overdue days, penalty amount, member key, book key, branch key, date key. The dimension tables, by contrast, are deliberately not normalized — the branch dimension keeps the branch's name, province, and region together. The rationale is the same one behind denormalization in the earlier lesson: dimensions are small, rarely change, and are joined in every query. The design's first decision is the **grain**: what does one row of the fact table represent? "One loan transaction" and "one branch's daily summary" are different grains, and the choice cannot be changed later — a finer grain can be rolled up to a coarser one, not the reverse. #### Separating the Two Workloads Running both workloads in the same database harms both. A long-running analytical query consumes the transactional workload's resources; the transactional workload's writes change the data an analytical query sees. The common answer is to move the analytical workload to a separate copy: data is extracted from the transactional system at regular intervals, transformed, and loaded into the analytical system. This has a cost: freshness. The analytical copy always lags by some amount, and how much lag is acceptable is an explicit requirement the system must meet. #### Summary - A transactional workload accesses few rows with many columns; an analytical workload accesses many rows with few columns; the two call for opposite design decisions. - Row-oriented storage keeps a row's columns next to each other; column-oriented storage keeps a column's values next to each other and lowers the bytes read in an aggregation. - Because a column's values are similar, the column layout compresses better with methods such as dictionary encoding and run-length encoding. - A covering index gives a similar gain for narrow queries in a row-oriented engine but does not scale to a widening set of queries. - A star schema surrounds a fact table with dimensions; choosing the grain is an irreversible decision. #### Next Step This lesson relied on the same assumption twice: writing a loan record either happens completely or not at all, and an analytical query must never see a half-written state. These assumptions also appeared in the course's first lesson — two processes writing to a file at the same time could not provide them. The next lesson defines the names the database management system gives these guarantees: atomicity, consistency, isolation, and durability. ### ACID Properties Source: https://academia.sh/en/courses/relational-theory/acid-properties The concept of a transaction and the definition of the atomicity, consistency, isolation, and durability guarantees; a runnable demonstration of rollback and durability; the client's responsibility in the face of an error. The previous lesson relied on the same assumption twice: writing a loan record either happens completely or not at all, and an analytical query never sees a half-written state. These same assumptions also appeared in the course's first lesson — two processes writing to a file at the same time could provide neither. This lesson's question is: under what name, and within what limits, does the database management system give these guarantees? #### Transaction A **transaction** is a sequence of statements handled as a single unit against the database. Transferring a copy from one branch to another requires two updates; the two must happen together, because the state in between leaves the library short one copy. A transaction's boundaries are stated explicitly: a start, followed by either a **commit** or a **rollback**. When not stated, each statement counts as a transaction on its own. The guarantees a transaction gives are grouped under four headings and referred to by their initials as **ACID**: atomicity, consistency, isolation, durability. #### Atomicity **Atomicity** requires that a transaction either be applied in full or not at all. An intermediate state is never made permanent. ```sh sqlite3 :memory: <<'SQL' .headers on .mode box CREATE TABLE stock (branch_code TEXT NOT NULL, isbn TEXT NOT NULL, quantity INTEGER NOT NULL, PRIMARY KEY (branch_code, isbn), CHECK (quantity >= 0)); INSERT INTO stock VALUES ('CEN', '975-01', 3), ('BHC', '975-01', 1); BEGIN; UPDATE stock SET quantity = quantity - 1 WHERE branch_code = 'CEN' AND isbn = '975-01'; UPDATE stock SET quantity = quantity + 1 WHERE branch_code = 'BHC' AND isbn = '975-01'; SELECT 'mid-transaction' AS moment, branch_code, quantity FROM stock ORDER BY branch_code; ROLLBACK; SELECT 'after rollback' AS moment, branch_code, quantity FROM stock ORDER BY branch_code; SQL ``` ``` ┌─────────────────┬─────────────┬──────────┐ │ moment │ branch_code │ quantity │ ├─────────────────┼─────────────┼──────────┤ │ mid-transaction │ BHC │ 2 │ │ mid-transaction │ CEN │ 2 │ └─────────────────┴─────────────┴──────────┘ ┌────────────────┬─────────────┬──────────┐ │ moment │ branch_code │ quantity │ ├────────────────┼─────────────┼──────────┤ │ after rollback │ BHC │ 1 │ │ after rollback │ CEN │ 3 │ └────────────────┴─────────────┴──────────┘ ``` Both updates are visible inside the transaction; after the rollback, neither is. Atomicity is not only for error conditions — a rollback can also be a decision the application makes on purpose. The file example in the course's first lesson had no such mechanism: a write left half-finished stayed half-finished in the file. #### Consistency **Consistency** requires that a transaction move the database from one valid state to another valid state. A state is "valid" when every constraint in the schema is satisfied. A constraint can be temporarily broken in the middle of a transaction; it cannot remain broken at the end of one. ```sh rm -f library.db sqlite3 library.db <<'SQL' CREATE TABLE stock (branch_code TEXT NOT NULL, isbn TEXT NOT NULL, quantity INTEGER NOT NULL, PRIMARY KEY (branch_code, isbn), CHECK (quantity >= 0)); INSERT INTO stock VALUES ('CEN', '975-01', 3), ('BHC', '975-01', 1); SQL sqlite3 library.db <<'SQL' .bail on BEGIN; UPDATE stock SET quantity = quantity - 1 WHERE branch_code = 'BHC' AND isbn = '975-01'; UPDATE stock SET quantity = quantity - 1 WHERE branch_code = 'BHC' AND isbn = '975-01'; COMMIT; SQL sqlite3 library.db <<'SQL' .headers on .mode box SELECT branch_code, quantity FROM stock ORDER BY branch_code; SQL ``` ``` Runtime error near line 4: CHECK constraint failed: quantity >= 0 (19) ┌─────────────┬──────────┐ │ branch_code │ quantity │ ├─────────────┼──────────┤ │ BHC │ 1 │ │ CEN │ 3 │ └─────────────┴──────────┘ ``` The second update was rejected because it would have driven the quantity negative; the first one was rolled back as well, and the quantity returned to its starting value of one. This result has a condition that is easy to miss. The first line in the block tells the client to stop at the first error; its name and syntax vary by engine and tool. Without that line, the client would ignore the error, send `COMMIT`, and a half-finished piece of work — **with only the first update applied** — would become permanent. The consistency guarantee starts with the engine enforcing the constraints, but it is completed by the client behaving correctly in the face of an error. A client that swallows the error takes away the guarantee the engine gave. #### Isolation **Isolation** requires that transactions running at the same time not see each other's half-finished state. Its strictest reading is this: the result of transactions running concurrently must be the same as some result obtainable by running the same transactions one at a time, in some order. This guarantee is expensive, and most systems stage it in levels rather than enforcing it in full. Relaxing it frees up specific anomalies, and these have names: a transaction seeing another transaction's uncommitted write, the same row giving a different value across two reads, a set of rows matching a condition differing across two reads. Which anomaly is visible at which level, the difference between locking and version-based approaches, and deadlock conditions belong to later courses in this curriculum. Here the treatment stays at the definition level: the lost update in the course's first lesson was an example of what happens in an environment without isolation. #### Durability **Durability** requires that a committed transaction's result stay permanent — even if the system crashes immediately afterward. A commit acknowledgment is a promise: the data is now secured in durable storage. ```sh rm -f library.db sqlite3 library.db <<'SQL' CREATE TABLE loan (loan_no INTEGER PRIMARY KEY, member_no INTEGER NOT NULL); BEGIN; INSERT INTO loan VALUES (1001, 41); COMMIT; BEGIN; INSERT INTO loan VALUES (1002, 52); ROLLBACK; SQL sqlite3 library.db <<'SQL' .headers on .mode box SELECT loan_no, member_no FROM loan; SQL ``` ``` ┌─────────┬───────────┐ │ loan_no │ member_no │ ├─────────┼───────────┤ │ 1001 │ 41 │ └─────────┴───────────┘ ``` The second call is a separate process; it inherits nothing from the memory of the process that wrote the data. It sees the committed row and not the rolled-back one. The measure is the transaction's boundary: nothing written before a commit counts as permanent, and everything written after one does. Durability's implementation usually rests on a log: changes are written to a sequential **write-ahead log** before they are applied to the data pages, and that write is secured on disk. On startup after a crash, the log is read; changes that were committed but not yet applied to the pages are completed, and uncommitted ones are rolled back. **The log's format, its name, and the point at which the disk write is secured vary by engine**; some engines let this behavior be tuned, and relaxing it also relaxes the durability guarantee. #### Cost and Limits The four guarantees are not free. Atomicity and durability require extra writes; isolation requires either locking or version storage. Keeping a transaction open longer than necessary increases how much data is locked for how long — a transaction that waits on user input is a common design mistake. Two limits should also be stated. First, ACID applies within a single database; the same guarantee does not come for free in a piece of work that spans two separate systems. Second, the consistency promise is bounded by the constraints **written into the schema**. A business rule not written into the schema can end up broken at the end of a committed transaction, and the engine has no way of knowing it. This is the counterpart, here, of the "write the rule into the schema" principle from the Integrity Constraints lesson. #### Summary - A transaction is a sequence of statements handled as a single unit; its boundary is set by a commit or a rollback. - Atomicity requires that an intermediate state never become permanent; consistency requires that every constraint be satisfied at the end of a transaction. - The engine rejecting a constraint is not enough on its own; a client that ignores the error and sends a commit makes half-finished work permanent. - Isolation is concurrent transactions not seeing each other's half-finished state; its levels and implementation are the subject of a separate course. - Durability is tied to the moment of commit and is usually provided by a write-ahead log; how strict the guarantee is varies by engine and can be tuned. #### Next Step This lesson gave exact names to the promises a relational database makes. That same naming also makes a comparison possible: some systems give up part of these promises and offer other things in return. The course's final lesson takes up that choice — which data and which access pattern require the relational model, when another data model is a better fit, and what criteria the decision is made on? ### Relational vs. Non-Relational Selection Source: https://academia.sh/en/courses/relational-theory/relational-vs-non-relational-selection What non-relational data model families give up and what they give in return, document storage's measured trade-off, the schema flexibility fallacy, and the decision criteria for the choice. The previous lesson named the promises a relational database makes. That same naming makes a comparison possible: some systems give up part of these promises and offer other things in return. This lesson's question is: which data and which access pattern require the relational model, when is another data model a better fit, and what criteria is that decision made on? #### What Gets Given Up The relational model gives four things: declarative querying independent of the data, constraints enforced in the schema, arbitrary joins, and transaction guarantees. Non-relational systems give up part of these. The reason for giving them up is usually one of three: distributing the data across multiple machines, driving a single access pattern down to a very low cost, or serving a domain whose schema is not known in advance. The decision is therefore not the question "which is better." The question is: which of the guarantees being given up does this task actually need? #### Data Model Families A **key-value store** supports a single access pattern: fetch by key, write by key. The inside of the value is meaningless to the system, so it cannot be queried by its contents. It fits work such as session state and caching. A **document store** keeps the value as a structured document and allows querying by the fields inside it. Its natural unit is the **aggregate**: data that is read together and written together sits in a single document. A **wide-column store** partitions rows by key and keeps a sparse, large number of columns per row. It is oriented toward distributing very high write volumes. A **graph database** makes nodes and edges first-class objects. It makes traversing long paths — such as "what else did the members who borrowed this member's books also borrow" — cheaper than a chain of relational joins. A **search index** breaks text into words and builds an inverted index; it returns results ranked by relevance. The catalog's free-text search is an example of this. A **time series store** stores time-stamped measurements with compression and is organized around range queries. These families do not exclude one another; many relational engines offer document handling, text search, and graph querying within themselves. **Which capability is found in which engine varies**, and for that reason the decision is made on requirements, not on a product's name. #### Document Storage's Trade-off The document approach's gain and cost come from the same place: the data has been assembled according to a single read pattern. ```sh sqlite3 :memory: <<'SQL' .headers on .mode box .nullvalue (empty) CREATE TABLE member_document (member_no INTEGER PRIMARY KEY, document TEXT NOT NULL); INSERT INTO member_document VALUES (41, '{"name":"Alice Kane","loans":[{"isbn":"975-01"},{"isbn":"975-02"}]}'), (52, '{"name":"Marcus Reyes","loans":[{"isbn":"975-01"}]}'), (63, '{"loans":[]}'); SELECT member_no, document ->> '$.name' AS name, json_array_length(document, '$.loans') AS loan_count FROM member_document ORDER BY member_no; SELECT o.value ->> '$.isbn' AS isbn, COUNT(*) AS times FROM member_document u, json_each(u.document, '$.loans') o GROUP BY isbn ORDER BY isbn; SQL ``` ``` ┌───────────┬──────────────┬────────────┐ │ member_no │ name │ loan_count │ ├───────────┼──────────────┼────────────┤ │ 41 │ Alice Kane │ 2 │ │ 52 │ Marcus Reyes │ 1 │ │ 63 │ (empty) │ 0 │ └───────────┴──────────────┴────────────┘ ┌────────┬───────┐ │ isbn │ times │ ├────────┼───────┤ │ 975-01 │ 2 │ │ 975-02 │ 1 │ └────────┴───────┘ ``` The name and syntax of the operators that access document fields **vary by engine**; the behavior shown here is the same everywhere. Three results can be read here. The first is the gain: a member's name and loan list come from a single row, with no join at all. The second is the cost: the question "how many times was a given book borrowed" requires opening every document and flattening the arrays inside them. Every question that crosses the document's boundary is expensive. The third is a loss: member `63`'s document has no `name` field at all, and this insert went through without error. Because there is nowhere to write `NOT NULL`, the rule has fallen back onto every program that writes a document. #### The Schema Flexibility Fallacy The document approach's most often cited advantage is schema flexibility: adding a new field requires no schema change. This is true, but it is incompletely stated. The schema does not disappear; it moves. In **schema-on-write**, the structure is defined in the database, and every write must conform to it. In **schema-on-read**, the structure lives in the expectation of the code that reads the document. When a field's name changes or its type diverges, the problem does not disappear; instead of showing up as an error at write time, it shows up as a difference in behavior at read time. Moreover, the store now holds documents in both forms, and the reading code has to handle both. The criterion is this: if the structure is genuinely unpredictable and no constraint is sought on it, flexibility is a gain. If the structure can be known, not writing it into the schema is only deferring validation. #### Scale and Distribution Distributing data across multiple machines — **partitioning** — strains two of the relational model's capabilities. A join between two relations whose parts sit on different machines has to happen over the network. Transaction guarantees require extra coordination and latency once they span multiple machines. Some non-relational systems cheapen distribution by limiting these two capabilities from the start: no join is offered, a transaction is limited to a single aggregate, and a read is not guaranteed to see the most recent write — this is called **eventual consistency**. The point to note here is ordering. Distribution is needed when the data no longer fits on a single machine, or when more requests arrive than a single machine can withstand. Taking on distribution's cost before reaching that threshold is a loss with no return. #### Decision Criteria Five questions settle most of the decision. - **Data shape.** Are there many-to-many relationships between entities, or does the data naturally split into independent aggregates? If many-to-many relationships are dense, the relational model wins. - **Access pattern.** Are the queries known in advance and always by the same key, or will arbitrary questions be defined later? The latter requires declarative querying. - **Constraint requirement.** Are there invariants that span multiple entities? The rule "a copy can be with only one member at a time" is a constraint that spills outside a document's boundary. - **Consistency requirement.** Must a read see the most recent write? It must at the loan desk; it need not for catalog recommendations. - **Schema stability.** Is the structure known and does it rarely change, or is it unpredictable field by field? The answers may not point to a single system. **Polyglot persistence** is using different stores for different jobs: keeping records in a relational engine, free-text search in a search index. Its cost is clear — consistency between the two stores is now the application's responsibility, and that responsibility is the course's first lesson's file problem returning at a larger scale. This is why the common ordering is: the system of record is kept in the relational engine, and secondary stores are derived from it. #### Summary - Non-relational systems give up part of declarative querying, schema constraints, arbitrary joins, and transaction guarantees for distribution or for the cheapness of a single access pattern. - Document storage makes a read within the aggregate join-free; a question that crosses the document's boundary requires opening every document. - Schema flexibility does not eliminate the schema, it moves it into the reading code; validation is not deleted, it is deferred. - Distribution makes joins and transaction guarantees more expensive; distribution taken on before reaching the threshold is cost with no return. - The choice is made on data shape, access pattern, constraint and consistency requirements, and schema stability; if polyglot persistence is used, there must be a single system of record. #### Course Wrap-Up This course began with the problems of keeping data in a file and built, step by step, the relational model's answer to those problems. The relation's definition, coming from set theory, explained why row order does not belong to the data and why a table is required in the schema to behave like a relation. Keys gave the rule for telling rows apart; Integrity Constraints gave that rule's enforcement by the engine. Null Values opened up the silent traps of writing queries by turning the true–false pair into three. Data Types showed that what a column holds is a decision separate from its representation. The second topic formalized the source of repetition. Functional Dependency was defined as the general form of the key concept; Normal Forms eliminated, step by step, dependencies whose left side is not a key. Denormalization tied the decision in the opposite direction to a measure, and Schema Design Patterns gave the established counterparts and anti-patterns for recurring situations. The third topic brought the schema together with workload: the opposite requirements of transactional and analytical workloads, the definition and limits of the ACID guarantees, and when the relational model is the right choice. There is a gap left deliberately open throughout the course. Queries were written in every lesson, but the query language itself was never taught; the parts of the `SELECT` statement, how conditions are written, join types, grouping, and ordering — all of it was used within examples, never defined as a rule. The tools for working with null values, the aggregate functions that summarize loan history, and the details of the statements that change data are waiting in the same way. The SQL Fundamentals course fills that gap. On top of the model built here — relations, keys, constraints, and null values — it systematically builds the language that queries that model. The library schema continues there as well: the tables designed in this course will be the subject of every query written there.