A3.2.6 · Database design

Normalizing a database to 3NF

A3.2.4 taught the key choices, A3.2.5 taught the dependency vocabulary, this statement is where both get used together to actually build a normalized database, for real, from a messy starting table.

Core for SL & HL Command term: construct Builds on A3.2.4 & A3.2.5

Explainer

A warehouse spreadsheet lists every stock order with the supplier's name and phone number copied onto every single line for that supplier. Change the supplier's phone number, and it has to change everywhere at once, or the database quietly starts disagreeing with itself. Normalizing to 3NF is the actual construction process that prevents this, not just a definition to recite, a repeatable procedure applied to a real starting table.

Constructing a database normalized to 3NF means starting from an unnormalized table (or a described real-world scenario) and applying each normal form's requirement in order, splitting off attributes into new tables whenever a dependency rule is broken, until every remaining table satisfies 1NF, 2NF and 3NF at once.

The procedure, in order

Reach 1NF: give the table a primary key, make every field atomic, and move any repeating group of values into its own linked table.

Reach 2NF: find any non-key attribute that depends on only PART of a composite primary key, and move it into a new table keyed by that part.

Reach 3NF: pick out any non-key attribute whose real determinant is a different non-key attribute rather than the key itself, and give it its own table, linked back through the attribute it actually depends on.

This exact procedure works the same way whatever the real-world scenario, a library system, a hospital, an e-commerce platform, a school, an employee register, an inventory system, a police reporting system, only the entities and fields change. Two different scenarios are worked through fully below.

Worked example 1

An inventory system, normalized step by step

STOCK_ORDER tracks what was ordered, from whom, and in what quantity. Click through and watch it get split apart, one violation at a time.

Step 1 of 5 Unnormalized

Worked example 2

The same procedure, a police crime-reporting system

Different domain entirely, same three-step procedure. CRIME_REPORT logs which officers were assigned to which incident.

Unnormalized: CRIME_REPORT

ReportID	OfficerID	OfficerName	StationID	StationName	CrimeType	CrimeDate
R101	O5	Olivia Hughes	ST2	Riverside Station	Burglary	2027-01-14
R101	O8	Malik Osei	ST3	Central Station	Burglary	2027-01-14
R102	O5	Olivia Hughes	ST2	Riverside Station	Vandalism	2027-02-02

Primary key: (ReportID, OfficerID), since one report can involve several officers, and one officer works several reports. CrimeType and CrimeDate depend only on ReportID (partial dependency), OfficerName depends only on OfficerID (partial dependency, the OTHER direction on the same composite key), and StationName depends on StationID, which itself depends on OfficerID, not the primary key (transitive dependency).

Normalized to 3NF: four tables

ReportID	CrimeType	CrimeDate
R101	Burglary	2027-01-14
R102	Vandalism	2027-02-02

REPORT: fixes the partial dependency on ReportID.

ReportID	OfficerID
R101	O5
R101	O8
R102	O5

REPORT_OFFICER: the linking table for the many-to-many.

OfficerID	OfficerName	StationID
O5	Olivia Hughes	ST2
O8	Malik Osei	ST3

OFFICER: fixes the partial dependency on OfficerID, StationID stays as a foreign key.

StationID	StationName
ST2	Riverside Station
ST3	Central Station

STATION: fixes the transitive dependency, StationName no longer depends on OfficerID at all.

Same three-step procedure as the inventory example, entirely different domain, entirely different tables, the METHOD is what transfers, not the specific entities.

Check your understanding

In the inventory example, why does SUPPLIER end up as its own separate table?

Common mistake

Markers reported this pattern for A3.2.6

RecurringWhen candidates split a violating attribute into its own new table, they often forget to leave a foreign key behind in the original table. The split fixes the dependency problem, but without the foreign key, the relationship between the two tables is lost entirely, there's no way to tell which SUPPLIER a PRODUCT actually came from anymore.

What a weak answer looks like: A student splits SupplierName and SupplierPhone into a new SUPPLIER table, but PRODUCT is left with no SupplierID at all, only ProductID and ProductName.

✓

What it should say: PRODUCT keeps SupplierID as a foreign key referencing SUPPLIER's primary key, even after SupplierName and SupplierPhone have moved out. The split removes the transitive dependency, it must not also remove the relationship itself.

Revision questions

Past-paper style. Write an answer, then reveal the marking points and mark yourself.

Question 1 · 2 marks

[2]The unnormalized inventory table above holds several supplier names in one field. State which normal form this breaks and what must change to satisfy it.

Question 2 · 4 marks

[4]An unnormalized HOSPITAL_APPOINTMENT table has the fields (PatientID, PatientName, DoctorID, DoctorName, DoctorDepartment, AppointmentDate). PatientName depends only on PatientID, and DoctorDepartment depends on DoctorID rather than on the primary key. Identify the type of dependency for each case, and state which normal form each violates.

Question 3 · 6 marks

[6]Construct a database normalized to 3NF for a school's library system, where LOAN(StudentID, StudentName, BookID, BookTitle, BookAuthor, DueDate) is the unnormalized starting table. Show the resulting tables and their primary and foreign keys.

Flashcards

Active recall for this statement's key terms.