Data Representation Model for Object/Relational Systems Oracle Database Tips by Donald Burleson

One of the confounding problems with the object-oriented extensions to the relational database is the issue of representing the data structures in a graphic form.  While early efforts by modeling diagrams have achieved limited success, this approach aspires to display the components of an object/relational database in a simple, easy to understand format.  This diagram technique was developed by Don Burleson, a leading object/relational database expert (and co-author of this book) out of necessity to create a consistent method for documenting object-oriented data models.  Let's take a look at some of the components of the model as shown in figure 3.8.

Figure 3.8   A description of the icons in the object/relational diagram model

1.  Entity Icon description - This portion of the diagram is the rectangle symbol.  Inside the rectangle we provide for the description of all base entities in the data model.  Information includes the entity name, the length of the data in the entity, the primary key for the object and  whether duplicate keys are allowed, and the place where the object is stored (usually a tablespace for object/relational databases).

The domain values for each entity symbol follow:

Entity_name = This is the internal name of the entity as it appears inside the database.

Type = An entity may be either a table, an OID table, a store table, a view, or an aggregate object.

            Table - This is a generic table as defined within any relational DBMS.

View - This is a traditional relational view.  A view in most relational databases is stored internally as an SQL statement that joins base tables together to form aggregate views of table data.  As such a View and an Aggregate table are very similar in purpose.

OID table - This is an object/relational table which has been defined to contain a special column to contain the object ID for each row in the table.

Store Table - A store table is an internal representation of a table whereby an owner table may be coupled with another internal table.  For more information on this concept, see Chapter 6, Relational database objects and reference pointers.

Aggregate Table - An aggregate table is a table that represents assemblies of base level objects.  These tables are usually made up exclusively of pointers to other OID tables within the model.

Length - This is the physical stored length of each row of the entity within the table 

Primary_key - This is the column or OID that may be used to uniquely identify each row within the entity.

Duplicate_Option - This describes whether duplicate rows are allowed within the entity.  Valid values are DN for duplicates not allowed and DA for duplicates allowed.

Tablespace_Name - This parameter describes the tablespace where the entity resides inside the database.

2.  Index descriptions - An index is represented as a line emanating from the entity rectangle, with a triangle at the end of the line.  This construct describes all of the information about an index.  These items include the name of the index, the type of the index (b-tree or bitmapped), keys for the index, and the place where the  physical index is stored.

Index_name - This is the internal index name as defined within the database management system.

Type - This can have the value of TREE for a B-Tree index or BITMAP for a bitmap structure index.

Duplicate_Option - This describes whether duplicate rows are allowed within the index.  Valid values are DN for duplicates not allowed and DA for duplicates allowed.

Key_name - This contains the names of each column within the entity that participates within the index as a key column.

Tablespace_Name - This parameter describes the tablespace where the entity resides inside the database.

3.  Class Descriptions - This component of the model uses polygons to represent that an entity has an attached class hierarchy.  While each class name is represented on the diagram, another diagram is used to describe the details about each class hierarchy.

4.  Set information - This includes all information regarding one-to-many relationships between database entities.  As we know there are several ways to represent data relationships between entities in the object relation model:

a) Foreign key relationships - These relationships are established by embedding the primary key of one table into another table.  The primary key that is embedded in the other table becomes a foreign key in the other table.  The relationship is established at runtime by using the SQL JOIN statement to relate the tables together.

For example, the following set description shows a  foreign key set:

          CUST-ORDER
          FK  DN
          cust_ID
          restrict

There are several options available when using foreign key relationships.  The relationship can be established in an ad-hoc fashion simply by having two matching columns within two tables, or the foreign key relationship can be defined in a more formal ways using reverential integrity (RI). 

When using RI, the database will manage the data relationship and insure that the constraint is complete.  At object insertion time, the RI will check to see the valid owner key exists before allowing new entities into the model.  At object deletion time, the RI will allow two options RESTRICT and DELETE.  RESTRICT will not allow the owner object to be deleted if it has any member objects. CASCADE will delete all member objects when the owner object is deleted.

b) Pointers to rows in other tables - This construct allows for a columns within a table to contain an array of pointers to rows in the member table.  Here is an example:

          salesperson_order
          row pointer to order
          orderList

Here we see that the relationship is called salesperson_order and that it is of the row pointer type, containing a list of pointers to order rows.  The column name in the salesperson table is called orderList.

c) Pointers to other tables - These are sometimes referred to as pointers to structures.  In the object-relational model, these pointers can point to many rows in a subordinate table.  Here is an example of this notation:

          salespersonHistory
          pointer to table history_table
          salesHistory

Figure 3.9 shows a portion of a complete object/relational diagram.  Here we see that there are two entities, one for customer and another for order.  The customer is a "table" type, while the order entity has been defined as the "OID table" type, meaning that each row contains a unique object identifier.

Figure 3.9  A sample object/relational diagram

Note that there are two indexes on the customer entity.  The region_ix index is a bitmapped structure index, which makes sense since a region has only four distinct values.  The customer_ix index is a tree index that contains two keys, the cust_ID column, and the cust_last_name column.

Here we also see that the order entity has an attached class hierarchy.  As we will learn in detail in later chapters, a class hierarchy contains a "base" class, (which is the order entity in this case).  While the order entity will contain all of the base data structures and methods for orders, there are two sub-types of orders, prepaid_order and COD_order, each with their own unique data structures and methods.  As we remember from chapter one, instantiations of COD_orders and prepaid_orders will inherit the data structures and methods from the order base class.

Summary

Now that we have briefly reviewed the conceptual differences behind the object-oriented extensions to analysis and design, we are ready to delve into the details of the exciting new extensions to the conceptual model for database objects.  The following chapter will cover pointers, the uses of inheritance, and the use of methods within the database object model.

Peter Chen, "The Entity Relationship Model, Toward a Unified View of Data", ACM Transactions on Database Systems 1 (March 1976), 9-36.

 

---