Introduction to Database Normalization and Normal Forms

What you will learn in this module

1. Distinguish repeated values from unnecessarily repeated facts.
2. Recognize repeating groups and organize data into first normal form.
3. Identify candidate keys and functional dependencies from business rules.
4. Recognize partial dependencies and decompose tables into second normal form.
5. Check that a decomposition preserves the information and relationships the application needs.

Why normalize a database?

Suppose each order line stores the order date, the customer's current name, and the product's name. An order with several products repeats its order date; a customer with several orders repeats their name. These repetitions can create three problems:

• Update anomaly: changing a customer's name requires multiple updates, and an omitted row leaves inconsistent names.
• Insertion anomaly: a product cannot be recorded until an order line exists if all product information is stored only in order lines.
• Deletion anomaly: removing the last order line for a product can also remove its name from the database.

Repeated identifiers are not themselves errors. Several order lines must reference the same order. The goal is to avoid maintaining the same independently changing fact in several places. The supporting article on reducing data redundancy explains this distinction with part and supplier examples.

Keys and dependencies: the vocabulary you need

• Candidate key: a minimal set of attributes that uniquely identifies a row under the business rules. A table may have several candidate keys; one is selected as its primary key.
• Functional dependency: X → Y means that rows agreeing on X must agree on Y. For example, ProductID → ProductName holds if each product has one current name.
• Non-prime attribute: an attribute that belongs to no candidate key. This term makes the normal-form definitions precise when multiple candidate keys exist.
• Partial dependency: a non-prime attribute depends on a proper subset of a candidate key.
• Transitive dependency: a dependency follows through an intermediate determinant. In a common 3NF violation, an order identifies a customer, and that customer identifies a current customer name.

A dependency is a rule about all permitted data, not a pattern established by a few sample rows. Two customers happening to have different names does not make CustomerName a candidate key. Likewise, adding an artificial ID does not remove the dependencies among the existing attributes.

A running example: orders and products

For this introductory example, assume that each order belongs to one customer and each product appears at most once in an order. Each product has one current name, and each customer has one current name. The quantity and agreed unit price belong to the order-product pair.

These assumptions make (OrderID, ProductID) the candidate key for an order line. If a real application allows the same product on several lines of one order, it needs a different line identifier, such as (OrderID, LineNumber). Always establish that rule before choosing a key.

First normal form: eliminate repeating groups

A spreadsheet-style record might contain Product1, Quantity1, Product2, Quantity2, and more numbered columns. Another design might put a comma-separated list of product identifiers into one cell. Both make individual order lines difficult to identify and constrain.

In the conventional relational model taught here, 1NF represents each attribute with a single value from its domain and avoids repeating groups. Atomicity depends on what the application treats as a value: a date does not violate 1NF simply because it has a day, month, and year.

Represent one order-product association per row:

OrderLine1NF(
    OrderID, ProductID, OrderDate,
    CustomerID, CustomerName, ProductName,
    Quantity, AgreedUnitPrice
)

Candidate key: (OrderID, ProductID)

This removes the repeated column groups, but order dates, customer names, and product names still repeat. Being in 1NF neither guarantees nor requires redundant facts; it is the starting point for further dependency analysis.

Second normal form: remove partial dependencies

A relation is in 2NF when it is in 1NF and every non-prime attribute depends on each candidate key in full, rather than on a proper subset of a candidate key. Check all candidate keys, not just the selected primary key. If every candidate key consists of a single attribute, the usual partial-key problem does not arise.

The order-line example has these dependencies:

OrderID → OrderDate, CustomerID
CustomerID → CustomerName
ProductID → ProductName
(OrderID, ProductID) → Quantity, AgreedUnitPrice

OrderDate and CustomerID depend only on OrderID. CustomerName is also determined by OrderID through CustomerID. ProductName depends only on ProductID. These attributes therefore depend on only part of the composite order-line key.

Separate the order facts, product facts, and order-line facts:

Orders2NF(OrderID, OrderDate, CustomerID, CustomerName)
Product(ProductID, ProductName)
OrderLine(OrderID, ProductID, Quantity, AgreedUnitPrice)

Primary keys:
Orders2NF: OrderID
Product: ProductID
OrderLine: (OrderID, ProductID)

Under the stated dependencies, these tables meet 2NF. Orders2NF deliberately retains CustomerName so that the remaining problem is visible: the same customer's current name can still appear in several orders.

Third normal form: a preview of the next step

In Orders2NF, OrderID → CustomerID → CustomerName. CustomerID is not a key of that table because a customer may place several orders, and CustomerName is non-prime. This is the transitive dependency that the next decomposition removes.

Customer(CustomerID, CustomerName)
Orders(OrderID, OrderDate, CustomerID)
Product(ProductID, ProductName)
OrderLine(OrderID, ProductID, Quantity, AgreedUnitPrice)

CustomerID is the primary key of Customer and a foreign key in Orders. OrderLine references Orders through OrderID and Product through ProductID. A customer's current name now has one place to be maintained.

More precisely, a relation is in 3NF when every nontrivial functional dependency X → A has either a superkey as X or a prime attribute as A. A superkey uniquely identifies rows but need not be minimal; a prime attribute belongs to at least one candidate key. This definition handles cases that the simplified phrase “no transitive dependencies” can misclassify.

The example keeps AgreedUnitPrice in OrderLine because it records the price agreed for that purchase. A current catalog price would be a different fact. Similarly, a name or address captured as an historical order snapshot has different semantics from the current customer details assumed here.

Check a decomposition before accepting it

• Preserve facts: every original business fact must still have a defined location.
• Check the join: reconstructing the original information must not invent combinations or lose valid associations. This is the lossless-join requirement.
• Check enforceability: retain dependencies in a form that can be enforced where possible, using keys, uniqueness, foreign keys, and other appropriate constraints.
• Test changes: try adding a product without an order, changing a customer's current name, and removing an order line without losing the product.

Splitting a table arbitrarily is not normalization. The reason for each split should follow from a key, a dependency, or the facts the table represents.

How to use the rest of this module

Begin with the definition of normalization, then work through first normal form, functional dependencies, and achieving second normal form. The next module develops third normal form and the design choices beyond it.

Normalize to establish a sound representation of the data, then evaluate performance using actual queries and measurements. Indexes and query design often matter; intentional denormalization requires a plan for maintaining consistency. Neither adding tables nor reducing their number guarantees a faster system.

Recommended reading: Database Design for Mere Mortals.