↑ 4 - Normal Forms: 1NF, 2NF, and 3NF

Applying Normal Forms: End-to-End Practice

1

Applying Normal Forms: End-to-End Practice

▶

Database normalization is not a collection of isolated techniques — it is a sequential, cumulative process in which each step builds directly on the last. Understanding any single normal form in isolation is useful, but the real skill lies in applying all of them together: starting with a messy, real-world table full of redundancy and anomalies, and methodically transforming it into a clean, dependency-correct relational schema. This end-to-end walkthrough does exactly that. A single realistic unnormalized table will be carried all the way through First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF), with every decision explained in detail so that the reasoning — not just the mechanics — is fully clear.

To make the journey concrete, consider a business scenario: a small company records its customer orders in a single spreadsheet-style table. That table looks like this:

OrderID CustomerID CustomerName CustomerCity CustomerZip Products Salesperson SalespersonRegion
1001 C01 Acme Corp Denver 80201 Widget (×2, $10), Gadget (×1, $25) Sarah Lee West
1002 C02 Globex Austin 73301 Gadget (×3, $25) Tom Ray South
1003 C01 Acme Corp Denver 80201 Widget (×5, $10), Sprocket (×2, $15) Sarah Lee West

Starting Point: Recognizing an Unnormalized Table

Before touching a single column, the first discipline is documentation and diagnosis. You must inventory exactly what is wrong with the table before deciding how to fix it. Attempting to normalize without a clear baseline leads to inconsistent transformations and the risk of losing data or relationships in the restructuring process.

The table above exhibits several classic problems that define an unnormalized relation:

  • Repeating groups / multi-valued cells: The Products column stores multiple product names, quantities, and unit prices all crammed into one cell as a comma-separated list. A single cell holding Widget (×2, $10), Gadget (×1, $25) is not atomic — it is a miniature table inside a cell. Relational databases cannot query or index into that structure reliably.
  • Redundant descriptive data: CustomerName, CustomerCity, and CustomerZip repeat every time the same customer places another order. If Acme Corp moves to a new city, every row for that customer must be updated — miss one and the database becomes inconsistent. This is an update anomaly.
  • Deletion anomalies: If order 1002 is deleted because Globex cancelled it, all knowledge that Globex exists and is based in Austin disappears from the database entirely.
  • Insertion anomalies: A new salesperson cannot be recorded until they are assigned to an order. There is no place to store a salesperson's region independently.
  • No clear primary key: OrderID alone almost works, but because one order can contain multiple products, it does not uniquely identify a row once we expand the repeating group.

Document these findings explicitly before proceeding. The original column list is: OrderID, CustomerID, CustomerName, CustomerCity, CustomerZip, Products (multi-valued), Salesperson, SalespersonRegion. The anomalies are: multi-valued Products cell, customer data repeated per order, and salesperson region repeated per salesperson appearance. This baseline will serve as the reference point for every transformation that follows.

Step 1 — Transforming to First Normal Form (1NF)

The fundamental requirement of 1NF is atomicity: every cell must hold exactly one value, and every row must be uniquely identifiable by a primary key. The transformation from the unnormalized table to 1NF involves two actions: expanding the multi-valued column into separate rows, and establishing a composite primary key that uniquely identifies each resulting row.

The multi-valued Products cell must be decomposed. Each product mentioned in an order becomes its own row. The hidden attributes inside that cell — product name, quantity ordered, and unit price — are separated into their own dedicated columns. Once expanded, OrderID alone no longer uniquely identifies a row (the same OrderID now spans multiple rows, one per product). The solution is a composite primary key of (OrderID, ProductID), where ProductID is a stable identifier assigned to each product.

The resulting 1NF table looks like this:

