Nordic (13)


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

Aggregation Associations (Collections)

We are now at slide #71 of the presentation.

Surrey with four horses

We are now at slide #72 of the presentation.

We will use the VARRAY data type.

This means that the relationship table, PULLS_SURREY, changes. The predicate for this table is no longer:

Surrey surrey_name is pulled by the horse called horse_name.

But, the new predicate for the table is:

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

Type Design

Instead of using a simple relational table to represent this relationship between the surrey and the horses, we need to create a more complex type with a user defined type for one of the attributes.

This can be implemented by a type, called SURREY_TEAM. The SQL for doing this, by using the CREATE TYPE command, is:

CREATE OR REPLACE TYPE surrey_team IS VARRAY( 4 ) OF VARCHAR2( 30 );
/

The association class is now expressed as:

CREATE OR REPLACE TYPE team_pulls_surrey AS OBJECT (
  surrey_name
    VARCHAR2(30),
  horse_team
    surrey_team
)
FINAL;
/

We will not use the object constructors to enforce some parts of data integrity because we found in Nordic (5) that the constructor can be bypassed.

Table Design

Now, we need to modify the PULLS_SURREY table. This means that the table has to be dropped and recreated because we are going to replace part of the composite key. When the table is dropped, the trigger, PULLS_SURREY_PRE, will be dropped as well.

In dropping the primary key, what are we going to replace it with? In the old version (see Nordic (11) for details), the constraints implied that only certain horses can pull certain surreys. This was a many-to-many relationship. These constraints failed to correctly implement the logical model as stated in the original predicate for the table.

With this change to have a team of horses, the constraints on the PULLS_SURREY table should imply the present. If Mabel and three (3) horses are currently hitched to the fancy surrey, then Mabel should only appear once in the PULLS_SURREY table.

As a surrey can only be pulled by one (1) team of horses, then the name of surrey can only appear once in the table. This means that the name of the surrey has the following constraints:

  1. The name of the surrey must be non-null. This requires a NOT NULL integrity constraint.
  2. The name of the surrey must be valid. This requires a referential integrity constraint to the SURREYS table.
  3. The surrey can only be pulled by at most one (1) team of horses. This means there is a unique column value constraint.

The first and third constraints are implied with the PRIMARY KEY constraint.

The table design is implemented with the DROP TABLE command followed by the CREATE TABLE command. The design of this table is similar to the COURTSHIPS table in Nordic (7).

DROP TABLE pulls_surrey;
CREATE TABLE pulls_surrey OF team_pulls_surrey (
  CONSTRAINT pulls_surrey_pk
    PRIMARY KEY ( surrey_name ),
  CONSTRAINT pulls_surrey_surrey_name_fk
    FOREIGN KEY ( surrey_name )
    REFERENCES surreys( name ),
  CONSTRAINT pulls_surrey_horse_team_nn
    CHECK ( horse_team IS NOT NULL )
  )
  OBJECT IDENTIFIER IS PRIMARY KEY
/

We have used the OBJECT IDENTIFIER clause of the CREATE TABLE command to use the primary key instead of the system generated object id.

Note that we have specified only a NOT NULL constraint on the horse_team column because the data type is a collection becuase UNIQUE and FOREIGN KEY ones are not allowed. In this design, we have violated First Normal Form by having a multi-valued entity in a column. To validate the entries in the horse team against the known horses which are in the ANIMALS table, we will have to use a trigger which is created through the use of a CREATE TRIGGER command. This code is based upon the COURTSHIPS_PRE trigger from Nordic (7).

