The anomaly lab

Each normal form removes one class of dependency. The plain-English version is short enough to hold in your head:

• 1NF - one value per cell. No comma-separated lists, no repeating groups (product_1, product_2, product_3), no arrays pretending to be strings. If you have to split() a column to query it, you are not in 1NF.
• 2NF - no partial dependency on a composite key. If the key is (order_id, product_id), then anything that depends on only part of that key belongs elsewhere. Order date depends on order_id alone, so it belongs on the order, not the line.
• 3NF - no transitive dependency. A non-key column must not depend on another non-key column. Product category depends on product name, not on the order line - so it belongs in a products table.

The shorthand every practitioner ends up with: every fact depends on the key, the whole key, and nothing but the key. The lab below is the same idea with the definitions removed and the failures left in.

Bazaar's order_items table stores unit_price even though products already has list_price, and it stores merchant_id even though products already knows which merchant owns each product. Both look like textbook 3NF violations. Neither is a mistake.

• Point-in-time capture, not redundancy. unit_price is the price actually charged on that line, on that day. The product's list price will change; what you charged in April must not. These are two different facts that share a word - exactly the case session b1 flagged.
• The same logic covers merchant_id: a product can be transferred between merchants, and the sale still belongs to whoever sold it at the time.
• How to tell the difference: ask whether the two copies are ever allowed to differ. If they must always agree, it is redundancy - normalize it. If they can legitimately differ over time, they are different facts - store both, and name them so the difference is visible (list_price vs unit_price, not price and price).

This is why the lab below does not flag those two columns. Normalization is a tool for removing accidental duplication, not a rule that forbids deliberate history.

Bazaar's real schema is already normalized, so we recreate the flat shape first, then break it. Each run starts from a fresh database, so this is safe.

CREATE TABLE wide AS
SELECT oi.order_id, oi.line_no, p.product_name, p.category, oi.unit_price
FROM order_items oi JOIN products p ON p.product_id = oi.product_id;

-- a well-meaning price correction that only finds one row
UPDATE wide SET unit_price = 149.00
WHERE product_name = 'Studio Headphones' AND order_id = (
  SELECT MIN(order_id) FROM wide WHERE product_name = 'Studio Headphones');

SELECT product_name,
       COUNT(DISTINCT unit_price) AS distinct_prices_stored,
       COUNT(*)                   AS rows_storing_a_price
FROM wide
WHERE product_name = 'Studio Headphones'
GROUP BY product_name;

Two distinct prices for one product, and nothing in the database says which is correct. In the normalized schema this update touches exactly one row in products and the question cannot arise.

UPDATE products SET list_price = 149.00 WHERE product_name = 'Studio Headphones';

SELECT product_name, COUNT(*) AS rows_storing_a_price,
       COUNT(DISTINCT list_price) AS distinct_prices_stored
FROM products
WHERE product_name = 'Studio Headphones'
GROUP BY product_name;

One row, one price. That is the entire benefit of normalization stated as a query result.

No SQL. Bazaar's source schema has no comma-separated columns, because it was designed rather than accreted. Almost every real schema has at least one: a tags column holding "sale,clearance,final", a sizes column holding "S/M/L", an address column holding a whole postal address.

Find one in a schema you work with. Then write the query someone would need to answer "how many products are tagged clearance" against it, and notice that it involves LIKE '%clearance%' - which also matches "not-clearance". That is what a 1NF violation costs: every query about that column becomes a string-matching guess.