next up previous
Next: Decomposition Up: Pitfalls in Relational DB Previous: Pitfalls in Relational DB

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:
  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.
  6. Under the new design, we need to change many tuples if the branch's assets change.
  7. Let's analyze this problem:
  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.

Osmar Zaiane
Thu Jun 18 12:56:34 PDT 1998