SQL views 

Reading material

Elmasri&Navathe: Chapter 8.5 (pages  278-282)

 

There are two types of tables in an SQL-database: base tables and derived tables. The tables we have discussed thus far are base tables. Their rows are physically stored on the disks of the database, and they obtain their data through the insert, update and delete operations. Derived tables are specified with queries. 

Views are derived tables that are not stored anywhere. They exist only as specifications.  However, they may be used in queries like base tables. They may also be provided user access like base tables. Some database management systems make it possible even to perform resticted database updates on the views.

Snapshots or materialized views are another form of derived tables. In this case the result of  a query is stored as a table to be refreshed according to the given specification. In the following we concentrate on traditional non-materialized views.

 

A view is defined as follows

 

format 
create view view_name [(column_list)] as query
example 
create view customer_yearly
  (CustomerId, Year, Orders, Revenue) as
  select Customer, extract(YEAR FROM WhenMade), 
           count(*), sum(TotalPrice)
  from Ordered
  group by Customer, extract(year FROM WhenMade)

This example makes  the yearly count of orders and the yearly  revenue of a customer available as a single row in  the table customer_yearly.

How to process view based queries

A query that uses  views is executed so that  the query and the specifications of the views it contains are combined and the resulting query is executed. Suppose we have the query

  select * from customer_yearly 
  where year=2001
The executable query will be obtained by combining this query with the specification of the view Customer_yearly, and it will be

 

  select * 
   from ( select Customer as CustomerId, 
                 extract(YEAR FROM WhenMade) as Year,
                 count(*) as Orders, 
                 sum(TotalPrice) as Revenue
           from Ordered
           group by Customer, extract(year FROM WhenMade))
   where year=2001

 

The main reasons for the use of views are

Data independence

Views increase data indepence. If the queries that are embedded in programs use views instead of base tables, it is possible to alter the structure of the base tables quite radically  without changing the queries and without changing  the programs. It is enough to respecify the views.

Let us assume that for the sake of efficiency  our example table Customer will be split into two tables  Active_customer and Passive_customer which together substitute the table Customer. After this modification the queries that use the table Customer become invalid.  They can be made valid again by specifying Customer as a view as follows: 

create view Customer as
  select * from active_customer
  union
  select * from passive_customer
 

No programs containing queries on table Customer need to be changed. However, this does not apply to those programs that modified the  Customer table. They have to be changed to modify the new tables, typically table Active_customer.

 

Better security control

Access privileges may be granted on views as on base tables. The privileges granted on views may be very specific. Access may be restricted on some rows of a table or  on summary information, only. The following example restricts the the selects on view Customer_yearly to the  role 'manager'.

 

grant select on customer_yearly to manager;

Even if the customer specific sales figures are restricted to the managers the total amount of yearly orders and the yearly revenue may be made available for the entire staff  as follows

create view yearly_sales
  (Year, Orders, Revenue) as
  select extract(YEAR FROM WhenMade), 
         count(*), sum(TotalPrice)
  from Ordered
  group by extract(year FROM WhenMade);

  grant select on yearly_sales to staff;

Views may also be defined to have user specific contents. The following example defines a delivery boy specific view that contains those oders assigned for the boy but not delivered yet..

create view my_deliveries 
  (customer_name, address, orderId, totalprice) as
  select customer.name, address, orderId, totalprice
  from delivery, ordered, customer
  where delivery.deliveredBy=current_user and
          delivery.orderId=ordered.orderId and
          ordered.customer=customer.customerId and
          whenDelivered is null;
 
  grant select on my_deliverables to public;

This query uses the function current_user to retrieve the user account name of the user.  To work properly it assumes that the user account name is used as the identifier (boyid) of delivery boys. The privilege to read data from this view is granted for all users. If a user is not a delivery boy the view has no rows. Each delivery boy sees his own deliveries.

Easier queries

Many queries contain complex join conditions and computations when they are based on the base tables of the database. If these are embedded in view specifications and the users base their queries on these view the composition of the queries becomes easier and less time consuming. It may cause savings in query contruction costs. However the use of views does not make the queries more efficient. Materialized views instead are used to achieve  efficiency..

Maintenanace operations on views

Some database management systems make it possible to maintain the database using views. The actual targets of the oparations are not the views but the base tables on which the views are based. All views cannot be used as targets for updates, inserts and deletes because there has to be an unambiguous way of how to tranform the operations on views to operations on the base tables. Thus views that provide aggregate data cannot be used. The use of the union, intersection and join operations in view definition also results in  views that cannot be used in maintaining the database. Thus our example views  Customer_yearly, Yearly_sales and my_deliveries cannot be used in updates.

Typically updating through a view is possible only if

The view 

create view customer_of_area_1 as
   select * from customer 
   where areacode='A01'

suits for maintenance.  All operations on it may be transformed into operations on the table Customer.  If we want to guarantee that the effects of updates remain within view definitions, for example, we do not allow insertion of customers living in area A02 through the view customer_of_area_1, we must add a 'check option' into the view definition and define it as: 

create view customer_of_area_1 with check option as
   select * from customer 
   where areacode='A01'