CREATE OR REPLACE TRIGGER pulls_surrey_horse_team_pre
  BEFORE INSERT
    OR UPDATE OF horse_team
  ON pulls_surrey
  FOR EACH ROW
  WHEN ( new.horse_team IS NOT NULL )
  DECLARE
    l_valid_name
      animals.name%TYPE;
    l_horse_name
      animals.name%TYPE;
  BEGIN
    -- Ensure there are four (4) elements in the horse team array
    IF :new.horse_team.count  4 THEN
      RAISE_APPLICATION_ERROR( -20011, 'There must be four (4) horses in a team.', FALSE );
    END IF;
    -- Ensure that all the elements in the array are not null
    FOR l_horse_idx IN 1..:new.horse_team.count
    LOOP
      IF :new.horse_team( l_horse_idx ) IS NULL THEN
        RAISE_APPLICATION_ERROR( -20012, 'There cannot be NULLs in a team.', FALSE );
      END IF;
    END LOOP;
    -- Ensure that there are no duplicate horses in the array
    FOR l_outer_idx IN 1..:new.horse_team.count-1
    LOOP
      FOR l_inner_idx IN l_outer_idx+1..:new.horse_team.count
      LOOP
        IF :new.horse_team( l_outer_idx ) = :new.horse_team( l_inner_idx ) THEN
          RAISE_APPLICATION_ERROR( -20013, 'There cannot be duplicate horses in a team.', FALSE );
        END IF;
      END LOOP;
    END LOOP;
    -- Ensure that all names are for valid, healthy horses
    FOR l_horse_idx IN 1..:new.horse_team.count
    LOOP
      l_horse_name := :new.horse_team( l_horse_idx );
      SELECT
          name
        INTO
          l_valid_name
        FROM
          animals ptr
        WHERE
            name = l_horse_name
          AND
            VALUE( ptr ) IS OF TYPE( horse )
          AND
            ptr.state.current_state = 'Healthy';
    END LOOP;
  EXCEPTION
    WHEN NO_DATA_FOUND
      THEN RAISE_APPLICATION_ERROR( -20014, l_horse_name || ' is not a healthy horse', FALSE );
  END pulls_surrey_horse_team_pre;
/
SHOW ERRORS

The RAISE_APPLICATION_ERROR procedure is used to create a user exception.

The FOR loop has upper bounds based on the number of elements in an array as returned by the COUNT function.

The SHOW ERRORS command displays any errors during compilation of the trigger.

The %TYPE allows us to reference the type details from another column in another table.

This trigger only allows healthy horses to be hitched up to the surrey.

This trigger is only fired for new values of the horse_team that are non-null. Without the WHEN clause, the following messages would appear:

INSERT INTO pulls_surrey VALUES( NULL, NULL )
 *
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at "ORDBMS.PULLS_SURREY_HORSE_TEAM_PRE", line 7
ORA-04088: error during execution of trigger
'ORDBMS.PULLS_SURREY_HORSE_TEAM_PRE'

Test Data

Let’s create some horses:

INSERT INTO animals( object_value ) VALUES( NEW horse( 'Phar Lap', NEW animal_state( 'Deceased' ) ) );
INSERT INTO animals( object_value ) VALUES( NEW horse( 'Rainbow', NEW animal_state( 'Ill' ) ) );
INSERT INTO animals( object_value ) VALUES( NEW horse( 'Son of Rainbow', NEW animal_state( 'Healthy' ) ) );
INSERT INTO animals( object_value ) VALUES( NEW horse( 'Dale', NEW animal_state( 'Healthy' ) ) );
INSERT INTO animals( object_value ) VALUES( NEW horse( 'Daleness', NEW animal_state( 'Healthy' ) ) );
COMMIT;

The test data suite is:

