Representation of Information

1. Suppose we have a schema, Lending-schema,

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

   and suppose an instance of the relation is Figure 7.1.

    
   Figure 7.1:   Sample lending relation.
   

2. A tuple t in the new relation has the following attributes:
   • t[assets] is the assets for t[bname]
   • t[bcity] is the city for t[bname]

   • is the loan number made by branch t[bname] to t[cname].
   • t[amount] 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:

    (SFU, L-31, Turner, 1K)
   
   

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

    (SFU, Burnaby, 2M, Turner, L-31, 1K)
   
   

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-schema.
   • 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.