A database can be thought of as a kind of electronic filing cabinet; it contains digitized information (“data”), which is kept in persistent storage of some kind, typically on magnetic disks. Users can insert new information into the database, and delete, change, or retrieve existing information in the database, by issuing requests or commands to the software that manages the database—which is to say, the database management system (DBMS for short). Note: Throughout this book, I take the term user to mean either an application programmer or an interactive user^[2] or both, as the context demands.

Now, in practice, those user requests to the DBMS can be formulated in a variety of different ways (e.g., by pointing and clicking with a mouse). For our purposes, however, it’s more convenient to assume they’re expressed in the form of simple text strings in some formal language. Given a human resources database, for example, we might write:

EMP WHERE JOB = 'Programmer'

And this expression represents (let’s agree) a retrieval request—more usually known as a query—for employee information for employees whose job title is ‘Programmer’.

The Running Example

Figure 1-1 below shows sample values for a typical database, having to do with suppliers, parts, and shipments (of parts by suppliers).

Figure 1-1. The suppliers-and-parts database—sample values

As you can see, this database contains three files, or tables. (Actually they’re relations, as we’ll see in Chapter 2, but for the purposes of this introductory chapter “files” or “tables” will do.) The tables are named S, P, and SP, respectively, and since they’re tables they’re made up of rows and columns (in conventional file terms, the rows correspond to records of the file in question and the columns to fields). They’re meant to be understood as follows:

• Table S represents suppliers under contract. Each supplier has one supplier number (SNO), unique to that supplier; one name (SNAME), not necessarily unique (though the sample values shown in Figure 1-1 do happen to be unique); one status value (STATUS); and one location (CITY). Note: In the rest of this book I’ll abbreviate “suppliers under contract,” most of the time, to just suppliers.

• Table P represents kinds of parts. Each kind of part has one part number (PNO), which is unique; one name (PNAME); one color (COLOR); one weight (WEIGHT); and one location where parts of that kind are stored (CITY). Note: In the rest of this book I’ll abbreviate “kinds of parts,” most of the time, to just parts.

• Table SP represents shipments—it shows which parts are shipped, or supplied, by which suppliers. Each shipment has one supplier number (SNO); one part number (PNO); and one quantity (QTY). Also, there’s at most one shipment at any given time for a given supplier and given part, and so the combination of supplier number and part number is unique to any given shipment.

Examples throughout the rest of this book are based for the most part on the foregoing database. Now, you might well have seen this database before—I’ve used it in several other books and writings, including SQL and Relational Theory in particular,^[3] as well as in numerous live presentations—and you might be forgiven for getting a little tired of it. But as I’ve written elsewhere, I believe using the same example in a variety of different publications can be a help, not a hindrance, in learning. Of course, it’s true that real databases tend to be much more complicated than this “toy” example; but the trouble with using more realistic examples is that they tend to be too complicated (they make it difficult to see the forest for the trees, as it were). Let me say too that even if it is somewhat unrealistic, the suppliers-and-parts database has at least been very carefully tailored to illustrate all kinds of points that we need to be examining later on; thus, it’s more than adequate for our purposes in this book. Please note, therefore, that in examples throughout the rest of this book, I’ll be assuming the specific sample values shown in Figure 1-1, barring explicit statements to the contrary.

Now, when I say a figure like Figure 1-1 “shows … a typical database,” what I mean is it shows that database as perceived by the user (what’s sometimes called a logical database). That logical database is contrasted with the corresponding physical database, which is the database as perceived by the DBMS (i.e., it’s the database as physically stored inside the computer system). Note carefully, therefore, that what I’m calling here the logical and physical databases aren’t two totally different things; rather, they’re two different perceptions of the same thing. Refer to Figure 1-2. As that figure is meant to suggest, the DBMS—the software that manages the database—effectively serves as a kind of intermediary between the logical and physical levels of the system: User requests for access to the database are expressed in terms of the logical database, and they’re implemented by the DBMS (“executed”) in terms of the corresponding physical database.

Figure 1-2. Database system architecture