OrderID (PK) ProductID (PK) CustomerID CustomerName CustomerCity CustomerZip ProductName UnitPrice Quantity Salesperson SalespersonRegion
1001 P01 C01 Acme Corp Denver 80201 Widget 10.00 2 Sarah Lee West
1001 P02 C01 Acme Corp Denver 80201 Gadget 25.00 1 Sarah Lee West
1002 P02 C02 Globex Austin 73301 Gadget 25.00 3 Tom Ray South
1003 P01 C01 Acme Corp Denver 80201 Widget 10.00 5 Sarah Lee West
1003 P03 C01 Acme Corp Denver 80201 Sprocket 15.00 2 Sarah Lee West

The table is now in 1NF. Every cell holds a single atomic value, and the composite key (OrderID, ProductID) uniquely identifies every row. However, looking at the data reveals that redundancy has increased — customer details and salesperson details now repeat even more than before, once for every product on an order. This is expected and correct: 1NF is a necessary foundation, not a finished product. The redundancy will be systematically eliminated in the next steps.

Step 2 — Identifying Partial Dependencies Before 2NF

Second Normal Form applies only to tables with a composite primary key. The 1NF table has the composite key (OrderID, ProductID). A partial dependency exists when a non-key attribute is functionally determined by part of that composite key rather than the whole key together.

The diagnostic step is to take every non-key column and ask: "To know this value, do I need both OrderID AND ProductID, or just one of them?" This question surfaces the structure of every functional dependency in the table.

Non-Key Attribute Determined By Dependency Type
Quantity OrderID + ProductID (full key) Full dependency — stays in original table
CustomerID OrderID only Partial dependency on OrderID
CustomerName OrderID only (via CustomerID) Partial dependency on OrderID
CustomerCity OrderID only (via CustomerID) Partial dependency on OrderID
CustomerZip OrderID only (via CustomerID) Partial dependency on OrderID
Salesperson OrderID only Partial dependency on OrderID
SalespersonRegion OrderID only (via Salesperson) Partial dependency on OrderID
ProductName ProductID only Partial dependency on ProductID
UnitPrice ProductID only Partial dependency on ProductID

Only Quantity is fully dependent on the composite key — it requires knowing both which order and which product to determine how many units were ordered. All other non-key attributes depend on only one part of the composite key. These partial dependencies are the problem that 2NF will resolve.

Identifying them explicitly before making structural changes is important. Writing them down in a table like the one above makes the decomposition decisions obvious and auditable. It also prevents the common mistake of accidentally leaving a partial dependency in one of the new tables.

Step 2 — Transforming to Second Normal Form (2NF)

The rule for 2NF transformation is mechanical once the dependencies are mapped: each partial dependency determinant becomes the primary key of a new table, and all attributes it determines move into that table. The original table retains only those non-key attributes with full dependencies on the composite key, plus the composite key itself (which doubles as the foreign key linkage mechanism).

From the dependency analysis, three groups emerge:

  • Determined by OrderID alone: CustomerID, CustomerName, CustomerCity, CustomerZip, Salesperson, SalespersonRegion → these move to an Orders table with OrderID as the primary key.
  • Determined by ProductID alone: ProductName, UnitPrice → these move to a Products table with ProductID as the primary key.
  • Determined by the full (OrderID, ProductID) composite key: Quantity → this stays in the intersection table, now called OrderItems.

The resulting 2NF schema consists of three tables:

Orders table (primary key: OrderID):

OrderID (PK) CustomerID CustomerName CustomerCity CustomerZip Salesperson SalespersonRegion
1001 C01 Acme Corp Denver 80201 Sarah Lee West
1002 C02 Globex Austin 73301 Tom Ray South
1003 C01 Acme Corp Denver 80201 Sarah Lee West

Products table (primary key: ProductID):

ProductID (PK) ProductName UnitPrice
P01 Widget 10.00
P02 Gadget 25.00
P03 Sprocket 15.00

OrderItems table (composite primary key: OrderID + ProductID):

OrderID (PK, FK) ProductID (PK, FK) Quantity
1001 P01 2
1001 P02 1
1002 P02 3
1003 P01 5
1003 P03 2

