SQL and Relational Theory, 2nd Edition

“WHAT IF” QUERIES

“What if” queries are a frequent requirement; they’re used to explore the effect of making certain changes without actually having to make (and subsequently unmake, possibly) the changes in question. Here’s an example (“What if parts in Paris were in Nice instead and their weight was doubled?”):

As you can see, the Tutorial D expression on the left here makes use of EXTEND once again. Note, however, that the target attributes in the assignments in braces aren’t “new” attributes, as they normally are for EXTEND; instead, they’re attributes already existing in the specified relation. What the expression does is this: It yields a relation containing exactly one tuple t2 for each tuple t1 in the current value of relvar P for which the city is Paris—except that, in that tuple t2, the weight is double that in tuple t1 and the city is Nice, not Paris.^[107] In other words, the expression overall is shorthand for the following (and this expansion should help you understand the SQL version of the query):

     WITH ( R1 := P WHERE CITY = 'Paris' ,
            R2 := EXTEND R1 : { NC := 'Nice' , NW := 2 * WEIGHT } ,
            R3 := R2 { ALL BUT CITY , WEIGHT } ) :
     R3 RENAME { NC AS CITY , NW AS WEIGHT }

And now I can take care of some unfinished business from Chapter 5. In that chapter, I said the relational UPDATE operator was shorthand for a certain relational assignment, but the details were a little ...