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

Examples

Example schema:

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

Example: Recent applicants to Santa Cruz with no decision

  CREATE VIEW RecentSC AS
    SELECT ID, date, major
    FROM Apply
    WHERE location = 'SC' AND date > 2/15/05 AND decision = 'U'

Can use in query:

  SELECT RecentSC.ID, name, major
  FROM RecentSC, Student
  WHERE RecentSC.ID = Student.ID
  AND GPA > 3.7

rewritten to:

  SELECT RecentSC.ID, name, major
  FROM (SELECT ID, date, major FROM Apply
        WHERE location = 'SC' AND date > 2/15/05
        AND decision = 'U') RecentSC,
       Student
  WHERE RecentSC.ID = Student.ID
  AND GPA > 3.7

or in "flattened" form:

  SELECT Apply.ID, name, major
  FROM Apply, Student
  WHERE Apply.ID = Student.ID
  AND location = 'SC' AND date > 2/15/05 AND decision = 'U'
  AND GPA > 3.7

Or could make query into another view:

  CREATE VIEW AdmitSC AS
    SELECT RecentSC.ID, name, major
    FROM RecentSC, Student
    WHERE RecentSC.ID = Student.ID
    AND GPA > 3.7

Example: Students who applied to more than one campus

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

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

  CREATE VIEW AllDB AS
    SELECT Student.ID, name, address, GPA, sizeHS, 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, sizeHS, 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 RecentSC WHERE date <= 3/15/05

translated to:

  DELETE FROM Apply WHERE date <= 3/15/05 AND location = 'SC'
    AND date > 2/15/05 AND decision = 'U'

Example:

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

Insertion into this view:

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

translated to:

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

Default values are used instead of NULL when defined.
If default for decision is 'U', insertion translated to:

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

Another insertion into this view:

  INSERT INTO CS_EE VALUES (123, 'Berkeley', 'math')

translated to:

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

Resulting tuple is not in the view!

Example: Insertion into RecentSC view:

  INSERT INTO RecentSC VALUES (123, 4/15/05, 'economics')

translated to:

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

System does not necessarily deduce location should be 'SC' and decision should be 'U'
If not, inserted Apply tuple doesn't appear in view!

Example:

  INSERT INTO AdmitSC VALUES (123, 'Mary', 'CS')

Question: Translated to?

Example:

  CREATE VIEW AvgGPA(a) AS SELECT avg(GPA) FROM Student;
  UPDATE AvgGPA SET a = 3.0;

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:
- SELECT on a single table T, without DISTINCT
- Attributes of T not projected in view allowed to be be NULL or default
- Subqueries in WHERE must not refer to T
- No aggregation
INSTEAD OF triggers on views provide a very nice way to define the meaning of updates on views when they cannot be deduced unambiguously by the DBMS.

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: Students who applied to more than one campus

  CREATE MATERIALIZED VIEW MultiApply AS
    SELECT DISTINCT Student.*
    FROM Student, Apply A1, Apply A2
    WHERE Student.ID = A1.ID
    AND Student.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 DBMS's support virtual views.
Most DBMS's have added materialized views 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.