Notice the improvement immediately: Widget's unit price of $10.00 now appears in exactly one place. If the price changes, one row in the Products table is updated — no risk of inconsistency. The same applies to Acme Corp's city. The foreign key relationships (OrderItems.OrderID → Orders.OrderID and OrderItems.ProductID → Products.ProductID) preserve all original data relationships without any loss. Any query that could be answered from the 1NF table can still be answered by joining these three tables.

However, the Orders table still has redundancy. Look at CustomerName, CustomerCity, and CustomerZip repeating across orders 1001 and 1003 (both for Acme Corp). And SalespersonRegion repeats every time Sarah Lee appears. These are not partial dependencies — they are a different kind of problem called transitive dependencies, which 3NF addresses.

Step 3 — Identifying Transitive Dependencies Before 3NF

A transitive dependency occurs when a non-key attribute determines another non-key attribute. More precisely: if the primary key determines attribute A, and A determines attribute B, then B depends on the key only indirectly — transitively through A. The presence of B in the same table as A causes the same kinds of update, deletion, and insertion anomalies seen earlier.

Focus on the Orders table, which now has a simple primary key (OrderID). The non-key attributes are: CustomerID, CustomerName, CustomerCity, CustomerZip, Salesperson, SalespersonRegion. The question to ask for each pair of non-key attributes is: "Does one of these determine the other?"

Functional Dependency Analysis
OrderID → CustomerID Direct — each order belongs to one customer. Not transitive.
CustomerID → CustomerName, CustomerCity, CustomerZip Transitive: CustomerID (a non-key) determines three other non-key attributes. Knowing the CustomerID tells you the name, city, and zip — the OrderID is not needed.
OrderID → Salesperson Direct — each order is assigned to one salesperson. Not transitive.
Salesperson → SalespersonRegion Transitive: Salesperson (a non-key) determines SalespersonRegion. Sarah Lee is always in the West regardless of which order we look at.

Two transitive dependency chains are identified:

  • Chain 1: OrderID → CustomerID → {CustomerName, CustomerCity, CustomerZip}
  • Chain 2: OrderID → Salesperson → SalespersonRegion

A practical signal to watch for: whenever you notice a set of attributes that always travel together — the same city and zip always appear alongside the same CustomerID, or the same region always appears alongside the same salesperson name — you are almost certainly looking at a transitive dependency. These "lookup-style" attributes are the most common real-world manifestation.

It is worth noting that the Products table (ProductID → ProductName, UnitPrice) has no transitive dependencies; both ProductName and UnitPrice depend directly on ProductID and not on each other. The OrderItems table has only one non-key attribute (Quantity), so there are no non-key-to-non-key dependencies possible. Only the Orders table needs further decomposition.

Step 3 — Transforming to Third Normal Form (3NF)

The 3NF transformation follows the same logic as 2NF: the transitive determinant becomes the primary key of a new table, and all attributes it determines move into that table. In the original table, the transitive determinant is replaced by a foreign key referencing the new table.

For the two transitive chains in the Orders table:

  • CustomerID → {CustomerName, CustomerCity, CustomerZip}: Create a Customers table with CustomerID as the primary key. Move CustomerName, CustomerCity, and CustomerZip into it. The Orders table retains CustomerID as a foreign key.
  • Salesperson → SalespersonRegion: Create a Salespersons table with Salesperson as the primary key (or better, introduce a SalespersonID). Move SalespersonRegion into it. The Orders table retains the salesperson reference as a foreign key.

For clarity, a SalespersonID surrogate key is introduced. The final 3NF schema has five tables:

Customers table (primary key: CustomerID):

CustomerID (PK) CustomerName CustomerCity CustomerZip
C01 Acme Corp Denver 80201
C02 Globex Austin 73301

Salespersons table (primary key: SalespersonID):

SalespersonID (PK) SalespersonName Region
S01 Sarah Lee West
S02 Tom Ray South

Orders table (primary key: OrderID — now fully in 3NF):

OrderID (PK) CustomerID (FK) SalespersonID (FK)
1001 C01 S01
1002 C02 S02
1003 C01 S01

