SQL and Relational Theory, 2nd Edition

EQUALITY COMPARISONS

Despite what I said a few moments ago about ignoring user defined types, I’m going to assume in the present section, purely for the sake of the example, that the supplier number (SNO) attributes in relvars S and SP are of some user defined type—sorry, domain—which I’ll assume for simplicity is called SNO as well. Likewise, I’m going to assume that the part number (PNO) attributes in relvars P and SP are also of a user defined type (or domain) with the same name, PNO. Please note that these assumptions aren’t crucial to my argument; it’s just that I think they make the argument a little more convincing, and perhaps easier to follow.

I’ll start with the fact that, as everyone knows (?), two values can be compared for equality in the relational model only if they come from the same domain. For example, the following comparison (which might be part of the WHERE clause in some SQL query) is obviously valid:

     SP.SNO = S.SNO         /* OK     */

By contrast, this one obviously (?) isn’t:

SP.PNO = S.SNO         /* not OK */

Why not? Because part numbers and supplier numbers are different kinds of things—they’re defined on different domains. So the general idea is that the DBMS^[18] should reject any attempt to perform any relational operation (join, union, whatever) that involves, either explicitly or implicitly, an equality comparison between values from different domains. For example, suppose some user wants to find suppliers (like supplier S5 in the sample values of Figure 1-3 in Chapter 1 ...