Using Pointer References with Object-Relational Databases Oracle Tips by Burleson Consulting

The ability to define data types that contain pointers to rows in other database tables will profoundly change the way that databases are created and maintained.  These extensions to the relational model will allow a cell in a table to reference a list of values, or another entire table.  This ability will allow the designer to define and implement "aggregate objects" which contain pointer references to the components, rather than having to define a relational view on the data.  This will also allow the database designer to more effectively model the real-world, reduce overhead, and also provide a way to attach "methods" or behaviors to aggregate objects.

Repeating groups and abstract data types

Let's take a look at how repeating values appear in the Oracle object/relational model. The Oracle databases language (called PL/SQL), uses the varying-array language construct (VARRAY) to indicate repeating groups, so we can use the VARRAY mechanism to declare repeating groups within table columns.  Let’s examine an example.  In the following SQL we will add a repeating group called job history to our customer table.  First, we create a TYPE called job_history with a maximum of three values:

 CREATE TYPE customer_address (
      street_address    char(20),
      city_address       char(20),
      state_name         char(2),
      zip_code             char(5));

 CREATE TYPE job_details (
     job_dates                  char(80),
     job_employer_name  char(80),
     job_title                     char(80)
     job_address                customer_address);

 CREATE TYPE job_history (
      VARRAY(3) OF REF job_details);

Now that we have defined the data types, here is how we can create the object/relational table using the data types:

 CREATE TABLE CUSTOMER (
      customer_name     full_name,
      cust_address          customer_address,
      prior_jobs               job_history);

CREATE TABLE JOB_HISTORY (
      job_stuff                job_details);

Here we see that we have created a repeating list within our table definition.  (Figure 6.1)  In addition to de-referencing the data type, we would also need to subscript the prior_jobs to tell Oracle which one of the repeating items we want to view:

 SELECT customer.prior_jobs.job_title(3)  
 FROM CUSTOMER
 WHERE
 customer.customer_name.last_name LIKE 'JONES%';

Here we select the first previous employer’s street address:

 SELECT customer.prior_jobs.job_address.street_address(1)  
 FROM CUSTOMER
 WHERE
 customer.customer_name.last_name LIKE 'JONES%';

Figure 6.1 - A repeating list within a table column.

Note that it is possible in an object/relational database to make the repeating groups contain either data or pointers to rows within other tables.  But what happens when we “nest” data types that have repeating groups?  In pre-relational databases, it was easy to create a record that contained a finite repeating group.  For example, in COBOL a record definition could be defined to contain three repeating groups of job history information:

 03 EMPLOYEE.
    05 EMPLOYEE-NAME                      PIC X(80).
    . . .
    05 JOB-HISTORY OCCURS 3 TIMES.
       07 JOB-DATE                        PIC X(80).
       07 JOB-EMPLOYER-NAME               PIC X(80).
       07 JOB-TITLE                       PIC X(80).
       07 EMPLOYER-ADDRESS
           09 STREET-ADDRESS              PIC X(80).
           09 CITY-ADDRESS                PIC X(80).
           09 ZIP-CODE                    PIC X(80);

So, in COBOL, the JOB-HISTORY component can be referenced by a subscript and any component, for example JOB_HISTORY can be referenced by a subscript:

MOVE JOB-HISTORY(2) TO OUT-REC.
 MOVE 'DATABASE ADMINISTRATOR' TO JOB-TITLE(3).

Now, let's take a look at how these repeating values may appear in an object/relational database. As we saw before, we use the VARRAY construct to indicate repeating groups, so it is a safe assumption that a VARRAY mechanism can be used to declare our job history item:

 CREATE TYPE customer_address (
      street_address    char(20),
      city_address       char(20),
      zip_code            char(5)); 

CREATE TYPE job_details (
     job_dates                  char(80),
     job_employer_name  char(80),
     job_title                     char(80)
     job_address                customer_address);

 CREATE TYPE job_history (
      VARRAY(3) OF job_details);

Now, look how we can create the CUSTOMER table using the data types that we have just defined:

 CREATE TABLE CUSTOMER (
      customer_name     full_name,
      cust_address          customer_address,
      prior_jobs               job_history);

We have created a table with three occurrences of job history details.  As we have already seen we need to subscript the prior_jobs to tell the database which one of the three items that we want:

 SELECT customer.prior_jobs.job_title(3)
 FROM CUSTOMER
 WHERE
 customer.customer_name.last_name LIKE 'JONES%';

Database designers will note that this data structure is a direct violation of first normal form!  As you may recall from your College days, one fundamental principle (or restriction, however you want to look at it) was that a relational table could not contain repeating values.  This was primarily because SQL had no mechanism to allow for repeating groups to be defined and subscripted.  Now that we understand repeating groups within object/relational columns, let’s introduce pointers into the model.

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.