Aggregate Objects and Pointers Oracle Tips by Burleson Consulting

Aggregate diagrams are used to describe objects that are composed of smaller objects within the database.  For example, we may have an order form that is composed entirely of pointers to other entities.  Here is the description of the order_form.

order_form
agg-table
customer_data
order_data
item_list(10)
total

Note the implied relationships to other objects.  Because the order_form does not contain any of it's own data, we see that it is similar to a relational "view".  However, there is one important exception.  While a view serves to consolidate data from many base tables, it is always created at runtime.  Unlike views, aggregate objects are pre-built, and have their own independent existence.  In the above example, we have pre-built all of the order forms, and we have pre-sorted all of the order information, placing each item in the proper order.  Since all of the items are pre-sorted, we do not have to do any run-time sorting of rows to get the requested information in the proper order.

Since we are talking about pointers to objects, we need to come up with a convention for noting pointers to rows.  In an object database, pointers are not generic.  Object databases use a principle called strong typing which means that a pointer that has been defined as a pointer to an order object will not be able to accept a pointer to any other object.

In the C language, pointers are described with an asterisk.  For example, pointers to orders would be described as *order and pointers to a customer object would be described as *customer.

We will use this notation to describe aggregate objects.  With an objects data structure, we may see a single pointer to another object or a series of pointers to other objects.  As such, we will denote a series of pointers within parenthesis, showing the lower and upper bounds of the pointers.  For example, consider the following pointer list definitions:

(0-3) *Job_history
(1-4) *items

Here we see that the first item is an array of pointers that may contain from zero to three pointers to job_history objects. The second line shows a pointer array with from one to four pointers to item objects.

Now, let's take a look at what an order_form object might look like:

order_form

(1)     *customer
(1)     *order
(1-10)  *item
(1-10)  *quantity_ordered
order_total_amount

Here we see that an order_form consists of one pointer to a customer object, one pointer to an order object, from one through 10 pointers to item objects, and from one through ten pointers to quantity_ordered objects.  When a pre-assembled order_form is accessed, the database will de-reference these pointers and display the data within the target object (or row, within an object/relational database).

Since many aggregate objects consist almost entirely of pointers, it is very tempting to include an order_form entity on the E/R model with a box for the order_form entity, and sets into every base object in the model.

Note that we have deliberately introduced order_total_amount as the only data item in the order form object.  While this number could be re-computed each time that a data item is displayed, we can choose to introduce the order total as a form of planned redundancy, knowing that this value would need to be re-computed whenever any of the order information has changed.

As we can see there can be so many aggregate entities that it is often too complex to include each and every aggregate object in the E/R model.  Hence, to avoid a "plate of spaghetti" with hundreds of arrows, we include all of the aggregate objects on another diagram. (Figure 6.9)

Figure 6.9 - An aggregate class diagram.

Note that it is possible to have aggregate objects that are composed of other aggregate objects.  For example, consider the relationship between a class roster and an instructor roster.  A class_roster is an aggregate object consisting of pointers to one course object and many student objects.  An instructor object is an object than contains a pointer to an instructor object, and many pointers to class_roster objects.

Class_roster (class_ID)
*student (student_name, student)

Instructor_roster (instructor_ID)

(1-1) *instructor (instructor_name, instructor_address)
(1-n) *class_roster (class_name, student_name)

Here we see that a class roster has the class_ID for a primary key.  Included in the class_roster object is a pointer to the pertinent instructor row (denoted by *instructor).  The instructor roster has the class_roster embedded within the definition, and we see that the instructor_roster consists of One pointer to an instructor, and a pointer to the class_roster objects.  Note that this is the equivalent of a C++ array of pointers (as denoted by **student).

Summary

Now that we have a basic understanding of how pointers are declared and implemented within the object/relational model, let’s move on to take a look at some of the other constructs of the object/relational databases including class hierarchies and inheritance, and the use of methods. 

 

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.