One general function provided by the DBMS is thus the shielding of users from details of the physical level of the system (very much as programming language systems also shield users from details of the physical level of the system). In other words, the DBMS provides users with a perception of the database that’s more abstract, and thus more user friendly, than the way the database looks physically—i.e., the way it’s physically stored inside the system.^[4]

Now, a DBMS is a complex piece of software, and it consists of many components. But there’s one component I’d like to mention right away, since it’s so important to the subject of this book, and that’s the optimizer. The optimizer is the DBMS component that’s responsible for deciding exactly how to implement user requests. The point is this: Most requests (indeed, very likely all requests) are capable of being implemented in a variety—typically a very large variety—of different ways. Moreover, those different ways will typically have widely differing performance characteristics; in particular, they could have execution times that vary, quite literally, from fractions of a second to many days. Thus, it’s very important that the optimizer choose a “good” way to implement any given request, where “good” essentially means having good performance.

One immediate and significant implication of the foregoing is this: Assuming the optimizer makes a reasonably good job of things, users shouldn’t have to get involved in performance issues at all. As a matter of fact—to jump ahead of myself for a moment—let me state here for the record that it is and always was a major objective of the relational model that it should be the system, not the user, that has to worry about performance issues. Indeed, to the extent this objective is not met, the system can be said to have failed (or certainly to be less than fully successful, at any rate).

So now we know what a DBMS is. But what’s a relational DBMS? Well, first, of course, it’s a DBMS, which means it provides all of the functionality mentioned in this chapter so far: data storage, query and update, recovery and concurrency, security and integrity, as well as other functions not discussed in this book. But second, it’s also relational, which means the user interface is based on—better: is a faithful implementation of—the relational model. In other words, the relational model can be thought of as a kind of recipe for what the user interface is supposed to look like in such a DBMS.

Before going any further, I’d like to stress the fact that that recipe (viz., the relational model) is very simple! The prescriptions of the relational model aren’t a straitjacket—rather, they’re a discipline, a discipline that makes life much easier for the user. (Easier for the system too, in certain respects; but the emphasis is on the user.) Now, it’s true that the recipe might sometimes look a little complicated, but that’s because the relational model is, above all, precise, and precision requires precise terminology, and precise terminology can sometimes be a little daunting. But the concepts the terminology refers to are actually quite simple (after all, the suppliers-and-parts database was pretty easy to understand, wasn’t it?). In fact, it seems to me that the concepts in question are much simpler than their counterparts were in older, prerational (and nonrelational) systems such as IMS and IDMS.^[7]

To repeat, then, a relational DBMS is a DBMS that supports a user interface that’s a faithful implementation of the relational model—meaning, as far as the user is concerned, that:

In this first part of the book, then, we’ll take a closer look at exactly what it means for data to “look relational,” and we’ll examine various relational operators and see how they can be used in practice. Please note, however, that the treatment isn’t meant to be exhaustive (a more complete treatment of these topics can be found in SQL and Relational Theory); but it is meant to be comprehensive, as far as it goes, and it’s certainly meant to be accurate.

Unfortunately, there’s a problem. In order to illustrate the concepts I’m going to be discussing, I obviously need to show coding examples; thus, I clearly need some formal language in which to express those examples. But the relational model doesn’t prescribe any such language; that is, it doesn’t prescribe a concrete syntax for how its concepts are to be realized in practice. Rather, it’s defined at a high level of abstraction, and is in principle capable of concrete realization in any number of different syntactic forms. Now, a standard concrete language does exist: viz., SQL (pronounced “ess cue ell,” or sometimes “sequel”), which is supported, more or less, by all of the mainstream database products on the market today. As noted in the preface, however, SQL is very deeply flawed: It’s complex, incomplete, hard to learn, and indeed actively misleading in numerous ways. So what I plan to do in this book is this:

(As fort Part II, a brief explanation of what that’s about can be found in the preface.) Note: Perhaps I should warn you that, precisely because of the foregoing plan, what follows doesn’t look much like most of the books and presentations currently available in the marketplace that purport to be “an introduction to relational databases.”

