2D Data Anomaly Simulator: Normalized vs Denormalized Schemas

Operations

Parameters

Departments 3

Table capacity (rows) 26

Autoplay speed 1.0×/s

Live Readouts

Denormalized anomalies0

Normalized anomalies0

Data-loss events0

Redundant copies0

How it works

The Employee table has attributes EmpID (key), DeptID, DeptPhone. DeptPhone does not depend on EmpID directly — it depends on DeptID, which itself depends on EmpID. That chain is a transitive functional dependency:

EmpID → DeptID → DeptPhone   (transitive, violates 3NF)

Storing DeptPhone once per employee (denormalized) means every employee row in the same department carries its own copy of the same fact, so operations can desynchronize them:

Update anomaly — changing a department's phone must rewrite every row where DeptID = d. This simulator deliberately updates only the first matching row, so the rest go stale: stale rows = N(d) − 1.
Insert anomaly — a new employee's DeptPhone copy is only as correct as the sibling row it's copied from; if that sibling is already stale, the new row inherits the wrong value.
Delete anomaly — deleting the only employee left in a department removes the sole surviving copy of that department's phone number, so the fact is lost entirely (a data-loss event below).

3NF removes the transitive dependency by giving DeptPhone its own table keyed on DeptID. Employee keeps only the DeptID foreign key; the phone is fetched with a join:

Department(DeptID, DeptPhone)      -- single source of truth
Employee(EmpID, DeptID → Department.DeptID)
SELECT e.*, d.DeptPhone FROM Employee e JOIN Department d ON e.DeptID = d.DeptID

Because the fact is stored exactly once, an update touches one row, an insert never needs to re-enter it, and a delete on Employee can never remove it — which is exactly why the normalized side's anomaly and data-loss counters never move.

Note on this 2D port: the source 3D engine's math checked out exactly (verified numerically) — stale rows per update = N(dept)−1, redundant copies = Σ max(0, N(dept)−1), data loss only fires when a department's employee count hits 0. This version reimplements the same rules on a flat canvas table plus a live time-series chart of the same counters, rather than re-rendering the 3D cubes in 2D.

Consistent value Stale / anomaly Department reference

Click a denormalized row to delete it. Hover a row to see its trail.