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 clerk
who needs to know an instructor’s ID, name and department name, but does not
have authorization to see the instructor’s salary amount. This person should see
a relation described in SQL, by:

select ID, name, dept_name
from instructor;

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. We may want to have a list of all course sections offered by the Physics
department in the Fall 2009 semester, with the building and room number of each
section. The relation that we would create for obtaining such a list is:

select course.course_id, sec_id, building, room_number
from course, section
where course.course_id = section.course_id

and course.dept_name = ’Physics’
and section.semester = ’Fall’
and section.year = ’2009’;

It is possible to compute and store the results of the above queries and then
make the stored relations available to users. However, if we did so, and the
underlying data in the relations instructor, course, or section changes, the stored
query results would then no longer match the result of reexecuting the query on
the relations. In general, it is a bad idea to compute and store query results such
as those in the above examples (although there are some exceptions, which we
study later).

Instead, SQL allows a “virtual relation” to be defined by a query, and the
relation conceptually contains the result of the query. The virtual relation is not
precomputed and stored, but instead is computed by executing the query when?ever the virtual relation is used.

Any such 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.

1. View Definition

We define a view in SQL by using the create view command. 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 command is:

create view v as <query expression>;

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

Consider again the clerk who needs to access all data in the instructor relation,
except salary. The clerk should not be authorized to access the instructor relation
(we see later, in Section 4.6, how authorizations can be specified). Instead, a
view relation faculty can be made available to the clerk, with the view defined as
follows:

create view faculty as
select ID, name, dept_name
from instructor;

As explained earlier, the view relation conceptually contains the tuples in the
query result, but is not precomputed and stored. Instead, the database system
stores the query expression associated with the view relation. Whenever the view
relation is accessed, its tuples are created by computing the query result. Thus,
the view relation is created whenever needed, on demand.

To create a view that lists all course sections offered by the Physics department
in the Fall 2009 semester with the building and room number of each section, we
write:

create view physics fall 2009 as

select course.course_id, sec_id, building, room_number
from course, section
where course.course_id = section.course_id

and course.dept_name = ’Physics’
and section.semester = ’Fall’
and section.year = ’2009’;

2. Using Views in SQL Queries

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 physics fall 2009, we can find
all Physics courses offered in the Fall 2009 semester in the Watson building by
writing:

select course_id
from physics fall 2009
where building= ’Watson’;

View names may appear in a query any place where a relation name may appear,
The attribute names of a view can be specified explicitly as follows:

create view departments_total_salary(dept_name, total_salary) as

select dept name, sum (salary)
from instructor
group by dept name;

The preceding view gives for each department the sum of the salaries of all the
instructors at that department. Since the expression sum(salary) does not have a
name, the attribute name is specified explicitly in the view definition.

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. 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 query 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 is
recomputed.

One view may be used in the expression defining another view. For example,
we can define a view physics fall 2009 watson that lists the course ID and room
number of all Physics courses offered in the Fall 2009 semester in the Watson
building as follows:

create view physics fall 2009 watson as

select course_id, room_number
from physics fall 2009
where building= ’Watson’;

where physics fall 2009 watson is itself a view relation. This is equivalent to:

create view physics fall 2009 watson as

(select course_id, room_number
from (select course.course_id, building, room_number

from course, section
where course.course_id = section.course_id

and course.dept_name = ’Physics’
and section.semester = ’Fall’
and section.year = ’2009’)

where building= ’Watson’;

3. Materialized Views

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.

For example, consider the view departments total salary. If the above view is
materialized, its results would be stored in the database. However, if an instructor
tuple is added to or deleted from the instructor relation, the result of the query
defining the view would change, and as a result the materialized view’s contents
must be updated. Similarly, if an instructor’s salary is updated, the tuple in
departments total salary corresponding to that instructor’s department must be
updated.

The process of keeping the materialized view up-to-date is called materialized view maintenance (or often, just view maintenance) and is covered in Sec?tion 13.5. View maintenance can be done immediately when any of the relations
on which the view is defined is updated. Some database systems, however, perform view maintenance lazily, when the view is accessed. Some systems update
materialized views only periodically; in this case, the contents of the materialized
view may be stale, that is, not up-to-date, when it is used, and should not be used
if the application needs up-to-date data. And some database systems permit the
database administrator to control which of the above methods is used for each
materialized view.

Applications that use a view frequently may benefit if the view is materi?alized. Applications that demand fast response to certain queries that compute
aggregates over large relations can also benefit greatly by creating materialized
views corresponding to the queries. In this case, the aggregated result is likely to
be much smaller than the large relations on which the view is defined; as a result
the materialized view can be used to answer the query very quickly, avoiding
reading the large underlying relations. 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.

SQL does not define a standard way of specifying that a view is materialized, but many database systems provide their own SQL extensions for this task.
Some database systems always keep materialized views up-to-date when the underlying relations change, while others permit them to become out of date, and
periodically recompute them.

4. Update of a View

Although views are a useful tool for queries, they present serious problems if
we express 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.
Suppose the view faculty, which we saw earlier, is made available to a clerk.
Since we allow a view name to appear wherever a relation name is allowed, the
clerk can write:

insert into faculty
values (’30765’, ’Green’, ’Music’);

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

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

• Insert a tuple (’30765’, ’Green’, ’Music’, null) into the instructor relation.

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

create view instructor info as
select ID, name, building
from instructor, department
where instructor.dept_name= department.dept_name;

This view lists the ID, name, and building-name of each instructor in the university.
Consider the following insertion through this view:

insert into instructor info
values (’69987’, ’White’, ’Taylor’);

Suppose there is no instructor with ID 69987, and no department in the Taylor
building. Then the only possible method of inserting tuples into the instructor
and department relations is to insert (’69987’, ’White’, null, null) into instructor
and (null, ’Taylor’, null) into department. Then, we obtain the relations shown in
Figure 4.7. However, this update does not have the desired effect, since the view
relation instructor info still does not include the tuple (’69987’, ’White’, ’Taylor’).
Thus, there is no way to update the relations instructor and department by using
nulls to get the desired update on instructor info.

Because of problems such as these, modifications are generally not permit?ted 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 modifica?tion through views has been the subject of substantial research, and the biblio?graphic notes provide pointers to some of this research.

In general, an SQL view is said to be updatable (that is, inserts, updates or
deletes can be applied on the view) if the following conditions are all satisfied by
the query defining the view:

• The from clause has only one database relation.

• The select clause contains only attribute names of the relation, and does not
have any expressions, aggregates, or distinct specification.

• Any attribute not listed in the select clause can be set to null; that is, it does
not have a not null constraint and is not part of a primary key.

• The query does not have a group by or having clause.

Under these constraints, the update, insert, and delete operations would be
allowed on the following view:

create view history_instructors as
select *
from instructor
where dept_name= ’History’;

Even with the conditions on updatability, the following problem still remains.
Suppose that a user tries to insert the tuple (’25566’, ’Brown’, ’Biology’, 100000)
into the history instructors view. This tuple can be inserted into the instructor
relation, but it would not appear in the history instructors view since it does not
satisfy the selection imposed by the view.

By default, SQL would allow the above update to proceed. However, views
can be defined with a with check option clause at the end of the view definition;
then, if a tuple inserted into the view does not satisfy the view’s where clause
condition, the insertion is rejected by the database system. Updates are similarly
rejected if the new value does not satisfy the where clause conditions.

SQL:1999 has a more complex set of rules about when inserts, updates, and
deletes can be executed on a view, that allows updates through a larger class of
views; however, the rules are too complex to be discussed here.

Frequently Asked Questions