Everyone knows (or should know!) that, in the relational model, two values can be tested for equality only if they come from the same domain. In the case of suppliers and parts, for example, the following comparison—which might be part of a WHERE clause—is obviously valid:

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

By contrast, the following one is not:

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

The reason is that part numbers and supplier numbers are different kinds of things, and they correspond to different domains. So the general idea is that the database management system (DBMS)^[*] should reject any attempt to perform any relational operation—join, union, divide, or whatever—that calls, explicitly or implicitly, for a comparison between values from different domains. For example, here’s an SQL query where the user is trying to find suppliers who supply no parts:

    SELECT S.SNO, S.SNAME, S.STATUS, S.CITY
    FROM   S
    WHERE  NOT EXISTS
         ( SELECT SP.PNO
           FROM   SP
           WHERE  SP.PNO = S.SNO )      /* not OK */

(There’s no terminating semicolon because this is an expression, not a statement. See Exercise 2-24 at the end of the chapter.)

As the comment says, this query is not OK. The reason is that, in the last line, the user presumably meant to say WHERE SP.SNO = S.SNO, but by mistake—probably just a slip of the typing fingers—he or she said WHERE SP.PNO = S.SNO instead. And, given that we’re indeed talking about a simple typo (probably), it would be a friendly act on the part of the DBMS to interrupt at this point, highlight the error, and ask if the user would like to correct it before proceeding.

Now, I don’t know any commercial product that actually behaves in the way I’ve just suggested; in today’s products, depending on how you’ve set up the database, either the query will simply fail or it’ll give the wrong answer. Well . . . not exactly the wrong answer, perhaps, but the right answer to the wrong question. (Does that make you feel any better?)

To repeat, therefore, the DBMS should reject a comparison like SP.PNO = S.SNO if it isn’t valid. Codd proposed, however, that there should be a way for the user to force the DBMS to go ahead and do the comparison anyway, even if it isn’t valid, on the grounds that sometimes the user will know more than the DBMS does. Now, it’s a little hard for me to do justice to this proposal, because I don’t agree with it—but let me try.

Suppose it’s your job to design a database involving, say, customers and suppliers, and you decide therefore to have a domain of customer numbers and another domain of supplier numbers. You build your database that way and load it, and everything works just fine for a year or two. Then, one day, one of your users comes along with a query you never heard before, namely: “Are any of our customers also suppliers to us?” Observe that this is a perfectly reasonable query; observe too that it might involve a comparison between a customer number and a supplier number (a cross-domain comparison), to see if they’re equal. And if it does, well, certainly the system mustn’t prevent you from doing it; certainly the system mustn’t prevent you from asking a reasonable query.

In light of the foregoing, Codd proposed what he called domain check override (DCO) versions of certain of his algebraic operators. A DCO version of join, for example, would perform the join even if the joining attributes were defined on different domains. In SQL terms, we might imagine this proposal being realized by means of a new clause, IGNORE DOMAIN CHECKS, that could be included in an SQL query, as here:

    SELECT ...
    FROM   ...
    WHERE  CUSTNO = SNO
    IGNORE DOMAIN CHECKS

And this new clause would be separately authorizable—most users wouldn’t be allowed to use it at all; perhaps only the database administrator (DBA) would be allowed to use it.

Before analyzing the DCO idea in detail, I want to look at a simpler example. Consider the following two queries:

    SELECT ...                 |   SELECT ...
    FROM   P, SP               |   FROM   P, SP
    WHERE  P.WEIGHT = SP.QTY   |   WHERE  P.WEIGHT - SP.QTY = 0

Assuming, reasonably enough, that weights and quantities are defined on different domains, the query on the left is clearly invalid. But what about the one on the right? According to Codd, that one’s valid! In his book The Relational Model for Database Management Version 2(Addison-Wesley, 1990), he says that in such a situation “the DBMS [merely] checks that the basic data types are the same”; in the case at hand, the “basic data types” are all numbers, loosely speaking, and so the check succeeds.

