Codd’s Twelve Rules

Rules that a DBMS should follow to be classified as fully relational

Oracle’s grading scale

0 = doesn’t meet criteria

1/2 = meets some of the criteria

1 = meets the criteria

1. Information Rule

information is to be represented as data stored in cells

Vehicle Identification Number in one cell would violate this rule paint color body style

2. Guaranteed access rule

Each data item must be accessible by combination of table name + primary key of row

+column name violation of rule--ability to access by

using arrays or pointers

How does Oracle fair on the 1st two points??BLOBs-Binary Large OBjects

early RDBMS technique to store and manage multimedia data types

typically stored outside the database (in files), and pointers are placed in their corresponding column positions

BLOBs push the 1st two rules Implementation is transparent to the user Do not explicitly break 1st two rules Score 1 1/2

1/2 pt deducted for “bad conduct”competitors grade out the same

3. Nulls must be used in a consistent manner

Violation--0 used as value for missing numbers and blank for missing character values

correct way--nulls simply be missing data and have no values

If values are desired for missing data, vendors usually offer the ability to use defaults for this purpose.

Grade for rule 3

1/2current implementation for nulls in

SQL is poor for all vendors has been attacked by Codd and another

relational pioneer, C. J. DateCodd proposes 3 valued logicDate proposes abolishing of nulls altogether

some very real data problems with the use of NULLs today in RDBMSs.

4. An active, online data dictionary should be stored as relational tables and accessible through the regular data access language.

If any part of the data dictionary is stored in the operating system, this rule would be violated.

Grade for rule 4

1/2Oracle allows SELECT only, but

INSERT, UPDATE, and DELETE are not possible This probably should not occur

frequently and may not even be desired, but it should be possible according to rule 4.

5. The data access language must provide all means of access and be the only means of access, except possibly for low-level access routines.

If you could access the file supporting table, through a utility other than an SQL interface, this might violate this rule.

6. All views that may be updatable should be updatable.

If, for example, you could join three tables as the basis for a view, but not be able to update that view, then this rule would be violated.

Rule 6 Grade

1/2Improved for version 8 over 7

Version 8 is not 100% compliant

7. There must be set-level inserts, updates, and deletes.

Currently, this is provided by most RDBMS vendors to some degree.

8. Physical data independence.

An application cannot depend on physical restructuring. If a file supporting table was moved from one disk to another, or renamed, then this should have no impact on the application.

9. Logical data independence.

How a User views data should not change when the logical structure (tables structure) of the database changes.

This rule is particularly difficult to satisfy.

Most databases rely on strong ties between the user view of the data and the actual structure of the underlying tables.

10. Integrity independence.

Integrity rules should be stored in the data dictionary. Primary key constraints, foreign key constraints, check constraints, triggers, and so forth should all be stored in the data dictionary.

11. Distribution dependence.

A database should continue to work properly even if distributed (extension of rule 8 -- except rather than only being distributed on a single system (locally), a database may also be distributed across a network of systems (remotely).

Rule 11 Grade

1/2 Oracle does offer

database links, snapshots, symmetric replication, and distributed database with a two-phase commit (2PC).

Some of the networking is user-apparent, 2PC works but not well, and replication has its own problems

Majority of complaints center on the fact that all these capabilities require the user to know more than one should have to know about the setup behind the technology

12. The nonsubversion rule.

If low-level access is allowed, it must not bypass security nor integrity rules, which would otherwise be obeyed by the regular data access language. A backup or load utility, for example, should not be able to bypass authentication, constraints, and locks. However, vendors often provide these abilities for the sake of speed. It is then the DBA’s responsibility to ensure that security and integrity, if momentarily compromised, are reinstated. An example is disabling and re-enabling constraints for a VLDB load.

Through some of its utilities, such as SQL*Loader and Import, Oracle allows data to be stored in the database by bypassing the standard SQL route, opening up the database to potential corruption

Final Grade8 1/2 out of 12

give or take a pointPoint is not the precision of final scoreFACT--Oracle does not meet all of Codd’s 12

rulesNeither does Sybase nor InformixIt is a RDBMS

Oracle and its competitors have more work to do Oracle 7 only fully passed rule 10

Codd’s Rule Zero

For a system to qualify as a RDBMS, that system must use its relational facilities exclusively to manage the database.

Meet all fundamentals discussed in relational database chapter and these 12 rules --- database may be designated as relational