DBMS: Check if Decomposition Resulted in Good Design or Not

Consider the schema R = {A, B, C, D} such that following functional dependency holds on it:

F = {A B, A BC, C D}

If R is decomposed into relations R1 = {A, B} and R2 = {B, C, D}, check if decomposition results in good design or not.

Back to top

1. Finding Candidate Keys

Before proceeding to answer the question, let's find out the candidate keys of R1 and R2.

1. For the relation R1

Closure of A:

= {A}+

= {A}, reflexivity axiom
= {AB}, from given FD A B


Hence, Candidate Key for R1 = {A}

2. For the relation R2

Closure of B, {B}+ = {B}

Closure of C, {C}+ = {CD}

Closure of D, {D}+ = {D}

Here it can be seen that B, C, or D alone cannot be candidate keys here.

Closure of BC:

= {BC}+
= {BCD}, from given FD C D


Hence, Candidate Key for R2 = {BC}

Now, we call a decomposition a good decomposition if it has the following properties:

  1. It is a lossless join decomposition.
  2. It is dependency preserving.


Back to top

2. Is it a lossless join decomposition?

Let's check if the above decomposition is 'lossless'. For a decomposition to be lossless, it should meet the following conditions:

R1 R2 = Candidate/Super Key of R1
R1 R2 = Candidate/Super Key of R2


Here, R1 R2 = {B}

{B} is neither a candidate for relation R1 nor for R2. This means the decomposition is not lossless. Therefore, it is NOT a good design.


Back to top

3. Is it dependency preserving decomposition?

Since we already determined that it is not a good design, we do not need to check for dependency preservation.

Back to top