-- Bypass the object constructor
-- This should fail with ORA-01400
INSERT INTO pulls_surrey VALUES( NULL, NULL );
-- This should fail with ORA-02290
INSERT INTO pulls_surrey VALUES( 'Fancy', NULL );
-- This should fail with ORA-20011
INSERT INTO pulls_surrey VALUES( 'Fancy', NEW surrey_team( 'Mabel' ) );
-- This should fail with ORA-20012
INSERT INTO pulls_surrey VALUES( 'Fancy', NEW surrey_team( 'Mabel', NULL, NULL, NULL ) );
-- This should fail with ORA-20013
INSERT INTO pulls_surrey VALUES( 'Fancy', NEW surrey_team( 'Phar Lap', 'Rainbow', 'Mabel', 'Mabel' ) );
-- This should fail with ORA-20014
INSERT INTO pulls_surrey VALUES( 'Fancy', NEW surrey_team( 'Phar Lap', 'Rainbow', 'Mabel', 'Dale' ) );
-- This should work
INSERT INTO pulls_surrey VALUES( 'Fancy', NEW surrey_team( 'Daleness', 'Son of Rainbow', 'Mabel', 'Dale' ) );
-- This should fail with ORA-00001
INSERT INTO pulls_surrey VALUES( 'Fancy', NEW surrey_team( 'Daleness', 'Son of Rainbow', 'Mabel', 'Dale' ) );
-- This should fail with ORA-02291
INSERT INTO pulls_surrey VALUES( 'Missing', NEW surrey_team( 'Daleness', 'Son of Rainbow', 'Mabel', 'Dale' ) );
-- Use the object constructor
-- This should fail with ORA-01400
INSERT INTO pulls_surrey( OBJECT_VALUE )
  VALUES( NEW team_pulls_surrey( NULL, NULL ) );
-- This should fail with ORA-02290
INSERT INTO pulls_surrey( OBJECT_VALUE )
  VALUES( NEW team_pulls_surrey( 'Plain', NULL ) );
-- This should fail with ORA-20011
INSERT INTO pulls_surrey( OBJECT_VALUE )
  VALUES( NEW team_pulls_surrey( 'Plain', NEW surrey_team( 'Mabel' ) ) );
-- This should fail with ORA-20012
INSERT INTO pulls_surrey( OBJECT_VALUE )
  VALUES( NEW team_pulls_surrey( 'Plain', NEW surrey_team( 'Mabel', NULL, NULL, NULL ) ) );
-- This should fail with ORA-20013
INSERT INTO pulls_surrey( OBJECT_VALUE )
  VALUES( NEW team_pulls_surrey( 'Plain', NEW surrey_team( 'Phar Lap', 'Rainbow', 'Mabel', 'Mabel' ) ) );
-- This should fail with ORA-20014
INSERT INTO pulls_surrey( OBJECT_VALUE )
  VALUES( NEW team_pulls_surrey( 'Plain', NEW surrey_team( 'Phar Lap', 'Rainbow', 'Mabel', 'Dale' ) ) );
-- This should work
INSERT INTO pulls_surrey( OBJECT_VALUE )
  VALUES( NEW team_pulls_surrey( 'Plain', NEW surrey_team( 'Daleness', 'Son of Rainbow', 'Mabel', 'Dale' ) ) );
-- This should fail with ORA-00001
INSERT INTO pulls_surrey( OBJECT_VALUE )
  VALUES( NEW team_pulls_surrey( 'Plain', NEW surrey_team( 'Daleness', 'Son of Rainbow', 'Mabel', 'Dale' ) ) );
-- This should fail with ORA-02291
INSERT INTO pulls_surrey( OBJECT_VALUE )
  VALUES( NEW team_pulls_surrey( 'Missing', NEW surrey_team( 'Daleness', 'Son of Rainbow', 'Mabel', 'Dale' ) ) );

To check the results, we use the OBJECT_VALUE pseudo-column:

SET LINESIZE 120
SELECT object_value FROM pulls_surrey;

The results are:

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

Undo the test suite by using the ROLLBACK command:

ROLLBACK;

Conclusion

We have came up against a limitation of the relational model as implemented by Oracle: cross-checking between rows of a table is not possible to be implemented as an integrity constraint. As we have seen in the test suite, we are able to hitch the same team to two different surreys at the same time. So, we are not able to fully implement the logical model.

Advertisements

2 thoughts on “Nordic (13)

  1. Nordic (14) « Yet Another OCM

  2. 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