9.1

        VAR NON_COLOCATED VIRTUAL
 ( ( S { SNO } JOIN P { PNO } ) NOT MATCHING ( S JOIN P ) )
KEY { SNO , PNO } ;
     CREATE VIEW NON_COLOCATED AS
          ( SELECT SNO , PNO
            FROM   S , P
            WHERE  S.CITY <> P.CITY )
            /* UNIQUE ( SNO , PNO ) */ ;

9.2 Substituting the view definition for the view reference in the outer FROM clause, we obtain:

     SELECT DISTINCT STATUS , QTY
     FROM ( SELECT SNO , SNAME , STATUS , PNO , QTY
            FROM   S NATURAL JOIN SP
            WHERE  CITY = 'London' ) AS Temp
     WHERE  PNO IN
          ( SELECT PNO
            FROM   P
            WHERE  CITY <> 'London' )

This simplifies (potentially!) to:

     SELECT DISTINCT STATUS , QTY
     FROM   S NATURAL JOIN SP
     WHERE  CITY = 'London'
     AND    PNO IN
          ( SELECT PNO
            FROM   P
            WHERE  CITY <> 'London' )

9.3 The sole key is {SNO,PNO}. The predicate is: Supplier SNO is under contract, is named SNAME, has status STATUS, is located in London, and supplies part PNO in quantity QTY.

9.4 Note that a. and b. are expressions, the rest are statements.

9.5 Here first is an SQL version of the view definition from Exercise 9.4:

     CREATE VIEW HP AS
          ( SELECT PNO , PNAME , COLOR , WEIGHT , CITY
            FROM   P
            WHERE  WEIGHT > 14.0 )
            /* UNIQUE ( PNO ) * / ;

For parts a.-e., I first show an SQL analog of the Tutorial D formulation, followed by the expanded form:

Syntactically valid equivalent:

UPDATE P
SET    COLOR = 'White'
WHERE  WEIGHT = 18.0 AND WEIGHT > 14.0 ;

9.6 Here are some:

Subsidiary exercise: Which if any of the foregoing points do you think apply not just to key constraints in particular but to integrity constraints in general?

9.7 One example is as follows: The suppliers relvar is equal to the join of its projections on {SNO,SNAME}, {SNO,STATUS}, and {SNO,CITY}—just so long as appropriate constraints are in force, that is (what are those constraints exactly?). So we could make those projections base relvars and make the join a view.

9.8 Here are some pertinent observations. First, the replacement process itself involves several steps, which might be summarized as follows:

     /* define the new base relvars: */
     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 } ;
     /* copy the data to the new base relvars: */
     LS  := ( S WHERE CITY = 'London' ) ;
     NLS := ( S WHERE CITY ≠ 'London' ) ;
     /* drop the old relvar: */
     DROP VAR S ;
     /* create the desired view: */
     VAR S VIRTUAL ( LS D_UNION NLS ) KEY { SNO } ;

Now we must do something about the foreign key in relvar SP that references the old base relvar S. Clearly, it would be best if that foreign key could now be taken as referring to the view S instead;^[207] if this is impossible (as it typically is in today’s products), then we might want to define another base relvar as follows:

     VAR SS BASE RELATION { SNO CHAR } KEY { SNO } ;

And copy the data (obviously this must be done before dropping base relvar S):

     SS := S { SNO } ;

Now we need to add the following foreign key specification to the definitions of relvars LS and NLS:

     FOREIGN KEY { SNO } REFERENCES SS

Finally, we must change the specification for the foreign key {SNO} in relvar SP to refer to SS instead of S.

9.9 a. No answer provided—except to note that if it’s difficult to answer the question for some product, then that very fact is part of the point of the exercise in the first place. b. Same as for part a., but more so. c. Ditto.

9.10 For the distinction, see the body of the chapter. SQL doesn’t support snapshots at the time of writing.

(It does support CREATE TABLE AS—see the last part of the answer to Exercise 1.16, earlier in this appendix—which allows a base table to be initialized when it’s created, but CREATE TABLE AS has no REFRESH option.)

9.11 “Materialized view” is a deprecated term for a snapshot. The term is deprecated because it muddies concepts that are logically distinct and ought to be kept distinct—by definition, views simply aren’t materialized, so far as the model is concerned—and it’s leading us into a situation in which we no longer have a clear term for a concept we did have a clear term for, originally. It should be firmly resisted. (I realize I’ve probably already lost this battle, but I’m an eternal optimist.) In fact, I’m tempted to go further; it seems to me that people who advocate use of the term “materialized view” are betraying their lack of understanding of the relational model in particular and the distinction between model and implementation in general.

