Chapter 10

Views

Topics in this Chapter

• What are Views For?• View Retrievals• View Updates• Snapshots• SQL Facilities

Views

• A view is created by applying an expression of relational algebra to a base relvar

• A view is a derived or virtual relvar• Updates applied to the base relvar are

immediately visible to the view• Updates to values in the view “really” only

update the base relvar by reiterating the original algebraic expression

What are Views For?

• A view can make subsequent queries much simpler (analogous to a macro)

• Views allow the same data to be seen by different users in different ways at the same time

• Views provide automatic security for hidden data

• Views can promote logical data independence

Logical Data Indendence

• Logical data independence means the immunity of users and user programs to changes in the logical structure of the database

• Growth aspect: New attributes or relvars can be added without affecting existing user programs

• Restructuring aspect: If a base relvar is split, the “old” structure can be emulated by a view; the “old” programs can now access the view

Base Relvars and Views

• The Principle of Interchangeability(of Base and Derived Relvars):– There are no arbitrary and

unnecessary distinctions between base and derived relvars

• The Principle of Database Relativity:– From a user’s point of view, base

relvars and views are logically equivalent

View Retrievals

• A relational expression is a relation-valued function

• It takes in relation(s) as arguments, and returns a relation

• A view is created using a relational expression, in which the relation returned is the view

View Updates

• A view is a relvar, and so is updateable by definition

• Earlier discussions of view updates tended to limit them

• It is in principle not necessary to limit them, apart from constraint enforcement

• View updates must adhere to the Golden Rule: no relvar must ever be allowed to violate its own predicate

Snapshots

• Snapshots are derived relvars, but they are not views

• Snapshots are the result of a relational operation that generates a read only (stored) relvar that is refreshed periodically

• An important case of controlled redundancy, used often for point in time reporting

• Snapshots have come to be misnamed “materialized views,” an oxymoron

SQL Facilities

CREATE VIEW <view name> AS

<table exp>

[WITH [<qualifier>] CHECK OPTION ] ;

• WITH CHECK OPTION is used for INSERTs and SELECTs: will make them fail if integrity constraints are violated; the default is that they will succeed regardless

• Can only be used with updateable views, as defined in SQL, which is narrower

Syntax: 

CREATE VIEW <view name> [ ( <column(s)> ) ]

AS <subquery> 

[ WITH [CASCADE | LOCAL] CHECK OPTION ] ;  

    some limitations on the subqueries—no “conceptual” reason—just implementation difficulty or oversight    except for “ORDER BY”

makes no sense since tableshave no order

 

by default, the VIEW “inherits” column names—optionally it can be given new ones

+------+-------+--------+--------+| S# | SNAME | STATUS | CITY |+------+-------+--------+--------+| S1 | Smith | 20 | London || S2 | Jones | 10 | Paris || S3 | Blake | 30 | Paris || S4 | Clark | 20 | London || S5 | Adams | 30 | Athens |+------+-------+--------+--------+ 

CREATE VIEW GOOD_SUPPLIERS AS  SELECT S#, STATUS, CITY

FROM SWHERE STATUS > 15;

 +------+-------+--------+--------+| S# | SNAME | STATUS | CITY |+------+-------+--------+--------+| S1 | | 20 | London || | | | || S3 | | 30 | Paris || S4 | | 20 | London || S5 | | 30 | Athens |+------+-------+--------+--------+

Example

CREATE VIEW GOOD_SUPPLIERS AS 

SELECT S#, STATUS, CITYFROM SWHERE STATUS > 15;

  The VIEW GOOD_SUPPLIERS: +------+--------+--------+| snum | status | city |+------+--------+--------+| S1 | 20 | London || S3 | 30 | Paris || S4 | 20 | London || S5 | 30 | Athens |+------+--------+--------+   The definition of the view is stored, the table is not.

Examplethe definition

The table doesn’t really exist—just the “knowledge” of how it is “composed.”  Thus, 

