Introduction

We start today by looking at virtual views which are relations that are defined by a query over other relations.
The contents of a virtual view are not stored in the database, but can be queried as if they existed.
Virtual views can be materialized. That is, periodically constructed and stored as tuples based on the query of the view.
This can speed up querying against the view.
A special type of "materialized view" is an index, a stored data structure computed from a table whose sole purpose is to speed up the execution of queries.

Virtual Views

The SQL command to declare a virtual view (we'll say just view) is:
```
CREATE VIEW < view-name > AS < view-definition >;
```
For example, suppose we wanted to make a view consisting of movie titles and the names of their producers.

We could create such a view with the command:

CREATE VIEW MovieProd AS 
    SELECT title, name 
    FROM Movies, MovieExec 
    WHERE producerC# = cert#;

If we want to get rid of a view we have already created, we can use the command:
```
DROP VIEW < view-name > ; 
```
This does not have any effect on the view's base tables.

Querying Views

Once created we can query a view as if it were a table:

SELECT title
FROM MovieProd
WHERE name = 'Spielberg';

The semantics of a view can be thought of as a from clause subquery:

SELECT title
FROM (
    SELECT title, name 
    FROM Movies, MovieExec 
    WHERE producerC# = cert#;
    ) MovieProd
WHERE name = 'Spielberg';

Renaming View Attributes

The columns names of the view can be renamed from the underlying query using a syntax like:

CREATE VIEW MovieProd(movieTitle, prodName) AS 
    SELECT title, name 
    FROM Movies, MovieExec 
    WHERE producerC# = cert#;

Quiz

Which of the following is true?

SQL doesn't allow you to have a subquery on the FROM line of a query.
The natural join of table R and S could be computed in SQL as:
```
SELECT *
FROM (R NATURAL JOIN  S);
```
Foreign key constraints in SQL are typically handled using triggers.

Modifying Views

Although usually not possible, in some circumstances, it is possible to execute insertion, deletion, and update on views.
Views which are updatable are called updatable views.
Updatable views have simple enough defining queries that insert/delete/update operation can be modified to corresponding operations on its query's base tables.
So called "instead-of" triggers can be used to turn a view modification into operations on the underlying tables.

Updatable View Conditions

SQL provides a formal definition of when modifications to a view are permitted without a trigger.
These are complicated. However, we can simplify them to:
- The view should be defined by selecting (without distinct) some attributes from one relation R (which is also allowed to be an updatable view).
- The WHERE clause is not allowed to involve R in a subquery.
- The FROM clause can only consist of one occurrence of `R` and no other relation.
- The list in the SELECT clause must include enough attributes that for every tuple inserted into the view, we can fill the other attributes out with NULL or the proper default values.
Sqlite does not support updateable views. Mysql, Postgres, DB2, Oracle do.

Example Updatable View

The following is an example updatable view:

CREATE VIEW ParamountMovies AS 
    SELECT studioName, title, year 
    FROM Movies 
    WHERE studioName = 'Paramount';

If we did:

INSERT INTO ParamountMovies
VALUES ('Paramount', 'Star Trek', 1979);

it would be mapped to the insert on Movies:

INSERT INTO Movies(studioName, title, year)
VALUES ('Paramount', 'Star Trek', 1979);

It would have values NULL on the attributes not in the underlying table but not in the view definition.

Similarly, the delete statement:

DELETE FROM ParamountMovies
WHERE title LIKE '%Trek%';

would map to:

DELETE FROM Movies
WHERE title LIKE '%Trek%' 
   AND studioName = 'Paramount';

Notice how we ANDd the condition from the view.

Finally, an update like:

UPDATE ParamountMovies
SET year = 1979 
WHERE title = 'Star Trek the Movie';

would also involve such an ANDing on the underlying table update:

UPDATE Movies
SET year = 1979 
WHERE title = 'Star Trek the Movie'
   AND studioName = 'Paramount';

Instead-of Triggers on Views

Last week, we gave an example of a trigger which executed AFTER an update on the MovieExec table.
We said one could also have triggers which execute BEFORE some DML operation occurs.
In the case of views, one has a third category of trigger which executes INSTEAD OF some DML operation.

For example, consider the view:

CREATE VIEW ParamountMovies AS 
    SELECT title, year 
    FROM Movies 
    WHERE studioName = 'Paramount';

Notice unlike before we don't have the attribute studioName (since it should obviously be Paramount).

We need the studioName to do an insert into the underlying table and this is where we make use of a trigger:

CREATE TRIGGER ParamountInsert
INSTEAD OF INSERT ON ParamountMovies
REFERENCING NEW ROW AS NewRow
FOR EACH ROW
    INSERT INTO Movies(title, year, studioName)
    VALUES(NewRow.title, NewRow.year, 'Paramount');

Indexes in SQL

An index on attributes `A` of a relation is a data structure that makes it efficient to find those tuples which have a fixed value for the attributes in `A`.
We can think of an index as implementing a binary search tree (or disk optimized variant known as a B-tree) of (key, value) pairs, where the key corresponds to the attributes we have an index for.
The index can be used to speed up queries with WHERE clauses containing expressions like A=3 or even `A <= 3`
For example, without an index
```
SELECT *
FROM Movies
WHERE studioName = 'Disney' and year=1990;
```
might require a full tables scan to return its results (O(n) where `n` is the table size)
Using an index to directly look up the tuples might reduce the time to be `O`(`(log n)` + number of results returned).

SQL Index Syntax

The simple SQL create index statement looks like:

CREATE INDEX < name-of-index> ON < table_name>(<indexed-attributes>);

If we don't want the index to have duplicates for the indexed attributes we can use:
```
CREATE UNIQUE INDEX < name-of-index> ON < table_name>(<indexed-attributes>);
```

For example,

CREATE INDEX KeyIndex ON Movies(title, year);

Different DBMSs have variants on this command. For example, in MYSQL we can specify the kind of index with the statements like:
```
CREATE INDEX KeyIndex1 USING HASH ON Movies(title, year);
CREATE INDEX KeyIndex2 USING BTREE ON Movies(title, year);
```
To get rid of an index on table we can use a syntax like:
```
DROP INDEX KeyIndex1 ON Movies;
```
How a DBMSs query engine chooses which index to use when processing a query is a CS157B topic, but is often based on a cost model for an estimated number of disk blocks that must be retrieved in order to process the query in different ways.

Materialized Views

Sometimes we have a view that is queried very often, but only updated occasionally.
In such a situation, it might make sense for query efficiency to make a table with the tuples of the contents of the view.
Such a table is called a materialized view.

The book suggests a syntax like:

CREATE MATERIALIZED VIEW MovieProd AS 
    SELECT title, year, name 
    FROM Movies, MovieExec 
    WHERE producerC# = cert#

to indicate a view ought to be materialized

Many DBMSs do not support this syntax.
One way to achieve this effect in Mysql is to have a table for the materialized view then use triggers to handle insert, delete, update requests on this original table or the materialized view.

More SQL DDL

Outline