SQL and Relational Theory, 2nd Edition

QUERIES

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 NULL

What’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.

Figure C-8. Revised (respectable) version of table S

Now, however, ...