One big table
A small shop keeps every order in one flat table: the order number, the customer, their city, the item and its price. Order 3 bought two things, so its cells hold lists: mug,tea and 8,4.
First normal form (1NF) says each cell holds one value. Order 3 becomes two rows, one per item. Now no single column picks out a row, but the pair (ord, item) does. That pair is the key.
Look at what repeats. Ana lives in Leeds, and the table says so three times. Tea costs 4, said twice. Every copy is a chance for the copies to disagree. Storing a fact more than once is called redundancy.
Ana moves to Hull. This UPDATE changes the row for order 1 only, so the rows for order 3 still say Leeds. Ask where Ana lives and the table gives two answers. This is an update anomaly.
Split by dependency
Put Leeds back. Now ask what decides what. An order number decides its customer, a customer decides their city, an item decides its price. These are functional dependencies, written ord → cust: one ord never has two custs.
Second normal form (2NF): every other column must depend on the whole key, not part of it. price needs only item, and cust and city need only ord. So they move to products and orders; items keeps the key.
Third normal form (3NF): no non-key column may depend on another non-key column. In orders, city depends on cust, not on the order itself. So city moves to customers, stored once per customer. Ana’s city, stored twice, is now stored once.
The tables stay linked by shared columns. Foreign keys make the database check them: orders.cust must match a customer, items.ord an order, items.item a product. An order for Cat, who is not a customer, is refused.
The payoff
Ana moves to Hull again. Now one UPDATE changes one row in customers, and every order of hers agrees at once, because her city is stored in just one place. No copies left to forget.
Need the flat view? A query joins the four tables on their shared columns and rebuilds every row: the same 4 rows, now all saying Hull for Ana. Splitting lost nothing; it is a lossless decomposition.
What happens
;; step 1/10 · flat table flat (as the shop first keeps it) ord cust city item price 1 Ana Leeds mug 8 2 Ben York tea 4 3 Ana Leeds mug,tea 8,4 order 3 crams two items into one cell, and two prices into another. to find who bought tea, every program must split those lists itself.
Real tables give each customer a generated id as the key, because two customers can share a name. Names keep this example short. Stricter forms exist beyond 3NF: Boyce–Codd normal form (BCNF), 4NF and 5NF. Most designs aim for 3NF or BCNF; the four tables here already meet BCNF.