Search code examples
databaserelational-databasedatabase-normalizationdecomposition

If a DB decomposition is not dependency preserving, is it safe to conclude that it is lossy as well without further proof?


If a database decomposition is not dependency preserving, is it safe to conclude that the decomposition is lossy as well without further proof?

To clarify - when I use decomposition in my question, I am referring to the act of breaking down a relation into smaller relations when trying to normalise a relation. E.g. decomposing a 1NF relation into a series of 2NF relations.

As outlined by this Quora post, a decomposition should be "lossless" and "preserve dependencies". I understand that a decomposition that preserves dependencies may not be lossless and vice versa; however, are there situations where a decomposition that doesn't preserve dependencies is not lossy?

A more formal definition of database decomposition can be outlined in "Database Systems: The Complete Book" (refer to below excerpt)

enter image description here


Solution

  • Consider a schema (A B C) with FDs {{A}->{B} ; {B}->{C}}.

    Because of these FDs the candidate key will be {A}.

    Consider the decomposition into schemas (A B) and (A C).

    Because of the key [of the (ABC) schema] being {A}, this decomposition is lossless, in the [usual] sense that for any valid relation value r1 appearing for the (ABC) schema, we can take the corresponding relation values r2/r3 that would appear for the (AB) (AC) schemas, natural join them and be guaranteed we'll get back the original r1.

    Now [do yourself a favour and do some thinking to] consider whether that decomposition is also dependency-preserving, and what the consequences are.