SELECT *FROM GOOD_SUPPLIERSWHERE CITY <> ‘London’;

 is effectively 

SELECT S#, STATUS, CITYFROM SWHERE CITY <> ‘London’AND STATUS > 15;

  as though the SELECT from the query were merged with the SELECT from the definition.

The VIEW GOOD_SUPPLIERS: +------+--------+--------+| snum | status | city |+------+--------+--------+| S1 | 20 | London || S3 | 30 | Paris || S4 | 20 | London || S5 | 30 | Athens |+------+--------+--------+

Updating

Views

DELETE FROM GOOD_SUPPLIERS WHERE S# = ‘S4’;deletes the “S4” row from S (and thus from the view)

INSERT INTO GOOD_SUPPLIERS VALUES (‘S6’, 35, ‘Tokyo’);

inserts row into S (with NULL SNAME)

UPDATE GOOD_SUPPLIERS SET CITY = ‘Madrid’ WHERE S# = ‘S3’;

changes S3’s CITY to Madrid in S (and thus in the view)

CREATE VIEW S#_CITYAS SELECT S#, CITYFROM S;   +------+--------+| S# | CITY |+------+--------+| S1 | London || S2 | Paris || S3 | Paris || S4 | London || S5 | Athens |+------+--------+ theoretically updatable

 CREATE VIEW STATUS_CITYAS SELECT STATUS, CITYFROM S;   +--------+--------+| STATUS | CITY |+--------+--------+| 20 | London || 10 | Paris || 30 | Paris || 20 | London || 30 | Athens |+--------+--------+ theoretically not

DML on Views

A View Updating Mechanism

• View updates must apply to appropriate underlying persistent relvars

• INSERT and DELETE should be inverses of each other

• UPDATE can be treated as a DELETE and an INSERT, so long as no intermediate actions or checks permitted

• View updates should be compatible with all relational operators

A View Updating Mechanism –UNION

• Let view UV be defined as follows:• VAR UV VIEW• ( S WHERE STATUS > 25 )

UNION• ( S WHERE CITY = ‘Paris’ ) ;• Insert the following tuple into the view:• ( S6, Smith, 50, Rome)• This will insert the tuple into the suppliers

base relvar, and thus appear in the view• Similar effect for DELETE and UPDATE

CREATE VIEW SHIPDETAIL ASSELECT S.S#, SNAME, P.P#, PNAME, COLOR, QTYFROM SP, S, PWHERE SP.S# = S.S#AND SP.P# = P.P#;

SHIPDETAIL:+------+-------+------+-------+-------+------+| S# | SNAME | P# | PNAME | COLOR | QTY |+------+-------+------+-------+-------+------+| S1 | Smith | P1 | Nut | Red | 300 || S1 | Smith | P2 | Bolt | Green | 200 || S1 | Smith | P3 | Screw | Blue | 400 || S1 | Smith | P4 | Screw | Red | 200 || S1 | Smith | P5 | Cam | Blue | 100 || S1 | Smith | P6 | Cog | Red | 100 || S2 | Jones | P1 | Nut | Red | 300 || S2 | Jones | P2 | Bolt | Green | 400 || S3 | Blake | P2 | Bolt | Green | 200 || S4 | Clark | P2 | Bolt | Green | 200 || S4 | Clark | P4 | Screw | Red | 300 || S4 | Clark | P5 | Cam | Blue | 400 |+------+-------+------+-------+-------+------+

VAR SHIPDETAIL VIEW ( ( (SP JOIN S) JOIN P) WHERE SP.S# = S.S# AND SP.P# = P.P# ) { S#, SNAME, P#, PNAME, COLOR, QTY }

Tutorial D

View with three underlying base tables (relvars) to be updated.

SQL

DROP VIEW <view name> 

“forgets” the definition

 DROP TABLE <base table name> 

causes any views based on the base table to be dropped

  no “ALTER VIEW” 

just drop one, create a new one  

an ALTER TABLE <base table name> (theoretically) has no effect on any views based on that table