What makes a decomposition of one table into two tables lossless-join, and what goes wrong when that condition does not hold?

## What the property actually says Normalization proceeds by splitting tables. The whole approach is only safe if the split is reversible, so the correctness criterion is: joining the pieces back together on their shared columns yields exactly the original set of rows. That is the lossless-join property, sometimes called non-additive join, which is the more honest name because the failure mode is extra rows rather than missing ones. ## The condition for a binary split For R decomposed into R1 and R2, the decomposition is lossless if and only if the common attributes determine one of the pieces entirely. Written as a functional dependency, either (R1 intersect R2) determines R1, or (R1 intersect R2) determines R2. Equivalently: the shared columns form a superkey of at least one of the two tables. The intuition is matching. During the join, each row of R1 finds the rows of R2 that share its join-column values. If the shared columns are a key of R2, each R1 row matches at most one R2 row, so the join cannot manufacture combinations. If they are a key of neither, one shared value can match many rows on both sides, and the join produces the cross product of those groups. ## The spurious-tuple example R(employee, department, project) with rows (Ann, Sales, Alpha) and (Bob, Sales, Beta). Split into ED(employee, department) and DP(department, project). ED holds (Ann, Sales) and (Bob, Sales). DP holds (Sales, Alpha) and (Sales, Beta). The shared column, department, is not a key of either. The join gives four rows: Ann-Alpha, Ann-Beta, Bob-Alpha, Bob-Beta. Two of those are fabrications. Worse, they are indistinguishable from the real ones, so the original data cannot be recovered from the decomposition. Information has been destroyed even though every original row is still present somewhere. ## The correct split Split the same table into EP(employee, project) and ED(employee, department) instead, assuming an employee belongs to one department. The shared column, employee, is a key of ED, so each EP row matches exactly one ED row and the join reconstructs R precisely. The general recipe when extracting a fact: the extracted table should be keyed by the column you leave behind as a foreign key. Every normalization step from 2NF up follows this pattern, which is why textbook decompositions are lossless by construction, you split on a determinant, and a determinant is by definition a key of the new table. ## Relationship to the anomalies Losslessness is the safety condition that makes anomaly removal legitimate. Any table can be split to reduce redundancy; the question is whether you can still answer the questions you could answer before. A lossy split trades a visible problem, duplicated facts, for an invisible one, fabricated facts, which is strictly worse because it produces confidently wrong query results with no error. ## How to verify in practice For a two-way split, checking the superkey condition is enough and takes seconds. For an n-way split there is a tableau-based chase algorithm, but interviews almost never go there. The practical check for engineers: after decomposing, does the join key uniquely identify rows in the parent table, and does a COUNT of the rejoined result match the COUNT of the original? A larger count is the immediate signal of a lossy split.