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:
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 , 2 * STATUS AS DOUBLE_STATUS FROM S ) ;Note in particular that this latter style means we’re telling the system once instead of twice that one of the view columns is called SNAME.
CREATE VIEW in SQL also allows WITH CHECK OPTION to be specified if (but only if!) it regards the view as updatable. Recommendation: Always specify this option if possible. See the section UPDATE OPERATIONS for further discussion.
Since views are relvars, essentially everything I said in previous chapters regarding relvars in general applies to views in particular. Subsequent sections discuss specific aspects of this observation in detail. First, however, there’s a more fundamental point I need to explain.
Consider the example of London vs. non London suppliers again. In that example, S is a base relvar and LS and NLS are views. But it could have been the other way around—that is, we could have made LS and NLS base relvars and S a view, like this (Tutorial D only, for simplicity):
VAR LS BASE RELATION
{ SNO CHAR , SNAME CHAR , STATUS INTEGER , CITY CHAR }
KEY { SNO } ;
VAR NLS BASE RELATION
{ SNO CHAR , SNAME CHAR , STATUS INTEGER , CITY CHAR }
KEY { SNO } ;
VAR S VIRTUAL ( LS UNION NLS ) ;Note: In order to guarantee that this design is logically equivalent to the original one, we would also have to state and enforce certain additional constraints—including in particular constraints to the effect that every CITY value in LS is London and no CITY value in NLS is—but I omit such details here. See the sections VIEWS AND CONSTRAINTS and UPDATE OPERATIONS later for further consideration of such matters.
Anyway, the message of the example is that, in general, which relvars are base ones and which virtual is arbitrary (at least from a formal point of view). In the example, we could design the database in at least two different ways: ways, that is, that are logically distinct but information equivalent. (By information equivalent here, I mean the two designs represent the same information; i.e., for any query on one, there’s a logically equivalent query on the other.) And The Principle of Interchangeability follows logically from such considerations:
Definition: The Principle of Interchangeability (of base and virtual relvars) states that there must be no arbitrary and unnecessary distinctions between base and virtual relvars; i.e., virtual relvars should “look and feel” just like base relvars so far as users are concerned.
Here are some implications of this principle:
As I’ve already suggested, views are subject to integrity constraints, just like base relvars. (We usually think of integrity constraints as applying to base relvars specifically, but The Principle of Interchangeability shows this position isn’t really tenable.) See the section VIEWS AND CONSTRAINTS, later.
In particular, views have keys (and so I should perhaps have included some key specifications in my examples of views prior to this point; Tutorial D permits such specifications but SQL doesn’t). They might also have foreign keys, and foreign keys might refer to them. Again, see the section VIEWS AND CONSTRAINTS, later.
I didn’t mention this point in Chapter 1, but the “entity integrity” rule is supposed to apply specifically to base relvars, not views. It thereby violates The Principle of Interchangeability. Of course, I reject that rule anyway, because it has to do with nulls (I also reject it because it has to do with primary keys specifically instead of keys in general, but let that pass).
Many SQL products, and the SQL standard, provide some kind of “row ID” feature.[126] If that feature is available for base tables but not for views—which in practice is quite likely—then it violates The Principle of Interchangeability. (It probably violates The Information Principle, too. See Appendix A.) Now, row IDs as such aren’t part of the relational model, but that fact in itself doesn’t mean they’re prohibited. But I observe as an important aside that if those row IDs are regarded—as they are, most unfortunately, in the SQL standard, as well as in at least some of the major SQL products—as some kind of object ID in the object oriented sense, then they are prohibited, very definitely! Object IDs are effectively pointers, and (to repeat from Chapter 2) the relational model explicitly prohibits pointers.
The distinction discussed in the previous chapter between single relvar and multirelvar constraints is more apparent than real (and the terminology is therefore deprecated, somewhat, for that very reason). Indeed, an example in that chapter—essentially the same London vs. non London suppliers example, in fact—showed that the very same constraint could be a “single relvar” constraint with one design for the database and a “multirelvar” constraint with another.
Perhaps most important of all, we must be able to update views—because if not, then that fact in itself would constitute the clearest possible violation of The Principle of Interchangeability. Again, see the section UPDATE OPERATIONS, later.
You might have noticed that, in the formal definition I gave for what a view was at the beginning of the present section, I said the defining expression had to mention at least one relvar. Why? Because if it didn’t, the “virtual relvar” wouldn’t be a relvar at all!—I mean, it wouldn’t be a variable, and it wouldn’t be updatable. For example, the following is a valid CREATE VIEW statement in SQL:
CREATE VIEW S_CONST AS
( SELECT TEMP.*
FROM ( VALUES ( 'S1' , 'Smith' , 20 , 'London' ) ,
( 'S2' , 'Jones' , 10 , 'Paris' ) ,
( 'S3' , 'Blake' , 30 , 'Paris' ) ,
( 'S4' , 'Clark' , 20 , 'London' ) ,
( 'S5' , 'Adams' , 30 , 'Athens' ) )
AS TEMP ( SNO , SNAME , STATUS , CITY ) ) ;But this view certainly can’t be updated. In other words, it’s not a variable at all, let alone a virtual one; rather, it’s what might be called a named relation constant. Let me elaborate:
First of all, I regard the terms constant and value as synonymous. Note, therefore, that there’s a logical difference between a constant and a literal; a literal isn’t a constant but is, rather, a symbol—sometimes referred to as a self-defining symbol—that denotes a constant (as in fact we already know from Chapter 2).
Strictly speaking, there’s also a logical difference between a constant and a named constant; a constant is a value, but a named constant is like a variable, except that its value can’t be changed. That said, however, for the remainder of this brief discussion I’ll take the term constant to mean a named constant specifically, for brevity.
Constants can be of any type we like, naturally, but relation constants (i.e., constants of some relation type) are our major focus here. Now, Tutorial D doesn’t currently support relation constants, but if it did, a relation constant (or “relcon”) definition would probably look something like this example:
CONST PERIODIC_TABLE INIT ( RELATION { TUPLE { ELEMENT 'Hydrogen' , SYMBOL 'H' , ATOMICNO 1 } , TUPLE { ELEMENT 'Helium' , SYMBOL 'He' , ATOMICNO 2 } , .................................................... TUPLE { ELEMENT 'Uranium' , SYMBOL 'U' , ATOMICNO 92 } } ) ;
Now, I do believe it would be desirable to provide some kind of relation constant or “relcon” functionality along the lines sketched above. In fact, Tutorial D already provides two system defined relcons: namely, TABLE_DUM and TABLE_DEE, both of which are extremely important, as we know. Apart from these two, however, neither Tutorial D nor SQL currently provides any direct support for relcons. It’s true that (as we’ve seen) such support can be simulated by means of the conventional view mechanism; however, there’s a serious logical difference involved here, and I don’t think it helps the cause of understanding to pretend that constants are variables.