Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Jul 13, 2005

Where Did Oracle Come From?

By Steve Callan

As an Oracle DBA, you know that Oracle is a relational database management system and that it performs many complex functions well. In the pressure to learn how to be a DBA or what it is you should be doing, it is hard to take the time to sit back and appreciate the origins of the RDBMS model in general and where and how Oracle came into being. It is important to have an appreciation of your profession's history and origins. Often times, we take for granted the functions an RDBMS performs. But where, exactly, did the concept of the relational model come from?

"A Relational Model of Data for Large Shared Data Banks"

A paper written by E.F. Codd (yes, that Codd) 35 years ago set the stage for the development of the relational model. Codd's "A Relational Model of Data for Large Shared Data Banks" has been hailed as a landmark paper. Even his own employer, IBM - an industry leader in several areas at the time, including a leading database system - largely ignored the brilliance of what Dr. Codd proposed in 1970. How relational database management systems operate today - including Oracle - can be traced back to what Dr. Codd proposed back then.

The paper consists of two major sections: a narrative describing the shortcomings of the then current models and systems, and a proposal for how a relational model should work. A key distinction between the model types is that of data independence. Current models then were so inherently tied to programs and physical file structures that minor changes could invalidate the entire system. In the relational model, the separation between data and how an application or computer system works is paramount - the further apart the better.

Limitations of the Old Systems

Network and hierarchal-based systems were hobbled by data dependency. This problem arose in three forms: ordering dependence, indexing dependence and access path dependence. Ordering dependency was common to all of the major systems then in use. Specifically, they all failed "to make a clear distinction between order of presentation on the one hand and stored ordering on the other." In practical terms, once entered, there was no way to change the order.

Indexing dependence involves the relationship between applications and indices (or indexes as the word is now used). If an index were removed, would the application still function? Thirty plus years ago, the answer was mostly "no." Even then, the benefits and limitations of indexes were well known. The major problem for some systems concerned the situation where an index was renamed or removed. If an application was hard coded to use a specific index name, and the index was no longer present, would the application still function?

Codd illustrates the third limitation - access path dependence - with a series of five different parts versus project structures, and goes on to point out how any program will fail on at least three of the arrangements. In concrete terms, if a program were tied to a specific structure or location of data, then changes in the structure or location would most likely cause a failure of the program. This dependency illustrates the need for a program to be "blind" to structure and location.

Enter the Relational View of Data

Consider what you take for granted today - "of course the database is supposed to act this way" - and put yourself in Codd's shoes back in 1970. What seems so obvious and practical today required a paradigm shift back then. IBM had invested tremendous amounts of time and capital on its own non-relational problem-ridden system, and here comes a proposal from an employee at an IBM location (San Jose) that was not even involved with programming. Quite a few years went by before the genius of what Codd proposed was successfully implemented.

What hindered and delayed the acceptance of the relational model was the lack of a structured query language that could take advantage of such a model, and obviously, the lack of a working relational model. Similar situations exist today. For example, scientists can tell you what the limitations are with the current rocket-based space program. At the same time, they can tell you what the star ship Enterprise should be capable of, but how to build it and how to make it work are not known at this time. Like Codd back in 1970, we have the theory, but we do not have the technology.

So what does the relational model do for us, and how is it better than what was being used? The first major difference discussed in the paper has to do with moving from using relations to using relationships. Relations depend on a domain ordering (the same concept seen in algebra classes - domain versus range, function versus relation). Relationships are indifferent to the order of data, so it is sufficient to be able to uniquely identify domains (table columns) without regard to order.

The implementation of relationships requires the introduction of primary and foreign keys as a "preferred way." The preferred way Codd introduced is what is also known as normal form (NF). The concept of what has become known as 1NF, 2NF and so on is well known today and Codd alludes to further normalization of the example he uses to illustrate the employee example.

What is another name for SQL? How about "applied predicate calculus?" As Codd notes, "The adoption of a relational model of data ... permits the development of a universal data sub-language based on an applied predicate calculus." The paper does not go into the language in detail, but Codd does discuss its salient details (the ship should go at least warp factor three...).

SQL statements work with sets of data, either all of the possible values, or a subset. We may fetch a set for query purposes, or hold it for possible changes. Once the set is obtained, we should be able to exploit its relations (manipulate the data). Codd recognized back then that a high-level language would be needed to "provide efficient response and throughput shifts from the individual user to the data system."

When writing a SQL statement, the user is performing operations on relations. Three major relational operators familiar to most users are restriction, projection, and joins. Restrictions are employed by using WHERE clauses, which can further be restricted by ANDing or ORing additional conditions. Projections are exemplified during partial selects out of a table (selecting specific domains, or columns as opposed to select *). Joins are frequently used to compare sets of data (one table against another, and even the same table against itself).

In addition to the topics of strong and weak redundancy (maintaining multiple copies of a column across tables for user convenience), the final major characteristic of the relational model covered in the paper deals with consistency. Although not directly mentioned, consistency relates to referential integrity. Whether or not user omission or commission introduces (or at least attempts to introduce) an inconsistency, the system should be able to perform a check prior to insertion, deletion, or update. This is one of the approaches discussed by Codd and is obviously the one modern relational databases use today.

As you read this historic paper, you can practically see the blueprint for Oracle and other modern relational database systems. The ideal system adheres to 12 rules later, well, codified by Dr. Codd in 1985. Looking back on history, it is not surprising that someone - Lawrence Ellison in this case - would fully realize the importance and relevance of Codd's relational model and seize upon the opportunity to build a better system. By then, SQL was in place and all (not to trivialize the amount of work "all" implies) that was needed was an engine to drive the system.



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM