1. We'll take another look at the schema

Lending-schema = (bname, assets, bcity, loan#, cname, amount)

which we saw was a bad design.

2. The set of functional dependencies we required to hold on this schema was:

bname → assets bcity
loan# → amount bname

3. If we decompose it into

Branch-schema = (bname, assets, bcity)
Loan-info-schema = (bname, loan#, amount)
Borrow-schema = (cname, loan#)

we claim this decomposition has several desirable properties.

Lossless-Join Decomposition

1. We claim the above decomposition is lossless. How can we decide whether a decomposition is lossless?

• Let R be a relation schema.
• Let F be a set of functional dependencies on R.
• Let R1 and R2 form a decomposition of R.
• The decomposition is a lossless-join decomposition of R if at least one of the following functional de-
  pendencies are in F+:

(a) R1 ∩ R2 → R1
(b) R1 ∩ R2 → R2

Why is this true? Simply put, it ensures that the attributes involved in the natural join (R1 \ R2) 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-schema into

Branch-schema = (bname, bcity, assets)
Loan-info-schema = (bname, cname, loan#, amount)

• Since bname → assets bcity, the augmentation rule for functional dependencies implies that
  bname → bname assets bcity
• Since Branch-schema ∩ Borrow-schema = bname, our decomposition is lossless join.
• Next we decompose Borrow-schema into
  Loan-schema = (bname, loan#, amount)
  Borrow-schema = (cname, loan#)
• As loan# is the common attribute, and
  loan# → amount bname

This is also a lossless-join decomposition.