next up previous
Next: Joined Relations Up: Modification of the Database Previous: Updates

Update of a view

  1. The view update anomaly previously mentioned in Chapter 3 exists also in SQL.
  2. An example will illustrate: consider a clerk who needs to see all information in the loan relation except amount.

    Let the view branch-loan be given to the clerk:

     create view branch-loan as

    select bname, loan#

    from loan

    Since SQL allows a view name to appear anywhere a relation name may appear, the clerk can write:

     insert into branch-loan

    values (``SFU'', ``L-307'')

    This insertion is represented by an insertion into the actual relation loan, from which the view is constructed. However, we have no value for amount.

    This insertion results in (``SFU'', ``L-307'', null) being inserted into the loan relation.

    As we saw, when a view is defined in terms of several relations, serious problems can result. As a result, many SQL-based systems impose the constraint that a modification is permitted through a view only if the view in question is defined in terms of one relation in the database.

Osmar Zaiane
Fri May 22 19:39:12 PDT 1998