Month Year Issue

April 1998 Issue: 1

Journal of Conceptual Modeling
www.inconcept.com/jcm

Composite Objects in Relational and Object Relational Constructs Using InfoModeler 3.1 (Part One)
by Patrick Hallock

Object Role Modeling creates a conceptual model, which can be used with different drivers giving different logical and physical models. Presented here are two simple examples of composite ORM objects. The first is a "Room" where a class is held. The second is an "Address" playing a "ship to" and a "bill to" role with a company.

In each case, the logical model is presented as relational and as object relational. The extra steps in the object-relation mapping are shown. SQL scripts are shown for both the relation and object-relation option. The examples use ORACLE 7.3 as the relational script and ORACLE 8.0 as the object-relation script. The conceptual model is presented once, since it is the same in both cases. IM preferences and "named row type" require new setting for the object-relation model. It is nice to know that the conceptual model facts are not changed.

Example 1:

The building name and the room number identify the composite object "Room". When compiled into the logical model, the object "Room" disappears into the "Class" table as columns. The ORM diagram (Figure One, below):

Figure One

yields in a relation notation (Figure Two, below):

Figure Two

The object "Room" maps into "Class" table since it plays no functional roles except its reference.

You can implement an object-relational model in InfoModeler, by doing the following:

Open Application Preferences under the File Menu Option
Select ORM Misc.
Select When Mapping Obey all Type Settings

This activates the object-relational options. Once this done you need not set this preference for the model again.

Next set the composite information for the object "Room".

Right Click on "Room" and select "Properties", then "Composite Type".
Select "Named Row type".
Notice that you get a default name of "Room_t", (I changed mine to "Room_Type")

Then, rebuild the logical model.

After building the logical model, you get the logical model as shown (below) in Figure Three. Notice how we can clearly see the "Room" object. Sometimes I build this model for review purposes, then I will do a final build in ORACLE 7.3. The non-technical reviewers find this notation a lot easier to understand versus the relational notation.

Figure Three

Here are the two scripts:

ORACLE 7.3 - Relational

create table CLASS ( <<<<<< NOTHING NEW HERE "CLASS ID" VARCHAR2(10) not null, "ROOM NUMBER" VARCHAR2(10) not null, "INSTRUCTOR ID" VARCHAR2(10) null, "ROOM BUILDING NAME" VARCHAR2(10) not null, constraint CLASS_PK primary key ("CLASS ID") );

ORACLE 8.0 – Object Relational

-- Create object type 'ROOM_TYPE'. create type ROOM_TYPE as object ( <<<< SOMETHING NEW "CREATE TYPE" NUMBER VARCHAR2(10), "BUILDING NAME" VARCHAR2(10) ); -- Create new table CLASS. create table CLASS ( "CLASS ID" VARCHAR2(10) not null, ROOM ROOM_TYPE not null, <<<<<< NOTE THE DATA TYPE HERE "INSTRUCTOR ID" VARCHAR2(10) null, constraint CLASS_PK primary key ("CLASS ID") );

Notice the creation of an object type called "ROOM_TYPE". Then in the CLASS table, ROOM is data type "ROOM_TYPE". For most programmers this is not a surprise, but to many database designers this is new. The idea of creating database objects, then using them to define columns is very handy. Not only for understanding, but to keep composite objects stable.

Example 2

This is a more common problem as we are always faced with addresses. Here (figure Four, below) we are saying that an address is including street, city, state and zip code:

Figure Four

This is the standard relation view (Figure Five, below):

Figure Five

However, I find that many people like this view (Figure Six, below) as it is easier to see how everything is coming together. Again the composite type was set to "Named Row Type" and the name was changed to "Address_Type".

Figure Six

ORACLE 7.3 ADDRESS SCRIPT

create table COMPANY ( <<<<<< SAME OLD BORING STUFF "COMPANY ID" VARCHAR2(10) not null, "SHIP ADDRESS STREET" VARCHAR2(10) null, "SHIP ADDRESS STATE" VARCHAR2(10) null, "SHIP ADDRESS CITY" VARCHAR2(10) null, "SHIP ADDRESS ZIPCODE" VARCHAR2(10) null, "BILL ADDRESS STREET" VARCHAR2(10) not null, "BILL ADDRESS STATE" VARCHAR2(10) not null, "BILL ADDRESS CITY" VARCHAR2(10) not null, "BILL ADDRESS ZIPCODE" VARCHAR2(10) not null, constraint COMPANY_PK primary key ("COMPANY ID") );

ORACLE 8.0 ADDRESS SCRIPT

create type ADDRESS_TYPE as object ( <<<<<<<< AGAIN SOMETHING NEW STREET VARCHAR2(10), STATE VARCHAR2(10), CITY VARCHAR2(10), ZIPCODE VARCHAR2(10) ); create table COMPANY ( "COMPANY ID" VARCHAR2(10) not null, "SHIP ADDRESS" ADDRESS_TYPE null, <<<<< USING THE OBJECT TYPE "BILL ADDRESS" ADDRESS_TYPE not null, <<<<< USING THE OBJECT TYPE constraint COMPANY_PK primary key ("COMPANY ID") );

Next Month: Nested Objects in the Object-Relational Model while looking at a more complex address model.

Patrick Hallock is a Senior Partner and Principal Consultant for InConcept. He has over 20 years of ORM/NIAM experience and is a certified ORM consultant, trainer/train the trainer and a certified Visio trainer.

Contact Information:

Patrick Hallock
President and Co-Founder
InConcept
8171 Hidden Bay Trail N
Lake Elmo, MN 55042
path@inconcept.com
(651) 777-8484
fax: (651) 777-9634
http://www.inconcept.com