Updates Through Views and Null Values

1. Updates, insertions and deletions using views can cause problems. The modifications on a view must be transformed to modifications of the actual relations in the conceptual model of the database.

2. An example will illustrate: consider a clerk who needs to see all information in the borrow relation except amount.

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

   

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

   

   This insertion is represented by an insertion into the actual relation borrow, from which the view is constructed.

   However, we have no value for amount. A suitable response would be

   • Reject the insertion and inform the user.
   • Insert (``SFU'',3,``Ruth'',null) into the relation.

   The symbol null represents a null or place-holder value. It says the value is unknown or does not exist.

4. Another problem with modification through views: consider the view

   

   This view lists the cities in which the borrowers of each branch live.

   Now consider the insertion

   

   Using nulls is the only possible way to do this (see Figure 3.22 in the textbook).

   If we do this insertion with nulls, now consider the expression the view actually corresponds to:

   

   As comparisons involving nulls are always false, this query misses the inserted tuple.

   To understand why, think about the tuples that got inserted into borrow and customer. Then think about how the view is recomputed for the above query.