Normal Forms   «Prev  Next»

Lesson 7Second Normal Form
ObjectiveExplain the requirements for Second Normal Form.

Second Normal Form (2NF): Requirements and Partial Dependencies

A relation is in second normal form when it is in first normal form and no non-prime attribute depends on a proper subset of any candidate key. In practical terms, facts about just one part of a composite key should not be repeatedly stored with every combination of that key.

For example, a dealer's name depends on the dealer identifier. It does not need to be stored again for every vehicle model the dealer offers. The price quoted by a particular dealer for a particular model, however, may depend on both identifiers.

The two requirements for 2NF

  1. Be in 1NF. Use individual values from appropriate domains and remove repeating groups in the conventional relational model taught here.
  2. Remove partial dependencies of non-prime attributes. Check every candidate key, not only the key selected as primary.

A candidate key is a minimal set of attributes that uniquely identifies a row under the business rules. A prime attribute belongs to at least one candidate key; a non-prime attribute belongs to none. Full functional dependency means that the complete determinant is needed: no proper subset determines the dependent attribute.

A single-column primary key alone is not sufficient evidence of 2NF. Another candidate key may be composite and have partial dependencies. In the usual case where every candidate key is a single attribute and there are no attributes constrained to a constant value, the partial-key problem does not arise.

A genuine 2NF violation: dealer–model offerings

Suppose a dealer may offer several vehicle models, and a model may be offered by several dealers. ModelID identifies a defined model specification, not a physical vehicle. The table stores one current offer per dealer-model pair:

DealerModelOffer(
    DealerID, ModelID, DealerName, ModelName, AskingPrice
)

Candidate key: (DealerID, ModelID)
DealerID → DealerName
ModelID → ModelName
(DealerID, ModelID) → AskingPrice

X → Y means that rows agreeing on X must agree on Y in every permitted state of the relation. These rules must be confirmed with the business; they are not proven by a few matching sample values.

Illustrative current offers
DealerIDModelIDDealerNameModelNameAskingPrice
501M1TimmonsCity Hatch24000
501M2TimmonsFamily Sedan28000
502M1SerranoCity Hatch23500

DealerName and ModelName are non-prime. DealerName depends only on DealerID, while ModelName depends only on ModelID. Both determinants are proper subsets of the candidate key, so this relation violates 2NF.

AskingPrice depends on the combination: neither the dealer nor the model alone determines it under the stated rules. A model-wide catalog price would be a different attribute with a different dependency.

Decompose according to the dependencies

Dealer(DealerID, DealerName)
VehicleModel(ModelID, ModelName)
Offer(DealerID, ModelID, AskingPrice)

Primary keys:
Dealer: DealerID
VehicleModel: ModelID
Offer: (DealerID, ModelID)

Foreign keys:
Offer.DealerID → Dealer.DealerID
Offer.ModelID → VehicleModel.ModelID

Dealer names and model names now have separate places to be maintained. Offer retains the association and its price. Under the stated dependencies, these relations meet 2NF. The decomposition is lossless: joining each offer to its one matching dealer and model reconstructs the original facts without creating new offerings.

If multiple offers per dealer-model pair must coexist, perhaps for different dates, trims, or contracts, revise the row meaning and key. A normalization result is valid only for the rules it assumes.

Why removing partial dependencies matters

  • Update: changing Timmons to a new dealer name requires one Dealer update, rather than changing every offer from that dealer.
  • Insertion: a dealer or model can be registered before any Offer exists.
  • Deletion: removing a dealer's last offer need not remove that dealer's name.

These improvements address the repeated facts caused by the identified partial dependencies. They do not guarantee freedom from every possible anomaly, enforce every business rule, or ensure faster queries. Appropriate constraints and application behavior are still necessary.

The original Car sample: a different dependency problem

The following historical teaching records can illustrate the boundary between 2NF and 3NF. Define CarID as identifying a particular physical vehicle, with one current dealer per vehicle. Assume CarID is the only candidate key represented by these columns.

Historical Car sample: one row per physical vehicle
CarID Color Make Model Year DealerID DealerName
101 Red Ford Escort 1999 501 Timmons
102 Blue Ford Escort 1999 502 Serrano
103 Green Chevy Malibu 1996 503 Tomason
104 Black Ford Explorer 2000 504 Frye

The years are model years, not claims about current inventory. Although each displayed dealer appears once, the business permits a dealer to stock several cars.

Why DealerName in Car is a 3NF issue

CarID → Color, Make, Model, Year, DealerID
DealerID → DealerName
Therefore: CarID → DealerName

Under these rules, DealerID and DealerName are functionally dependent on CarID. Saying that a name “describes the dealer” does not negate the dependency. The problem is that DealerName is determined through DealerID, which is not a key of Car.

With the stated single-attribute candidate key, this relation has no partial-key dependency. It can satisfy 2NF while failing 3NF. Separating DealerName is therefore a 3NF improvement in this example:

Car(CarID, Color, Make, Model, Year, DealerID)
Dealer(DealerID, DealerName)

CarID remains the vehicle key, DealerID identifies the dealer, and Car.DealerID references Dealer. A transfer changes a vehicle's current dealer reference; it does not require a second row with the same CarID. If the business instead wants to record models offered by multiple dealers, use the dealer-model association described earlier.

Do not assume other dependencies from attribute names alone. Whether Model determines Make, for example, depends on how model names are defined and whether they are unique across manufacturers.

Why a surrogate key does not repair the offering table

Suppose OfferID becomes the primary key of the original combined DealerModelOffer table, while a unique constraint preserves the rule of one offer per (DealerID, ModelID). That pair remains a candidate key. DealerName still depends on its DealerID subset, so the partial dependency remains.

The selected primary key is an implementation choice among candidate keys. Normalization considers the dependencies and all candidate keys; changing which one is primary does not change the facts being modeled.

Apply the 2NF test systematically

  1. Define one row's meaning and confirm the 1NF representation.
  2. List every candidate key supported by the business rules.
  3. Identify non-prime attributes.
  4. For each composite candidate key, check whether a proper subset determines a non-prime attribute.
  5. Move such facts into relations keyed by their determinants, retaining the linking attributes.
  6. Verify lossless reconstruction and preservation of enforceable dependencies.
  7. Continue checking for other dependencies rather than treating 2NF as the final design.

“One table per entity” is a useful modeling aid, but it is not the definition of 2NF. Association and event tables legitimately connect several entities. The dependency test explains whether a particular split is needed.

Two Relations — Exercise

Practice with the Two Relations exercise. Then continue to functional dependencies for a closer examination of the rules behind the examples.

2NF terminology: every non-prime attribute must be fully dependent on every candidate key, and the relation must be in 1NF.
Functional dependency: agreement on the determinant requires agreement on the dependent attributes across all valid rows.

SEMrush Software 7 SEMrush Banner 7