Lossless Decomposition, Dependency Preservation and Denormalisation

Splitting tables safely

Decomposing a relation must not lose information. A decomposition of R into R1 and R2 is lossless-join if joining them back always gives exactly R, with no spurious tuples. The test for two relations: the common attributes R1 ∩ R2 must functionally determine all of R1 or all of R2, that is, they form a key of at least one part. A decomposition is dependency-preserving if every original FD can be checked within individual relations, without joins. 3NF synthesis always achieves both properties; BCNF decomposition is always lossless but may not preserve every dependency, which is the classic reason designers sometimes stop at 3NF. In practice, denormalisation deliberately reintroduces controlled redundancy for read performance: storing an order's total, caching a customer's name in an orders table, or building reporting tables. Do it consciously, keep the normalised data as the source of truth, and maintain the copies with transactions, triggers or materialised views.

Testing a decomposition for losslessness

R(A, B, C) with F = { A → B }

decomposition 1:  R1(A, B)   R2(A, C)
  common = {A};  A -> B, so A is a key of R1      -> lossless

decomposition 2:  R1(A, B)   R2(B, C)
  common = {B};  B determines neither all of R1 nor all of R2   -> lossy

example of the lossy case:
  R:  (1, x, p), (2, x, q)
  R1: (1, x), (2, x)      R2: (x, p), (x, q)
  R1 join R2: (1,x,p), (1,x,q), (2,x,p), (2,x,q)   <- two spurious tuples

Lossy decompositions add tuples, not remove them

A lossy join produces extra, spurious tuples, so it "loses information" by no longer telling you which combinations were real. Students often expect missing rows instead.

त्वरित जाँच: When is the decomposition of R into R1 and R2 lossless?

• When the common attributes functionally determine all attributes of R1 or of R2
• When R1 and R2 have no attributes in common
• Whenever both are in BCNF
• Only when R has a single candidate key