To me, this conclusion seems unreasonable. The semantics of an expression should not depend on the arbitrary choice of syntax we use to formulate it! Thus, I believe the expressions P.WEIGHT = SP.QTY and P.WEIGHT - SP.QTY = 0 must either both be valid or both be invalid; the suggestion that they have different semantics is unacceptable. So it seems to me there’s something strange about Codd-style domain checks in the first place, before we even get to “domain check override.” (In essence, in fact, Codd-style domain checks apply only in the very special case where both comparands are specified as relational attributes and not as anything else, such as an operational expression like P.WEIGHT - SP.QTY.)

Let’s look at some even simpler examples. Consider the following comparisons (each of which might appear as part of an SQL WHERE clause, for example):

    S.SNO = 'X4'         P.PNO = 'X4'         S.SNO = P.PNO

I hope you agree that it’s at least possible that the first two are valid and the third not. But if so, then I hope you also agree there’s something strange going on; apparently, we can have three values a, b, and c such that a = c is true and b = c is true, but as for a = b . . . well, we can’t even do the comparison, let alone have it come out true! So what is going on?

I return now to the fact that attributes S.SNO and P.PNO are defined on domains SNO and PNO, respectively, and to my claim that domains are really types; in fact, I said in the introduction that domains SNO and PNO in particular were user-defined types. Now, it’s likely that both types are physically represented in terms of the built-in type CHAR—but physical representations are part of the implementation, not the model; they’re irrelevant to the user, and in fact they’re hidden from the user (or should be), as we saw in Chapter 1. In particular, the operators that apply to supplier numbers and part numbers are the operators defined in connection with those types, not the operators that happen to be defined in connection with type CHAR. For example, we can concatenate two character strings, but we probably can’t concatenate two supplier numbers (we could do this latter only if concatenation was an operator defined in connection with type SNO).

Now, when we define a type, one operator we must define is what’s called a selector operator, which allows us to select, or specify, an arbitrary value of the type in question.^[*] The selector for type SNO, for example (which, as we’ll see in Chapter 6, will probably also be called SNO), allows us to select the particular SNO value that has some specified CHAR representation. Here’s an example:

    SNO('S1')

This expression is an invocation of the SNO selector, and it returns a certain supplier number: namely, the one that’s conceptually represented by the character string value ‘S1’. Likewise, the expression:

    PNO('P1')

is an invocation of the PNO selector, and it returns a certain part number: namely, the one that’s conceptually represented by the character string value ‘P1’. As you can see, therefore, the SNO and PNO selectors effectively work by converting a certain CHAR value to a certain SNO value and a certain PNO value, respectively.

Returning now to the comparison S.SNO = ‘X4’: What happens here is that the system notices that the left and right comparands are of different types (SNO and CHAR, to be specific). Since they’re of different types, they certainly can’t be equal. However, the system also knows there’s an operator—the SNO selector—that effectively performs CHAR-to-SNO conversions. So it can invoke that operator implicitly to convert the right comparand to a supplier number, thereby effectively replacing the original comparison with this one:

    S.SNO = SNO('X4')

Now we’re comparing two supplier numbers, which is legitimate.

In the same kind of way, the system effectively replaces the comparison P.PNO = ‘X4’ with this one:

    P.PNO = PNO('X4')

But in the case of the comparison S.SNO = P.PNO, there’s no conversion operator known to the system (at least, let’s assume not) that will convert a supplier number to a part number or the other way around, and so the comparison fails on a type error: the comparands are of different types, and there’s no way to make them be of the same type.

To continue with the example, another operator you must define when you define a type like SNO or PNO is what’s called, generically, a THE_ operator, which effectively converts a given SNO or PNO value to the character string (or whatever else it is) that’s used to represent it.^[*] Assume for the sake of the example, not unreasonably, that the THE_ operators for types SNO and PNO are both called THE_CHAR. Then, if we really did want to compare S.SNO and P.PNO, the only sense I can make of that requirement is that we want to see if the character-string representations are the same, which we might do like this:

    THE_CHAR ( S.SNO ) = THE_CHAR ( P.PNO )

In other words: convert the supplier number to a string, convert the part number to a string, and compare the two strings.

As I’m sure you can see, the mechanism I’ve sketched above effectively provides both (a) the domain checking we want, in the first place, and (b) a way of overriding that checking when we want, in the second place. Moreover, it does all this in a clean, fully orthogonal, non ad hoc manner. By contrast, “domain check override” doesn’t really do the job; in fact, it doesn’t really make sense at all, because it confuses types and representations (as noted previously, types are a model concept, representations are an implementation concept).

