Insert, Update, and Delete Anomalies

Learning outcomes

Normalization is often taught as a sequence of formal rules, but its practical motivation is simpler: badly structured relations make ordinary inserts, updates, and deletes unsafe. These are called data anomalies. Understanding them makes the purpose of normalization concrete.

A deliberately bad relation

WorkOrderReport(  work_order_id,  asset_id,  asset_serial_number,  customer_id,  customer_name,  status_code,  status_name,  technician_id,  technician_name)

One row mixes WorkOrder, Asset, Customer, Status, and Technician facts.

Update anomaly

Suppose Customer 17 appears in 200 WorkOrderReport rows. If the customer's legal name changes, all 200 copies must be updated consistently.

If 199 rows are updated and one is missed, the database now claims two names for the same customer ID.

The dependency behind the update anomaly

The real rule is:

\[ customer\_id \rightarrow customer\_name \]

but customer_name is stored repeatedly at WorkOrder/Technician grain. The fact is stored with the wrong determinant.

Insert anomaly

Suppose WorkshopHub wants to create a new status code awaiting_parts before any work order uses it. If status metadata exists only inside WorkOrderReport, the system cannot store the new status without inventing a fake work order row.

Delete anomaly

Suppose the last WorkOrder with status legacy_review is deleted. If the status name and description exist only inside work-order rows, deleting that final order also deletes the only stored definition of the status.

Three anomaly types

PartUsage example

PartUsageBad(  work_order_id,  part_id,  part_description,  quantity,  current_catalog_price,  charged_unit_price)

Here part_description and current_catalog_price belong to Part, while charged_unit_price is legitimately historical and belongs to the usage event.

Redundancy is not always wrong

Suppose Part currently costs 12.00 but a past WorkOrder charged 10.50. Storing both values in different places is correct because they are different facts:

• Part.current_catalog_price — current catalog state;
• PartUsage.charged_unit_price — historical transaction fact.

Normalization removes accidental redundancy, not meaningful historical facts.

Update anomaly in status labels

work_order_id | status_code | status_name--------------+-------------+-------------1001          | closed      | Closed1002          | closed      | Closed1003          | closed      | Completed

Which label is correct? The database should not permit multiple canonical labels if the rule is:

\[ status\_code \rightarrow status\_name \]

Insert anomaly in products

If Product data is stored only in OrderLine, a product cannot be created until it is ordered. That is a strong sign Product is an independent entity whose facts should live separately.

Delete anomaly in departments

If department details are stored only on Employee rows, deleting the last employee in a department may erase the department's name, location, and manager facts. The model has accidentally made Department existence depend on Employee.

Anomalies reveal mixed grains

When a row tries to represent multiple grains, anomalies are likely. Ask:

• What does one row represent?
• Which attributes depend on the row's key?
• Which attributes actually depend on some other identifier?

Spotting anomalies from repeated columns

Repeated descriptive attributes next to foreign keys are a warning sign:

customer_id + customer_namepart_id + part_descriptionstatus_code + status_namemanufacturer_id + manufacturer_name

Sometimes repetition is intentional for snapshots or performance, but it should be explicit and governed.

Anomaly review before normalization

For each relation, ask:

1. Can I create every independent business concept without inventing unrelated data?
2. If one descriptive fact changes, how many rows must change?
3. Can deleting one event accidentally erase the last copy of another concept?
4. Are repeated values historical snapshots or accidental copies?

WorkshopHub repair

Split the bad report relation into:

Customer(customer_id, customer_name)Asset(asset_id, customer_id, serial_number)WorkOrder(work_order_id, asset_id, status_code)WorkOrderStatus(status_code, status_name)Technician(technician_id, technician_name)WorkOrderAssignment(work_order_id, technician_id, ...)

Each independently governed fact now has a natural place.

Practice: identify all three anomalies

Registration(  student_id,  student_name,  course_id,  course_title,  instructor_id,  instructor_name,  grade)

Summary and next lesson

Insert, update, and delete anomalies are symptoms of incorrectly stored dependencies and mixed row grains. The next lesson becomes more formal: attribute closure gives us an algorithmic way to test what a set of attributes determines and whether it forms a candidate key.

References

• C. J. Date, Database Design and Relational Theory.
• Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems.
• Jeffrey Ullman and Jennifer Widom, A First Course in Database Systems.