Dependency preserving and lossless join decompositions

I figured it out and got it confirmed by the lecture, hopefully this will help someone else:

i)

The key is {A,B,E}.

This means that FD1 and FD3 are both partial — but note that FD2 is not, because {D,E} is not a partial key. The decomposition creates one table for each of the FDs, and one for the PK FD:

R1(A,B,E) R2(A,B,C) R3(B,D)

-

ii) Using the above we can create the following tables:

        `R1                R2              R3`
| A  | B  | E  |   | A  | B  | C  |    | B  | D  |
| a1 | b1 | e1 |   | a1 | b1 | c1 |    | b1 | d1 | 
| a2 | b1 | e2 |   | a2 | b1 | c2 |    | b2 | d3 | 
| a2 | b2 | e2 |   | a2 | b2 | c1 |    

Joining R1 and R2 gives:

        R1R2                  R3
| A  | B  | E  | C  |    | B  | D  | 
| a1 | b1 | e1 | c1 |    | b1 | d1 | 
| a2 | b1 | e2 | c2 |    | b2 | d3 | 
| a2 | b2 | e2 | c1 | 

Joining R1R2 with R3:

| A  | B  | E  | C  | D  | 
| a1 | b1 | e1 | c1 | d1 | 
| a2 | b1 | e2 | c2 | d1 | 
| a2 | b2 | e2 | c1 | d3 |

which is equal to:

| A  | B  | C  | D  | E  |
| a1 | b1 | c1 | d1 | e1 |
| a2 | b1 | c2 | d1 | e2 |
| a2 | b2 | c1 | d3 | e2 |

Hence decomposition is lossles with respect to joins.

Given your decomposition, a projection of F over R2 is:

D → J I
A → D E

while the dependencies that hold on R1 are:

B → F
F → G H

Instead, the dependency:

A B → C

is lost.

The reason is that the attributes of this dependency are in two different tables, and there is no way to obtain it, since the tables do not have attributes in common.

A decomposition that preserves the dependencies can be obtained by applying the analysis algorithm to produce the Boyce-Codd Normal Form, or the synthesis algorithm to produce the Third Normal Form.