Part 3 of 5 · 1 min read
A decomposition that loses information
Splitting on a column that is not a key creates phantom rows when joined back.
Think of cutting that same photograph along a jagged, badly chosen line, so that taping the pieces back together produces extra, meaningless overlap that was never in the original picture.
In plain words: splitting a table on a column that is not a key of either resulting piece can produce extra, spurious rows when joined back, rows that never existed in the original data. This is a genuine design mistake, not a stylistic choice.
A worked example. Splitting enrollments (student_id, course_id, instructor_id) into (student_id, instructor_id) and (course_id, instructor_id), joined back on instructor_id, can produce a phantom row claiming a student enrolled in a course they never actually took, if that instructor teaches more than one course.
The key lines.
-- joining back on instructor_id can fabricate enrollments that never existed
SELECT a.student_id, b.course_id
FROM a_split a JOIN b_split b ON a.instructor_id = b.instructor_id;
Lossy decomposition
A lossy decomposition splits a table in a way that, when joined back, produces spurious rows not present in the original data.
Example: joining (student_id, instructor_id) and (course_id, instructor_id) back on instructor_id can fabricate a student-course pairing that never actually occurred.