# Decomposition and lossless joins

If someone walk me though the following questions I'd be very grateful.

Consider a relation R(A, B, C, D, E) with the following functional dependencies:

``````A, B → C
D, E → C
B → D
``````

i) decompose R so that it is in 2nd normal form.

Second part of the question: ii) suppose R is populated with the following data:

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

Show that your 2NF decomposition is lossless with respect to joins. To do this , produce a join expression amongst the decomposed tables, and show that its execution results in the original table above.

Thanks.

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.