Home > NoSQL, Oracle, SQL > What’s so sacred about relational anyway?

What’s so sacred about relational anyway?


Derivability, Redundancy, and Consistency of Relations

The true importance of relational theory is highlighted by the title of the original (and considerably shorter) version of Codd’s first paper. That version predated the published version by a year, and the title was Derivability, Redundancy, and Consistency of Relations Stored in Large Data Banks.” The title of this unpublished version emphasizes that the real importance of relational theory is that it provides a rigorous method of asserting arbitrarily complex consistency constraints that must be satisfied by the data within the database. For example, the following assertion written in Structured Query Language (SQL) states that the company must have at least 50 employees:

CREATE ASSERTION employees_a1 AS CHECK (
  (SELECT COUNT(*) FROM employees) >= 50
)

In practice, the above consistency check would have to be implemented using a database trigger, since relational vendors do not support the CREATE ASSERTION feature.

As another example, the following “referential integrity constraint” links the Employees table to the Departments table:

CREATE ASSERTION employees_departments_fk AS CHECK (
  NOT EXISTS (
    SELECT * FROM employees e WHERE NOT EXISTS (
      SELECT * FROM departments d WHERE d.department_id = e.department_id
    )
  )
)

In practice, referential integrity constraints are implemented using simplified syntax that obfuscates the theoretical underpinnings. The database administrator or application developer need simply say:

ALTER TABLE employees ADD CONSTRAINT
  employee_departments_fk FOREIGN KEY department_id REFERENCES departments

The relational model is sacred because it gives application software developers the ability to assert and enforce consistency of data in databases.

Strange as it may sound, Oracle Database did not enforce referential integrity constraints until Version 7 was released in the 1990s (by which time Oracle Corporation was already the world’s largest database company). From the January 1989 issue of Software Magazine: “About six or seven years ago when I worked for a vendor that made a Codasyl DBMS called Seed, I spoke at a conference. Also speaking was Larry Rowe, one of the founders of Relational Technology, Inc. and one of the developers of the relational DBMS Ingres. We were about to be clobbered by these new relational systems. He suggested to me that the best way to compete against the relational systems was to point out that they did not support referential integrity. Well, back then, virtually no one understood the problem enough to make it an issue. Today, Codasyl DBMSs are an endangered species, and referential integrity is a hot topic used by the relational DBMSs to compete among themselves.”

So there you have it. The relational model is sacred because it gives application software developers the ability to assert and enforce consistency of data in databases.

Now go tell everyone! But before you leave, here’s a little tidbit that may surprise you—something to tell your NoSQL friends.

Pages: 1 2 3 4

Categories: NoSQL, Oracle, SQL
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: