SQL / CAPSTONE PROJECTS
Project: a migration from a spreadsheet to 3NF
Turn a wide spreadsheet export into 3NF tables: audit its hidden dependencies, split them out, and prove the split loses no row.
What you will learn
- Load a spreadsheet export into an all-text staging table before reshaping anything
- Test a dependency with GROUP BY x HAVING COUNT(DISTINCT y) > 1
- Split a wide row into 3NF tables using INSERT ... SELECT DISTINCT and a foreign key
- Prove a decomposition lossless by joining the new tables back to the staging rows
Understanding Project: a migration from a spreadsheet to 3NF
A spreadsheet row is a printed line, not a fact: whatever a reader might want beside a loan gets copied onto the row, so form 9B's tutor is retyped once per pupil and again once per accessory line. The file has no way to say that the tutor name belongs to the form rather than to the pupil, which is exactly why the copies drift - 'Mr Hale' on one row, 'Mr Hail' on another, both equally official. Migrating the sheet is mostly the work of writing those unspoken 'belongs to' statements down as keys, so that a fact has one home and one spelling.
The test for each column is a question you can ask in SQL: if I know form_code, do I already know tutor_name? GROUP BY form_code HAVING COUNT(DISTINCT tutor_name) > 1 answers it over the real data - no rows means the dependency held in this export, rows mean either the dependency is not real or the sheet contradicts itself. When the answer is yes and form_code is not the key of the table the column sits in, tutor_name is a transitive dependency and belongs in a form table keyed on form_code. Repeat until every remaining column depends on the whole key of its own table and on nothing else; that is 3NF.
Order matters during the migration itself. Land the file untouched in a staging table where every column is TEXT and nothing is constrained, so the import cannot fail; audit and clean there; then create the target tables, fill the lookups with INSERT ... SELECT DISTINCT, and fill the fact table last so its foreign keys always find a parent. Finish by proving the split lossless - the join of the new tables must return as many rows as the staging table and must reproduce each one - then hand the flat shape back as a view so the people who liked the sheet lose nothing.
CREATE TABLE sheet_import (
loan_ref TEXT,
pupil_name TEXT,
form_code TEXT,
tutor_name TEXT,
instrument TEXT,
accessory TEXT
);
INSERT INTO sheet_import VALUES
('L-001','Ada Okafor','9B','Mr Hale','Cello 1/2','bow'),
('L-001','Ada Okafor','9B','Mr Hale','Cello 1/2','rosin'),
('L-002','Ben Wu','9B','Mr Hail','Violin 3/4','case'),
('L-003','Cleo Ruiz','10A','Ms Pike','Violin 3/4','bow'),
('L-004','Dev Shah','10A','Ms Pike','Violin 3/4','case');
SELECT form_code,
COUNT(DISTINCT tutor_name) AS distinct_tutors,
MIN(tutor_name) AS spelling_a,
MAX(tutor_name) AS spelling_b
FROM sheet_import
GROUP BY form_code
HAVING COUNT(DISTINCT tutor_name) > 1;A column belongs in another table when some non-key column already determines it, because that value is a fact about the other thing, not about this row's key.
Worked examples
Split out the transitive fact and check nothing was lost
Moves tutor into a form table keyed on form_code, then verifies the two new tables rebuild every original row.
CREATE TABLE sheet (pupil TEXT, form_code TEXT, tutor TEXT);
INSERT INTO sheet VALUES
('Ada Okafor','9B','Mr Hale'),
('Ben Wu','9B','Mr Hale'),
('Cleo Ruiz','10A','Ms Pike');
CREATE TABLE form (form_code TEXT PRIMARY KEY, tutor TEXT NOT NULL);
INSERT INTO form (form_code, tutor)
SELECT DISTINCT form_code, tutor FROM sheet;
CREATE TABLE pupil (
name TEXT PRIMARY KEY,
form_code TEXT NOT NULL REFERENCES form(form_code)
);
INSERT INTO pupil (name, form_code)
SELECT pupil, form_code FROM sheet;
SELECT (SELECT COUNT(*) FROM sheet) AS sheet_rows,
(SELECT COUNT(*) FROM pupil p JOIN form f ON f.form_code = p.form_code) AS rebuilt_rows,
(SELECT COUNT(*) FROM sheet s
LEFT JOIN pupil p ON p.name = s.pupil
LEFT JOIN form f ON f.form_code = p.form_code AND f.tutor = s.tutor
WHERE f.form_code IS NULL) AS unmatched;Example explained
Line 1SELECT DISTINCT form_code, tutor collapses the two 9B lines into one form row, so the tutor name is stored exactly once.
Line 2REFERENCES form(form_code) is what makes the split safe: no pupil can name a form that was never loaded.
Line 3sheet_rows and rebuilt_rows must be equal, otherwise the decomposition dropped rows or multiplied them.
Line 4unmatched counts staging rows the rebuilt join cannot reproduce value for value; anything above 0 means the split altered data.
A repeated date on accessory lines is a partial dependency
Shows how a sheet with one row per accessory lets a single loan carry two different due dates.
CREATE TABLE sheet_rows (loan_ref TEXT, accessory TEXT, date_due TEXT);
INSERT INTO sheet_rows VALUES
('L-001','bow','2026-04-01'),
('L-001','rosin','2026-04-10'),
('L-002','case','2026-04-01');
SELECT loan_ref,
COUNT(DISTINCT date_due) AS due_dates,
MIN(date_due) AS earliest,
MAX(date_due) AS latest
FROM sheet_rows
GROUP BY loan_ref
HAVING COUNT(DISTINCT date_due) > 1;Example explained
Line 1The key of these rows is (loan_ref, accessory), since only the pair identifies a line.
Line 2date_due is determined by loan_ref alone, so it is copied onto every accessory line of that loan.
Line 3COUNT(DISTINCT date_due) > 1 finds loans whose copies already disagree, because someone extended the loan on one line only.
Line 4MIN and MAX print both values so a person can choose; SQL has no way to know which line was edited last.
One UPDATE, and the flat sheet handed back as a view
Demonstrates that after the split a tutor change is a single-row update, while users still read the wide row.
CREATE TABLE form (form_code TEXT PRIMARY KEY, tutor TEXT NOT NULL);
CREATE TABLE pupil (
name TEXT PRIMARY KEY,
form_code TEXT NOT NULL REFERENCES form(form_code)
);
INSERT INTO form VALUES ('9B','Mr Hale'), ('10A','Ms Pike');
INSERT INTO pupil VALUES ('Ada Okafor','9B'), ('Ben Wu','9B'), ('Cleo Ruiz','10A');
CREATE VIEW pupil_sheet AS
SELECT p.name AS pupil, p.form_code, f.tutor
FROM pupil p
JOIN form f ON f.form_code = p.form_code;
UPDATE form SET tutor = 'Ms Adeyemi' WHERE form_code = '9B';
SELECT * FROM pupil_sheet ORDER BY pupil;Example explained
Line 1The view rebuilds the wide spreadsheet row on demand, so no table has to store it.
Line 2The UPDATE touches one row; there is no second copy of the tutor name left behind to contradict it.
Line 3Both 9B pupils change together because neither ever held a tutor value of its own.
Line 4ORDER BY pupil is needed because a join has no defined row order.
Important notes
A dependency that merely happens to hold in one export is not yet a rule - four rows can agree by accident. Confirm with whoever maintains the sheet before keying a table on it.
SQLite only enforces REFERENCES when PRAGMA foreign_keys = ON, so a migration can look clean while orphan rows accumulate; check that your engine really enforces what you drew.
Common mistakes
Importing the file straight into the final typed tables: the first empty cell or stray 'n/a' aborts the load, and the contradictions you needed to see never reach the database where you can query them.
Assuming SELECT DISTINCT form_code, tutor_name gives one row per form: the 'Mr Hail' typo makes two, so the insert fails on the primary key - or, with no key declared, two 9B rows survive and every join through form silently doubles the pupil count.
Widening instead of splitting, with accessory_1, accessory_2, accessory_3 on the loan row: 'which loans include a bow' now has to search three columns, and the fourth accessory has nowhere to go.
Try it yourself
Change, predict, then run
Add an instrument_family column to sheet_import (Cello 1/2 and Violin 3/4 are both Strings), then prove that instrument determines family with a HAVING COUNT(DISTINCT ...) audit. Move family into an instrument table keyed on the model, and check that joining sheet_import back to it still reproduces all five original rows.
Open the SQL workspaceCheck your understanding
A sheet has one row per (loan_ref, accessory) pair and repeats the loan's date_due on each of those rows. Which normal form does that break, and why?
- 1NF, because the accessory values were originally crammed into a single cell
- 2NF, because date_due depends on only part of the composite key (loan_ref, accessory)
- 3NF, because date_due depends on accessory, which in turn depends on loan_ref
- None of them; the repeated dates only waste storage
Show answer
date_due is determined by loan_ref alone, which is a proper subset of the key (loan_ref, accessory), so it is a partial dependency and 2NF is broken - which is why two accessory lines of one loan can carry different dates. Option 0 is tempting because multi-value cells really are a 1NF problem, but the accessories have already been split to one per row, so every cell is atomic; the fault is that the split copied a fact belonging to the loan onto each accessory line.