One last point to close this section: You might or might not know (but I hope you do) that the relational model was originally the invention of E. F. Codd, when he was employed as a researcher at IBM (E for Edgar and F for Frank—but he always signed with his initials; to his friends, among whom I was proud to count myself, he was Ted). It was late in 1968 that Codd, a mathematician by training, first realized that the discipline of mathematics could be used to inject some solid principles and rigor into a field, database management, that prior to that time was all too deficient in any such qualities. His original description of the ideas of the relational model appeared in an IBM Research Report in 1969 (see Appendix E for further discussion).

The point doesn’t often seem to be recognized, but there are some analogies that can helpfully be drawn between database systems (relational or otherwise) and programming systems; in fact, it doesn’t often seem to be recognized that a database system actually is a programming system (more precisely, a special case of such a system). Consider the code fragment shown in Figure 1-3. The purpose of that code fragment—which is expressed in a hypothetical but self-explanatory language—is to compute and display the sum of the integers in a certain one-dimensional array called A.

Figure 1-3. A code fragment

Note the following points:

Finally, the reason for rehearsing all of this extremely familiar material is that (as I hope you were expecting) all of the foregoing concepts are directly relevant to databases, as we’ll see in the chapters to come.

Now it’s your turn. Of course, it isn’t possible to set any particularly searching exercises at this early point in the book, and the following are little more than review questions. Nevertheless, I’d like to recommend that you try to answer them yourself before going on to read my answers in the next section. Note: The first two exercises in particular are slightly unfair, because I haven’t yet told you enough to answer them properly—but I think you should have a go at them anyway. They’re not very difficult.

1.1 a. Parts with weight less than 12.5. This example involves a relational restriction operation. b. Part number, color, and city for every part. This part of the exercise was definitely unfair, but you probably guessed the answer anyway—just as restriction picks out certain rows, so the operation of projection, illustrated by this example, picks out certain columns. See Chapter 4 for further explanation.

1.2 a. Delete all suppliers with status 10. b. Increase the status by 5 for all suppliers with status greater than 10. By the way, notice the use of the keyword UPDATE (in upper case) in this exercise. Perhaps a little confusingly, it has become standard practice in the database world to use the uppercase term UPDATE to refer to the specific operator that changes existing data (as opposed to DELETE, which deletes existing data, and INSERT, which inserts new data), and the lowercase term update to refer to the INSERT, DELETE, and UPDATE operators considered generically. In this book, therefore, when I want to refer to the UPDATE operator as such, I’ll set it in upper case (“all caps”) as just shown. As for the INSERT and DELETE operators, however, where no ambiguity arises, it can be a little tedious always to set them in all caps—especially when they’re being used as qualifiers, as in, e.g., “INSERT statement” (“insert statement”?). I’ve therefore decided to use both forms in this book, letting context be my guide in any given situation (and I won’t pretend I’ve been all that consistent in this regard, either).

1.3 A database is a repository for data stored electronically (an “electronic filing cabinet”). A DBMS is a software system that manages databases and access to those databases.

1.4 Security controls protect the database from unauthorized operations. Integrity controls protect the database from authorized but invalid operations. Concurrency controls protect user operations from interfering with one another. Recovery controls protect against data loss.

1.5 SQL is the standard language for interacting with “relational” databases; it’s supported by just about every mainstream database product on the market today. Tutorial D is a language expressly designed for use in illustrating relational concepts; prototype implementations do exist—see the website www.thethirdmanifesto.com—but, at the time of writing, no commercial products.

1.6 A type is a named set of values. A value is an “individual constant”—e.g., the integer three—and it can’t be changed. A variable is a holder for (some encoded representation of) some value, and it can be changed—i.e., its current value can be replaced by another value. A literal is a “self-defining symbol” denoting a value—e.g., the numeral 3. Assignment is the operator by which a variable is updated. A comparison is a read-only operator that (usually) takes two values as operands and returns a truth value. A read-only operator is an operator that takes zero or more values as operands and returns a value. An update operator is an operator that updates some variable; in other words, it’s an assignment, logically speaking. Note: The operators INSERT, DELETE, and UPDATE mentioned in the answer to Q: are all relational update operators, and are thus by definition all effectively relational assignments. For further explanation of this point, see Chapter 2.