Now I return to the question I raised earlier: Given a design like that of Figure C-7, aren’t some queries going to get awfully complex? In particular, what’s involved with that design in doing a query analogous to the “simple” SQL query SELECT * FROM S?
Before I address that issue, let me first point out that some queries—queries, I venture to suggest, that are more likely to be needed in practice than ones like SELECT * FROM S—are actually easier to formulate with the design of Figure C-7. As a trivial example, the query “For suppliers for whom CITY is both applicable and known, get supplier numbers and cities” becomes just—
SELECT SNO , CITY
FROM SC—instead of:
SELECT SNO , CITY
FROM S
WHERE CITY IS NOT NULLWhat’s more, the query “Get suppliers for whom CITY is applicable but unknown” is not only simpler with the design of Figure C-7, it can’t be done at all with the original design of Figure C-1. (In other words, not only does the design of Figure C-1 not deal very well with the missing information problem in general, it actually manages to lose information!)
Be that as it may, let’s now consider the “SELECT * FROM S” question. More precisely, let’s see how a respectable version of the table in Figure C-1 can be obtained from those in Figure C-7—where by respectable, I mean the table will contain proper and informative data values everywhere (no shaded entries! no nulls!), as indicated in Figure C-8 below.
Now, however, I’ll switch to Tutorial D (doing the example in SQL would make it too hard to see the forest for the trees). I’ll show the solution a step at a time, using the values from Figure C-7 as a basis for illustrating the result of each step in turn; then I’ll bring all the steps together at the end.
WITH ( T1 := EXTEND ST : { XSTATUS := CAST_AS_CHAR ( STATUS ) } ) :T1WITH ( T2 := T1 { ALL BUT STATUS } ) :T2SNOXSTATUSS120S210WITH ( T3 := EXTEND SUT : { XSTATUS := 'd/k’ } ) :T3SNOXSTATUSS3d/kS4d/kWITH ( T4 := UNION { T2 , T3 } ) :T4SNOXSTATUSS120S210S3d/kS4d/kWITH ( T5 := SC RENAME { CITY AS XCITY } ) :T5SNOXCITYS1LondonS3ParisWITH ( T6 := EXTEND SUC : { XCITY := 'd/k’ } ) :T6SNOXCITYS2d/kWITH ( T7 := EXTEND SNC : { XCITY := 'n/a’ } ) :T7SNOXCITYS4n/aWITH ( T8 := UNION { T5 , T6 , T7 } ) :T8SNOXCITYS1LondonS2d/kS3ParisS4n/aWITH ( S := JOIN { SN , T4 , T8 } ) : SSSNOSNAMEXSTATUSXCITYS1Smith20LondonS2Jones10d/kS3Blaked/kParisS4Clarkd/kn/aPutting all of these steps together and simplifying slightly:
WITH ( T1 := EXTEND ST : { XSTATUS := CAST_AS_CHAR ( STATUS ) } , T2 := T1 { ALL BUT STATUS } , T3 := EXTEND SUT : { XSTATUS := 'd/k' } , T4 := UNION { T2 , T3 } , T5 := SC RENAME { CITY AS XCITY } , T6 := EXTEND SUC : { XCITY := 'd/k' } , T7 := EXTEND SNC : { XCITY := 'n/a' } , T8 := UNION { T5 , T6 , T7 } , S := JOIN { SN , T4 , T8 } ) : S
Now, it’s certainly true that this expression looks a little complicated (or tedious, at any rate), and it would look even more so if I hadn’t formulated it a step at a time, using WITH. However:
Various shorthands could be defined, if desired, that could be used to simplify it.
I frankly doubt whether tables such as that in Figure C-8 would ever be wanted much in practice anyway, except perhaps as the basis for some kind of periodic report.
In any case, the complexity, such as it is, can always be concealed by making the table a view.