# 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

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.

## 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.