Nordic (11)


This series is a commentary of sorts on a PowerPoint™ presentation called Nordic – Object Relational Database Design. We are now at slide #59 of the presentation.

Associations Retrieval Facade

We are now at slide #59 of the presentation.

ObjectAssociations (Class,ObjCode)

I presume this function returns all associations that involve the object identified by Class and ObjCode. This is not easily done in Oracle because associations are not readily identifiable as such. In the case of the COURTSHIPS association, we implemented it as a type. The references to other types are hidden away in the code for the trigger. (See Nordic (7) for the details).

ObjectFreeAssociations (Class, ObjCode)

Without the associated commentary on this slide, I am unable to comment further about ObjectFreeAssociations (Class, ObjCode). There are examples of this procedure in use on slides #65 to #69 (inclusive).

ClassAssociations (Class1, Class2)

The preceding comments about ObjectAssociations (Class,ObjCode) apply to ClassAssociations (Class1, Class2).

ObjectNotAssociated (Class, AMM)

The preceding comments about ObjectAssociations (Class,ObjCode) apply to ObjectNotAssociated (Class, AMM).

Association Mapping – 1 Level

We are now at slide #60 of the presentation.

Conform the Data to the Slide

We will use the following TRUNCATE and DELETE commands to remove existing data. (This script comes from Nordic (7).)

TRUNCATE TABLE courtships;
DELETE FROM animals;
COMMIT;
TRUNCATE TABLE buildings;
TRUNCATE TABLE surreys;

Now to use the INSERT command to add the correct data to the OBJECT_VALUE psuedo-column of the persistent storage for each type or type hierarchy. All animals are assumed to be healthy, and all surreys are assumed to be working.

INSERT INTO animals( OBJECT_VALUE ) VALUES( NEW guy( 'Jim Bob', NEW human_state( 'Healthy' ) ) );
INSERT INTO animals( OBJECT_VALUE ) VALUES( NEW girl( 'Mary Smith', NEW human_state( 'Healthy' ) ) );
INSERT INTO animals( OBJECT_VALUE ) VALUES( NEW horse( 'Mabel', NEW animal_state( 'Healthy' ) ) );
INSERT INTO animals( OBJECT_VALUE ) VALUES( NEW girl( 'Sue Jones', NEW human_state( 'Healthy' ) ) );
INSERT INTO animals( OBJECT_VALUE ) VALUES( NEW guy( 'Jimmy Adams', NEW human_state( 'Healthy' ) ) );
INSERT INTO animals( OBJECT_VALUE ) VALUES( NEW girl( 'Peggy W.', NEW human_state( 'Healthy' ) ) );
INSERT INTO buildings( OBJECT_VALUE ) VALUES( NEW building( 'Barn' ) );
INSERT INTO buildings( OBJECT_VALUE ) VALUES( NEW building( 'Mary''s Home' ) );
INSERT INTO surreys( OBJECT_VALUE ) VALUES( NEW surrey( 'Fancy', NEW surrey_stateflow( 'Working' ) ) );
INSERT INTO surreys( OBJECT_VALUE ) VALUES( NEW surrey( 'Plain', NEW surrey_stateflow( 'Working' ) ) );
COMMIT;

Functions are really Associations

Without the accompanying commentary, I am going to take a guess that member functions and procedures for the types are really associations in Nordic. The reason for this is that the graph on slide #60 shows multiple associations between Guy:H101 and Surrey:S101. I guess that these are the DRIVES and FIXES member procedures.

The following table is my guess about the associations implied by slide #60.

Source Association Name Destination
Class Name Class Name
Guy Jim Bob Uses Building Barn
Courts Girl Mary Smith
Drives Surrey Fancy
Fixes
Girl Mary Smith Uses Building Mary’s Home
Drives Surrey Fancy
Horse Mabel Pulls Surrey Fancy
Plain
Girl Sue Jones Drives Surrey Plain

Generate Type Associations

If the above table truly reflects what is going on with associations in Nordic, then we can generate the associations between types in Oracle by using the following data dictionary view:

  • USER_METHOD_PARAMS describes the method parameters of the object types owned by the current user. Its columns (except for OWNER) are the same as those in ALL_METHOD_PARAMS
  • USER_TYPES describes the object types owned by the current user. Its columns (except for OWNER) are the same as those in ALL_TYPES
COLUMN type_name       HEADING "Source|Class"
COLUMN method_name     HEADING "Association"
COLUMN param_type_name HEADING "Destination|Class"
SELECT
    type_name,
    method_name,
    param_type_name
  FROM
    user_method_params
  WHERE
      param_type_name  type_name
    AND
      param_no > 1
    AND
      param_type_name IN (
        SELECT type_name FROM user_types
      )
  ORDER BY
    1,2,3
/

The SQL*Plus® command, COLUMN, to change column headings.

The above query returns all types that have methods which have parameters (other the self one which is always the first parameter to a method) that are user types.

The resulting report is:

Source                                                        Destination
Class                          Association                    Class
------------------------------ ------------------------------ -----------
ANIMAL                         USES                           BUILDING
GIRL                           DRIVES                         SURREY
GIRL                           FIXES                          SURREY
GIRL                           USES                           BUILDING
GUY                            COURTS                         GIRL
GUY                            DRIVES                         SURREY
GUY                            FIXES                          SURREY
GUY                            USES                           BUILDING
HORSE                          PULLS                          SURREY
HORSE                          USES                           BUILDING
HUMAN                          DRIVES                         SURREY
HUMAN                          FIXES                          SURREY
HUMAN                          USES                           BUILDING

Creating Other Association Types

We have already created the COURTSHIP type earlier. (The final creation script is in Nordic (7).)

Since we are using NAME as the PRIMARY KEY constraint and the OBJECT IDENTIFIER, we can just create tables to store the pair of names for the source class and the destination class.

USES_BUILDING

The simplest association table is USES_BUILDING because there is no restriction on the sub-types of ANIMAL. The SQL is:

CREATE TABLE uses_building (
  animal_name
    VARCHAR2(30)
    CONSTRAINT uses_building_animal_name_nn
      NOT NULL
    CONSTRAINT uses_building_animal_name_fk
      REFERENCES animals( name ),
  building_name
    VARCHAR2(30)
    CONSTRAINT uses_building_building_name_nn
      NOT NULL
    CONSTRAINT uses_building_building_name_fk
      REFERENCES buildings( name ),
  CONSTRAINT uses_building_pk
    PRIMARY KEY (
      animal_name,
      building_name
    )
  )
/

Loading data into this table is easier:

INSERT INTO uses_building ( animal_name, building_name ) VALUES( 'Jim Bob', 'Barn' );
INSERT INTO uses_building ( animal_name, building_name ) VALUES( 'Mary Smith', 'Mary''s Home' );
COMMIT;
DRIVES_SURREY

Let’s extend the same technique to a more complex table, DRIVES_SURREY, in which there is a restriction on the type of the source class.

CREATE TABLE drives_surrey (
  human_name
    VARCHAR2(30)
    CONSTRAINT drives_surrey_human_name_nn
      NOT NULL
    CONSTRAINT drives_surrey_human_name_fk
      REFERENCES animals( name ),
  surrey_name
    VARCHAR2(30)
    CONSTRAINT drives_surrey_surrey_name_nn
      NOT NULL
    CONSTRAINT drives_surrey_surrey_name_fk
      REFERENCES surreys( name ),
  CONSTRAINT drives_surrey_pk
    PRIMARY KEY (
      human_name,
      surrey_name
    )
);

We will use the same technique as shown in Nordic (7) for checking the type of objects.

CREATE OR REPLACE TRIGGER drives_surrey_pre
  BEFORE INSERT
    OR UPDATE OF human_name
  ON drives_surrey
  FOR EACH ROW
  DECLARE
    l_valid_name
       VARCHAR2(30)
        := NULL;
  BEGIN
    SELECT
        name
      INTO
        l_valid_name
      FROM
        animals ptr
      WHERE
          name = :new.human_name
        AND
          VALUE( ptr ) IS OF TYPE( human );
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR( -20009, :new.human_name || ' is not a human', FALSE );
  END drives_surrey_pre;
/
SHOW ERRORS

Let’s load data:

INSERT INTO drives_surrey ( human_name, surrey_name ) VALUES( 'Jim Bob', 'Fancy' );
INSERT INTO drives_surrey ( human_name, surrey_name ) VALUES( 'Mary Smith', 'Fancy' );
INSERT INTO drives_surrey ( human_name, surrey_name ) VALUES( 'Sue Jones', 'Plain' );
COMMIT;
FIXES_SURREY

Let’s extend the same technique to a more complex table, FIXES_SURREY, in which there is a restriction on the type of the source class.

CREATE TABLE fixes_surrey (
  human_name
    VARCHAR2(30)
    CONSTRAINT fixes_surrey_human_name_nn
      NOT NULL
    CONSTRAINT fixes_surrey_human_name_fk
      REFERENCES animals( name ),
  surrey_name
    VARCHAR2(30)
    CONSTRAINT fixes_surrey_surrey_name_nn
      NOT NULL
    CONSTRAINT fixes_surrey_surrey_name_fk
      REFERENCES surreys( name ),
  CONSTRAINT fixes_surrey_pk
    PRIMARY KEY (
      human_name,
      surrey_name
    )
);

We will use the same technique as shown in Nordic (7) for checking the type of objects.

CREATE OR REPLACE TRIGGER fixes_surrey_pre
  BEFORE INSERT
    OR UPDATE OF human_name
  ON fixes_surrey
  FOR EACH ROW
  DECLARE
    l_valid_name
       VARCHAR2(30)
        := NULL;
  BEGIN
    SELECT
        name
      INTO
        l_valid_name
      FROM
        animals ptr
      WHERE
          name = :new.human_name
        AND
          VALUE( ptr ) IS OF TYPE( human );
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR( -20009, :new.human_name || ' is not a human', FALSE );
  END fixes_surrey_pre;
