CS145 Lecture Notes (11) -- Views

Three-level vision of database:

Virtual database = Views
built on top of...
Conceptual database = Tables (Relations)
built on top of...
Physical database = Pages on disk

Why use views?

To hide some data from some users
To make certain queries easier or more natural to express
For modularity

=> Real database applications use lots and lots (and lots and lots) of views

Defining Views

View V = ViewQuery(R1, R2, ..., Rn)
Schema of V is schema of query result

Query Q involving V, conceptually:

  V := ViewQuery(R1, R2, ..., Rn);
  Evaluate Q;

In reality, Q is rewritten automatically to use R1, R2, ..., Rn instead of V

SQL Syntax

  CREATE VIEW ViewName AS Query

or to rename attributes:

  CREATE VIEW ViewName(A1, A2, ..., Am) AS Query

[[On-Line Views Demo]]

Schema for examples:

   Student(ID, name, GPA, sizeHS)       // ID is key
   Campus(location, enrollment, rank)   // location is key
   Apply(ID, location, major, decision) // (ID,location,major) is key

Topics covered:

View definitions
Using views in queries
Automatic rewrites to eliminate views
View layering
Modifying views (see below): deletes, inserts, updates

Modifying Views

Doesn't make sense: views are not stored
Has to make sense: views constitute some users' entire "view" of the database
Conclusion: some unambiguous modifications of views are allowed

Precise conditions for views with meaningful, unambiguous modifications are very complicated: may involve keys, equality conditions, etc.

SQL2 View Modifications

Conservative conditions to guarantee meaningful and unambiguous modifications. View must be defined as:

SELECT on a single table T, without DISTINCT
Attributes of T not projected in view allowed to be NULL or default
Subqueries in WHERE must not refer to T
No aggregation

MySQL View Modifications

Somewhat less restrictive:

"Must be a one-to-one relationship between the rows in the view and the rows in the underlying tables"
Multi-table views
- UPDATE usually permitted (when other conditions satisfied)
- INSERT to one table's attributes usually permitted (when other conditions satisfied)
- DELETE never permitted
No DISTINCT or aggregation
Attributes not projected in view allowed to be NULL or default
Subqueries in WHERE must not refer to tables in FROM clause

Materialized Views

So far we've discussed virtual views:

View consists of definition only
Definition used to rewrite queries that refer to views into queries that refer to "base tables" only

Materialized views:

View V = ViewQuery(R1, R2, ..., Rn)
Create relation called V with schema of query result
Execute ViewQuery and populate relation V with the results
Queries referring to V access relation V without rewriting

Question: Why are views not always materialized?

Example: Berkeley applicants

  CREATE MATERIALIZED VIEW Berk AS
    SELECT Student.ID, name, major
    FROM Student, Apply
    WHERE Student.ID = Apply.ID AND location = 'Berkeley';

Question: What modifications to regular relations might invalidate current contents of Berk?

Even though materialized views are stored as tables, updates to materialized views incur same ambiguity problems as updates to virtual views.

Question: Why not just update materialized view table?

Question: How does one decide whether to use a virtual or a materialized view?

Final Notes

Virtual Views
- Supported by every real DBMS
Materialized Views
- Many DBMS's (not MySQL) have added them recently, especially for "decision support", "OLAP", and "data warehousing."
- Queries may be rewritten automatically to take advantage of materialized views. See required reading in textbook.
- Picking a good set of materialized views is a difficult design decision, similar to but more complicated than picking good indexes. See required reading in textbook.