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.