Nordic (14)


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

Aggregation Associations (Collections)

Current Problem with the Physical Design

In Nordic (13), we had the problem of having the same teams pulling different surreys at the same time.

This problem is a manifestation of a deeper one: a horse can be hitched up to more than one (1) surrey at a time.

The reason for this is that the current implementations of RDBMS can only provide integrity for columns via constraints, or for rows via triggers. There is no way of providing integrity for a table outside of these two (2) methods.

New Physical Design

The predicate for the PULLS_SURREY table is:

PULLS_SURREY( surrey_name, horse_team ) = Surrey surrey_name is pulled by a team, called horse_team, of four (4) horses.

Let’s try a different approach. Instead of hitching all of the horses at once to the surrey, let’s hitch them individually. Now, there are three (3) attributes:

  1. The surrey itself which is identified by its name. This name cannot be null. The name must be valid.
  2. The horse that is being hitched to the surrey. The horse must have a valid, non-null name. The horse must be healthy.
  3. The position at which the horse is hitched to the surrey.

Let’s call the new table HITCHED_SURREY with the following predicate:

PULLS_SURREY( surrey_name, hitch_pos, horse_name ) = Horse, horse_name, is hitched at position no. hitch_pos to surrey surrey_name.

Later on, once we have four (4) horses hitched to a surrey then we have a horse team that pulls the surrey. Instead of trying to create a team of horses from the start, we have gone into more detail about how to create a team of horses.

New Implementation

New Table for Hitching

Based on the attributes specified, we can readily produce the following CREATE TABLE statement:

CREATE TABLE hitched_surrey (
  surrey_name
    VARCHAR2( 30 )
    CONSTRAINT hitched_surrey_surrey_name_nn
      NOT NULL
    CONSTRAINT hitched_surrey_surrey_name_fk
      REFERENCES surreys( name ),
  hitch_pos
    NUMBER
    CONSTRAINT hitched_surrey_hitch_pos_nn
      NOT NULL
    CONSTRAINT hitched_surrey_hitch_pos_ck
      CHECK (
        hitch_pos IN ( 1, 2, 3, 4 )
      ),
  horse_name
    VARCHAR2( 30 )
    CONSTRAINT hitched_surrey_horse_name_nn
      NOT NULL
    CONSTRAINT hitched_surrey_horse_name_fk
      REFERENCES animals( name )
    CONSTRAINT hitched_surrey_horse_name_uk
      UNIQUE,
  CONSTRAINT hitched_surrey_pk 
    PRIMARY KEY (
      surrey_name,
      hitch_pos
    )
);

The UNIQUE keyword enforces unique column value for the name of the animal. This means that an animal can only be hitched to one (1) surrey at most. We need a trigger to ensure that the animal is really a horse.

PRIMARY KEY constraint of surrey_name and hitch_pos ensures that, at most, only (1) animal can be hitched at a position on a surrey.

Type Integrity

One of the ways to ensure that only horses are hitched to the surrey to have a trigger that validates the type of animal being hitched.

CREATE OR REPLACE TRIGGER hitched_surrey_horse_name_pre
  BEFORE INSERT
    OR UPDATE OF horse_name
  ON hitched_surrey
  FOR EACH ROW
  WHEN ( new.horse_name IS NOT NULL )
  DECLARE
    l_valid_name
      animals.name%TYPE;
  BEGIN
    SELECT
        name
      INTO
        l_valid_name
      FROM
        animals ptr
      WHERE
          name = :new.horse_name
        AND
          ptr.state.current_state = 'Healthy'
        AND
          VALUE( ptr ) IS OF TYPE( horse );
  EXCEPTION
    WHEN NO_DATA_FOUND
      THEN RAISE_APPLICATION_ERROR( -20014, :new.horse_name || ' is not a healthy horse.', FALSE );
END hitched_surrey_horse_name_pre;
/
SHOW ERRORS

