Stage 2 · L13

Normalize facts using functional dependencies

Core · original Session 2; extended normalization/cardinality visuals

Rental relationships are implemented. Optional profile/tag/warehouse/price designs are conceptual comparisons.

Normalization asks which facts depend on which keys. In this model equipment_id determines the current catalogue name, stock and daily rate; user_id determines the customer's current email. A rental row stores its own owner/item references, quantity, state and creation time. It does not duplicate the customer's email or catalogue name on every row.

Suppose rentals copied equipment_name. Renaming Drill would require updating every rental, and a missed row would disagree about the same equipment identity. That is an update anomaly. Deleting the last rental should not delete the only stored fact about an item; that would be a deletion anomaly. Keeping Equipment as its own table lets the catalogue exist before a rental.

Normalization is not a rule to remove every repeated value. A historical charged_rate is a different fact from today's catalogue daily_rate. If pricing history is required, store the agreed rental price deliberately, because a later catalogue update must not rewrite the charge. The present checkpoint does not calculate or store charges. Do not claim its current-price join reconstructs a past invoice.

Functional dependencies make the choice explicit. A composite association key (equipment_id, tag_id) determines a link, while tag_id determines tag_name in its own table. A link table containing tag_name would store a dependency on only part of its key. For this checkpoint, identify dependent facts before proposing a table split; splitting blindly can add joins without fixing an anomaly.

First normal form (1NF) keeps one value per cell under the chosen domain model. A comma-separated list of tag IDs is several independently queried facts packed into one cell; a tag link table gives each fact its own row. Atomicity is relative to the application: a person's name containing spaces is not automatically a1NF violation.

Second normal form (2NF) addresses partial dependencies on a composite key. In an equipment_tags link keyed by (equipment_id, tag_id), tag_name depends on tag_id alone. Put the name in Tags, leaving the link's own facts dependent on the whole pair.

Third normal form (3NF) addresses indirect non-key dependencies. In an optional warehouse example, equipment_id determines warehouse_id, which determines warehouse_city; storing the city on every equipment row creates a repeated update problem. A Warehouse table owns that fact. Tags, profiles and warehouses remain schema-design comparisons here; they are not claimed as installed API resources.

The implemented references avoid copying a customer email into every rental. This lesson reasons about keys and anomalies only; session operations and the related-row transfer are introduced in L15, after the model vocabulary.

equipment_id → current name, stock, daily_rate
user_id → current email
(equipment_id, tag_id) → link facts
 tag_id → tag_name
rental_id → deliberately agreed historical charge (if implemented)

Move tag_name into a Tags table because it depends on only tag_id, part of the composite link key (2NF). Keep one independently queried link fact per row (1NF); move a warehouse_city owned by warehouse_id into Warehouse to avoid an indirect non-key dependency (3NF). An agreed historical charge is a different fact, not accidental duplication. These proposed designs are not installed.