What do 1NF, 2NF and 3NF each require, and what does each one fix?
Each of the three normal forms removes one kind of repetition, and each one builds on the one before it.
The first normal form says that every value in a cell is one single value, and that there are no repeating groups of columns. So there is no column that holds a list, and no set of columns that repeats.
The second normal form adds that every column that is not part of the key depends on the whole key. This only matters when the key is made of more than one column. If the key is the pair of a room and a night, and the guest name depends only on the room, then the name belongs in a table of its own.
The third normal form adds that no column that is not part of the key depends on another column that is not part of the key. That is the transitive one. If the customer name depends on the customer number, and the customer city also depends on the customer number, then the city does not belong with the name.
-- 1NF: atomic values, one row per order line.
CREATE TABLE order_items_1nf (
order_id INTEGER NOT NULL,
line_no INTEGER NOT NULL,
product_name VARCHAR(120) NOT NULL,
quantity INTEGER NOT NULL,
PRIMARY KEY (order_id, line_no)
);
So what does each one fix? The first one stops you having a comma separated list in a cell, which nothing can query properly. The second one stops you having a value that only knows part of the key. The third one stops you having a value that could be derived from another value in the same row, which is where most of the duplicated data comes from.
The next question in this topic walks one table all the way up.