View Definitions

View Definitions

Relations that are defined with a CREATE TABLE statement in fact exist in the database. That is an SQL system stores tables in some physical organization. They are persistent, in the sense that they can be expected to exist indefinitely and not to change unless they are clearly told to change by an INSERT or one of the other modification statements we examined in "Database Modifications".

There is another class of SQL relations, called views, that do not exist physically. Rather, they are defined by an expression much like a query. Views, in turn, can be queried as if they existed physically, and in some cases, we can even modify view.

Declaring Views

The simplest form of view definition is

1. The keywords CREATE VIEW,
2. The name of the view,
3. The keyword AS, and                                                   
4. A query Q. This query is the definition of the view. Any time we query the view, SQL behaves as if Q were executed at that time and the query were applied to the relation produced by Q.

That is, a simple view declaration has the form


Example 1 :  Assume we want to have a view that is a part of the

Movie(title, year, length, inColor, studioName, producerC#)

relation, particularly, the titles and years of the movies made by Paramount Studios. We can define this view by

1) CREATE VIEW ParamountMovie AS
2) SELECT title, year
3) FROM Movie
4) WHERE studioName = 'Paramount';

First, the name of the view is ParamountMovie, as we see from line (1). The attributes of the view are those listed in line (2), namely title and year. The definition of the view is the query of lines (2) through (4).

Querying Views

Relation ParamountMovie does not include tuples in the usual sense. Rather, If we query ParamountMovie, the appropriate tuples are obtained from the base table Movie, so the query can be answered. Therefore, we can ask the same query about ParamountMovie twice and get different answers. The reason is that, even though we have not changed the definition of view ParamountMovie, the base table Movie may have changed in the interim.

Example 2 :  We may query the view ParamountMovie just as if it were a stored table, for example:

SELECT title
FROM ParamountMovie
WHERE year = 1979;

Relations Tables and Views

The definition of the view ParamountMovie is used to turn the query above into a new query that addresses only the base table Movie. We shall show how to convert queries on views to queries on base tables in "Interpreting Queries Involving Views". Though, in this simple case it is not hard to infer what the example query about the view means. We observe that ParamountMovie differs from Movie in only two ways:

1. Only attributes title and year are produced by ParamountMovie.

2. The condition studioName = 'Paramount' is part of any WHERE clause about ParamountMovie.

Since our query wants only the title produced, (1) does not present a problem. For (2), we need only to introduce the condition studioName = 'Paramount' into the WHERE clause of our query. Then, we can use Movie in place of ParamountMovie in the FROM clause, assured that the meaning of our query is preserved. Therefore, the query:

SELECT title
FROM Movie
WHERE studioName = 'Paramount' AND year = 1979;

is a query about the base table Movie that has the same effect as our original query about the view ParamountMovie.  Note that it is the job of the SQL system to do this translation. We show the reasoning process only to indicate what a query about a view means.

Example 3 :  It is also possible to write queries involving both views and base tables. An example is

FROM ParamountMovie, StarsIn
WHERE title = movieTitle AND year = movieYear;

This query asks for the name of all stars of movies made by Paramount. Note that the use of DISTINCT assures that stars will be listed only once, even if they appeared in several Paramount movies.

Example 4 :  Let us look at a more complex query used to define a view. Our goal is a relation MovieProd with movie titles and the names of their producers. The query defining the view involves both relation

Movie(title, year, length, inColor, studioName, producerC#)

from which we get a producer's certificate number, and the relation

MovieExec(name, address, cert#, netWorth)

where we connect the certificate to the name. We may write:

      SELECT title, name
      FROM Movie, MovieExec
      WHERE producerC# = cert#;

We can query this view as if it were a stored relation. For example, to find the producer of Gone With the Wind, ask:

FROM MovieProd
WHERE title = 'Gone With the Wind';

AS with any view, this query is treated as if it were an equivalent query over the base tables alone, such as:

FROM Movie, MovieExec
WHERE producerC# = cert# AND title = 'Gone With the Wind';

Renaming Attributes

Sometimes, we might prefer to give a view's attributes names of our own choosing, rather than use the names that come out of the query defining the view. We may specify the attributes of the view by listing them, surrounded by parentheses, after the name of the view in the CREATE VIEW statement. For instance, we could rewrite the view definition of Example 4 as:

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

The view is the same, but its columns are headed by attributes movieTitle and prodName instead of title and name