/
SHOW ERRORS

Let’s load data:

INSERT INTO fixes_surrey ( human_name, surrey_name ) VALUES( 'Jim Bob', 'Fancy' );
COMMIT;
PULLS_SURREY

Let’s extend the same technique to a more complex table, PULLS_SURREY, in which there is a restriction on the type of the source class.

CREATE TABLE pulls_surrey (
  horse_name
    VARCHAR2(30)
    CONSTRAINT pulls_surrey_horse_name_nn
      NOT NULL
    CONSTRAINT pulls_surrey_horse_name_fk
      REFERENCES animals( name ),
  surrey_name
    VARCHAR2(30)
    CONSTRAINT pulls_surrey_surrey_name_nn
      NOT NULL
    CONSTRAINT pulls_surrey_surrey_name_fk
      REFERENCES surreys( name ),
  CONSTRAINT pulls_surrey_pk
    PRIMARY KEY (
      horse_name,
      surrey_name
    )
);

We will use the same technique as shown in Nordic (7) for checking the type of objects.

CREATE OR REPLACE TRIGGER pulls_surrey_pre
  BEFORE INSERT
    OR UPDATE OF horse_name
  ON pulls_surrey
  FOR EACH ROW
  DECLARE
    l_valid_name
       VARCHAR2(30)
        := NULL;
  BEGIN
    SELECT
        name
      INTO
        l_valid_name
      FROM
        animals ptr
      WHERE
          name = :new.horse_name
        AND
          VALUE( ptr ) IS OF TYPE( horse );
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR( -20010, :new.horse_name || ' is not a horse', FALSE );
  END pulls_surrey_pre;
/
SHOW ERRORS

Let’s load data:

INSERT INTO pulls_surrey ( horse_name, surrey_name ) VALUES( 'Mabel', 'Fancy' );
INSERT INTO pulls_surrey ( horse_name, surrey_name ) VALUES( 'Mabel', 'Plain' );
COMMIT;
COURTSHIPS

The remaining table to consider is COURTSHIPS. All we need to do here is to add the data:

INSERT INTO courtships VALUES( 'Jim Bob', 'Mary Smith' );
COMMIT;

Spidering

While we have reproduced the data presented on slide #60, we still have not reproduced the query that the author is asking. “What are the objects that Jim Bob link to?”

Let’s create a view that gets the associations between two classes by using the CREATE VIEW command. This is a kludge.

CREATE OR REPLACE VIEW associations AS
  SELECT
      'ANIMAL'
        AS source_class,
      animal_name
        AS source_object,
      'USES'
        AS association,
      'BUILDING'
        AS dest_class,
      building_name
        AS dest_object
    FROM
      uses_building
UNION ALL
  SELECT
      'HUMAN'
        AS source_class,
      human_name
        AS source_object,
      'DRIVES'
        AS association,
      'SURREY'
        AS dest_class,
      surrey_name
        AS dest_object
    FROM
      drives_surrey
UNION ALL
  SELECT
      'HUMAN'
        AS source_class,
      human_name
        AS source_object,
      'FIXES'
        AS association,
      'SURREY'
        AS dest_class,
      surrey_name
        AS dest_object
    FROM
      fixes_surrey
UNION ALL
  SELECT
      'HORSE'
        AS source_class,
      horse_name
        AS source_object,
      'PULLS'
        AS association,
      'SURREY'
        AS dest_class,
      surrey_name
        AS dest_object
    FROM
      pulls_surrey
UNION ALL
  SELECT
      'GUY'
        AS source_class,
      guy_name
        AS source_object,
      'COURTS'
        AS association,
      'GIRL'
        AS dest_class,
      girl_name
        AS dest_object
    FROM
      courtships
/

Let’s see if we can reproduce the query results from slide #60.

COLUMN source_class HEADING 'Source|Class'
COLUMN dest_class   HEADING 'Dest|Class'
SELECT * FROM associations WHERE source_object = 'Jim Bob';

The results are:

Source                                       Dest
Class  SOURCE_OBJECT                  ASSOCI Class    DEST_OBJECT
------ ------------------------------ ------ -------- -----------
ANIMAL Jim Bob                        USES   BUILDING Barn
HUMAN  Jim Bob                        DRIVES SURREY   Fancy
HUMAN  Jim Bob                        FIXES  SURREY   Fancy
GUY    Jim Bob                        COURTS GIRL     Mary Smith

This construct does not handle sub-types at all.

Conclusion

Because Nordic implements associations in a matrix, it is able to do some fancy queries that Oracle would find difficult to replicate.

In Oracle, we are able to get better quality of data by introducing constraints on the relationships that is visible outside of a package such as Nordic.

Advertisements

2 thoughts on “Nordic (11)

  1. Nordic (12) « Yet Another OCM

  2. Nordic (13) « Yet Another OCM

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s