Problem solve Get help with specific problems with your technologies, process and projects.

The relational database dictionary

This tip highlights three definitions from C.J. Date's new book, "The Relational Database Dictionary": 'primary key,' 'relational model' and 'table.'

The following is an excerpt from The Relational Database Dictionary by C.J. Date, ISBN: 0-596-52798-5, Copyright © 2006 C.J. Date. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

Primary key: A candidate key that has been singled out for special syntactic treatment for some reason. While a given relvar can have any number of candidate keys, it can have at most one primary key. For a given relvar, however, whether some candidate key is to be chosen as primary, and if so, which one, are essentially psychological issues, outside the purview of the relational model. Note: The relational model originally insisted that base relvars, at least, should always have a primary key. It also insisted that foreign keys refer to primary keys specifically. However, there were never any good logical reasons for these rules, and rules that apply to base relvars but not to other kinds are more than a little suspect in any case (because they violate The Principle of Interchangeability); thus, the primary key notion could be dropped without serious loss. We mention it here mainly for historical reasons.

Relational model: The formal theory or foundation on which relational databases in particular and relational technology in general are based. The relational model is often loosely characterized as having three aspects: a structural aspect, which has to do with relations as such; an integrity aspect, which has to do with candidate and foreign keys; and a manipulative aspect, which has to do with operators such as join. More precisely, the relational model consists of the following components:

  • an open-ended collection of scalar types, including in particular type BOOLEAN;
  • a relation type generator and an intended interpretation for relations of types generated thereby;
  • facilities for defining relation variables of such generated relation types;
  • a relational assignment operator; and
  • an open-ended collection of generic readonly operators (i.e., relational algebra or relational calculus) for deriving relations from relations.
Notice the last item in particular; it's a far too common error to regard the relational model as consisting of structure only and to overlook the operators, and yet (as Codd once said) structure without operators is rather like anatomy without physiology. Note, moreover, that those operators aren't just meant for writing queries, as many seem to think; rather, they're for writing expressions, expressions that serve many purposes (including query but not limited to it alone). One particularly important purpose is the formulation of constraints (though in this case, the relational expression will be just a subexpression of some boolean expression, frequently though not invariably an invocation of IS_EMPTY, q.v.). Note: In the interest of physical data independence, the relational model is deliberately silent on everything to do with performance.

Table: 1. SQL analog of either a relation or a relvar, as the context demands. Here are some of the major differences between tables in SQL and their relational counterparts:

  • SQL tables can contain duplicate rows;
  • SQL tables can contain nulls;
  • SQL tables have a left-to-right column sequence;
  • SQL tables can have two or more columns with the same name;
  • SQL tables can have what are, in effect, columns with no name at all.
2. More generally, a picture of a relation (on paper, for example). Note: A confusion between relations and such tabular pictures probably accounts for the popular misconception that "relations are flat," or two-dimensional. While it's obviously true that those pictures are two-dimensional, relations in general aren't; rather, a relation of degree n is n-dimensional, in the sense that its tuples correspond to points in some n-dimensional space (one dimension for each attribute of the relation in question). One specific consequence of such considerations is that (again contrary to popular opinion) relations are perfectly capable of representing so-called multidimensional data and thereby supporting so-called online analytical processing (OLAP).

About the author

C.J. Date is an independent author, lecturer and researcher, specializing in relational database technology (a field he helped pioneer). He is best known for his books, especially An Introduction to Database Systems, which has sold nearly 750,000 copies and is used by universities worldwide.

Dig Deeper on Oracle DBA jobs, training and certification

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.