Said a young mathematician named Gene
“I always say what I mean—
Or mean what I say—
It’s the same, anyway—
Or—at least—well, you know what I mean.”
By now you should be very familiar with the idea that relation values (relations for short) vs. relation variables (relvars for short) is one of the great logical differences. Now it’s time to take a closer look at that difference; more specifically, it’s time to take a closer look at issues that are relevant to relvars in particular, as opposed to relations. Caveat: Unfortunately, you might find the SQL portions of the discussion that follows a little confusing, because SQL doesn’t clearly distinguish between the two concepts—as you know, it uses the same term table to mean sometimes a table value, sometimes a table variable. For example, the keyword TABLE in CREATE TABLE clearly refers to a table variable; but when we say, e.g., that table S has five rows, the phrase “table S” clearly refers to a table value (namely, the current value of the table variable called S). Be on your guard for potential confusion in this area.
Let me also remind you of a few further points:
First of all, a relvar is a variable whose permitted values are relations, and it’s specifically relvars, not relations, that are the target for INSERT, DELETE, and UPDATE operations (more generally, for relational assignment operations—recall that INSERT, DELETE, and UPDATE are all just shorthand for certain relational assignments).
Next, if R is a relvar and r is a relation to be assigned to R, then R and r must be of the same (relation) type.
Last, the terms heading, body, attribute, tuple, cardinality, and degree, formally defined in Chapter 3 for relations, can all be interpreted in the obvious way to apply to relvars as well (see Exercise 1.5 in Chapter 1).
The present chapter deals with base relvars (base tables, in SQL). In fact, it won’t hurt too much if you assume throughout this book until further notice that all relvars are base relvars and all tables are base tables, barring explicit statements to the contrary; Chapter 9 discusses the special considerations, such as they are, that apply to virtual relvars or views. The topics I’ll be covering in the present chapter form something of a mixed bag, but generally speaking they fall into the following broad categories:
Updating (relational assignment)
Candidate and foreign keys
Predicates
As a basis for examples, I’ll use the following definitions for the suppliers-and-parts database (Tutorial D on the left and SQL on the right, a pattern I’ll follow in most of my examples in this chapter and throughout the rest of the book):
