Codd's 12 Rules for Relational DBMS

One consequence of this rule: Any system claimed to be a relational DBMS must support data base insert update and delete at the relational level (multiple-record-at-a-time). Another consequence is the necessity of supporting the information rule and the guaranteed access rule.

"Multiple-record-at-a-time" includes as special cases those situations in which zero or one record is retrieved, inserted, updated or deleted. In other words, a relation (table) may have either zero tuples (rows) or one tuple and still be a valid relation.

Any statement in the manuals of a system claimed to be a relational DBMS that advises users to revert to some nonrelational capabilities "to achieve acceptable performance" - or for any reason other than compatibility with programs written in the past on nonrelational data base systems - should be interpreted as an apology by the vendor. Such a statement indicates the vendor has not done the work necessary for achieving good performance with the relational approach.

What is the danger to buyers and users of a sytem that is claimed to be a relational DBMS and that failes on Rule Zero? Buyers and users will expect all the advantages of a truly relational DBMS, and they will fail to get these advantages.

Now I shall describe the 12 rules that, together with the nine structural, 18 manipulative and three integrity features of the relational model, determine in specific detail the extent of validity of a vendor's claim to have a "fully relational DBMS."

All 12 rules are motivated by Rule Zero defined above, but a DBMS can be more readily checked for compliance with these 12 than with Rule Zero.

The information rule is enforced not only for user productivity but also to make it a reasonably simple job for software vendors to define additional software packages (such as application development aids, expert systems and so on) that interface with relational DBMS and, by definition, are well integrated with the DBMS.

That is, these packages retrieve information already existing in the catalog and, as needed, put new information in the catalog by the very act of using the DBMS.

An additional reason to enforce this rule is to make the data base administrator's task of maintaining the data base in a state of overall integrity both simpler and more effective. There is nothing more embarrassing to a data base administrator than being asked if his data base contains certain specific information and his replying after a week's examination of the data base that he does not know.

Note that the guaranteed access rule represents an associative addressing scheme that is unique to the relational model. The rule does not depend at all on the usual computer-oriented addressing. However, the primary key concept is an essential part of it.

Past techniques entailed defining a special value (peculiar to each column or field) to represent missing information. This would be most unsystematic in a relational data base because users would have to employ different techniques for each column or domain - a difficult task because of the high level of language in use (and a task that I believe would decrease user productivity).

Another consequence is that authorized users can easily extend the catalog to become a full-fledged, active relational data dictionary whenever the vendor fails to do so.

1. Data definition.
2. View definition.
3. Data manipulation (interactive and by program).
4. Integrity constraints.
5. Authorization.
6. Transaction boundaries (begin, commit and rollback).

The relational approach is intentionally highly dynamic - that is, it should rarely be necessary to bring the data base activity to a halt (in contrast to nonrelational DBMS). Therefore, it does not make sense to separate the services listed above into distinct languages.

In the mid-'70s, the Ansi Standards Planning and Requirements Committee generated a document advocating 42 distinct interfaces and (potentially) 42 distinct languages for DBMS. Fortulately, that idea has apparently been abandoned.

It can also be extremely important in obtaining efficient handling of transactions across a distributed data base. In this case, users would prefer that communications costs are saved by avoiding the necessity of tranmsmitting a separate request for each record obtained from remote sites.

Nonrelational DBMS rarely provide complete support for this rule - in fact, I know of none that do.

To provide this service whenever possible, the DBMS must be capable of handling inserts, updates and deletes on all views that are theoretically updatable. This rule permits logical data base design to be changed dynamically if, for example, such a change would improve performance.

The physical and logical data independence rules permit data base designers for relational DBMS to make mistakes in their designs without the heavy penalties levied by nonrelational DBMS. This, in turn, means that it is much easier to get started with a relational DBMS because not nearly as much performance-oriented planning is needed prior to "blast-off."

Assume the relational model is faithfully reflected. Then, the additional integrity constraints are defined in terms of the high-level data sublanguage and the definitions stored in the catalog, not in the application programs.

Information about inadequately identified objects is never recorded in a relational data base. To be more specific, the following two integrity rules apply to every relational data base:

1. Entity integrity. No component of a primary key is allowed to have a null value.
2. Referential integrity. For each distinct nonnull foreign key value in a relational data base, there must exist a matching primary key value from the same domain.

In many cases, neither the application programs nor the terminal activities are logically impaired.

Nonrelational DBMS rarely support this rule as part of the DBMS engine, where it belongs. Instead, they depend on a dictionary package, which may or may not be present and can readily be bypassed.

1. when data distribution is first introduced (if the originally installed DBMS manages nondistributed data only);
2. when data is redistributed (if the DBMS manages distributed data).

This has been demonstrated as follows: SQL programs have been written to operate on nondistributed data (using System R) run correctly on distributed versions of that data (using System R*, the IBM San Jose Research Laboratory prototype), and the distributed Ingres project at the University of California at Berkeley has shown the same capability for the Quel language of Ingres.

It is important to distinguish distributed processing from distributed data. In the former case, work (for example, programs) is transmitted to the data; in the latter case, data is transmitted to the work. Many nonrelational DBMS support distributed processing but not distributed data. The only systems that support the concept of making all the distributed data appear to be local are relational DBMS.

In the case of a distributed relational DBMS, a single transaction may straddle several remote sites. Such straddling is managed entirely under the covers - the system may have to execute recovery at multiple sites. Each program or terminal activity treats the totality of data as if it were all local to the site where the application program or terminal activity is being executed.

A fully relational DBMS that does not support distributed data bases has the capability of being extended to provide that support while leaving application programs and terminal activities logically unimpaired, both at the time of initial distribution and whenever later redistribution is made.

There are four important reasons why relational DBMS enjoy this advantage:

1. Decomposition flexibility in deciding how to deploy the data.
2. Recomposition power of the relational operators when combining the results of subtransactions executed at different sites.
3. Economy of transmission resulting from the fact that there need not be a request message sent for each record to be retrieved from any remote site.
4. Analyzability of intent (owing to the very high level of relational languages) for vastly improved optimization of execution.