The Place of Design Theory

Design theory as such isn't part of the relational model; rather, it's a separate theory in its own right that builds on top of that model. (It's appropriate to think of it as part of relational theory overall, but it's not, to repeat, part of the model as such.) However, it does rely on certain fundamental notions for example, the operators projection and join that are part of the model.

And another thing: the design theory I'm talking about doesn't really tell you how to do design! Rather, it tells you what goes wrong if you don't design the database in the "obvious" way. Consider suppliers and parts, for example. The obvious design is the one I've been assuming in this book all along; I mean, it's "obvious" that three relvars are necessary, that attribute STATUS belongs in relvar S, that attribute COLOR belongs in relvar P, that attribute QTY belongs in relvar SP, and so on. But why exactly are these things obvious? Well, suppose we tried a different design; for example, suppose we moved the STATUS attribute out of relvar S and into relvar SP (intuitively the wrong place for it, since status has to do with suppliers, not shipments). Figure 7-1 shows a sample value for this revised shipments relvar (which I'll call STP to avoid confusion).

Figure 7-1. Relvar STP sample value

A glance at the figure is sufficient to show what's wrong with this design: it's redundant, in the sense that every tuple for supplier S1 tells us S1 has status 20, every tuple for supplier S2 tells us S2 has status 10, and so on. And design theory tells us that not designing the database in the obvious way will lead to such redundancy, and it also tells us the consequences of such redundancy. In other words, design theory is basically all about reducing redundancy, as we'll soon see. For such reasons, design theory has been characterized perhaps a little unkindly as a good source of bad examples. What's more, it has also been criticized on the grounds that it's all just common sense anyway. I'll come back to this criticism in the next section.

To put a more positive spin on matters, design theory can be useful in checking that designs produced via some other methodology don't violate any formal design principles. Then again . . . the sad fact is, while those formal design principles do constitute the scientific part of the design discipline, there are numerous aspects of design that they simply don't address at all. Database design is still largely subjective in nature; the formal principles I'm going to describe in this chapter represent the one small piece of science in what's otherwise a mostly artistic endeavor.

So I want to consider the scientific part of design. To be specific, I want to examine two broad topics, normalization and orthogonality. Now, I assume you already know a lot about the first of these, at least. In particular, I assume you know that:

• There are several different normal forms (first, second, third, and so on).

• Loosely speaking, if relvar R is in (n+1)st normal form, then it's certainly in n th normal form.

• It's possible for a relvar to be in n th normal form and not in (n+1)st normal form.

• The higher the normal form the better, from a design point of view.

• These ideas all rely on certain dependencies (in this context, just another term for integrity constraints).

I'd like to elaborate briefly on the last of these points. I've said that constraints in general are highly relevant to the design process. It turns out, however, that the particular constraints we're talking about here the so-called dependencies enjoy certain formal properties that constraints in general don't (so far as we know). I can't get into this issue very deeply here; however, the basic point is that it's possible to define certain inference rules for such dependencies, and it's the existence of those inference rules that make it possible to develop the design theory that I'm going to describe.

To repeat, I assume you already know something about these matters. As noted in the previous section, however, I want to focus on aspects of the subject that you might not be so familiar with; I want to highlight the more important parts and downplay the others, and more generally I want to look at the whole subject from a perspective that might be a little different from what you're used to.