9.12 First, here’s a definition of Design b. in terms of Design a. (pertinent constraints included):

     VAR SSP VIRTUAL ( S JOIN SP )
         KEY { SNO , PNO } ;

     VAR XSS VIRTUAL ( S NOT MATCHING SP )
         KEY { SNO } ;
     CONSTRAINT B_FROM_A IS_EMPTY ( SSP JOIN XSS ) ;

(Constraint B_FROM_A and the specified key constraints are together what we would have to tell the user if we wanted the user to think of relvars SSP and XSS as base relvars, not views.) And here’s a definition of Design a. in terms of Design b.:

     VAR S VIRTUAL ( XSS D_UNION SSP { ALL BUT PNO , QTY } )
         KEY { SNO } ;

     VAR SP VIRTUAL ( SSP { SNO , PNO , QTY } )
         KEY { SNO , PNO } ;
     CONSTRAINT A_FROM_B IS_EMPTY ( SP NOT MATCHING S ) ;

Given these constraints, the designs are information equivalent. But Design a. is superior, because the relvars in that design are in fifth normal form. By contrast, relvar SSP in Design b. isn’t even in second normal form; as a consequence, it displays redundancy and is thereby subject to certain “update anomalies.” Consider also what happens with Design b. if some supplier ceases to supply any parts, or used not to supply any but now does. Further discussion of the problems with Design b. is beyond the scope of this book; I just note that (as the example suggests) database design disciplines like normalization can help in the task of choosing “the best” design from a set of designs that are information equivalent.

Incidentally, I note in passing that—given that {SNO} is a key for relvar S—constraint A_FROM_B here shows another way of formulating a referential constraint. In practice, of course, it would be simpler just to include the following foreign key specification as part of the definition of relvar SP:

     FOREIGN KEY { SNO } REFERENCES S

9.13 The following discussion relies on the fact that (as Appendix A explains in more detail) databases are really variables—i.e., we really need to draw a distinction between database values and database variables, analogous to that between relation values and relation variables. Let DBD1 and DBD2 be (logical) database designs; let DB1 and DB2 be database variables conforming to DBD1 and DBD2, respectively; and let db1 and db2 be the current values of DB1 and DB2, respectively. Further, let there exist mappings M12 and M21—i.e., sequences of relational algebra operations, loosely speaking—that transform db1 into db2 and db2 into db1, respectively. Then db1 and db2 are information equivalent, meaning that for every expression involving only relations from db1, there’s an expression involving only relations from db2 that yields the same result (and vice versa).

Now let database variables DB1 and DB2 be such that for every possible value db1 of DB1 there exists an information equivalent value db2 of DB2 (and vice versa). Then DB1 and DB2 per se are information equivalent, as are the corresponding designs DBD1 and DBD2.

Now let database variables DB1 and DB2, as well as their current values db1 and db2, be information equivalent. Let U1 be an update on DB1 that transforms db1 into db1′. Then there must exist an update U2 on DB2 that transforms db2 into db2′, such that db1′ and db2′ are information equivalent. Note that the remarks of this paragraph apply in particular to the case in which DB1 consists only of base relvars and DB2 consists only of views of relvars in DB1.

Finally, let database variables DB1 and DB2, as well as their current values db1 and db2, not be information equivalent. Then there must exist an expression involving only relations from db1 with no counterpart involving only relations from db2 (or vice versa), and there must exist an update on DB1 with no counterpart on DB2 (or vice versa)—speaking somewhat loosely in both cases. Again note that the remarks of this paragraph apply in particular to the case in which DB1 consists only of base relvars and DB2 consists only of views of relvars in DB1.

9.14 (You might want to review the section THE RELIANCE ON ATTRIBUTE NAMES in Chapter 6 before reading this answer.) Yes, views should indeed have been sufficient to solve the logical data independence problem. But the trouble with views as conventionally understood is that a view definition specifies both the application’s perception of some portion of the database and the mapping between that perception and the database “as it really is.” In order to achieve the kind of data independence I’m talking about here, those two specifications need to be kept separate (and the mapping specification in particular should be hidden from the user).