Codd's Twelve Rules - SQL Database:
In his 1985 Computerworld article, Ted Codd presented 12 rules that a database must
obey if it is to be considered truly relational. Codd's 12 rules, shown in the following list,
have since become a semi-official definition of a relational database. The rules come out
of Codd's theoretical work on the relational model and actually represent more of an ideal
goal than a definition of a relational database.
1. The information rule. All information in a relational database is represented explicitly
at the logical level and in exactly one way—by values in tables.
2.Guaranteed access rule. Each and every datum (atomic value) in a relational
database is guaranteed to be logically accessible by resorting to a combination of
table name, primary key value, and column name.
3. Systematic treatment of null values. Null values (distinct from an empty character
string or a string of blank characters and distinct from zero or any other number) are supported in a fully relational DBMS for representing missing information and
inapplicable information in a systematic way, independent of the data type.
4.
Dynamic online catalog based on the relational model. The database description is
represented at the logical level in the same way as ordinary data, so that authorized
users can apply the same relational language to its interrogation as they apply to the
regular data.
5.
Comprehensive data sublanguage rule. A relational system may support several
languages and various modes of terminal use (for example, the fill-in-the-blanks
mode). However, there must be at least one language whose statements are
expressible, per some well-defined syntax, as character strings, and that is
comprehensive in supporting all of the following items:
• Data definition
• View definition
• Data manipulation (interactive and by program)
• Integrity constraints
• Authorization
• Transaction boundaries (begin, commit, and rollback)
6. View updating rule. All views that are theoretically updateable are also updateable by
the system.
7.
High-level insert, update, and delete. The capability of handling a base relation or a
derived relation as a single operand applies not only to the retrieval of data but also to
the insertion, update, and deletion of data.
8.
Physical data independence. Application programs and terminal activities remain
logically unimpaired whenever any changes are made in either storage
representations or access methods.
9.
Logical data independence. Application programs and terminal activities remain
logically unimpaired when information preserving changes of any kind that
theoretically permit unimpairment are made to the base tables.
10.
Integrity independence. Integrity constraints specific to a particular relational
database must be definable in the relational data sublanguage and storable in the
catalog, not in the application programs.
11. Distribution independence. A relational DBMS has distribution independence.
12.
Nonsubversion rule. If a relational system has a low-level (single record at a time)
language, that low level cannot be used to subvert or bypass the integrity rules and
constraints expressed in the higher-level relational language (multiple records at a
time).
During the early 1990s, it became popular practice to compile "scorecards" for
commercial DBMS products, showing how well they satisfy each of the rules.
Unfortunately, the rules are subjective so the scorecards were usually full of footnotes
and qualifications, and didn't reveal a great deal about the products. Today, the basis of
competition for database vendors tends to revolve around performance, new features,
the availability of development tools, the quality of vendor support, and other issues, rather than conformance to Codd's rules. Nonetheless, they are an important part of the
history of the relational model.
Rule 1 is basically the informal definition of a relational database presented at the
beginning of this section.
Rule 2 stresses the importance of primary keys for locating data in the database. The
table name locates the correct table, the column name finds the correct column, and the
primary key value finds the row containing an individual data item of interest. Rule 3
requires support for missing data through NULL values, which are described in Chapter 5.
Rule 4 requires that a relational database be self-describing. In other words, the
database must contain certain system tables whose columns describe the structure of the
database itself. These tables are described in Chapter 16.
Rule 5 mandates using a relational database language, such as SQL, although SQL is
not specifically required. The language must be able to support all the central functions of
a DBMS—creating a database, retrieving and entering data, implementing database
security, and so on.
Rule 6 deals with views, which are virtual tables used to give various users of a database
different views of its structure. It is one of the most challenging rules to implement in
practice, and no commercial product fully satisfies it today. Views and the problems of
updating them are described in Chapter 14.
Rule 7 stresses the set-oriented nature of a relational database. It requires that rows be
treated as sets in insert, delete, and update operations. The rule is designed to prohibit
implementations that only support row-at-a-time, navigational modification of the
database.
Rule 8 and Rule 9 insulate the user or application program from the low-level
implementation of the database. They specify that specific access or storage techniques
used by the DBMS, and even changes to the structure of the tables in the database,
should not affect the user's ability to work with the data.
Rule 10 says that the database language should support integrity constraints that restrict
the data that can be entered into the database and the database modifications that can
be made. This is another of the rules that is not supported in most commercial DBMS
products.
Rule 11 says that the database language must be able to manipulate distributed data
located on other computer systems. Distributed data and the challenges of managing it
are described in Chapter 20.
Finally, Rule 12 prevents "other paths" into the database that might subvert its relational
structure and integrity.
