SQL and Relational Theory, 2nd Edition

DIVIDE

I include the following discussion of divide in this chapter only to show why (contrary to conventional wisdom, perhaps) I don’t think it’s very important; in fact, I think it should be dropped. You can skip this section if you like.

I have several reasons (three at least) for wanting to drop divide. One is that any query that can be formulated in terms of divide can alternatively, and much more simply, be formulated in terms of image relations instead, as I’ll demonstrate in just a moment. Another is that there are at least seven different divide operators anyway!—that is, there are, unfortunately, at least seven different operators all having some claim to be called “divide,” and I certainly don’t want to explain all of them. Instead, I’ll limit my attention here to the original and simplest one.

Definition: Let relations r1 and r2 be such that the heading {Y} of r2 is some subset of the heading of r1 and the set {X} is the other attributes of r1. Then the division of r1 by r2, r1 DIVIDEBY r2,^[96] is shorthand for the following:

     r1 { X } NOT MATCHING ( ( r1 { X } JOIN r2 ) NOT MATCHING r1 )

For example, the expression

     SP { SNO , PNO } DIVIDEBY P { PNO }

(given our usual sample data values) yields:

SNO

S1

The expression can thus be loosely characterized as a representation of the query “Get supplier numbers for suppliers who supply all parts” (I’ll explain the reason for that qualifier “loosely” in a few moments). In practice, however, we’re more likely to want full supplier details ...