Now, you might have realized that what I’m really talking about is here is what’s known in language circles as strong typing . Different writers have slightly different definitions for this term, but basically it means that (a) everything—in particular, every value and every variable—has a type, and (b) whenever we try to perform some operation, the system checks that the operands are of the right types for the operation in question.^[*] Observe too that this mechanism works for any operation, not just for the comparison operations we’ve been discussing; the emphasis on comparison operations in discussions of domain checking is sanctified by historical usage but is in fact misplaced. For example, consider the following expressions:

    P.WEIGHT * SP.QTY
    P.WEIGHT + SP.QTY

The first of these is probably valid (it yields another weight: namely, the total weight of the pertinent shipment). The second, by contrast, is probably not valid (what could it mean to add a weight and a quantity?).

I hope the previous section succeeded in convincing you that domains are indeed types, no more and no less. Now I want to turn to the issue of data value atomicity and the related notion of first normal form (1NF for short). In Chapter 1, I said that 1NF meant that every tuple in every relation contains just a single value (of the appropriate type, of course) in every attribute position—and it’s usual to add that those “single values” are supposed to be atomic. But this latter requirement raises the obvious question: what does it mean for data to be atomic?

Well, on page 6 of the book mentioned earlier, Codd defines atomic data as data that “cannot be decomposed into smaller pieces by the DBMS (excluding certain special functions).” But even if we ignore that parenthetical exclusion, this definition is a trifle puzzling, and not very precise. For example, what about character strings? Are character strings atomic? Every product I know provides several operators on such strings—LIKE, SUBSTR (substring), “||” (concatenate), and so on—that clearly rely on the fact that character strings in general can be decomposed by the DBMS. So are those strings atomic? What do you think?

Here are some other examples of values whose atomicity is at least open to question and yet we would certainly want to be able to include as attribute values in tuples in relations:

Now I’d like to move on to a potentially more startling example. Refer to Figure 2-1. Relation R1 in that figure is a reduced version of the shipments relation from our running example; it shows that certain suppliers supply certain parts, and it contains one tuple for each legitimate SNO-PNO combination. For the sake of the example, let’s agree that supplier numbers and part numbers are indeed “atomic”; then we can presumably agree that R1, at least, is in 1NF.

Figure 2-1. Relations R1, R2, and R3

Now suppose we replace R1 by R2, which shows that certain suppliers supply certain groups of parts (attribute PNO in R2 is what some would call multivalued, and values of that attribute are groups of part numbers). Then most people would surely say that R2 is not in 1NF; in fact, it looks like an example of “repeating groups,” and repeating groups are the one thing that almost everybody agrees 1NF is supposed to prohibit (because such groups are obviously not atomic, right?).

Well, let’s assume for the sake of the argument that R2 isn’t in 1NF. But suppose we now replace R2 by R3. Then I claim that R3 is in 1NF! ^[*] For consider:

The real point I’m getting at here is that the notion of atomicity has no absolute meaning;it just depends on what we want to do with the data. Sometimes we want to deal with an entire set of part numbers as a single thing, and sometimes we want to deal with individual part numbers within that set—but then we’re descending to a lower level of detail (a lower level of abstraction). The following analogy might help. In physics (which after all is where the terminology of atomicity comes from) the situation is exactly parallel: sometimes we want to think about individual physical atoms as indivisible things, and sometimes we want to think about the protons, neutrons, and electrons that go to make up those atoms. What’s more, protons and neutrons, at least, aren’t really indivisible, either—they contain a variety of “subsubatomic” particles called quarks. And so on, possibly.

Let’s return for a moment to relation R3. In Figure 2-1, I showed PNO_SET values as general sets. But it would be more useful in practice if they were, more specifically, relations (see Figure 2-2, where I’ve changed the attribute name to PNO_REL). Why would it be more useful? Because relations, not general sets, are what the relational model is all about.^[*] As a consequence, the full power of the relational algebra immediately becomes available for the relations in question—they can be restricted, projected, joined, and so on. By contrast, if we use general sets instead of relations, then we need to introduce new operators (set union, set intersection, and so on) for dealing with those sets. Much better to get as much mileage as we can out of the operators we already have!

Figure 2-2. Relation R4 (a revised version of R3)