We have an extra criterion that the horse has to be healthy at the time it is hitched to the surrey.

Valid Test Data

Let’s test the hitching be entering valid data first, and saving this.

INSERT INTO hitched_surrey( surrey_name, hitch_pos, horse_name )
  VALUES( 'Fancy', 1, 'Mabel' );
INSERT INTO hitched_surrey( surrey_name, hitch_pos, horse_name )
  VALUES( 'Fancy', 2, 'Dale' );
INSERT INTO hitched_surrey( surrey_name, hitch_pos, horse_name )
  VALUES( 'Fancy', 3, 'Daleness' );
INSERT INTO hitched_surrey( surrey_name, hitch_pos, horse_name )
  VALUES( 'Fancy', 4, 'Son of Rainbow' );
COMMIT;
Invalid Test Data

Now for the invalid data:

-- This should fail with ORA-01400
INSERT INTO hitched_surrey( surrey_name, hitch_pos, horse_name )
  VALUES( NULL, NULL, NULL );
-- This should fail with ORA-00001
INSERT INTO hitched_surrey( surrey_name, hitch_pos, horse_name )
  VALUES( 'Plain', 1, 'Mabel' );
-- This should fail with ORA-20014
INSERT INTO hitched_surrey( surrey_name, hitch_pos, horse_name )
  VALUES( 'Plain', 1, 'Fred' );
-- Extra test data
INSERT INTO animals( object_value )
  VALUES( NEW horse( 'Son of Phar Lap', NEW animal_state( 'Healthy' ) ) );
-- This should fail with ORA-02290
INSERT INTO hitched_surrey( surrey_name, hitch_pos, horse_name )
  VALUES( 'Plain', 0, 'Son of Phar Lap' );
-- This should fail with ORA-00001
INSERT INTO hitched_surrey( surrey_name, hitch_pos, horse_name )
  VALUES( 'Fancy', 1, 'Son of Phar Lap' );
-- Throw away the test suite
ROLLBACK;

We have proved the data integrity rules for hitching up horses to a surrey.

Create PULLS_SURREY View

First of all, we get rid of the old object table by using the DROP TABLE command with the PURGE to bypass the recycle bin.

DROP TABLE pulls_surrey PURGE;

Now, we use an object view through the use of the CREATE VIEW command.

CREATE OR REPLACE VIEW pulls_surrey
  OF team_pulls_surrey
  WITH OBJECT IDENTIFIER( surrey_name )
  AS
    SELECT
        surrey_name,
        CAST(
          MULTISET(
            SELECT
                horse_name
              FROM
                hitched_surrey inner
              WHERE
                inner.surrey_name = outer.surrey_name
              ORDER BY
                hitch_pos
          ) AS surrey_team
        ) AS horse_team
      FROM (
        SELECT
            surrey_name
          FROM
            hitched_surrey
          GROUP BY
            surrey_name
          HAVING
            COUNT(*) = 4
      ) outer
;

This view only includes surreys that have four (4) horses hitched up. This is achieved through the sub-query in the FROM clause. This is a more flexible solution as the rules for hitching can be changed relatively easily here.

The VARRAY for the team of horses is constructed from the result set from the correlated sub-query by using the Multiset Operators, MULTISET and CAST. The ORDER BY clause is included to ensure that the hitching position corresponds to the array index.

PULLS_SURREY With Valid Data

Let’s look at the data from the view:

SET PAGESIZE 50000 LINESIZE 120
SELECT object_value FROM pulls_surrey;

The result is as expected.

OBJECT_VALUE(SURREY_NAME, HORSE_TEAM)
--------------------------------------------------------------------------------------
TEAM_PULLS_SURREY('Fancy', SURREY_TEAM('Mabel', 'Dale', 'Daleness', 'Son of Rainbow'))

What happens if there is a surrey with less than four (4) horses hitched up? Let’s find out by adding some test data:

INSERT INTO animals( object_value )
  VALUES( NEW horse( 'Son of Phar Lap', NEW animal_state( 'Healthy' ) ) );
