=========================================================================
LECTURE NOTES - VIEWS
=========================================================================

Three-level vision of database

"Virtual database"        views
on top of
"Conceptual database"     relations (tables)
on top of
"Physical database"       stored information


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 of views


View Definition
---------------

View V = V-query(R1, R2, ..., Rn)

Schema of V is schema of query result

Query Q involving V, conceptually:

   V <- V-query(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 CREATE VIEW ViewName(A1, A2, ..., Am) AS Query
   to rename attributes


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 to:

  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 SmartSC 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!


Example: 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
---------------

- Does not seem to make sense since views are not stored.

- But does make sense if this is how user views database.

-> Some unambiguous modifications of views are allowed.


Example: DELETE FROM ApplySC WHERE date < 1/1/01

  translated to:

  DELETE FROM Apply WHERE date < 1/1/01 AND location = "SC"


Example: INSERT INTO ApplySC VALUES (123, 4/15/01, "economics")

  translated to:

  INSERT INTO Apply VALUES (123, NULL, 4/15/01, "economics", NULL)

Note:
  - Default values used instead of NULL when defined
  - Systems do not necessarily deduce location should be "SC"


Example: INSERT INTO SmartSC VALUES (123, "Mary", 4/15/01, "CS")

Q: Translated to?  Many possibilities...








Example: CREATE VIEW AvgSAT(a) AS 
            SELECT avg(SAT) FROM Student

         UPDATE AvgSAT SET a = 1125

Q: 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.

Materialized views:

  - View V = V-query(R1, R2, ..., Rn)
  - Create relation called V with schema of query result.
  - Populate relation with results of V-query.
  - Queries referring to V access relation V without rewriting.

Q: Why are views not always materialized?







Recall 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

What insertions to or deletions from regular relations might
invalidate current contents of MultiApply?








What updates 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.

Q: Why not just update materialized view table?





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









* All DBMSs support virtual views.

* Support for materialized views added recently, especially for
  "decision support", "OLAP", and "data warehousing."