Attribute PNO_REL in Figure 2-2 is an example of a relation-valued attribute (RVA). Of course, the underlying domain is relation-valued too (that is, the values it’s made up of are relations). I’ll have more to say about RVAs in Chapters 5 and 7; here let me just note that SQL doesn’t support them. (More precisely, it doesn’t support what would be its analog of RVAs, table-valued columns—though oddly enough it does support both (a) columns whose values are arrays and (b) columns whose values are “multisets of rows.” A multiset , also known as a bag , is like a set except that it permits duplicates. Columns whose values are multisets of rows thus do look a bit like “table-valued columns” in some respects; however, they aren’t table-valued columns, because the values they contain can’t be operated upon by means of SQL’s regular table operators.)

Now, I chose the foregoing example deliberately, for its shock value. After all, relations with RVAs do look rather like relations with repeating groups, and you’ve probably always heard that repeating groups are a no-no in the relational world. But I could have used any number of different examples to make my point: I could have shown attributes (and therefore domains) that contained arrays; or bags; or lists; or photographs; or audio or video recordings; or X rays; or fingerprints; or XML documents; or any other kind of value, “atomic” or “nonatomic,” that you might care to think of. Attributes, and therefore domains, can contain anything (any values, that is). All of which goes a long way, incidentally, toward explaining why a true “object/relational" system would be nothing more nor less than a true relational system—which is to say, a system that supports the relational model, with all that such support entails. After all, the whole point of an “object/relational” system is precisely that we can have attribute values in relations that are of arbitrary complexity. Perhaps a better way to say it is this: a proper object/relational system is just a relational system with proper type support—which just means it’s a proper relational system, no more and no less.

From this point forward I’ll switch to the term type in preference to domain. What is a type, exactly? In essence, it’s a named, finite set of value s^[*]—all possible values of some specific kind: for example, all possible integers, or all possible character strings, or all possible supplier numbers, or all possible XML documents, or all possible relations with a certain heading (and so on). Moreover:

The fact that parameters in particular are declared to be of some type touches on an issue that I’ve mentioned but haven’t properly discussed yet: namely, the fact that every type has an associated set of operators for operating on values and variables of the type in question. For example, integers have the usual arithmetic operators; dates and times have special calendar arithmetic operators; XML documents have what are called “XPath” operators; relations have the operators of the relational algebra; and every type has the operators of assignment (“:=”) and equality comparison (“=”). Thus, any system that provides proper type support—and “proper type support” here certainly includes allowing users to define their own types—must provide a way for users to define their own operators, too, because types without operators are useless.

It’s important to understand also that values and variables of a given type can be operated upon solely by means of the operators defined for that type. For example, in the case of the system-defined type INTEGER:

By contrast, in the case of the user-defined type SNO, we would certainly define assignment and comparison operators (“:=”, “=”, “≠”, possibly “>”, and so on); however, we probably wouldn’t define operators “+”, “*”, and so on, which would mean that arithmetic on supplier numbers wouldn’t be supported (why would we ever want to add or multiply two supplier numbers?).

From everything I’ve said so far, then, it should be clear that defining a new type involves at least all of the following:

Observe that points 4 and 5 taken together imply that the system knows precisely which expressions are legal, as well as the type of the result for each such legal expression.

By way of example, suppose we have a user-defined type POINT, representing geometric points in two-dimensional space. Here then is the Tutorial D definition^[*] for an operator called REFLECT which, given a point P with cartesian coordinates (x,y), returns the “reflected” or “inverse” point with cartesian coordinates (-x,-y):

    1 OPERATOR REFLECT ( P POINT ) RETURNS POINT ;
    2    RETURN ( POINT ( - THE_X ( P ) , - THE_Y ( P ) ) ) ;
    3 END OPERATOR ;

Explanation:

Now, for the most part, I’ve been talking so far about user-defined types specifically. For a system-defined or built-in type, similar considerations apply, of course, but in this case the definitions are furnished by the system instead of by some user. For example, if INTEGER is a built-in type, then it’s the system that defines the name, specifies legal integers, defines the hidden representation, and defines the corresponding operators. Of course, to someone who merely makes use of some user-defined type that’s been defined by somebody else, that type looks just like a system-defined type anyway; indeed, in many ways that’s the whole object of the exercise.

