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:

Topics You May Be Interested In
Constraints And Characteristics Of Specialization And Generalization Hierarchies Specialty Databases
View Of Data Keys
Database Languages Relational Query Languages
Database Design Sql Data Definition
Data Mining And Information Retrieval Null Values

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.

Topics You May Be Interested In
View Of Data Database Users And Administrators
Example Of Other Notation: Representing Specialization And Generalization In Uml Class Diagrams Database Schema
Database Security Keys
Relational Databases Relationship Types Of Degree Higher Than Two
Data Mining And Information Retrieval Aggregate Functions

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>;

Topics You May Be Interested In
Database-system Applications Structure Of Relational Databases
Data Abstraction, Knowledge Representation, And Ontology Concepts Overview Of Object Database Concepts
Using High-level Conceptual Data Models For Database Design Introduction To Database Security Issues
Database Architecture A Sample Database Application
Data Mining And Information Retrieval Ordering The Display Of Tuples

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.

Topics You May Be Interested In
Example Of Other Notation: Representing Specialization And Generalization In Uml Class Diagrams Relational Operations
Database Users And Administrators Sql Data Definition
Keys Ordering The Display Of Tuples
Introduction To Database Security Issues Sensitive Data And Types Of Disclosures
Chapter 11 Object And Object-relational Databases Aggregate Functions

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’;

Topics You May Be Interested In
Subclasses, Superclasses, And Inheritance Chapter 11 Object And Object-relational Databases
Specialization And Generalization Relational Query Languages
Constraints And Characteristics Of Specialization And Generalization Hierarchies Entity Types, Entity Sets, Attributes, And Keys-1
Using High-level Conceptual Data Models For Database Design Where Clause Predicates
Purpose Of Database Systems Control Measures

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:

Topics You May Be Interested In
View Of Data Additional Basic Operations-the Rename Operation
Relational Databases Attribute Specification In Select Clause
Relational Query Languages Weak Entity Types
Basic Structure Of Sql Queries Access Control, User Accounts, And Database Audits
Introduction To Database Security Issues Database Security And The Dba

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.

Topics You May Be Interested In
Specialization And Generalization Data Mining And Information Retrieval
Example Of Other Notation: Representing Specialization And Generalization In Uml Class Diagrams Relational Query Languages
Using High-level Conceptual Data Models For Database Design Introduction To Database Security Issues
Purpose Of Database Systems Refining The Er Design For The Company Database
Database Security Relationship Between Information Security Versus Information Privacy

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:

Topics You May Be Interested In
Using High-level Conceptual Data Models For Database Design Introduction To Database Security Issues
Purpose Of Database Systems Initial Conceptual Design Of The Company Database
Database Design Refining The Er Design For The Company Database
Transaction Management Er Diagrams, Naming Conventions, And Design Issues
Structure Of Relational Databases Ordering The Display Of Tuples

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’)

Topics You May Be Interested In
Database-system Applications Introduction To Database Security Issues
Constraints And Characteristics Of Specialization And Generalization Hierarchies Additional Basic Operations-the Rename Operation
Database Security Initial Conceptual Design Of The Company Database
Specialty Databases Attribute Specification In Select Clause
Discretionary Access Control Based On Granting And Revoking Privileges Refining The Er Design For The Company Database

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.

Topics You May Be Interested In
Using High-level Conceptual Data Models For Database Design Additional Basic Operations-the Rename Operation
Structure Of Relational Databases Entity Types, Entity Sets, Keys, And Value Sets-2
Database Schema Refining The Er Design For The Company Database
Overview Of The Sql Query Language Ordering The Display Of Tuples
Sql Data Definition Set Operations-introduction

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

Topics You May Be Interested In
Subclasses, Superclasses, And Inheritance Schema Diagrams
Using High-level Conceptual Data Models For Database Design Relational Operations
View Of Data Relationship Types, Relationship Sets, Roles, And Structural Constraints
History Of Database Systems Attribute Specification In Select Clause
Xml: Extensible Markup Language Relationship Types Of Degree Higher Than Two

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.

Topics You May Be Interested In
View Of Data Relational Databases
Using High-level Conceptual Data Models For Database Design Specialty Databases
View Of Data Introduction To Database Security Issues
Database Languages Additional Basic Operations-string Operations
Database Design Aggregate Functions

• 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’;

Views

 

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

+
Ans: We introduced the natural join operation. SQL provides other forms of the join operation, including the ability to specify an explicit join predicate, and the ability to include in the result tuples that are excluded by natural join. We shall discuss these forms of join in this section. view more..
+
Ans: We have restricted our attention until now to the extraction of information from the database. Now, we show how to add,remove, or change information with SQL. view more..
+
Ans: SQL provides a mechanism for nesting subqueries. A subquery is a select-from where expression that is nested within another query. view more..
+
Ans: 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. view more..
+
Ans: A transaction consists of a sequence of query and/or update statements. view more..
+
Ans: Integrity constraints ensure that changes made to the database by authorized users do not result in a loss of data consistency. view more..




Rating - 3/5
485 views

Advertisements