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} JOINr2) NOT MATCHINGr1)For example, the expression
SP { SNO , PNO } DIVIDEBY P { PNO }
(given our usual sample data values) yields:
|
|
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 (not just supplier numbers) for the suppliers in question, in which case the division will need to be followed by a join
( SP { SNO , PNO } DIVIDEBY P { PNO } ) JOIN SBut we already know how to formulate this query more simply using image relations:
S WHERE ( !!SP ) { PNO } = P { PNO }This latter formulation is (a) more succinct, (b) easier to understand (at least, it seems so to me), and (c) correct. This last point is the crucial one, of course, and I’ll explain it below. First, however, I want to explain why divide is called divide, anyway. The reason is that if r1 and r2 are relations with no attribute names in common and we form the product r1 TIMES r2, and then divide the result by r2, we get back to r1. (At least, we do so just as long as r2 isn’t empty. What happens if it is?) In other words, product and divide are inverses of each other, in a sense.
As I’ve said, the expression
SP { SNO , PNO } DIVIDEBY P { PNO }can loosely be characterized as a formulation of the query “Get supplier numbers for suppliers who supply all parts”; indeed, this very example is often used as a basis for explaining, and justifying, the divide operator in the first place. Unfortunately, however, that characterization isn’t quite correct. Rather, the expression is a formulation of the query “Get supplier numbers for suppliers who supply at least one part and in fact supply all parts.”[97] In other words, the divide operator not only suffers from problems of complexity and lack of succinctness—it doesn’t even solve the problem it was originally, and explicitly, intended to address.
[96] Tutorial D doesn’t directly support this operator, and r1 DIVIDEBY r2 is thus not valid Tutorial D syntax.
[97] If you’re wondering what the logical difference is here, consider the slightly different query “Get suppliers who supply all purple parts” (the point being, of course, that there are no purple parts). If there aren’t any purple parts, then every supplier supplies all of them!—even supplier S5, who supplies no parts at all, and is thus not represented in relvar SP, and so can’t be returned by an analogous DIVIDEBY expression. And if you’re still wondering, then see the further discussion of this example in Chapter 11.