VIEWS ARE RELVARS

Of those informal characterizations listed above of what a view is, the following definition might appear to favor one over the rest—but those informal characterizations are all equivalent anyway, loosely speaking:

Definition: A view V is a relvar whose value at time t is the result of evaluating a certain relational expression at that time t. The expression in question (the view defining expression) is specified when V is defined and must mention at least one relvar.

The following examples (“London suppliers” and “non London suppliers”) are repeated from Chapter 8, except that I now give SQL definitions as well:

image with no caption

Note that these are restriction views—their value at any given time is a certain restriction of the value at that time of relvar S. Some syntax issues:

  • The parentheses in the SQL examples are unnecessary but not wrong; I include them for clarity. The parentheses in the Tutorial D examples are required.

  • CREATE VIEW in SQL allows a parenthesized commalist of view column names to appear following the view name, as in this example:

         CREATE VIEW SDS ( SNAME , DOUBLE_STATUS )
           AS ( SELECT DISTINCT SNAME , 2 * STATUS
                FROM   S ) ;

    Recommendation: Don’t do this—follow the recommendations given in Chapter 3 under “Column Naming in SQL” instead. For example, the foregoing view can equally well (in fact, better) be defined like this:

     CREATE VIEW SDS AS ( SELECT DISTINCT SNAME ...

Get SQL and Relational Theory, 2nd Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.