INSERT INTO hitched_surrey( surrey_name, hitch_pos, horse_name )
  VALUES( 'Plain', 4, 'Son of Phar Lap' );
SELECT object_value FROM pulls_surrey;
ROLLBACK;

The result is unchanged as expected.

OBJECT_VALUE(SURREY_NAME, HORSE_TEAM)
--------------------------------------------------------------------------------------
TEAM_PULLS_SURREY('Fancy', SURREY_TEAM('Mabel', 'Dale', 'Daleness', 'Son of Rainbow'))
Data Manipulation Using the View

Can we manipulate data directly through the view without all of the hassle of hitching up horses individually? The answer is yes.

First of all, there is misleading information in the data dictionary view, USER_UPDATABLE_COLUMNS. (USER_UPDATABLE_COLUMNS describes columns in a join view that can be updated by the current user, subject to appropriate privileges. Its columns are the same as those in “ALL_UPDATABLE_COLUMNS“.)

SELECT
    column_name,
    updatable, 
    insertable, 
    deletable 
  FROM
    user_updatable_columns
  WHERE
    table_name='PULLS_SURREY'
/

We get the following answer:

COLUMN_NAME                    UPD INS DEL
------------------------------ --- --- ---
SURREY_NAME                    YES YES YES
HORSE_TEAM                     YES YES YES

If we tried to insert new data, we get the following:

SQL> insert into pulls_surrey( object_value ) values( new team_pulls_surrey( 'Dummy', new surrey_team( 'Fred', 'Fred', ' Fred', 'Fred' ) ) );
insert into pulls_surrey( object_value ) values( new team_pulls_surrey( 'Dummy', new surrey_team( 'Fred', 'Fred', 'Fred', 'Fred' ) ) )
                          *
ERROR at line 1:
ORA-01733: virtual column not allowed here


SQL> insert into pulls_surrey values( 'Dummy', new surrey_team( 'Fred', 'Fred', 'Fred', 'Fred' ) ) ;
insert into pulls_surrey values( 'Dummy', new surrey_team( 'Fred', 'Fred', 'Fred', 'Fred' ) )
*
ERROR at line 1:
ORA-01733: virtual column not allowed here


SQL> insert into pulls_surrey( surrey_name, horse_team) values( 'Dummy', new surrey_team( 'Fred', 'Fred', 'Fred', 'Fred' ) ) ;
insert into pulls_surrey( surrey_name, horse_team) values( 'Dummy', new surrey_team( 'Fred', 'Fred', 'Fred', 'Fred' ) )
                                       *
ERROR at line 1:
ORA-01733: virtual column not allowed here

What if we tried to change the name of the surrey?

SQL> update pulls_surrey set surrey_name = 'Plain' where surrey_name = 'Fancy';
update pulls_surrey set surrey_name = 'Plain' where surrey_name = 'Fancy'
                        *
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table

How about when we try to unhitch the team from the fancy surrey?

SQL> DELETE FROM pulls_surrey WHERE surrey_name = 'Fancy';
DELETE FROM pulls_surrey WHERE surrey_name = 'Fancy'
            *
ERROR at line 1:
ORA-01752: cannot delete from view without exactly one key-preserved table
INSTEAD OF TRIGGER for DELETE

What we need is an INSTEAD OF TRIGGER on the view to convert the DML commands to changes in the underlying table, HITCHED_SURREY.

Let’s start with the DELETE command because this is the simplest to implement. We just delete matching rows from the underlying table.

CREATE OR REPLACE TRIGGER pulls_surrey_delete
  INSTEAD OF DELETE ON pulls_surrey
  BEGIN
    DELETE FROM hitched_surrey
      WHERE surrey_name = :old.surrey_name;
  END pulls_surrey_delete_pre;
/
SHOW ERRORS

Let’s try it out:

DELETE FROM pulls_surrey WHERE surrey_name = 'Fancy';
SELECT object_value FROM pulls_surrey;
SELECT * FROM hitched_surrey;
ROLLBACK;

The SELECT statements show no data in the view or in the table.

INSTEAD OF TRIGGER for INSERT

Let’s now add support for the INSERT command:

CREATE OR REPLACE TRIGGER pulls_surrey_insert
  INSTEAD OF INSERT ON pulls_surrey
  BEGIN
    IF :new.horse_team IS NULL THEN
      RAISE_APPLICATION_ERROR( -20015, 'The horse team cannot be NULL.', FALSE );
    END IF;
    IF :new.horse_team.count  4 THEN
      RAISE_APPLICATION_ERROR( -20011, 'There must be four (4) horses in a team.', FALSE );
    END IF;
    FOR l_idx IN 1..:new.horse_team.count
    LOOP
      INSERT INTO hitched_surrey( surrey_name, hitch_pos, horse_name )
        VALUES( :new.surrey_name, l_idx, :new.horse_team( l_idx ) );
    END LOOP;
  END pulls_surrey_insert;
/
SHOW ERRORS

This trigger is more complex because we have two (2) conditions: NULL checking; and cardinality. The NULL checking is done in the trigger so that a more meaningful message can be returned. The cardinality checking is done so that the data in the view remains visible as the selection criterion for the view involves checking for the cardinality. Thus, if the row was inserted with only three (3) horses, then the row would not appear in the view even though the INSERT statement was reported as successful.

We have introduced a maintenance problem by having the cardinality check done in two (2) places: the view definition, and this trigger.

Let’s test this trigger out:

-- Look at the current data in the underlying table
SELECT surrey_name, hitch_pos, horse_name FROM hitched_surrey ORDER BY 1,2,3;
-- Remove the data via the view
DELETE FROM pulls_surrey WHERE surrey_name = 'Fancy';
-- Add the team again with Mabel at position #3
INSERT INTO pulls_surrey( object_value )
  VALUES( NEW team_pulls_surrey( 'Plain', NEW SURREY_TEAM('Dale', 'Daleness', 'Mabel', 'Son of Rainbow') ) );
-- Check that the team is still visible through the view
SELECT object_value FROM pulls_surrey;
-- Check the order of horses in the underlying table
SELECT surrey_name, hitch_pos, horse_name FROM hitched_surrey ORDER BY 1,2,3;
-- Revert back to the original data
ROLLBACK;

The output from the last two (2) SELECT statements is:

SQL> SELECT object_value FROM pulls_surrey;

OBJECT_VALUE(SURREY_NAME, HORSE_TEAM)
--------------------------------------------------------------------------------------

TEAM_PULLS_SURREY('Plain', SURREY_TEAM('Dale', 'Daleness', 'Mabel', 'Son of Rainbow'))

SQL> -- Check the order of horses in the underlying table
SQL> SELECT surrey_name, hitch_pos, horse_name FROM hitched_surrey ORDER BY 1,2,3;

SURREY_NAME                     HITCH_POS HORSE_NAME
------------------------------ ---------- ------------------------------
Plain                                   1 Dale
Plain                                   2 Daleness
Plain                                   3 Mabel
Plain                                   4 Son of Rainbow

The test worked by moving Mabel to position #3.

INSTEAD OF TRIGGER for UPDATE

The laziest way to implement support for the UPDATE command is to do a DELETE of the old data followed by an INSERT of the new data. This is the way we will follow. We will combine the code from the two (2) triggers above, and introduce more maintenance problems.

