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.

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]

Figure 7.1: Sample lending relation.

Figure 7.2: The decomposed lending relation.

• t[loan#] is the loan number made by branch t[bname] to t[cname].
• t[amount] is the amount of the loan for t[loan#]

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 o .