Nordic (12)


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

Getting Type Association From Data Dictionary

Staying on slide #60, we can extract more information by using data dictionary views. The following complex SQL query gets the relationship between two (2) types by interrogating the referential integrity constraints between two (2) tables.

To do this, the following two (2) data dictionary views are used:

The query joins USER_CONSTRAINTS to itself in order to get a relationship between the child table and a parent table.

The DENSE_RANK function is used to generate a sequence of numbers that correspond to the order of the name of the referential integrity constraints for each child table. These numbers are then used to convert to column positions in the final part of the query.

The USER_OBJECT_TABLES is joined to convert the name of the parent table to a type name.

The WITH clause is syntatic sugar to remove a complex subquery out of the main body of the query.

The DECODE and MAX functions work together to pivot the TYPE_NAME column in each row into a series of columns: TYPE_1, and TYPE_2.

WITH
  half_relationship AS (
    SELECT
        assoc.table_name
          AS association,
        obj.table_type
          AS type_name,
        DENSE_RANK() OVER(
          PARTITION BY assoc.table_name
          ORDER BY assoc.constraint_name
          ) AS type_order
      FROM
          user_constraints assoc
        INNER JOIN
          user_constraints key
        ON (
          assoc.r_constraint_name = key.constraint_name
        )
        INNER JOIN
          user_object_tables obj
        ON (
          key.table_name = obj.table_name
        )
      WHERE
        assoc.constraint_type = 'R'
    )
SELECT
    association,
    MAX(
      DECODE(
        type_order,
          1, type_name
        )
      ) AS type_1,
    MAX(
      DECODE(
        type_order,
          2, type_name
        )
      ) AS type_2
  FROM
    half_relationship
  GROUP BY
    association
  ORDER BY
    association
/

The results are:

ASSOCIATION                    TYPE_1                         TYPE_2
------------------------------ ------------------------------ ------------------------------
COURTSHIPS                     ANIMAL                         ANIMAL
DRIVES_SURREY                  ANIMAL                         SURREY
FIXES_SURREY                   ANIMAL                         SURREY
PULLS_SURREY                   ANIMAL                         SURREY
USES_BUILDING                  ANIMAL                         BUILDING

This report only gives the base type instead of the correct sub-type for each relationship.

Association Mapping – 2 levels

We now move onto slide #61 of the presentation and we find that the view created in Nordic (11) is inadequate because we did not capture the reverse relationship. Let’s create a view that gathers the inverse relationships as well as the normal relationships together:

CREATE OR REPLACE VIEW full_associations AS
    SELECT
      source_class,
      source_object,
      association,
      dest_class,
      dest_object
    FROM
      associations
  UNION ALL
    SELECT
      assoc.dest_class
        AS source_class,
      assoc.dest_object
        AS source_object,
      association,
      assoc.source_class
        AS dest_class,
      assoc.source_object
        AS dest_object
    FROM
      associations assoc
/

With this view, we can use the techniques developed in the Nested Set series of articles to find the associations as a hierarchical query. Because the associations are seen as a simple graph as opposed to an oriented graph as in a simple hierarchy, we encounter cycles in following the links. To overcome this, we need two (2) fixes:

  1. NOCYCLE keyword is needed to stop the query from complaining about loops
  2. The WHERE clause in the outer query to remove the root node is needed because multiple rows in the data set match the root node.
WITH
  tree AS (
    SELECT
        dest_object,
        LEVEL
          AS node_level
      FROM
        full_associations
      START WITH
        source_object = 'Jim Bob'
      CONNECT BY NOCYCLE
        source_object = PRIOR dest_object
  )
SELECT
    dest_object,
    MIN( node_level )
      AS graph_distance
  FROM
    tree
  WHERE
    dest_object  'Jim Bob'
  GROUP BY
    dest_object
  HAVING
    MIN( node_level ) <= 2
  ORDER BY
    graph_distance,
    dest_object
/

The result is:

DEST_OBJECT                    GRAPH_DISTANCE
------------------------------ --------------
Barn                                        1
Fancy                                       1
Mary Smith                                  1
Mabel                                       2
Mary's Home                                 2

Association Mapping – 3 levels

Turning now to slide #62 of the presentation, we change the HAVING clause to be:

    MIN( node_level ) <= 3

The results are:

DEST_OBJECT                    GRAPH_DISTANCE
------------------------------ --------------
Barn                                        1
Fancy                                       1
Mary Smith                                  1
Mabel                                       2
Mary's Home                                 2
Plain                                       3

Association Mapping – 4 levels

Turning now to slide #63 of the presentation, we change the HAVING clause to be:

    MIN( node_level ) <= 4

The results are:

DEST_OBJECT                    GRAPH_DISTANCE
------------------------------ --------------
Barn                                        1
Fancy                                       1
Mary Smith                                  1
Mabel                                       2
Mary's Home                                 2
Plain                                       3
Sue Jones                                   4

Controlling Association Mapping

We now turn to slide #64 of the presentation.

Depth of Levels scanned

This is done through the WHERE clause on the outer query.

Limiting Class Backtracking

This cannot be done in our current set-up because an object maps to multiple classes.

First Class Objects

Let’s try:

COLUMN dest_class  HEADING "Dest|Class"
WITH
  tree AS (
    SELECT
        dest_class,
        dest_object,
        LEVEL
          AS node_level
      FROM
        full_associations
      START WITH
        source_object = 'Jim Bob'
      CONNECT BY NOCYCLE
        source_object = PRIOR dest_object
  )
SELECT
    dest_class,
    MIN( node_level )
      AS graph_distance
  FROM
    tree
  WHERE
    dest_object  'Jim Bob'
  GROUP BY
    dest_class
  HAVING
    MIN( node_level ) <= 2
  ORDER BY
    graph_distance,
    dest_class
/

The result is:

Dest
Class    GRAPH_DISTANCE
-------- --------------
BUILDING              1
GIRL                  1
SURREY                1
HORSE                 2
HUMAN                 2

Association Mapping

Slides #65 to #69 all involve different types of queries on the association matrix. I am not going to decipher what is going on here.

Conclusion

We can simluate some association queries between classes through the use of data dictionary views. The free associations queries in Nordic are much easier because of the implementation of the association matrix (AMM).

Advertisements

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