CS145 Lecture Notes -- 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

Examples

Example schema:

  Student(ID, name, address, GPA, SAT)
  Campus(location, enrollment, rank)
  Apply(ID, location, date, major, decision)

Example: Applicants to Santa Cruz

  CREATE VIEW ApplySC AS
    SELECT ID, date, major
    FROM Apply
    WHERE location = 'SC'

Can use in query:

  SELECT Student.ID, name, major
  FROM Student, ApplySC
  WHERE Student.ID = ApplySC.ID
  AND (GPA > 3.7 OR SAT > 1400)

rewritten to:

  SELECT Student.ID, name, major
  FROM Student,
       (SELECT ID, date, major
        FROM Apply
        WHERE location = 'SC') as ApplySC
  WHERE Student.ID = ApplySC.ID
  AND (GPA > 3.7 OR SAT > 1400)

or in "flattened" form:

  SELECT Student.ID, name, major
  FROM Student, Apply
  WHERE Student.ID = Apply.ID
  AND (GPA > 3.7 OR SAT > 1400)
  AND location = 'SC'

Or could make query into another view:

  CREATE VIEW HighSC AS
    SELECT Student.ID, name, date, major
    FROM Student, ApplySC
    WHERE Student.ID = ApplySC.ID
    AND (GPA > 3.7 OR SAT > 1400)

Another example: Students who applied to more than one campus

  CREATE VIEW MultiApply AS
    SELECT DISTINCT Student.*
    FROM Student as S, Apply as A1, Apply as A2
    WHERE S.ID = A1.ID
    AND S.ID = A2.ID
    AND A1.location <> A2.location

Can make entire join into a view ("universal relation"):

  CREATE VIEW AllDB AS
    SELECT Student.ID, name, address, GPA, SAT, Campus.location,
           enrollment, rank, date, major, decision
    FROM Student, Campus, Apply
    WHERE Student.ID = Apply.ID
    AND Apply.location = Campus.location

No more joins in queries!

Now can write:

  SELECT * FROM AllDB
  WHERE GPA < 3.5
  AND rank > 3
  AND major = 'psychology'

rewritten to:

  SELECT Student.ID, name, address, GPA, SAT, Campus.location,
         enrollment, rank, date, major, decision
  FROM Student, Campus, Apply
  WHERE Student.ID = Apply.ID
  AND Apply.location = Campus.location
  AND GPA < 3.5
  AND rank > 3
  AND major = 'psychology'

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

Example:

  DELETE FROM ApplySC WHERE date < 1/1/03

translated to:

  DELETE FROM Apply WHERE date < 1/1/03 AND location = 'SC'

Another example:

  CREATE VIEW CsEe AS
    SELECT ID, location, major
    FROM Apply
    WHERE major = 'CS' OR major = 'EE'

Insertion into this view:

  INSERT INTO CsEe VALUES (123, 'Berkeley', 'CS')

translated to:

  INSERT INTO Apply VALUES (123, 'Berkeley', NULL, 'CS', NULL)

Note:

Default values are used instead of NULL when defined

Insertion into ApplySC view:

  INSERT INTO ApplySC VALUES (123, 4/15/03, 'economics')

translated to:

  INSERT INTO Apply VALUES (123, NULL, 4/15/03, 'economics', NULL)

Note:

System does not necessarily deduce location should be 'SC'
If not, inserted Apply tuple doesn't appear in view! (but system allows it)

Another example:

  INSERT INTO HighSC VALUES (123, 'Mary', 4/15/03, 'CS')

Question: Translated to?

Example:

  CREATE VIEW AvgSAT(a) AS SELECT avg(SAT) FROM Student;
  UPDATE AvgSAT SET a = 1125;

Question: Translated to?

Precise conditions for updatable views are very complicated, may involve keys, equality conditions, etc.
SQL2 uses conservative conditions. View must be defined as:
- single-relation SELECT
- simple WHERE clause
- attributes not in view can be NULL or default

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?

Recall example: Students who applied to more than one campus

  CREATE MATERIALIZED VIEW MultiApply AS
    SELECT DISTINCT Student.*
    FROM Student as S, Apply as A1, Apply as A2
    WHERE S.ID = A1.ID
    AND S.ID = A2.ID
    AND A1.location  <> A2.location

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

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

All DBMSs support virtual views.
Support for materialized views added recently, especially for "decision support", "OLAP", and "data warehousing."
Picking a good set of materialized views is a difficult design decision, similar to but more complicated than picking good indexes.