SQL / DATA MODELLING AND NORMALISATION
Second normal form and partial dependencies
Spot a non-key column that depends on only part of a composite key, and split the table so every non-key column depends on the whole key.
What you will learn
- Recognise a partial dependency: a column determined by only part of a composite key
- Split the key fragment and its dependants into a table keyed by that fragment
- Use GROUP BY with COUNT(DISTINCT) to test whether half a key already fixes a column
- Explain why a single-column primary key makes 2NF automatic
Understanding Second normal form and partial dependencies
Second normal form only has something to say once the key of a table is made of more than one column. If the key is (warehouse_id, product_id), every non-key column is supposed to be a fact about that pair. A column such as warehouse_city is not: you can look its value up from warehouse_id alone and product_id contributes nothing. A dependency on a proper subset of a key is a partial dependency, and a table in first normal form with no partial dependencies is in second normal form.
The price you pay is that one fact is stored once per row that shares the key fragment. If warehouse 1 stocks forty products, the city of warehouse 1 is written forty times, and no constraint can force those forty copies to agree, because uniqueness is declared on the full key (warehouse_id, product_id) and not on warehouse_id. So a single UPDATE can leave the same warehouse in two cities, a warehouse that stocks nothing has nowhere to live, and deleting its last stock row also deletes its address.
The repair is mechanical. Take the key fragment plus everything that depends on the fragment alone, put them in a new table where that fragment is the entire primary key, and leave the fragment behind in the original table as a foreign key. Nothing is lost, because the fragment value was already present in every original row, so a join on it reproduces those rows exactly. The habit to build is to read each column and ask which part of the key you would need in order to look this value up; an answer of "only part of it" means the column is describing a different thing.
placeholder
-- PostgreSQL
CREATE TABLE stock (
warehouse_id integer,
product_id integer,
quantity integer,
warehouse_city text,
PRIMARY KEY (warehouse_id, product_id)
);
INSERT INTO stock VALUES
(1, 100, 40, 'Leeds'),
(1, 200, 15, 'Leeds'),
(2, 100, 8, 'Cardiff');
-- the city is a fact about the warehouse, not about the stock line,
-- so it is stored twice and one copy can be changed on its own
UPDATE stock SET warehouse_city = 'Sheffield'
WHERE warehouse_id = 1 AND product_id = 200;
SELECT warehouse_id,
COUNT(*) AS row_count,
COUNT(DISTINCT warehouse_city) AS city_count
FROM stock
GROUP BY warehouse_id
ORDER BY warehouse_id;A non-key column must be determined by the whole of a candidate key, not by a fragment of a composite one.
Worked examples
Splitting the table into 2NF
Moving the partially dependent column into its own table makes the inconsistent state unreachable.
CREATE TABLE warehouse (
warehouse_id integer PRIMARY KEY,
warehouse_city text NOT NULL
);
CREATE TABLE stock_line (
warehouse_id integer NOT NULL REFERENCES warehouse (warehouse_id),
product_id integer NOT NULL,
quantity integer NOT NULL,
PRIMARY KEY (warehouse_id, product_id)
);
INSERT INTO warehouse VALUES (1, 'Leeds'), (2, 'Cardiff');
INSERT INTO stock_line VALUES (1, 100, 40), (1, 200, 15), (2, 100, 8);
UPDATE warehouse SET warehouse_city = 'Sheffield' WHERE warehouse_id = 1;
SELECT s.warehouse_id, s.product_id, s.quantity, w.warehouse_city
FROM stock_line s
JOIN warehouse w ON w.warehouse_id = s.warehouse_id
ORDER BY s.warehouse_id, s.product_id;Example explained
Line 1warehouse_city now sits in a table whose whole key is warehouse_id, so the fact is stored once.
Line 2stock_line keeps only quantity, the one column that genuinely needs both key columns.
Line 3The UPDATE changes exactly one row, and both stock lines for warehouse 1 read Sheffield afterwards.
Line 4The join rebuilds the original four columns and three rows, which is what makes the split lossless.
Finding the partial dependency with a query
Grouping by each half of the key shows which half already determines the suspect column.
CREATE TABLE enrolment (
student_id integer,
course_id integer,
grade integer,
tutor text,
PRIMARY KEY (student_id, course_id)
);
INSERT INTO enrolment VALUES
(1, 10, 62, 'Okonjo'),
(2, 10, 71, 'Okonjo'),
(1, 20, 55, 'Varga'),
(3, 20, 48, 'Varga');
SELECT 'by course_id' AS grouped_by, MAX(n) AS distinct_tutors
FROM (SELECT course_id, COUNT(DISTINCT tutor) AS n
FROM enrolment GROUP BY course_id) c
UNION ALL
SELECT 'by student_id', MAX(n)
FROM (SELECT student_id, COUNT(DISTINCT tutor) AS n
FROM enrolment GROUP BY student_id) s
ORDER BY 1;Example explained
Line 1The first subquery counts distinct tutors within each course_id; a maximum of 1 means course_id alone fixes the tutor.
Line 2The second branch reaches 2 because student 1 takes courses 10 and 20 with different tutors, so student_id does not determine tutor.
Line 3course_id is half of the primary key, so tutor is partially dependent and belongs in a course table keyed by course_id.
Line 4grade stays where it is: its value changes with the student and with the course, so it needs the whole key.
Important notes
2NF is judged against every candidate key, not only the one you declared PRIMARY KEY, so a column determined by part of an alternate composite key is still partially dependent.
Adding a surrogate id column does not remove the problem: (warehouse_id, product_id) remains a candidate key, warehouse_city still depends on half of it, and the city is still repeated once per stock line.
Common mistakes
Hunting for partial dependencies in a table with a single-column primary key and splitting it anyway; with a one-column key nothing can depend on part of it, and the repetition they noticed is a transitive dependency instead, so the extra join buys nothing.
Deciding the dependency from the sample rows: every order line for a product currently shows the same unit_price, so unit_price is moved into the product table, and afterwards changing a product price silently rewrites the totals on invoices that were already sent.
Moving warehouse_city into a new table but also dropping warehouse_id from the stock table, which leaves no column to join on and no way to say which warehouse a stock line belongs to.
Try it yourself
Change, predict, then run
Create invoice_line(invoice_id, line_no, quantity, customer_name) with PRIMARY KEY (invoice_id, line_no) and insert five rows across two invoices. Run a GROUP BY invoice_id with COUNT(DISTINCT customer_name) to show that invoice_id alone fixes the customer, then split the table in two and join them back to the original five rows.
Open the SQL workspaceCheck your understanding
team_member(team_id, person_id, role, team_founded_year) has PRIMARY KEY (team_id, person_id). Which statement is correct?
- The table is in 2NF because both team_id and person_id are needed to identify a row
- role breaks 2NF because the same role value repeats across many rows
- team_founded_year depends on team_id alone, so the table breaks 2NF and the year belongs in a team table
- Adding a surrogate member_id as the primary key puts the table in 2NF and removes the repetition
Show answer
team_founded_year is a fact about the team, so team_id by itself determines it, and that is a dependency on part of the key. Option 0 is tempting because it is true that both columns are needed to identify a row, but 2NF asks what each non-key column depends on, not how rows are identified. Repeated values, as in option 1, are a symptom rather than the test: role really does vary with both team and person.