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.
Related Topics
Privacy Policy, Terms and Conditions, DMCA Policy and Compliant
Copyright © 2018-2024 BrainKart.com; All Rights Reserved. Developed by Therithal info, Chennai.