I don’t propose to go into much more detail regarding type and operator definitions, because they aren’t specifically relational topics, for the most part.

It’s usual to think of types as being either scalar or nonscalar. Loosely, a type is scalar if it has no user-visible components and nonscalar otherwise—and the values, variables, attributes, operators, parameters, and expressions of some type T are scalar or nonscalar according as type T itself is scalar or nonscalar. For example:

That said, I must now stress the point that these notions are quite informal. Indeed, we’ve already seen that the concept of atomicity has no absolute meaning, and “scalarness” is just the concept of atomicity by another name. Thus, the relational model nowhere formally relies on the scalar versus nonscalar distinction. In this book, however, I do rely on it informally; to be specific, I use the term scalar in connection with types that are neither tuple nor relation types, and the term nonscalar in connection with types that are either tuple or relation types.

Let’s look at an example. Here’s a Tutorial D definition for the base relvar S (suppliers):

    1 VAR S BASE
    2     RELATION { SNO SNO, SNAME NAME, STATUS INTEGER, CITY CHAR }
    3     KEY { SNO } ;

Explanation:

In fact, the example also illustrates another point—namely, that the type:

    RELATION { SNO SNO, SNAME NAME, STATUS INTEGER, CITY CHAR }

is an example of a generated type. In general, a generated type is one that’s obtained by invoking some type generator (in the example, the type generator is RELATION). You can think of a type generator as a special kind of operator; it’s special because (a) it returns a type instead of (for example) a scalar value, and (b) it’s invoked at compile time instead of run time. For instance, most programming languages support a type generator called ARRAY, which lets users define a variety of specific array types. For the purposes of this book, however, the only type generators we need to consider are TUPLE and, of course, RELATION. Here’s an example involving the TUPLE type generator:

    VAR SINGLE_SUPPLIER
        TUPLE { STATUS INTEGER, SNO SNO, CITY CHAR, SNAME NAME } ;

The value of variable SINGLE_SUPPLIER at any given time is a tuple with the same heading as that of relvar S. (I’ve specified the attributes in a different sequence deliberately, just to show that the sequence doesn’t matter.) Thus, we might imagine a code fragment that, first, extracts a one-tuple relation (perhaps the relation containing just the tuple for supplier S1) from the current value of relvar S; then extracts the single tuple from that one-tuple relation; and, finally, assigns that tuple to the variable SINGLE_SUPPLIER. In Tutorial D:

    SINGLE_SUPPLIER := TUPLE FROM ( S WHERE SNO = SNO('S1') ) ;

By the way, note carefully that a tuple t and a relation r that contains just that tuple t aren’t the same thing. In particular, they’re of different types—t is of some tuple type and r is of some relation type (though of course they do have the same heading).

There’s just one more thing I want to say about tuple and relation types: even though such types do obviously have user-visible components (namely, their attributes), there’s no suggestion that those components have to be physically stored as such. In fact, the physical representation of values of such types should be hidden from the user, just as it is for scalar types.

It’s a very common misconception that the relational model deals only with rather simple types: numbers, strings, perhaps dates and times, and not much else. In this chapter, I’ve tried to show that this is indeed a misconception. Rather, relations can have attributes of any type whatsoever—the relational model nowhere prescribes what those types must be, and in fact they can be as complex as we like (except as noted in just a moment). In other words, the question as to what types are supported is orthogonal to the question of support for the relational model itself. Or (less precisely but more catchily): types are orthogonal to tables.

I also remind you that the foregoing state of affairs in no way violates the requirements of first normal form. First normal form just means that every tuple in every relation contains a single value, of the appropriate type, in every attribute position. Now that we know those types can be anything, we also know that all relations are in first normal form by definition.

Finally, I mentioned in the introduction to this chapter that there are certain important exceptions to the rule that relational attributes can be of any type whatsoever. In fact, there are two. The first—which I’ll simplify just slightly for present purposes—is that if relation r is of type T, then no attribute of r can itself be of type T (think about it!). The second is that no relation in the database can have an attribute of any pointer type. As you probably know, prerelational databases were full of pointers, and access to such databases involved a lot of pointer-chasing: a fact that made application programming error-prone and direct end-user access impossible. Codd wanted to get away from such problems in his relational model, and of course he succeeded.