Limitations of First Normal Form (1NF)

First normal form does not guarantee that a table is free of redundant facts or insertion, update, and deletion anomalies. Removing repeating groups makes individual values easier to manage, but you must still examine which attributes determine the others.

In the conventional relational model used in this course, 1NF represents a single value from the appropriate domain at each row-column intersection and avoids repeating groups. Atomicity is relative to the model: a date is one value even though it contains year, month, and day components.

A table containing lists can still be queried with database functions or application code. The problem is that managing individual list members and their relationships becomes more complicated. Equally, a table containing only scalar values can be straightforward to query while remaining difficult to update consistently.

1NF does not require non-prime attributes to depend on every candidate key in full, nor does it exclude the transitive dependencies addressed by later normal forms. A non-prime attribute belongs to no candidate key; it is not simply an attribute outside the selected primary key.

Do not infer that every table in 1NF has these problems. A table in 3NF is also in 1NF. The limitation is that checking 1NF alone does not establish which higher normal forms the table satisfies.

Consider one row per item on an order. For this example, an item can occur only once per order, each order belongs to one customer, and customer details represent current information. ItemPrice means the current catalog price, whereas PriceAtSale means the price agreed for that order line.

FlatOrderLine(
    OrderNo, ItemNo, OrderDate,
    CustID, CustName, CustAddress, CustPhone,
    ItemTitle, ItemPrice, PriceAtSale, Qty,
    LineTotal, ShippedVia, ShippedDate
)

Candidate key: (OrderNo, ItemNo)

All attributes hold single values, and there are no numbered item groups. Nevertheless, the same customer's current details repeat across order lines, and the same item's title and current catalog price repeat across orders.

The key follows from the stated rule, not from the column names. If a product may appear on multiple lines of the same order, use a suitable line identifier such as (OrderNo, LineNo). Changing the key requires reviewing the dependencies again.

Problems permitted by a 1NF-only design

Problem	Example
Insertion anomaly	If the combined table is the only place to store customer or item details, required order-line identifiers prevent recording a new customer or item before an order exists.
Update anomaly	A customer's current phone number must be changed on every row that stores it. A missed row creates conflicting values.
Deletion anomaly	Removing the last order line containing an item can remove the only stored copy of that item's description.
Redundant facts	The same customer and catalog details are maintained in many places, increasing the work required to keep them consistent.

Cancellation need not mean physical deletion: applications often retain orders with a cancelled status. The deletion example concerns removing rows, not a recommendation to erase transaction history.

A functional dependency X → Y means that rows agreeing on X must agree on Y. Under the example's rules:

OrderNo → OrderDate, CustID
CustID → CustName, CustAddress, CustPhone
ItemNo → ItemTitle, ItemPrice
(OrderNo, ItemNo) → PriceAtSale, Qty, ShippedVia, ShippedDate

OrderDate depends on OrderNo alone, and ItemTitle depends on ItemNo alone. Both determinants are proper subsets of the composite candidate key. These are partial dependencies of non-prime attributes, so the combined relation is not in 2NF.

Customer details are also determined by OrderNo through CustID. An order with five item rows therefore repeats the current customer details five times. Several orders from that customer repeat them further. Storing one value in each cell does nothing to remove that repetition.

Second normal form requires 1NF and no partial dependency of a non-prime attribute on any candidate key. Check all candidate keys. Adding a generated LineID does not erase dependencies involving a remaining candidate key such as (OrderNo, ItemNo).

First separate order facts, item facts, and line facts:

Orders2NF(OrderNo, OrderDate, CustID,
          CustName, CustAddress, CustPhone)
Item(ItemNo, ItemTitle, ItemPrice)
OrderLine(OrderNo, ItemNo, PriceAtSale, Qty,
          ShippedVia, ShippedDate)

This removes the partial-key problems identified above. Orders2NF still repeats customer details across the customer's orders, because OrderNo → CustID and CustID determines those details. CustID is not a key of Orders2NF when a customer can place several orders.

Separating Customer removes that transitive-dependency problem:

Customer(CustID, CustName, CustAddress, CustPhone)
Orders(OrderNo, OrderDate, CustID)
Item(ItemNo, ItemTitle, ItemPrice)
OrderLine(OrderNo, ItemNo, PriceAtSale, Qty,
          ShippedVia, ShippedDate)

Customer, Orders, and Item use their respective identifiers as primary keys. OrderLine uses (OrderNo, ItemNo). Foreign keys connect Orders to Customer and OrderLine to both Orders and Item.

The common 3NF teaching pattern removes non-key facts determined through another non-key attribute. Formally, for each nontrivial dependency X → A, 3NF requires X to be a superkey or A to be prime. This avoids treating every transitive dependency as a violation regardless of the keys involved.

Under the stated dependencies, the decomposition gives customer details and catalog details one place to be maintained. It must also be lossless: joining the relations through their keys should reconstruct the original associations without inventing combinations. Preserving enforceable dependencies is another important check.

Account for derived, historical, and shipping facts

LineTotal has not been silently discarded. If the business defines it exactly as Qty × PriceAtSale, with no discounts, taxes, or other adjustments, it can be calculated when needed instead of stored independently. If it means something else, preserve that fact explicitly and document its derivation or source.

PriceAtSale belongs to the transaction and must not change when ItemPrice changes. Similarly, an address captured for a historical delivery is different from the current customer address assumed here.

The example assumes each line has at most one shipping method and date. Multiple shipments per line require a shipment model; order-wide shipping details instead depend on OrderNo. Normalization cannot choose the correct interpretation without business requirements.

A good decomposition reduces these anomalies; it does not guarantee that every inconsistency is impossible. Declare and enforce appropriate keys, foreign keys, nullability, and other constraints, and choose deletion policies deliberately.

Staging tables, reporting structures, and event records may intentionally retain repeated values or denormalized facts. Their purpose and maintenance rules determine whether that is justified; the table's label alone does not establish its normal form.

• Staging: raw source data may be retained temporarily for validation and transformation. Malformed or inconsistent records still need handling.
• Reporting: a maintained summary can reduce query work, but refresh logic must keep it consistent with its source.
• Event history: a snapshot of a value at a particular time may be a distinct historical fact rather than a redundant copy of its current value.

Append-only storage does not make all integrity problems disappear. Duplicate events, inconsistent input, corrections, and retention policies still matter. Likewise, warehouses are not universally read-only. Evaluate the workload rather than assuming that a category of table is exempt from consistency requirements.

1. State what one row represents and identify all candidate keys.
2. Distinguish repeated references from independently repeated facts.
3. Check whether any non-prime attribute depends on part of a candidate key.
4. Look for dependencies through determinants that are not keys, then apply the actual 3NF rule.
5. Test insertion, update, and deletion scenarios against the business requirements.
6. Verify that a proposed split preserves facts, dependencies where possible, and valid associations.

Do not split a table merely because a transaction updates several rows or because it references several entities. Association tables legitimately connect entities, and many multirow updates represent real business changes.

1NF organizes values; it does not finish the dependency analysis. The order-line example needs further decomposition because customer, order, and item facts depend on different identifiers. The next lesson defines second normal form and develops the rule for eliminating partial dependencies.