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.

  1. Finding Candidate Keys
    1. For the relation R1
    2. For the relation R2
  2. Is it a lossless join decomposition?
  3. Is it dependency preserving decomposition?

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
OR
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