| Lesson 7 | Second Normal Form |
| Objective | Explain the requirements for Second Normal Form. |
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.
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.
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.
| DealerID | ModelID | DealerName | ModelName | AskingPrice |
|---|---|---|---|---|
| 501 | M1 | Timmons | City Hatch | 24000 |
| 501 | M2 | Timmons | Family Sedan | 28000 |
| 502 | M1 | Serrano | City Hatch | 23500 |
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.
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.
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 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.
| 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.
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.
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.
“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.
Practice with the Two Relations exercise. Then continue to functional dependencies for a closer examination of the rules behind the examples.