Views (Virtual Tables) in SQL

Views (Virtual Tables) in SQL

In this section we introduce the concept of a view in SQL. We show how views are specified, and then we discuss the problem of updating views and how views can be implemented by the DBMS.

1. Concept of a View in SQL

A view in SQL terminology is a single table that is derived from other tables. These other tables can be base tables or previously defined views. A view does not necessarily exist in physical form; it is considered to be a virtual table, in contrast to base tables, whose tuples are always physically stored in the database. This limits the possible update operations that can be applied to views, but it does not provide any limitations on querying a view.

We can think of a view as a way of specifying a table that we need to reference frequently, even though it may not exist physically. For example, referring to the COMPANY database in Figure 3.5 we may frequently issue queries that retrieve the employee name and the project names that the employee works on. Rather than having to specify the join of the three tables EMPLOYEE, WORKS_ON, and PROJECT every time we issue this query, we can define a view that is specified as the result of these joins. Then we can issue queries on the view, which are specified as single-table retrievals rather than as retrievals involving two joins on three tables. We call the EMPLOYEE, WORKS_ON, and PROJECT tables the defining tables of the view.

2. Specification of Views in SQL

In SQL, the command to specify a view is CREATE VIEW. The view is given a (vir-tual) table name (or view name), a list of attribute names, and a query to specify the contents of the view. If none of the view attributes results from applying functions or arithmetic operations, we do not have to specify new attribute names for the view, since they would be the same as the names of the attributes of the defining tables in the default case. The views in V1 and V2 create virtual tables whose schemas are illustrated in Figure 5.2 when applied to the database schema of Figure 3.5.

V1:    CREATE VIEW   WORKS_ON1

          AS SELECT         Fname, Lname, Pname, Hours

          FROM        EMPLOYEE, PROJECT, WORKS_ON

          WHERE     Ssn=Essn AND Pno=Pnumber;

V2:    CREATE VIEW   DEPT_INFO(Dept_name, No_of_emps, Total_sal)

          AS SELECT         Dname, COUNT (*), SUM (Salary)

          FROM        DEPARTMENT, EMPLOYEE

          WHERE     Dnumber=Dno

          GROUP BY         Dname;

In V1, we did not specify any new attribute names for the view WORKS_ON1 (although we could have); in this case, WORKS_ON1 inherits the names of the view attributes from the defining tables EMPLOYEE, PROJECT, and WORKS_ON. View V2

explicitly specifies new attribute names for the view DEPT_INFO, using a one-to-one correspondence between the attributes specified in the CREATE VIEW clause and those specified in the SELECT clause of the query that defines the view.

We can now specify SQL queries on a view—or virtual table—in the same way we specify queries involving base tables. For example, to retrieve the last name and first name of all employees who work on the ‘ProductX’ project, we can utilize the WORKS_ON1 view and specify the query as in QV1:

QV1:       SELECT  Fname, Lname

FROM                   WORKS_ON1

WHERE                 Pname=‘ProductX’;

The same query would require the specification of two joins if specified on the base relations directly; one of the main advantages of a view is to simplify the specification of certain queries. Views are also used as a security and authorization mecha-nism (see Chapter 24).

A view is supposed to be always up-to-date; if we modify the tuples in the base tables on which the view is defined, the view must automatically reflect these changes. Hence, the view is not realized or materialized at the time of view definition but rather at the time when we specify a query on the view. It is the responsibility of the DBMS and not the user to make sure that the view is kept up-to-date. We will discuss various ways the DBMS can apply to keep a view up-to-date in the next subsection.

If we do not need a view any more, we can use the DROP VIEW command to dispose of it. For example, to get rid of the view V1, we can use the SQL statement in V1A:

V1A:       DROP VIEW WORKS_ON1;

3. View Implementation, View Update, and Inline Views

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 refer-ence 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:                              UPDATE WORKS_ON1

SET                                              Pname = ‘ProductY’

WHERE                                        Lname=‘Smith’ AND Fname=‘John’

AND Pname=‘ProductX’;

This query can be mapped into several updates on the base relations to give the desired update effect on the view. In addition, some of these updates will create additional side effects that affect the result of other queries. For example, here are two possible updates, (a) and (b), on the base relations corresponding to the view update operation in UV1:

(a):       UPDATE WORKS_ON                     

            SET     Pno =  ( SELECT       Pnumber

                                    FROM PROJECT

                                    WHERE          Pname=‘ProductY’ )

            WHERE          Essn IN ( SELECT      Ssn

                                    FROM EMPLOYEE

                                    WHERE          Lname=‘Smith’ AND Fname=‘John’ )

                        AND               

                        Pno =  ( SELECT       Pnumber

                                    FROM PROJECT

                                    WHERE          Pname=‘ProductX’ );

(b):       UPDATE PROJECT  SETPname = ‘ProductY’

            WHERE          Pname = ‘ProductX’; 

Update (a) relates ‘John Smith’ to the ‘ProductY’ PROJECT tuple instead of the ‘ProductX’ PROJECT tuple and is the most likely desired update. However, (b) would also give the desired update effect on the view, but it accomplishes this by changing the name of the ‘ProductX’ tuple in the PROJECT relation to ‘ProductY’. It is quite unlikely that the user who specified the view update UV1 wants the update to be interpreted as in (b), since it also has the side effect of changing all the view tuples with Pname = ‘ProductX’.

Some view updates may not make much sense; for example, modifying the Total_sal attribute of the DEPT_INFO view does not make sense because Total_sal is defined to be the sum of the individual employee salaries. This request is shown as UV2:

UV2:       UPDATE DEPT_INFO

SET                       Total_sal=100000

WHERE                 Dname=‘Research’;

A large number of updates on the underlying base relations can satisfy this view update.

Generally, a view update is feasible when only one possible update on the base rela-tions can accomplish the desired update effect on the view. Whenever an update on the view can be mapped to more than one update on the underlying base relations, we must have a certain procedure for choosing one of the possible updates as the most likely one. Some researchers have developed methods for choosing the most likely update, while other researchers prefer to have the user choose the desired update mapping during view definition.

In summary, we can make the following observations:

              A view with a single defining table is updatable if the view attributes contain the primary key of the base relation, as well as all attributes with the NOT NULL constraint that do not have default values specified.

              Views defined on multiple tables using joins are generally not updatable.

              Views defined using grouping and aggregate functions are not updatable.

In SQL, the clause WITH CHECK OPTION must be added at the end of the view definition if a view is to be updated. This allows the system to check for view updatabil-ity and to plan an execution strategy for view updates.

It is also possible to define a view table in the FROM clause of an SQL query. This is known as an in-line view. In this case, the view is defined within the query itself.