O'Reilly logo

SQL and Relational Theory, 2nd Edition by C.J. Date

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required