Data & spreadsheets · course 40 of 105

Database Design From Keys to BCNF

Tell what’s wrong with a table by working it out: functional dependencies, closures, every candidate key, and the highest normal form a table reaches, from 1NF up to BCNF.

4 modules

Try a question

Not on sale yet. When sales open: $4.99 USD for this course, paid once, or every course for $9.99 USD a month. Prices

Runs in Claude (claude.ai, the Claude apps or Claude Code) with your own Claude account. A course opens there as soon as your payment goes through; nothing is shipped.

Preview

What it teaches

How to tell what is wrong with a small table, by working it out. A functional dependency (X → Y: two rows with the same X values always have the same Y values) and how to check one against a table; the closure of a set of columns (everything those columns pin down, found by chaining dependencies); every candidate key of a small table (the smallest sets of columns that identify a row), plus superkeys (any set of columns that identifies a row) and prime attributes (columns that sit in some candidate key); and the highest normal form of a table (normal forms are named levels of well-designed: 1NF, 2NF, 3NF, then BCNF) with the dependency that stops it reaching the next one. The AI asks 3 questions, places you, shows a short card per module, then gives you one question at a time.

Not a SQL course, and it can’t judge your own schema.

How long

38 items. About 12-25 rounds for a confident student up to about 55-75 for a beginner, plus a 12-item final: 1-2 sessions (estimate; simulated students took 16 and 69 rounds; not timed with a real student). The tutor gives a one-line save code at the end of each session.

From the answer key · M1, row F3

Columns: Student, Course, Grade. Row 1: Ana, DB, A. Row 2: Ana, OS, B. Row 3: Ben, DB, A. Row 4: Ana, DB, C. In this table, does {Student, Course} → Grade hold? If not, name two rows that break it.

Think first, then show the answer

AnswerNo: rows 1 and 4

WorkingGroup the rows by Student, Course: Ana, DB (rows 1, 4): A and C; Ana, OS (row 2): B; Ben, DB (row 3): A; rows 1 and 4 agree on Student, Course but differ on Grade, so it breaks.

The wrong answer it catchesNo: rows 1 and 2 → rows 1 and 2 have different Courses, so {Student, Course} → Grade says nothing about them; the break is rows 1 and 4 (same Student and Course, different Grades).

The general rule: “two tuples sharing the same values of X will necessarily have the same values of Y.” (A tuple is a row.) So X → Y holds in a table when no two rows agree on X but differ on Y; one such pair of rows breaks it.

The modules

4 modules, drilled one item at a time.

M0
The words
The key words: functional dependency, superkey, candidate key, primary and foreign key, prime attributes, normal forms
M1
Functional dependencies
Does a dependency hold in a table, find a closure, does one set of columns determine another
M2
Candidate keys
Find every candidate key, tell a candidate key from a superkey, name the prime attributes
M3
Normal forms
Check 1NF, then name a table’s highest normal form (up to BCNF) and the dependency that stops the next one

Before it teaches, it asks

1
What do you do with databases now (design tables at work, study for a course, or nothing yet), and have you written SQL or built a spreadsheet with more than one sheet?
2
R(A, B, C, D, E) has the dependencies A → B; B → C; D → E. What is the closure of {A}?
3
R(A, B, C, D) has the dependencies AB → C; C → D. List every candidate key.

What’s exact, and what isn’t

Show

Straight from the course’s own notes.

Words and rules (M0, and the quoted lines on every card): Quoted word for word from ten Wikipedia pages (functional dependency, superkey, candidate key, primary key, foreign key, first, second and third normal form, Boyce–Codd normal form, database normalization; text under CC BY-SA 4.0), read 2026-10-13. A script checks all 40 quotes on saved copies of the pages and fails if a page changes; it does not re-fetch the live pages. M0's Answers are built from those quotes; on the cards, only the text inside quote marks is quoted, and the plain sentences around it (and the method lines) are the course’s own.
Computed by script (every M1, M2 and M3 Key, and every Working): Closures two ways (repeat-until-nothing-new, and by trying every two-row table over {0,1} against the page’s definition of a dependency); candidate keys two ways (every attribute subset, and the algorithm printed on Wikipedia’s Candidate key page); normal forms by applying the definitions to every dependency the listed ones imply, then comparing with the listed-only shortcut. 400 random small cases are cross-checked the same way. Each of the 28 wrong-answer rows is the output of a coded wrong method or a fact checked by script, and none equals its key.
Checked against the pages' own examples: A script reproduces the closure example and the Lectures table on the Functional dependency page, and what the other pages say about their examples: Toothbrush (violates 2NF), Tournament (fails 3NF), Nearest shops (in 3NF, not BCNF) and court bookings (the page’s four candidate keys; in 2NF and 3NF, not BCNF). M3 uses those relations in four of its questions (one of them shortened).
Honest limits: Small made-up tables only (up to 5 columns); it drills textbook rules, not whether a real database should be normalised. Textbooks word 2NF and 3NF in more than one way: this course uses “no partial dependency of a non-prime attribute on a candidate key” for 2NF and “every non-trivial dependency begins with a superkey or ends with a prime attribute” for 3NF. Whether a cell is “atomic” is debated; the course only tests a cell that lists several separate values (like “Maths, Art”). Wikipedia is not a textbook: its Database normalization page carries a banner asking for an expert’s attention, the BCNF page section that holds the court bookings example is marked as disputed (a script recomputes that example instead of trusting it), and its Third normal form page marks the sentence introducing Zaniolo’s definition as needing a quotation. That is why every M1, M2 and M3 key is computed instead of copied.
Not in it yet: 4NF, 5NF and multivalued dependencies, finding a minimal cover, decomposing a table, SQL, foreign-key design, entity-relationship diagrams, tables of 6 or more columns.