HP 3000 Manuals HP 3000 Manuals
Designing Views
A view is a virtual table defined on one or more tables or views, or a
combination of tables and views. The term base table refers to the table
in which data is actually stored. Views and query results, although they
appear in the form of a table, are virtual tables derived from one or
more base tables. ALLBASE/SQL maintains information and statistics on
views in the SYSTEM.TABLE, SYSTEM.SECTION, and SYSTEM.VIEWDEF views in
the system catalog, which is presented fully in the chapter, "System
Catalog." The following are some primary reasons for defining a view:
* Restricting access to the base tables.
* Keeping data independent of the applications that use it.
In an interactive environment, views are commonly used to restrict access
to base tables. A large table containing data from several areas can be
broken up into smaller views, one for each area. The data is available
to those who need it, but the entire table is secure from unauthorized
users. For example, in a personnel database, a view containing employee
names, extensions, and locations can be derived from a table that also
contains home addresses, salary, and other information to which access
should be restricted. Users can then be granted access to the views
without being able to access the sensitive data in the base tables.
Refer to Chapter 5, "Database Creation and Security," for information on
coordinating view definition with security design.
Data independence is usually a concern in programmatic environments. An
application that accesses several tables would have to be modified each
time the tables were altered. A view defined to look like the old tables
would keep applications independent of database changes. Refer to the
appropriate ALLBASE/SQL Application Programming Guide for more
information on how views affect and are manipulated by application
programs.
View definition is not restricted to base tables. You can define a view
on another view's derived columns. Refer to the ALLBASE/SQL Reference
Manual for a complete list of restrictions on defining views.
To define a view, use the same guidelines as for defining a table (refer
to "Defining Tables" in this chapter), plus the following:
* ALLBASE/SQL does not limit the number of views you can define.
* A view cannot be created with an ORDER BY clause.
* You cannot define an index on a view.
* You can define as many as 1023 columns per view.
* You can use up to 32 base tables to define a view, which includes
the tables that comprise a view from which another view is
defined.
You can manipulate data through views as you would through tables, but
certain restrictions apply. In general, you cannot INSERT, DELETE, or
UPDATE through a view if any of the following are used to define it:
* Multiple tables (joins).
* A GROUP BY clause.
* An aggregate function (AVG, MAX, MIN, SUM, COUNT).
* A DISTINCT in the SELECT clause.
* Defined fields in the SELECT clause, e.g., ColumnA + ColumnB. In
this case, DELETE is allowed. For more information, refer to
"Updatability of Queries" in the "SQL Queries" chapter of the
ALLBASE/SQL Reference Manual.
* A UNION clause.
A view is essentially a stored SELECT statement. Therefore you cannot
alter a view by adding columns to it. To add a column to a view, simply
delete the old view and create a new view, specifying the additional
column in the SELECT clause.
For the most part views are used to restrict data access. However, views
are actually an additional layer to the base tables. This means that
each time a view is used, ALLBASE/SQL must perform an additional step to
build the view before the user gets the data. Preprocessing applications
also takes longer if the application contains views because the system
catalog must be accessed twice: once for the view and once for the base
tables on which the view is defined. Ultimately, the DBA must weigh the
factors for and against creating views depending on the needs of the
users.