Database Tutorial

Course Tutorial Site

Site Admin

Introduction to Databases

with 12 comments

The world of databases is very competitive and evolutionary. Current trends point to XML databases like Mark Logic leading us into a new way of discovery in data warehouses, which is the world of Online Analytical Processing (OLAP). Oracle continues to dominate in Online Transactional Processing (OLTP) databases, partly because they have a tremendous process improvement cycle matched to intelligent product acquisitions.

Regardless of implementation, OLTP and OLAP are the two types of databases that people use. Sometimes an implementation isn’t large enough to separate a company’s OLTP from the OLAP, and in those cases they implement a dual purpose database. This is generally an unfortunate decision because the response time and resource requirements of these two types of databases are quite the opposite.

Databases were once no more than a collection of files. Each file contained something unique, like a single subject (but not always). The expense of managing the disparate file structures led to the development of a Hierarchical Linear Model of storing information, often called a hierarchical database (very much like an XSchema in XML). The collection of programs to run a Hierarchical database became known as a Database Management System (DBMS). The problem with hierarchical databases is that the organization of information never quite represented the different ways that were required by consumers of the data. These consumers are known as knowledge workers thanks to the seminal work of Alvin Toffler – PowerShift: Knowledge, Wealth, and Violence at the Edge of the 21st Century. It covers his Third Wave of his view of the future. You may be more familiar with its repackaged and economic focus in The World is Flat by Thomas Friedman.

The Networked Model replaced the hierarchical model by introducing pointers. Pointers allow an optimized join between a node in one part of the hierarchy with another. These pointers redirected the search tree when matching data wasn’t found in sibling (side-by-side) nodes of a hierarchy. The Relational Model abstracted these pointers into indexes (a list of available nodes) and put them in the data catalog as a list of tables in a Relational Database Management System. This list of tables allows SQL to create relationship through matching values (equijoin) from one column with values in another column. It also can compare a value of one column against a pair of range values (non-equijoin) by using the BETWEEN operator. A non-equijoin also applies to comparisons with inequality operators.

The object model was proposed as a replacement to the relational model because it would group data into related and meaningful human objects. The problem with the object model was the database had to store the data between actions in a serialized (or collapsed) format. These databases were and are highly memory intensive and therefore don’t scale as well as the relational model. Oracle had committed to the object model but back peddled when they found it impractical. They developed an extended relational model, which quickly was renamed to the object relational model. This model only marshals (places into memory) those objects required at run time, and is the cornerstone of the Object Relational Database Management System (ORDBMS).

The basic architecture of these products is simple, while the implementation details can take months and years to master as a Database Administrator (DBA). They work on the concept of a server engine and client interface. The engine is known as a Database Management System (DBMS), and it generally has subsystems that run Structured Query Language (SQL) and a database catalog.

SQL is actually a shortened form of IBM’s SEQUEL (Structured English Query Language), and is generally accepted as meaning Structured Query Language. Alan Beaulieu makes the point that SQL really doesn’t “stand for anything” (Learning SQL), and while he may be technically right more people now understand it as such. Therefore, I’d accept its meaning as is because it simply makes sense to do so.

The interface is a typical client-side interactive and batch programming interface, and it lets you access the engine. Both the client-side interface and DBMS engine are deployed on each server. More or less, the client-side component (a) enables the DBA to administer the database and (b) supports server-side development.

The database catalog contains the metadata, or a master index to all the structures and algorithms that make a DBMS work. Database catalogs are implementation specific and therefore quite product specific. In the folding sections, you’ll cover the database engine and SQL, then the shell interface that provides you interactive and batch access to the database.

This is just a summary that leverages Web 2 by using links. The benefit of the summary increases as you explore the material on your own.

Written by michaelmclaughlin

September 19th, 2009 at 11:50 pm

Posted in

12 Responses to 'Introduction to Databases'

Subscribe to comments with RSS or TrackBack to 'Introduction to Databases'.

  1. On your list of items in the DDL, at the top you do not have TRUCATE included, but then when you expand it it does include TRUNCATE.

    John Draper

    5 Apr 10 at 12:20 pm

  2. I’ve put it in the prefacing paragraph.

    michaelmclaughlin

    5 Apr 10 at 7:53 pm

  3. The link for pointers, appears to not go anywhere.

    spencer

    10 Jan 11 at 4:55 pm

  4. It does go where it should go now.

    michaelmclaughlin

    16 Mar 11 at 2:26 pm

  5. Under “The Database Engine” it says DMBS, but do you mean DBMS or am I mistaken?

    Craig W. Christensen

    7 Jan 12 at 9:41 am

  6. Juxtaposed seems an odd word in context here. It means to place (on purpose) to show comparison and difference. It seems you mean that they are dissimilar or have a hard time with synergism.

    Joe Mller

    9 Jan 12 at 12:31 pm

  7. in the first paragraph of the database engine roll-down, i think the acronym wants to be ‘DBMS’ not ‘DMBS’ subsystem.

    james

    23 Apr 12 at 10:43 pm

  8. You’ve got a typo. Right at the top of the “Database Engine” section:

    “A description of the working components of a DBMS.
    The DMBS subsystems…”

    I believe you meant “DBMS” both times, yes?

    Brandon MacGregor

    11 Jun 12 at 10:20 am

  9. The link to Oracle SQL*Plus User’s Guide and Reference is broken

    Willis Barton

    1 May 13 at 2:29 pm

  10. Willis, I’ve fixed the broken link.

    michaelmclaughlin

    18 Mar 14 at 11:15 am

  11. Brandon, yes. It’s fixed.

    michaelmclaughlin

    22 Oct 14 at 1:17 am

  12. Joe, I concur and changed juxtaposed to quite the opposite.

    michaelmclaughlin

    22 Oct 14 at 1:38 am

Leave a Reply