Chapter 2 discussed types and relations, among other things. However, I wasn’t in a position in that chapter to explain the most important logical difference between those two concepts—but now I am, and I will.
I’ve shown that the database at any given time can be thought of as a collection of true propositions: for example, the proposition Supplier S1 is under contract, is named Smith, has status 20, and is located in city London. More specifically, I’ve shown that the argument values appearing in such a proposition (S1, Smith, 20, and London, in the example) are, precisely, the attribute values from the corresponding tuple, where each such attribute value is a value of the associated type. It follows that:
Types are sets of things we can talk about; relations are (true) statements we make about those things.
In other words, types give us our vocabulary—the things we can talk about—and relations give us the ability to say things about the things we can talk about. For example, if we limit our attention to suppliers only, for simplicity, we see that:
The things we can talk about are character strings and integers—and nothing else. (In a real database, of course, our vocabulary will usually be much more extensive than this, especially if any user defined types are involved.)
The things we can say are things of the form “The supplier with the supplier number denoted by the specified character string is under contract; has the name denoted by another specified character string; has the status denoted by the specified integer; and is located in the city denoted by yet another specified character string”—and nothing else. (Nothing else, that is, except for things logically implied by things we can say explicitly. For example, given the things we already know we can say explicitly about supplier S1, we can also say things like Supplier S1 is under contract, is named Smith, has status 20, and is located in some city—where the city is left unspecified. (And if you’re thinking that what I’ve just said is very reminiscent of, and probably has some deep connection to, relational projection ... well, you’d be absolutely right. See the section WHAT DO RELATIONAL EXPRESSIONS MEAN? in Chapter 6 for further discussion.)
The foregoing state of affairs has at least three important corollaries. To be specific, in order to “represent some portion of the real world” (as I put it in the previous section):
Types and relations are both necessary—without types, we would have nothing to talk about; without relations, we couldn’t say anything.
Types and relations are sufficient, as well as necessary—we don’t need anything else, logically speaking. (Well, we do need relvars, in order to reflect the fact that the real world changes over time, but we don’t need them to represent the situation at any given time.)[68]
Types and relations aren’t the same thing. Beware of anyone who tries to pretend they are! In fact, pretending a type is just a special kind of relation is precisely what certain products try to do (though it goes without saying that they don’t usually talk in such terms)—and I hope it’s clear that any product that’s founded on such a logical error is doomed to eventual failure. (As a matter of fact, at least one of the products I have in mind here already has failed.) The products in question aren’t relational products, though; typically, they’re products that support “objects” in the object oriented sense, or products that try somehow to marry such objects and SQL tables. Further details of such products are beyond the scope of this book.
Here’s a slightly more formal perspective on what I’ve been saying. As we’ve seen, a database can be thought of as a collection of true propositions. In fact, a database, together with the operators that apply to the propositions represented in that database (or sets of such propositions, rather), is a logical system. And by “logical system” here, I mean a formal system—like euclidean geometry, for example—that has axioms (“given truths”) and rules of inference by which we can prove theorems (“derived truths”) from those axioms. Indeed, it was Codd’s very great insight, when he invented the relational model back in 1969, that a database (despite the name) isn’t really just a collection of data; rather, it’s a collection of facts, or in other words true propositions. Those propositions—the given ones, that is to say, which are the ones represented by the tuples in the base relvars—are the axioms of the logical system under discussion. And the inference rules are essentially the rules by which new propositions can be derived from the given ones; in other words, they’re the rules that tell us how to apply the operators of the relational algebra.[69] Thus, when the system evaluates some relational expression (in particular, when it responds to some query), it’s really deriving new truths from given ones; in effect, it’s proving theorems!
Once we understand the foregoing, we can see that the whole apparatus of formal logic becomes available for use in attacking “the database problem.” In other words, questions such as
What should the database look like to the user?
What should integrity constraints look like?
What should the query language look like?
How can we best implement queries?
More generally, how can we best evaluate database expressions?
How should results be presented to the user?
How should we design the database in the first place?
(and others like them) all become, in effect, questions in logic that are susceptible to logical treatment and can be given logical answers.
It goes without saying that the relational model supports the foregoing perception very directly—which is why, in my opinion, that model is rock solid, and “right,” and will endure. It’s also why, again in my opinion, other data models are simply not in the same ballpark. Indeed, I seriously question whether those other data models deserve to be called models at all, in the same sense that the relational model does. Certainly most of them are ad hoc to a degree, instead of being firmly founded, as the relational model is, on set theory and predicate logic. I’ll expand on these issues in Appendix A.
[68] When I say types and relations are necessary and sufficient, I am of course talking only about the logical level. Obviously other constructs (pointers, for example) are needed at the physical level, as we all know—but that’s because the design goals are different at that level. The physical level is beyond the purview of the relational model, deliberately.
[69] Or the relational calculus. Either way, it comes to the same thing (see Chapter 10).