Lesson 2	First Step in Normalization
Objective	Define normalization and recognize anomalies that it prevents.

Database Normalization Defined

Database normalization is the process of organizing relations according to their keys and dependencies to reduce unnecessary repetition and prevent data anomalies. It often involves decomposing a table into smaller, related tables while preserving the facts and relationships represented by the original data.

The purpose is not simply to make tables narrower or increase their number. A useful decomposition gives each independently maintained fact an appropriate location. Keys and foreign keys retain the connections between those facts, and joins retrieve the combined information when needed.

For example, an order line describes a product purchased on a particular order. The customer's current name describes the customer, while the product's current name describes the product. Keeping all three kinds of facts in every order-line row creates avoidable repetition.

What problems does normalization prevent?

Consider a combined order-line table containing customer and product names. Assume products and customers should exist independently of orders. Storing their details only in order lines can cause:

An insertion anomaly: a new product cannot be recorded until someone orders it, because an order identifier is required to create its row.
An update anomaly: changing a customer's current name requires updating every affected order-line row. Missing one row leaves inconsistent versions of the same fact.
A deletion anomaly: removing the last order line for a product also removes the only stored copy of its name, although the product still belongs in the catalog.

Normalization separates facts with different lifecycles. A product can then be added without an order, and removing an order line does not require removing the product it references.

Visual walkthrough of the normalization process

The four stages below summarize the design process. They are not four normal forms, and each arrow does not necessarily represent one normal-form transition. The initial record is abbreviated; the following text supplies the full attributes and business rules.

Four stages of normalization: start with mixed order facts, represent one product per order-line row, identify functional dependencies, and separate Customer, Orders, Product, and OrderLine tables linked by keys. — Normalization begins with the meaning of the data, removes repeating groups, and uses dependencies to separate facts. The resulting tables retain the original relationships through foreign keys. The lines in the final panel show links, not cardinality notation; OrderLine has the composite primary key (OrderID, ProductID).

Represent repeated items consistently. Replace numbered product groups with one row per order-product association under the example's rules.
Identify what determines each attribute. Separate customer, order, and product facts according to their dependencies.
Retain the connections. Give each resulting table its key and link the tables without losing associations.

Business rules determine keys and dependencies

A candidate key is a minimal set of attributes that uniquely identifies a row. “Minimal” means that removing any attribute from that set would lose the uniqueness guarantee. A table can have multiple candidate keys; one is chosen as the primary key.

A functional dependency, written X → Y, means that whenever two rows agree on X, they must agree on Y. For example, CustomerID → CustomerName expresses the rule that each customer has one current name. Two customers can still have the same name.

For this example, assume:

Each order belongs to exactly one customer.
Each product appears at most once within an order.
Each customer and product has one current name.
The ordered quantity belongs to the order-product combination.

These rules make (OrderID, ProductID) the order-line candidate key. If a product may occur on multiple lines in the same order, use a line identifier such as (OrderID, LineNumber) instead. Normalization must follow the actual rules, not force the application to match an example.

Worked example: separate the facts without losing them

1. Remove repeating groups

A record with Product1, Quantity1, Product2, Quantity2, and further numbered groups limits the number of items and complicates validation. In the conventional relational model used here, first normal form represents a single value from the appropriate domain at each row-column intersection and avoids repeating groups.

For the example, represent each order-product association as one row:

OrderLine1NF(
    OrderID, ProductID, CustomerID,
    CustomerName, ProductName, Qty
)

Candidate key: (OrderID, ProductID)

Customer and product names still repeat, so reaching 1NF is not the end of the analysis. Conversely, a table is not necessarily redundant just because it is in 1NF.

2. Remove partial dependencies to reach 2NF

The relevant dependencies are:

OrderID → CustomerID
CustomerID → CustomerName
ProductID → ProductName
(OrderID, ProductID) → Qty

CustomerID depends on OrderID alone. CustomerName is also determined by OrderID through CustomerID. ProductName depends on ProductID alone. Each of these attributes depends on only part of the order-line key.

Separate those attributes while keeping the quantity with the complete order-line key:

Orders2NF(OrderID, CustomerID, CustomerName)
Product(ProductID, ProductName)
OrderLine(OrderID, ProductID, Qty)

A relation is in second normal form when it is in 1NF and no non-prime attribute depends on a proper subset of any candidate key. A non-prime attribute belongs to no candidate key. Check all candidate keys, not just the selected primary key.

3. Remove the remaining transitive dependency

Orders2NF still repeats a customer's current name across that customer's orders. Here OrderID → CustomerID → CustomerName, and CustomerID is not a key of Orders2NF. Move the name into Customer:

Customer(CustomerID, CustomerName)
Orders(OrderID, CustomerID)
Product(ProductID, ProductName)
OrderLine(OrderID, ProductID, Qty)

Under the stated dependencies, these relations satisfy 3NF. CustomerID, OrderID, and ProductID identify their respective parent rows; OrderLine uses the composite key (OrderID, ProductID). Orders.CustomerID references Customer, and OrderLine references Orders and Product.

More generally, third normal form requires every nontrivial functional dependency X → A to have either a superkey as X or a prime attribute as A. A superkey uniquely identifies rows, and a prime attribute belongs to a candidate key. The customer-name example illustrates the common transitive-dependency problem, but the formal rule also covers more complicated key arrangements.

Repeated identifiers are necessary connections

Several orders can contain the same CustomerID, and several order lines can contain the same ProductID. These values identify different relationships; they are not redundant copies of a customer's or product's descriptive details.

A join can show the names alongside each order line:

SELECT ol.OrderID, c.CustomerName,
       ol.ProductID, p.ProductName, ol.Qty
FROM OrderLine AS ol
JOIN Orders AS o ON o.OrderID = ol.OrderID
JOIN Customer AS c ON c.CustomerID = o.CustomerID
JOIN Product AS p ON p.ProductID = ol.ProductID;

Names may repeat in the query result without being independently maintained in every underlying row. Normalization concerns how facts are represented and updated; it does not prohibit repetition in a report.

How normalization complements an ER diagram

An ER diagram helps identify entities, relationships, and their participation rules. Normalization checks attribute placement using keys and dependencies. The two approaches support each other, but a well-drawn diagram does not by itself prove that every mapped relation meets a normal form.

Focused tables can represent entities, associations, or events. Customer represents an entity; OrderLine represents an association with its own quantity. “One table per business object” is therefore a useful starting idea, not a complete normalization test.

Check that the decomposition is sound

Preserve information: each original fact and association must still be represented.
Verify a lossless join: joining the decomposed relations must reconstruct the original information without creating false combinations.
Preserve dependencies where possible: important rules should remain enforceable without unnecessary cross-table checks.
Enforce keys and references: use primary-key, unique, foreign-key, and nullability constraints appropriate to the rules.
Test independent changes: adding a product, updating a customer, or removing an order line should not require unrelated facts to be added, changed, or deleted.

Practical limits and the next lesson

Normalization is not duplicate-row removal, compression, or a promise of faster queries. It reduces certain kinds of redundancy by organizing dependencies. An artificial row ID does not eliminate partial or transitive dependencies among the remaining attributes.

Historical facts also need careful interpretation. A customer's name captured on an invoice may deliberately preserve what was recorded at the time; it is different from the current customer name used in this example. Likewise, denormalized reporting tables can be justified by measured requirements, provided their source of truth and refresh process are documented.

Next, examine the objectives of normalization. For supporting detail, review redundant facts versus repeated values and functional dependencies.