Repeated rows are not the same problem as repeated facts. A customer may place ten orders, and each order may contain ten lines. Repeating the customer's name on every line stores the same customer fact many times. A rename then requires every copy to change; missing one update creates conflicting answers. Deleting the last line might also erase the only record of that customer. These are update and deletion anomalies.
A functional dependency X → Y means that, in every permitted state, rows agreeing on X agree on Y. It does not mean Y identifies X. Two SKUs may have the same product name. A sample can refute a proposed dependency by showing conflicting values, but an agreeable sample cannot establish the rule for all future data. The build supplies the domain rules explicitly.
Our source has line identity (order_id,line_no). Customer ID determines customer name; SKU determines product name; order ID determines customer ID. Quantity and purchase-time unit price belong to the full line identity. The dependency diagram tells us where each fact should live.
First normal form models one value per attribute; a comma-separated list of SKUs hides relationships inside text. Second normal form removes dependencies of nonprime attributes on only part of a candidate key. Third normal form requires every nontrivial dependency X → A to have X as a superkey or A as a prime attribute (part of some candidate key). In the simple single-key cases here, moving non-key-to-non-key dependencies into their own tables removes the familiar transitive anomalies. BCNF strengthens the determinant condition: every nontrivial determinant must be a superkey. This course applies these ideas to a stated schema; the Berkeley reading develops the general algorithms.
A decomposition must be lossless: joining the pieces on their identifying columns should recover the original facts without inventing combinations. Merely splitting columns into smaller tables does not guarantee that. Keeping the customer ID, order ID and SKU references gives each line an unambiguous route back to its facts. Dependency preservation is another consideration: can the original rules be enforced on the pieces without a cross-table validation query?
CREATE TABLE customers(id INTEGER PRIMARY KEY,name TEXT NOT NULL);
CREATE TABLE orders(id INTEGER PRIMARY KEY,customer_id INTEGER NOT NULL REFERENCES customers(id));
INSERT INTO customers VALUES(7,'Lee'),(9,'Lee');
INSERT INTO orders VALUES(100,7),(200,9),(300,7);
UPDATE customers SET name='L. Lee' WHERE id=7;
SELECT o.id,c.id AS customer_id,c.name FROM orders o
JOIN customers c ON c.id=o.customer_id ORDER BY o.id;Normalization follows meaning. A current catalog price and the price charged on a past line describe different facts. Replacing every line price with today's product price would corrupt history even if it reduced the number of price columns. Repeated SKUs at different prices in one order are deliberately valid fixtures.
The build loads distinct parent facts before child lines, then verifies exact values and reconstructs the source through joins. Empty inputs still create the complete schema. In the bugs and fill, pay attention to the whole line key and to which rows may be deduplicated. DISTINCT is appropriate for repeated identical parent facts; applying it to line events can silently delete legitimate history.