Representation of Information

1. If we were to replace Branch-scheme and Borrow-scheme with the single scheme:

   

   we would get the relation of Figure 6.3 produced by a natural join of branch and borrow.

2. A tuple in the new relation has the following attributes:
   • is the assets for
   • is the city for

   • is the loan number made by branch to .
   • is the amount of the loan for
3. If we wish to add a loan to our database, the original design would require adding a tuple to borrow:

   

4. In our new design, we need a tuple with all the attributes required for Lending-scheme. Thus we need to insert

   

5. We are now repeating the assets and branch city information for every loan.
   • Repetition of information wastes space.
   • Repetition of information complicates updating.
6. Under the new design, we need to change many tuples if the branch's assets change.

7. Let's analyze this problem:
   • We know that a branch is located in exactly one city.
   • We also know that a branch may make many loans.
   • The functional dependency bname bcity holds on Lending-scheme.
   • The functional dependency bname loan# does not.
   • These two facts are best represented in separate relations.
8. Another problem is that we cannot represent the information for a branch (assets and city) unless we have a tuple for a loan at that branch.

9. Unless we use nulls, we can only have this information when there are loans, and must delete it when the last loan is paid off.