A view, also called a virtual relvar, is a relvar that doesn't have separate existence in its own right but looks to the user as if it did. Here's a definition:
The following are a couple of examples, "London suppliers" and "non-London suppliers" (Tutorial D on the left, SQL on the right): VAR LS VIRTUAL | CREATE VIEW LS AS ( S WHERE CITY = 'London' ) ; | ( SELECT S.* WHERE S.SNO IN | FROM S | WHERE S.CITY = 'London' ) ; VAR NLS VIRTUAL | CREATE VIEW NLS AS ( S WHERE CITY 'London ) ; | ( SELECT S.* | FROM S WHERE S.CITY <> 'London' ) ; VAR NLS VIRTUAL | CREATE VIEW NLS AS ( S WHERE CITY 'London ) ; | ( SELECT S.* FROM S | | WHERE S.CITY <> 'London' ) ; The parentheses in all of these examples are unnecessary but not wrong. I include them for clarity. View RetrievalsTo repeat, views are meant to look to the user as if they had their own separate existence; in other words, they're supposed to "look and feel" just like base relvars so far as the user is concerned. In particular, the user should be able to operate on views as if they were base relvars, and the DBMS should be able to map those user operations into suitable operations on the base relvars in terms of which the views are ultimately defined. I say "ultimately" here because (of course) one thing we can do, if views really do behave just like base relvars, is define further views on top of them, as in this SQL example: CREATE VIEW LS_STATUS AS ( SELECT LS.SNO, LS.STATUS FROM LS ) ; Mapping read-only operations is straightforward. For example, suppose we issue this SQL query on view LS: SELECT LS.SNO FROM LS WHERE LS.STATUS > 10 First, the DBMS replaces the reference to the view in the FROM clause by the expression that defines that view, yielding: SELECT LS.SNO FROM ( SELECT S.* FROM S WHERE S.CITY = 'London' ) AS LS WHERE LS.STATUS > 10 This expression can now be simplified to: SELECT S.SNO FROM S WHERE S.CITY = 'London' AND S.STATUS > 10 The reason the foregoing process works is precisely because of the closure property of the relational algebra. Closure implies, among other things, that wherever we're allowed to have the name of something for example, in a query we can always have a more general expression that evaluates to a thing of the appropriate type.[*] In the FROM clause, for example, we can have an SQL table name; thus we can also have a more general SQL table expression, and that's why we're allowed to substitute the expression that defines the view LS for the name LS in the example.
By the way, it's worth mentioning that the foregoing process didn't always work in early versions of SQL (to be specific, in versions of the SQL standard prior to 1992), because those early versions failed to support closure properly. As a result, certain innocuous-looking queries against certain innocuous-looking tables (actually views) failed and failed, moreover, in ways that were hard to explain. Here's a simple example: CREATE VIEW V AS ( SELECT S.CITY, SUM ( S.STATUS ) AS ST FROM S GROUP BY S.CITY ) ; SELECT V.CITY FROM V WHERE V.ST > 25 This example failed in the SQL standard prior to 1992. And although the standard has now been fixed, it doesn't follow that all of the products have! And indeed there's at least one major product that still hasn't, at the time of writing (early 2005). View UpdatesI turn now to update operations. Before I get into specifics, I want to look at the London and non-London supplier views again (and now I'll switch to Tutorial D): VAR LS VIRTUAL ( S WHERE CITY = 'London' ) ; VAR NLS VIRTUAL ( S WHERE CITY 'London ) ; The important point here is as follows: instead of S being the base relvar and LS and NLS views, LS and NLS could be base relvars and S could be a view, like this: VAR LS BASE RELATION { SNO SNO, SNAME NAME, STATUS INTEGER, CITY CHAR } KEY { SNO } ; VAR NLS BASE RELATION { SNO SNO, SNAME NAME, STATUS INTEGER, CITY CHAR } KEY { SNO } ; VAR S VIRTUAL ( LS UNION NLS ) ; NOTE In order to achieve complete equivalence, we would also have to specify certain constraints in particular, constraints to the effect that every CITY value in LS is London and every CITY value in NLS is not London but I omit such details here. See Chapter 6 for further discussion of such matters. The message of this example is that, to a very large extent, which relvars are base and which virtual is arbitrary from which it follows that there must be no arbitrary and unnecessary distinctions between base and virtual relvars. This state of affairs is referred to as The Principle of Interchangeability (of base and virtual relvars). Here are some implications:
And, to revert to the main point of this discussion, we must be able to update views because if we can't, then that fact in itself constitutes the clearest possible violation of The Principle of Interchangeability. As you probably know, SQL's support for this requirement is quite weak, both in the standard and in the major commercial products. It does at least typically include support for updates on views defined as simple restrictions and/or projections of a single underlying base relvar (though even here there are some problems). For example, consider the following view (which is essentially identical to one we saw in Chapter 1): CREATE VIEW SST_PARIS AS ( SELECT S.SNO, S.STATUS FROM S WHERE S.CITY = 'Paris' ) ; This view is a projection of a restriction of base table S, and so we might, for example, be able to perform the following DELETE on it: DELETE FROM SST_PARIS WHERE SST_PARIS.STATUS > 15 ; This DELETE maps to: DELETE FROM S WHERE S.CITY = 'Paris' AND S.STATUS > 15 ; But few products provide support for updating views that are much more sophisticated than this one. Unfortunately, I'm now straying into an area in which there's still a certain amount of controversy. My own opinion is that the view updating problem has largely been solved (that is, the theory exists); however, not everybody agrees with me, and in any case a detailed discussion of the subject requires rather more background than it's possible to include in a book of this nature. Thus, I'm afraid the best I can do here is refer you to another book Databases, Types, and the Relational Model: The Third Manifesto, Third Edition (Addison-Wesley, 2006), by C. J. Date and Hugh Darwen, where the subject is examined in depth if you want more specifics. Miscellaneous PointsThere are a few more things I need to say in order to finish up this section. First of all, it's well known, but worth mentioning anyway, that views serve two rather different purposes:
Second, when I explained what a view was at the beginning of this section, I said the relational expression that defined the view had to mention at least one relvar. Why? Because if it doesn't, the "virtual relvar" won't be a relvar at all! I mean, it won't be a variable, and it certainly won't be updatable. Instead, it'll be a relation constant, or what we might call a "relcon." For example (to invent some syntax on the fly): CONST PERIODIC_TABLE ( RELATION { TUPLE { ELEMENT 'Hydrogen', SYMBOL 'H' , ATOMICNO 1 }, TUPLE { ELEMENT 'Helium' , SYMBOL 'He', ATOMICNO 2 }, ... TUPLE { ELEMENT 'Uranium' , SYMBOL 'U' , ATOMICNO 92 } } ) ; While it certainly might be desirable to provide some kind of "relcon" functionality along the foregoing lines, I don't think we should think of such things as relvars. I don't think it helps the cause of understanding to pretend that constants are variables. Third, an unfortunate terminological clash is arising as I write, certainly in the academic world, and to some extent in the commercial world also. Recall from Chapter 1 that a view can be thought of as a derived relvar. Well, there's another kind of derived relvar, too, called a snapshot. As the name might suggest, a snapshot, although it's derived, is real, not virtual meaning it's represented not just by its definition in terms of other relvars but also, at least conceptually, by its own separate copy of the data. For example (to invent some syntax again): VAR LSS SNAPSHOT ( S WHERE CITY = 'London' ) REFRESH EVERY DAY ; Defining a snapshot is just like executing a query, except that:
In the example, therefore, snapshot LSS represents the data as it was at most 24 hours ago. Snapshots are important in data warehouses, distributed systems, and many other contexts. In all cases, the rationale is that many applications can tolerate, or might even require, data "as of " some particular point in time. Reporting and accounting applications are a case in point; such applications typically require the data to be frozen at an appropriate moment (for example, at the end of an accounting period), and snapshots allow such freezing to occur without locking out other applications. So far, so good. The problem is that snapshots have come to be known (at least in some circles) not as snapshots at all but as materialized views. But snapshots aren't views! Indeed, the whole point about views, at least so far as the relational model is concerned, is that they aren't materialized; as we've seen, operations on views are implemented by mapping them into suitable operations on the underlying base relvars. Thus, "materialized view" is simply a contradiction in terms. Worse yet, the unqualified term view is now often taken to mean a "materialized view" specifically again, at least in some circles and so we're in danger of no longer having a good term to mean a view in the original sense. In this book, of course, I do use the term view in its original sense, but be warned that it doesn't always have that meaning elsewhere. Caveat lector. |