Lossless-Join Decomposition

1. We claim the above decomposition is lossless. How can we decide whether a decomposition is lossless?
   • Let be a relation scheme.
   • Let be a set of functional dependencies on .
   • Let and form a decomposition of .
   • The decomposition is a lossless-join decomposition of if at least one of the following functional dependencies are in :
     1. 
     2. 
   Why is this true? Simply put, it ensures that the attributes involved in the natural join () are a candidate key for at least one of the two relations.

   This ensures that we can never get the situation where spurious tuples are generated, as for any value on the join attributes there will be a unique tuple in one of the relations.

2. We'll now show our decomposition is lossless-join by showing a set of steps that generate the decomposition:
   • First we decompose Lending-scheme into

     

   • Since bname assets bcity, the augmentation rule for functional dependencies implies that

     

   • Since Branch-scheme Borrow-scheme = bname, our decomposition is lossless join.
   • Next we decompose Borrow-scheme into

     

   • As loan# is the common attribute, and

     

     This is also a lossless-join decomposition.