SQL in a Nutshell

Principles of Relational Databases

Following are E.F. Codd’s Twelve Principles of Relational Databases. These principles continue to be the litmus test used to validate the “relational” characteristics of a database product; a database product that does not meet all of these rules is not fully relational. These rules do not apply to applications development, but they do determine whether the database engine itself can be considered truly “relational.” Currently, most RDBMSs pass Codd’s test, including all of the databases discussed in this book, except MySQL. (MySQL does not currently support views or atomic transactions. Therefore, it does not qualify as a true relational DBMS under Codd’s rules.)

Codd’s Rules for a Truly Relational Database System

Codd’s criteria provide the benchmarks for defining RDBs. Knowing and understanding these principles will help you develop and design RDBs:

1. Information is represented logically in tables.

2. Data must be logically accessible by table, primary key, and column.

3. Null values must be uniformly treated as “missing information,” not as empty strings, blanks, or zeros.

4. Metadata (data about the database) must be stored in the database just as regular data is.

5. A single language must be able to define data, views, integrity constraints, authorization, transactions, and data manipulation.

6. Views must show the updates of their base tables and vice versa.

7. A single operation must be able to retrieve, insert, update, or delete data.

8. Batch and end-user operations are logically separate from physical storage and access methods.

9. Batch and end-user operations can change the database schema without having to recreate it or the applications built upon it.

10. Integrity constraints must be available and stored in the RDB metadata, not in an application program.

11. The data manipulation language of the relational system should not care where or how the physical data is distributed and should not require alteration if the physical data is centralized or distributed.

12. Any row processing done in the system must obey the same integrity rules and constraints that set-processing operations do.

Knowing and understanding these principles assists programmers and developers in the proper development and design of Relational Databases (RDBs).