Updating Table Row Views

Views are helpful in reporting because they can automate your queries a bit and save you the trouble of constantly resubmitting complex queries. As we saw in the last exercise, you can quickly select from the view, which can filter and combine information from more than one table:

CREATE VIEW
MyView AS
SELECT * FROM 
CustomerInfo, PhoneNumbers 
WHERE
CustomerInfo.CustomerID = PhoneNumbers.CustomerID

You create views as shown above and use them to update information in the underlying tables. There are rules that are enforced by your database engine, but generally speaking, views are created as either read-only or updateable.
To understand the difference, you need to view this process through the eyes of your database engine. You need to understand what the engine has to do to fulfill an update request so you can to understand what makes a view updateable or read-only. To make a view updateable, you must have a SELECT statement that identifies the specific rows with which it is working. What does this mean? It means that when you send information to the database, the engine must be able to determine, very specifically, where that information should be stored.We will look at an example in the next lesson.

The problem of efficiently implementing a view for querying is complex. Two main approaches have been suggested. One strategy, called query modification, involves modifying or transforming the view query (submitted by the user) into a query on the underlying base tables. For example, the query QV1 would be automatically modified to the following query by the DBMS:

SELECT Fname, Lname
FROM EMPLOYEE, PROJECT, WORKS_ON
WHERE Ssn=Essn AND Pno=Pnumber
AND Pname='ProductX';

The disadvantage of this approach is that it is inefficient for views defined via complex queries that are time-consuming to execute, especially if multiple queries are going to be applied to the same view within a short period of time. The second strategy, called view materialization, involves physically creating a temporary view table when the view is first queried and keeping that table on the assumption that other queries on the view will follow. In this case, an efficient strategy for automatically updating the view table when the base tables are updated must be developed in order to keep the view up-to-date. Techniques using the concept of incremental update have been developed for this purpose, where the DBMS can determine what new tuples must be inserted, deleted, or modified in a materialized view table when a database update is applied to one of the defining base tables. The view is generally kept as a materialized (physically stored) table as long as it is being queried. If the view is not queried for a certain period of time, the system may then automatically remove the physical table and recompute it from scratch when future queries reference the view.
Updating of views is complicated and can be ambiguous. In general, an update on a view defined on a single table without any aggregate functions can be mapped to an update on the underlying base table under certain conditions. For a view involving joins, an update operation may be mapped to update operations on the underlying base relations in multiple ways. Hence, it is often not possible for the DBMS to determine which of the updates is intended. To illustrate potential problems with updating a view defined on multiple tables, consider the WORKS_ON1 view, and suppose that we issue the command to update the PNAME attribute of 'John Smith' from 'ProductX' to 'ProductY'. This view update is shown in UV1:

UV1: UPDATEWORKS_ON1
SET Pname = 'ProductY'
WHERE Lname='Smith' AND Fname='John'
AND Pname='ProductX';