Nordic (6)


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

Fixing Design Problems

At the end of the previous article, we had listed two (2) problems with the current physical design of the COURTSHIPS table.

We are going to remove the constructors and put in some more meaningful error messages.

Courtship Type Version Eight

(We need to be more careful in our design versioning.)

Clean Up Previous Design

Although we can use the ALTER TYPE command to remove the interface for the constructors, and the DROP TYPE BODY command to remove the constructors because of Oracle’s type evolution, we would drop the definitions in order to keep the creation scripts because we doing physical design.

connect ordbms/*******  -- password suppressed
DROP TABLE courtships;  -- remove dependent objects
DROP TYPE courtship;    -- drops type interface and body

Courtship Type Design Version Eight

We are back to the Going Courting Again! of the Fourth Design Attempt in Nordic (4) with a very simple type design for COURTSHIP.

CREATE TYPE courtship AS OBJECT (
  guy_name  VARCHAR2(30),
  girl_name VARCHAR2(30)
);
/

We have solved one (1) problem by removing the duplicate code by not defining the constructors because the constructors could be bypassed.

Persistent of Courtship Version Eight

The same table definition from Nordic (5) is used.

CREATE TABLE courtships OF courtship
  (
    CONSTRAINT courtships_pk
      PRIMARY KEY (
        guy_name,
        girl_name
      ),
    CONSTRAINT courtships_guy_name_nn
      CHECK (
        guy_name IS NOT NULL
      ),
    CONSTRAINT courtships_girl_name_nn
      CHECK (
         girl_name IS NOT NULL
      ),
    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
/

Courtships Trigger Design Version Eight

We are going to define our own error messages by using the RAISE_APPLICATION_ERROR procedure in the trigger code from Nordic (5).

CREATE OR REPLACE TRIGGER courtships_pre
  BEFORE INSERT OR
    UPDATE OF guy_name, girl_name
  ON courtships
  FOR EACH ROW
  DECLARE
    l_valid_name
       VARCHAR2(30)
        := NULL;
    l_bad_data
      BOOLEAN
        := FALSE;
    l_error_message
      VARCHAR2(2048)
        := NULL;
  BEGIN
    -- Check that guy_name is really the name of a guy
    BEGIN
      SELECT
          name
        INTO
          l_valid_name
        FROM
          animals ptr
        WHERE
            name = :new.guy_name
          AND
            VALUE( ptr ) IS OF TYPE( guy );
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        l_bad_data := TRUE;
        l_error_message := :new.guy_name || ' is not a guy';
    END;
    -- Check that girl_name is really the name of a girl
    BEGIN
      SELECT
          name
        INTO
          l_valid_name
        FROM
          animals ptr
        WHERE
            name = :new.girl_name
          AND
            VALUE( ptr ) IS OF TYPE( girl );
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        l_bad_data := TRUE;
        IF l_error_message IS NULL THEN
          l_error_message := :new.girl_name || ' is not a girl';
        ELSE
          l_error_message := l_error_message || ', and ' ||
            :new.girl_name || ' is not a girl';
        END IF;
    END;
    IF l_bad_data THEN
      RAISE_APPLICATION_ERROR( -20000, l_error_message, FALSE );
    END IF;
END courtships_pre;
/

The internal code of the trigger has changed. We are actively looking for an exception (NO_DATA_FOUND and handling it. Any other exceptions are left to the calling code to handle.

Physical Design Test Suite Version Eight

This is the same test case from Nordic (5).

-- Test Suite for Courtships Table (V8)
-- Remove all previous test runs
TRUNCATE TABLE courtships;
-- Test the constructors
-- This should succeed
INSERT INTO courtships VALUES(
  NEW courtship( 'Jim Bob', 'Mary Lou' )
  );
-- This should fail
INSERT INTO courtships VALUES(
  NEW courtship( 'Mary Lou', 'Jim Bob' )
  );
-- This should fail
INSERT INTO courtships VALUES(
  NEW courtship( 'Billy Joe' )
  );
-- This should fail
INSERT INTO courtships VALUES(
  NEW courtship( 'Billy Joe', 'Mabel' )
  );
-- Bypass the constructors
-- This should fail
INSERT INTO courtships VALUES( 'Jim Bob', 'Mary Lou' );
-- This should fail
INSERT INTO courtships VALUES( 'Billy Joe' );
-- This should fail
INSERT INTO courtships VALUES( 'Billy Joe', 'Mabel' );
-- Test data modification
-- This should fail
UPDATE courtships
  SET girl_name = 'Mabel'
  WHERE girl_name = 'Mary Lou';
-- Only one (1) row should appear
-- Jim Bob   Mary Lou
SELECT * FROM courtships;
-- All done.  Save the results
COMMIT;

The results of the test run are:

SQL> -- Test Suite for Courtships Table (V8)
SQL> -- Remove all previous test runs
SQL> TRUNCATE TABLE courtships;

Table truncated.

SQL> -- Test the constructors
SQL> -- This should succeed
SQL> INSERT INTO courtships VALUES(
  2    NEW courtship( 'Jim Bob', 'Mary Lou' )
  3    );

1 row created.

SQL> -- This should fail
SQL> INSERT INTO courtships VALUES(
  2    NEW courtship( 'Mary Lou', 'Jim Bob' )
  3    );
INSERT INTO courtships VALUES(
          *
ERROR at line 1:
ORA-20000: Mary Lou is not a guy, and Jim Bob is not a girl
ORA-06512: at "ORDBMS.COURTSHIPS_PRE", line 52
ORA-04088: error during execution of trigger 'ORDBMS.COURTSHIPS_PRE'


SQL> -- This should fail
SQL> INSERT INTO courtships VALUES(
  2    NEW courtship( 'Billy Joe' )
  3    );
  NEW courtship( 'Billy Joe' )
      *
ERROR at line 2:
ORA-02315: incorrect number of arguments for default constructor


SQL> -- This should fail
SQL> INSERT INTO courtships VALUES(
  2    NEW courtship( 'Billy Joe', 'Mabel' )
  3    );
INSERT INTO courtships VALUES(
          *
ERROR at line 1:
ORA-20000: Mabel is not a girl
ORA-06512: at "ORDBMS.COURTSHIPS_PRE", line 52
ORA-04088: error during execution of trigger 'ORDBMS.COURTSHIPS_PRE'


SQL> -- Bypass the constructors
SQL> -- This should fail
SQL> INSERT INTO courtships VALUES( 'Jim Bob', 'Mary Lou' );
INSERT INTO courtships VALUES( 'Jim Bob', 'Mary Lou' )
*
ERROR at line 1:
ORA-00001: unique constraint (ORDBMS.COURTSHIPS_PK) violated


SQL> -- This should fail
SQL> INSERT INTO courtships VALUES( 'Billy Joe' );
INSERT INTO courtships VALUES( 'Billy Joe' )
                               *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected ORDBMS.COURTSHIP got CHAR


SQL> -- This should fail
SQL> INSERT INTO courtships VALUES( 'Billy Joe', 'Mabel' );
INSERT INTO courtships VALUES( 'Billy Joe', 'Mabel' )
          *
ERROR at line 1:
ORA-20000: Mabel is not a girl
ORA-06512: at "ORDBMS.COURTSHIPS_PRE", line 52
ORA-04088: error during execution of trigger 'ORDBMS.COURTSHIPS_PRE'


SQL> -- Test data modification
SQL> -- This should fail
SQL> UPDATE courtships
  2    SET girl_name = 'Mabel'
  3    WHERE girl_name = 'Mary Lou';
UPDATE courtships
          *
ERROR at line 1:
ORA-20000: Mabel is not a girl
ORA-06512: at "ORDBMS.COURTSHIPS_PRE", line 52
ORA-04088: error during execution of trigger 'ORDBMS.COURTSHIPS_PRE'


SQL> -- Only one (1) row should appear
SQL> -- Jim Bob   Mary Lou
SQL> SELECT * FROM courtships;

GUY_NAME                       GIRL_NAME
------------------------------ ------------------------------
Jim Bob                        Mary Lou

SQL> -- All done.  Save the results
SQL> COMMIT;

Commit complete.

The error messages are more meaningful. Although not all the error messages are immediately obvious, they, however, relate back to the physical design. Depending on the requirements of the evolving application design, we could leave the effort to higher layers.

Conclusion

Looks like we have managed to implement all of the logical design criteria in a physical design. We have also developed a suite of test cases to validate the physical design.

However, we have a remaining issue: the COURT function in the GUY. This was dropped from the physical design while we worked on the COURTSHIP type.

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