Products table (unchanged from 2NF):

ProductID (PK) ProductName UnitPrice
P01 Widget 10.00
P02 Gadget 25.00
P03 Sprocket 15.00

OrderItems table (unchanged from 2NF):

OrderID (PK, FK) ProductID (PK, FK) Quantity
1001 P01 2
1001 P02 1
1002 P02 3
1003 P01 5
1003 P03 2

Now every non-key attribute in every table depends on the primary key of that table, and only on the primary key. The classic mnemonic captures the requirement perfectly: "The key, the whole key, and nothing but the key." If Sarah Lee transfers to the East region, exactly one row in the Salespersons table is updated. If Acme Corp moves to Boulder, exactly one row in the Customers table changes. No anomalies, no inconsistency risk.

Validating the Final Schema and Reviewing the Full Journey

Completing the normalization is only part of the work. A rigorous validation pass over the finished schema confirms that every transformation was correct and that no information was accidentally lost or corrupted. The validation has four distinct checks.

1NF Verification: Examine every column in every table and confirm that all values are atomic — no lists, no sets, no comma-separated values, no repeating groups hidden inside a single field. Then confirm that every table has a declared primary key. In our schema: Customers (CustomerID), Salespersons (SalespersonID), Orders (OrderID), Products (ProductID), OrderItems (OrderID + ProductID). All columns hold atomic values. 1NF confirmed.

2NF Verification: For every table that has a composite primary key, confirm that every non-key attribute depends on the entire composite key, not just part of it. Only OrderItems has a composite key. Its only non-key attribute is Quantity, which requires knowing both OrderID and ProductID to determine. A Quantity of 2 means nothing without knowing both the order and the product it refers to. 2NF confirmed.

3NF Verification: In every table, confirm that no non-key attribute determines any other non-key attribute. Work through each table systematically:

  • Customers: Does CustomerName determine CustomerCity or CustomerZip? No — two companies can share a city, and a city name certainly does not determine a zip code in general. Each attribute describes the customer independently.
  • Salespersons: Does SalespersonName determine Region? That was exactly the transitive dependency we removed by creating this table. Now Region depends on SalespersonID (the primary key), not on the name. If two salespeople happen to share a name, they would have different IDs and could have different regions. No transitive dependencies remain.
  • Orders: The only non-key attributes are CustomerID and SalespersonID, both of which are foreign keys. Does CustomerID determine SalespersonID or vice versa? No — a customer can be served by different salespeople on different orders, and a salesperson can serve different customers. They are independent facts about the order.
  • Products and OrderItems: Products has ProductName and UnitPrice, neither of which determines the other. OrderItems has only Quantity.

3NF confirmed across all five tables.

Lossless-Join Check: The most important practical test is to confirm that the original data can be fully recovered by joining the decomposed tables. This is called a lossless join (sometimes lossless decomposition). The test is to mentally or physically reconstruct the original 1NF table from the 3NF schema using SQL joins, and verify that every original row reappears exactly — no rows added, no rows lost.

The reconstruction query would be:

SELECT
    oi.OrderID,
    oi.ProductID,
    o.CustomerID,
    c.CustomerName,
    c.CustomerCity,
    c.CustomerZip,
    p.ProductName,
    p.UnitPrice,
    oi.Quantity,
    sp.SalespersonName AS Salesperson,
    sp.Region AS SalespersonRegion
FROM OrderItems oi
JOIN Orders o         ON oi.OrderID      = o.OrderID
JOIN Customers c      ON o.CustomerID    = c.CustomerID
JOIN Products p       ON oi.ProductID    = p.ProductID
JOIN Salespersons sp  ON o.SalespersonID = sp.SalespersonID;

Running this query against the 3NF tables would produce exactly the five rows of the 1NF table shown earlier — all data preserved, all relationships intact. No row is duplicated because all join keys are primary keys (one-to-many relationships with no fan-out on the "one" side). This confirms the decomposition was lossless.

It is worth pausing to survey the