Views

Views

In our examples up to this point, we have operated at the logical-model level. That is, we have assumed that the relations in the collection we are given are the actual relations stored in the database.

It is not desirable for all users to see the entire logical model. Security considerations may require that certain data be hidden from users. Consider a person who needs to know a customer’s loan number and branch name, but has no need to see the loan amount. This person should see a relation described, in the relational algebra, by

Aside from security concerns, we may wish to create a personalized collection of relations that is better matched to a certain user’s intuition than is the logical model.

An employee in the advertising department, for example, might like to see a relation consisting of the customers who have either an account or a loan at the bank, and the branches with which they do business. The relation that we would create for that employee is

Any relation that is not part of the logical model, but is made visible to a user as a virtual relation, is called a view. It is possible to support a large number of views on top of any given set of actual relations.

View Definition

We define a view by using the create view statement. To define a view, we must give the view a name, and must state the query that computes the view. The form of the create view statement is

create view v as <query expression>

where <query expression> is any legal relational-algebra query expression. The view name is represented by v.

As an example, consider the view consisting of branches and their customers. We wish this view to be called all-customer. We define this view as follows:

Once we have defined a view, we can use the view name to refer to the virtual relation that the view generates. Using the view all-customer, we can find all customers of the Perryridge branch by writing

Recall that we wrote the same query in Section 3.2.1 without using views.

View names may appear in any place where a relation name may appear, so long as no update operations are executed on the views. We study the issue of update operations on views in Section 3.5.2.

View definition differs from the relational-algebra assignment operation. Suppose that we define relation r1 as follows:

We evaluate the assignment operation once, and r1 does not change when we up- date the relations depositor, account, loan, or borrower. In contrast, any modification we make to these relations changes the set of tuples in the view all-customer as well. Intuitively, at any given time, the set of tuples in the view relation is the result of evaluation of the query expression that defines the view at that time.

Thus, if a view relation is computed and stored, it may become out of date if the relations used to define it are modified. To avoid this, views are usually implemented as follows. When we define a view, the database system stores the definition of the view itself, rather than the result of evaluation of the relational-algebra expression that defines the view. Wherever a view relation appears in a query, it is replaced by the stored query expression. Thus, whenever we evaluate the query, the view relation gets recomputed.

Certain database systems allow view relations to be stored, but they make sure that, if the actual relations used in the view definition change, the view is kept up to date. Such views are called materialized views. The process of keeping the view up to date is called view maintenance, covered in Section 14.5. Applications that use a view frequently benefit from the use of materialized views, as do applications that demand fast response to certain view-based queries. Of course, the benefits to queries from the materialization of a view must be weighed against the storage costs and the added overhead for updates.

Updates through Views and Null Values

Although views are a useful tool for queries, they present serious problems if we ex- press updates, insertions, or deletions with them. The difficulty is that a modification to the database expressed in terms of a view must be translated to a modification to the actual relations in the logical model of the database.

To illustrate the problem, consider a clerk who needs to see all loan data in the loan relation, except loan-amount. Let loan-branch be the view given to the clerk. We define this view as

Since we allow a view name to appear wherever a relation name is allowed, the clerk can write:

This insertion must be represented by an insertion into the relation loan, since loan is the actual relation from which the database system constructs the view loan-branch. However, to insert a tuple into loan, we must have some value for amount. There are two reasonable approaches to dealing with this insertion:

• Reject the insertion, and return an error message to the user.

• Insert a tuple (L-37, “Perryridge”, null) into the loan relation.

Another problem with modification of the database through views occurs with a view such as

This view lists the loan amount for each loan that any customer of the bank has. Consider the following insertion through this view:

The only possible method of inserting tuples into the borrower and loan relations is to insert (“Johnson”, null) into borrower and (null, null, 1900) into loan. Then, we obtain the relations shown in Figure 3.36. However, this update does not have the desired effect, since the view relation loan-info still does not include the tuple (“Johnson”, 1900). Thus, there is no way to update the relations borrower and loan by using nulls to get the desired update on loan-info.

Because of problems such as these, modifications are generally not permitted on view relations, except in limited cases. Different database systems specify different conditions under which they permit updates on view relations; see the database system manuals for details. The general problem of database modification through views has been the subject of substantial research, and the bibliographic notes pro- vide pointers to some of this research.

Views Defined by Using Other Views

In Section 3.5.1 we mentioned that view relations may appear in any place that a relation name may appear, except for restrictions on the use of views in update expressions. Thus, one view may be used in the expression defining another view. For example, we can define the view perryridge-customer as follows:

where all-customer is itself a view relation.

View expansion is one way to define the meaning of views defined in terms of other views. The procedure assumes that view definitions are not recursive; that is, no view is used in its own definition, whether directly, or indirectly through other view definitions. For example, if v1 is used in the definition of v2, v2 is used in the definition of v3, and v3 is used in the definition of v1, then each of v1, v2, and v3 is recursive. Recursive view definitions are useful in some situations, and we revisit them in the context of the Data log language, in Section 5.2.

Let view v1 be defined by an expression e1 that may itself contain uses of view relations. A view relation stands for the expression defining the view, and therefore a view relation can be replaced by the expression that defines it. If we modify an expression by replacing a view relation by the latter’s definition, the resultant expression may still contain other view relations. Hence, view expansion of an expression repeats the replacement step as follows:

As long as the view definitions are not recursive, this loop will terminate. Thus, an expression e containing view relations can be understood as the expression resulting from view expansion of e, which does not contain any view relations.

As an illustration of view expansion, consider the following expression:

There are no more uses of view relations, and view expansion terminates.