*Interpreting Queries Involving Views*

On March 06, 2015, In The Database Language SQL by Admin

Views (900)

We can get a good idea of what view queries mean by following the way a query involving a view would be processed. The matter is taken up in more generality in "Algebraic Laws for Improving Query Plans", when we study query processing in general.

The basic idea is shown in Figure 1. A query Q is there represented by its expression tree in relational algebra. This expression tree uses as leaves some relations that are views. We have suggested two such leaves, the views V and W. To interpret Q in terms of base tables, we find the definition of the views V and W. These definitions are also expressed as expression trees of relational algebra.

To form the query over base tables, we substitute, for each leaf in the tree for Q that is a view, the root of a copy of the tree that defines that view. Therefore, in Figure 1 we have illustrated the leaves labeled V and W replaced by the definitions of these views. The resulting tree is a query over base tables that is equivalent to the original query about views.

Example 1 : Let us look at the view definition and query of "View Definitions" Example 2. Remember the definition of view ParamountMovie is:

**CREATE VIEW ParamountMovie AS**

SELECT title, year

FROM Movie

WHERE studioName = 'Paramount';

An expression tree for the query that defines this view is illustrated in Figure 2. The query of "View Definitions" Example 2 is

SELECT title, year

FROM Movie

WHERE studioName = 'Paramount';

**SELECT title**

FROM ParamountMovie

WHERE year = 1979;

FROM ParamountMovie

WHERE year = 1979;

asking for the Paramount movies made in 1979. This query has the expression tree illustrated in Figure 3. Note that the one leaf of this tree represents the view ParamountMovie.

We therefore interpret the query by substituting the tree of Figure 2 for the leaf ParamountMovie in Figure 3. The resulting tree is illustrated in Figure 4.

The tree of Figure 4 is an acceptable interpretation of the query. On the other hand, it is expressed in an unnecessarily difficult way. An SQL system would apply transformations to this tree in order to make it look like the expression tree for the query we suggested in "View Definitions" Example 2:

**SELECT title**

FROM Movie

WHERE studioName = 'Paramount' AND year = 1979;

FROM Movie

WHERE studioName = 'Paramount' AND year = 1979;

For instance, we can move the projection π

_{title, year}above the selection σ

_{year=1979}. The reason is that delaying a projection until after a selection can never change the result of an expression. Then, we have two projections in a row, first onto title and year and then onto title alone. Clearly the first of these is redundant, and we can remove it. Thus: the two projections can be replaced by a single projection onto title.

The two selections can also be combined. Normally, two consecutive selections can be replaced by one selection for the AND of their conditions. The resulting expression tree is illustrated in Figure 5. It is the tree that we would obtain from the query directly.

**SELECT title**

FROM Movie

WHERE studioName = 'Paramount' AND year = 1979;

FROM Movie

WHERE studioName = 'Paramount' AND year = 1979;

### Tags

- expression tree
- relational algebra
- base table
- Modifying Views
- View Definitions
- Full-Relation Operations
- Union, Intersection, and Difference of Queries
- Interpreting Multirelation Queries
- Queries Involving More Than One Relation
- Selection in SQL
- The Database Language SQL
- Additional Constraint Examples
- Referential Integrity Constraints
- Constraints on Relations
- Extending the Projection Operator
- Extended Operators of Relational Algebra
- Selection on Bags / Product of Bags / Joins of Bags
- Relational Operations on Bags
- A Linear Notation for Algebraic Expressions
- Dependent and Independent Operations
- Renaming
- Combining Operations to Form Queries
- Set Operations on Relations
- An Algebra of Relational Operations
- Database Programming
- The Query Processor