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 **R _{1}** = {A, B} and

**R**= {B, C, D}, check if decomposition results in good design or not.

_{2}## A: DBMS: Check if decomposition resulted in good design or not: 79

_{}

## 1. **Finding Candidate Keys**

Before proceeding to answer the question, let's find out the candidate keys of **R _{1}** and

**R**.

_{2}### 1. **For the relation R**_{1}

_{1}

Closure of A:

= {A}+

= {A}, reflexivity axiom

= {AB}, from given FD A → B

Hence, Candidate Key for **R _{1}** = {A}

### 2. **For the relation R**_{2}

_{2}

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 **R _{2}** = {BC}

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

- It is a
**lossless**join decomposition. - 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:

**R _{1}** ∩

**R**= Candidate/Super Key of

_{2}**R**

_{1}OR

**R**∩

_{1}**R**= Candidate/Super Key of

_{2}**R**

_{2}

Here, **R _{1}** ∩

**R**= {B}

_{2}{B} is neither a candidate for relation **R _{1}** nor for

**R**. This means the decomposition is not lossless. Therefore, it is

_{2}**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