CREATE OR REPLACE TRIGGER pulls_surrey_update
  INSTEAD OF UPDATE ON pulls_surrey
  BEGIN
    IF :new.horse_team IS NULL THEN
      RAISE_APPLICATION_ERROR( -20015, 'The horse team cannot be NULL.', FALSE );
    END IF;
    IF :new.horse_team.count  4 THEN
      RAISE_APPLICATION_ERROR( -20011, 'There must be four (4) horses in a team.', FALSE );
    END IF;
    DELETE FROM hitched_surrey
      WHERE surrey_name = :old.surrey_name;
    FOR l_idx IN 1..:new.horse_team.count
    LOOP
      INSERT INTO hitched_surrey( surrey_name, hitch_pos, horse_name )
        VALUES( :new.surrey_name, l_idx, :new.horse_team( l_idx ) );
    END LOOP;
  END pulls_surrey_update;
/
SHOW ERRORS

Let’s test this trigger out by moving the team of horses from the fancy surrey to the plain one.

UPDATE pulls_surrey SET surrey_name = 'Plain' WHERE surrey_name  = 'Fancy';
-- Check that the team is still visible through the view
SELECT object_value FROM pulls_surrey;
-- Check the order of horses in the underlying table
SELECT surrey_name, hitch_pos, horse_name FROM hitched_surrey ORDER BY 1,2,3;
-- Revert back to the original data
ROLLBACK;

The output from the last two (2) SELECT statements is:

SQL> SELECT object_value FROM pulls_surrey;

OBJECT_VALUE(SURREY_NAME, HORSE_TEAM)
--------------------------------------------------------------------------------------

TEAM_PULLS_SURREY('Plain', SURREY_TEAM('Mabel', 'Dale', 'Daleness', 'Son of Rainbow'))

SQL> SELECT surrey_name, hitch_pos, horse_name FROM hitched_surrey ORDER BY 1,2,3;

SURREY_NAME                     HITCH_POS HORSE_NAME
------------------------------ ---------- ------------------------------
Plain                                   1 Mabel
Plain                                   2 Dale
Plain                                   3 Daleness
Plain                                   4 Son of Rainbow
Combined INSTEAD OF TRIGGER

To reduce the code duplication introduced above, we create just one (1) trigger and use the DELETING, INSERTING, and UPDATING keywords in Coding the Trigger Body to determine the reason the trigger was fired:

-- Remove the old triggers
DROP TRIGGER pulls_surrey_delete;
DROP TRIGGER pulls_surrey_insert;
DROP TRIGGER pulls_surrey_update;
-- Create the new combined trigger
CREATE OR REPLACE TRIGGER pulls_surrey_dml
  INSTEAD OF DELETE OR INSERT OR UPDATE ON pulls_surrey
  BEGIN
    -- Check the new values for validity
    IF inserting OR updating THEN
      IF :new.horse_team IS NULL THEN
        RAISE_APPLICATION_ERROR( -20015, 'The horse team cannot be NULL.', FALSE );
      END IF;
      IF :new.horse_team.count  4 THEN
        RAISE_APPLICATION_ERROR( -20011, 'There must be four (4) horses in a team.', FALSE );
      END IF;
    END IF;
    -- Remove the old data 
    IF deleting OR updating THEN
      DELETE FROM hitched_surrey
        WHERE surrey_name = :old.surrey_name;
    END IF;
    -- Add the new data
    IF inserting OR updating THEN
      FOR l_idx IN 1..:new.horse_team.count
      LOOP
        INSERT INTO hitched_surrey( surrey_name, hitch_pos, horse_name )
          VALUES( :new.surrey_name, l_idx, :new.horse_team( l_idx ) );
      END LOOP;
    END IF;
  END pulls_surrey_dml;
/
SHOW ERRORS

Rerunning the test cases above produce the same results.

Conclusion

We were able to implement the logical design for pulling a surrey by a team of horses by increasing the scope of the physical design by including the hitching of horses to the surrey. The hitching was used to create an object view of a team of horses pulling a surrey. We were able to maintain the abstraction of having a team of horses by using an INSTEAD OF TRIGGER to convert DML on an object view to DML on the underlying table.

However, we introduced a maintenance problem by having a cardinality constraint in the view definition and in the trigger definition.

Advertisements

One thought on “Nordic (14)

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