Nordic (4)


This series is a commentary of sorts on a PowerPoint™ presentation called Nordic – Object Relational Database Design. The previous posts are at

More on Associations

I have since discovered the TREAT function which allows us to overcome the problems encountered in Type Evolution of Nordic (3).

This will allow us to tell Oracle database what it already knows – the type of the object.

Fourth Design Attempt

Remove Old Design

CONNECT ordbms/******* -- password suppressed
DROP TABLE courtships;
DROP TYPE courtship;

Implement New Design

This is really the second design revisited.

CREATE TYPE courtship AS OBJECT (
  my_guy  REF guy,
  my_girl REF girl
);
/
CREATE TABLE courtships OF courtship;

Persist an Object

INSERT INTO
  courtships(
    my_guy,
    my_girl
  )
  VALUES(
    (SELECT TREAT(REF(ptr) AS REF guy)  FROM animals ptr WHERE name='Jim Bob'),
    (SELECT TREAT(REF(ptr) AS REF girl) FROM animals ptr WHERE name='Mary Lou')
  )
;
COMMIT;

This is really ugly. I use the TREAT function to tell Oracle that the pointer to the current row (obtained by the REF function) is really a REF data type pointing to a user type (GUY, or GIRL), not a pointer to the base type of the table: ANIMAL.

Look at the data

SELECT * FROM courtships;

The output is:

MY_GUY
--------------------------------------------------------------------------
MY_GIRL
--------------------------------------------------------------------------
0000220208AA99CF23FF3E4F0A918C1FDC270A83690199AA9B12344248A683451EF6A6B64E
00002202083E54268F549447DE81D5A90CEC17AF150199AA9B12344248A683451EF6A6B64E

Yuck! Let’s try the DEREF function to clean things up a bit.

SELECT DEREF(my_guy),DEREF(my_girl) FROM courtships;

And the pretty output is:

DEREF(MY_GUY)(NAME)
--------------------
DEREF(MY_GIRL)(NAME)
--------------------
GUY('Jim Bob')
GIRL('Mary Lou')

Further Investigation

Let’s increase the human population by 50%.

INSERT INTO animals VALUES( guy('Billy Joe') );
COMMIT;

Let’s find all of the guys who are not in a relationship.

SELECT
    *
  FROM
    animals ptr
  WHERE
      VALUE( ptr ) IS OF TYPE( guy )
    AND
      REF( ptr ) NOT IN (
        SELECT my_guy FROM courtships
      )
/

And the answer is, of course:

NAME
------------------------------
Billy Joe

Who is Mary Lou dating?

SELECT
    guy_ptr.name
  FROM
      animals girl_ptr
    INNER JOIN
      courtships
    ON (
      REF( girl_ptr ) = courtships.my_girl
    )
    INNER JOIN
      animals guy_ptr
    ON (
      REF( guy_ptr ) = courtships.my_guy
    )
  WHERE
    girl_ptr.name = 'Mary Lou'
/

The answer is:

NAME
------------------------------
Jim Bob

Design Review

All of the above examples have been making queries based on the NAME attribute. In this application, we could use the NAME attribute as the object identifier.

DROP TABLE courtships;
DROP TABLE animals;
DROP TABLE buildings;

Persistent Storage for Building Object

Create the table and populate it.

CREATE TABLE buildings
  OF building
  (
    CONSTRAINT
      buildings_pk
      PRIMARY KEY (
        name
      )
   )
   OBJECT IDENTIFIER IS PRIMARY KEY
;
INSERT INTO buildings VALUES( building( 'Barn' ) );
COMMIT;

The CREATE TABLE statement is more complex in that we are using a PRIMARY KEY constraint (as an out of line constraint) and an OBJECT IDENTIFIER clause.

Let’s check the data.

SQL> SELECT * FROM buildings;

NAME
------------------------------
Barn

SQL> SELECT VALUE( ptr ) FROM buildings ptr;

VALUE(PTR)(NAME)
--------------------------------------------
BUILDING('Barn')

Persist the Animals

Create the table in a similar, and populate.

CREATE TABLE animals
  OF animal
  (
    CONSTRAINT
      animals_pk
      PRIMARY KEY (
        name
      )
   )
   OBJECT IDENTIFIER IS PRIMARY KEY
;
INSERT INTO animals VALUES( horse( 'Mabel' ) );
INSERT INTO animals VALUES( girl( 'Mary Lou' ) );
INSERT INTO animals VALUES( guy( 'Jim Bob' ) );
INSERT INTO animals VALUES( guy( 'Billy Joe' ) );
COMMIT;

Check the data:

SQL> SELECT * FROM animals;

NAME
------------------------------
Billy Joe
Jim Bob
Mabel
Mary Lou

SQL> SELECT VALUE( ptr ) FROM animals ptr;

VALUE(PTR)(NAME)
------------------------------------------
HORSE('Mabel')
GIRL('Mary Lou')
GUY('Jim Bob')
GUY('Billy Joe')

Going Courting Again!

Let’s redefine the COURTSHIP type once more.

DROP TYPE courtship;
CREATE TYPE courtship AS OBJECT (
  guy_name  VARCHAR2(30),
  girl_name VARCHAR2(30)
);
/
CREATE TABLE courtships OF courtship
  (
    CONSTRAINT
      courtships_pk
      PRIMARY KEY (
        guy_name,
        girl_name
      ),
    CONSTRAINT
      courtships_guy_name_fk
      FOREIGN KEY (
        guy_name
      )
      REFERENCES animals (
        name
      ),
    CONSTRAINT
      courtships_girl_name_fk
      FOREIGN KEY (
        girl_name
      )
      REFERENCES animals (
        name
      )
   )
   OBJECT IDENTIFIER IS PRIMARY KEY
;
INSERT INTO courtships VALUES( 'Jim Bob', 'Mary Lou' );
INSERT INTO courtships VALUES( 'Billy Joe', 'Mabel' );
INSERT INTO courtships VALUES( 'Mabel', 'Mabel' );
COMMIT;

This is more complex definition. Here we have a composite primary key constraint and two (2) referential integrity constraints which are implemented as foreign key constraints.

Note that the data entry is far simpler than before. However, the cost of simplicity comes from the weakening of the business rules about courtship: now any animal can court any other animal (including itself). Previously, we had held that only a guy could court a girl, but the problem was with REF data types that could be left dangling. Now, we constrain the relationship to the continued existence of rows in the ANIMALS table, at the cost of losing type information.

Let’s check the data:

SQL> SELECT * FROM courtships;

GUY_NAME                       GIRL_NAME
------------------------------ ------------------------------
Billy Joe                      Mabel
Jim Bob                        Mary Lou
Mabel                          Mabel

SQL> SELECT VALUE( ptr ) FROM courtships ptr;

VALUE(PTR)(GUY_NAME, GIRL_NAME)
-------------------------------------------------------------
COURTSHIP('Billy Joe', 'Mabel')
COURTSHIP('Jim Bob', 'Mary Lou')
COURTSHIP('Mabel', 'Mabel')

Yet Another Redesign of Courtship

This is not good. We need to keep the object type. Let’s try using the out of line REF constraint which seems to contains the best of the REF datatype and the referential integrity constraint:

DROP TABLE courtships;
DROP TYPE courtship;
CREATE TYPE courtship AS OBJECT (
  my_guy  REF guy,
  my_girl REF girl
);
/
CREATE TABLE courtships OF courtship
  (
    CONSTRAINT
      courtships_my_guy_fk
      FOREIGN KEY ( my_guy )
      REFERENCES animals,
    CONSTRAINT
      courtships_my_girl_fk
      FOREIGN KEY ( my_girl )
      REFERENCES animals
  )
;

When OBJECT IDENTIFIER IS PRIMARY KEY is included, the following error message is issued:

CREATE TABLE courtships OF courtship
*
ERROR at line 1:
ORA-22971: invalid datatype for PRIMARY KEY-based object identifier

There is no primary key because:

ORA-02329: column of datatype REF cannot be unique or a primary key

Testing Data Integrity

Let’s add wholesome data:

INSERT INTO
  courtships(
    my_guy,
    my_girl
  )
  VALUES(
    (SELECT TREAT(REF(ptr) AS REF guy)  FROM animals ptr WHERE name='Jim Bob'),
    (SELECT TREAT(REF(ptr) AS REF girl) FROM animals ptr WHERE name='Mary Lou')
  )
;
COMMIT;

This works as expected. Now for unwholesome data:

INSERT INTO
  courtships(
    my_guy,
    my_girl
  )
  VALUES(
    (SELECT TREAT(REF(ptr) AS REF guy)  FROM animals ptr WHERE name='Billy Joe'),
    (SELECT TREAT(REF(ptr) AS REF girl) FROM animals ptr WHERE name='Mabel')
  )
;
COMMIT;

This worked as well. What happened? Let’s look at the data:

SQL> SELECT deref( my_guy ), deref( my_girl ) FROM courtships;

DEREF(MY_GUY)(NAME)
--------------------------------------------------------------
DEREF(MY_GIRL)(NAME)
--------------------------------------------------------------
GUY('Jim Bob')
GIRL('Mary Lou')
GUY('Billy Joe')

If Billy Joe can not love a girl, he gets nobody (that is a NULL value). Unfortunately, a NULL value satisfies the existing constraints.

We need to modify the table definition to include NOT NULL integrity constraints. Because we are still working out the proper design, it is better to drop the table and start again:

DROP TABLE courtships;
CREATE TABLE courtships OF courtship
  (
    CONSTRAINT
      courtship_my_guy_nn
      CHECK ( my_guy IS NOT NULL ),
    CONSTRAINT
      courtship_my_girl_nn
      CHECK ( my_girl IS NOT NULL ),
    CONSTRAINT
      courtships_my_guy_fk
      FOREIGN KEY ( my_guy )
      REFERENCES animals,
    CONSTRAINT
      courtships_my_girl_fk
      FOREIGN KEY ( my_girl )
      REFERENCES animals
  )
;

Add Wholesome and Unwholesome Data

Let’s try adding the same data again:

SQL> INSERT INTO
  2    courtships(
  3      my_guy,
  4      my_girl
  5    )
  6    VALUES(
  7      (SELECT TREAT(REF(ptr) AS REF guy)  FROM animals ptr WHERE name='Jim Bob'),
  8      (SELECT TREAT(REF(ptr) AS REF girl) FROM animals ptr WHERE name='Mary Lou')
  9    )
 10  ;

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL> INSERT INTO
  2    courtships(
  3      my_guy,
  4      my_girl
  5    )
  6    VALUES(
  7      (SELECT TREAT(REF(ptr) AS REF guy)  FROM animals ptr WHERE name='Billy Joe'),
  8      (SELECT TREAT(REF(ptr) AS REF girl) FROM animals ptr WHERE name='Mabel')
  9    )
 10  ;
INSERT INTO
*
ERROR at line 1:
ORA-02290: check constraint (ORDBMS.COURTSHIP_MY_GIRL_NN) violated

The wholesome data is accepted while the unwholesome data is rejected. The reason for rejection is not obvious if you have not been following the article. The data is rejected because the MY_GIRL value is NULL. This is because Mabel is not of the GIRL type.

What happens if we get rid of the TREAT function?

SQL> INSERT INTO
  2    courtships(
  3      my_guy,
  4      my_girl
  5    )
  6    VALUES(
  7      (SELECT ref(ptr)  FROM animals ptr WHERE name='Billy Joe'),
  8      (SELECT ref(ptr) FROM animals ptr WHERE name='Mabel')
  9    )
 10  ;
INSERT INTO
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected REF ORDBMS.GIRL got REF ORDBMS.ANIMAL

This looks pretty bullet-proof.

Testing Referential Integrity

Let’s try to get rid of Jim Bob but his love for Mary Lou prevents us:

SQL> DELETE FROM animals WHERE name = 'Jim Bob';
DELETE FROM animals WHERE name = 'Jim Bob'
*
ERROR at line 1:
ORA-02292: integrity constraint (ORDBMS.COURTSHIPS_MY_GUY_FK) violated - child record found

Conclusion to Part the Fourth

The commands to manipulating association types in Oracle looks ugly, but we can implement business rules to meet our needs.

Advertisements

One thought on “Nordic (4)

  1. Nordic (5) « 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