Nordic (7)


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

Completing the Physical Design of Courtship Type

At this stage, we have completed the physical design of the COURTSHIPS table, and the COURTSHIP type. However, there is an unresolved circular dependency: the GUY type has a member function that returns the COURTSHIP type.

Physical Design Clean Up

Using the same reasoning about maintaining the creation scripts as we did in Nordic (6), we continue with:

CONNECT ordbms/*******   -- password suppressed
-- remove persistent storage for types (child tables first)
DROP TABLE courtships;
DROP TABLE animals;
DROP TABLE surreys;
DROP TABLE buildings;
-- remove dependent types on COURTSHIP type
DROP TYPE guy;
-- remove type definition
DROP TYPE courtship; 
-- remove type definitions for ANIMAL and all sub-types
DROP TYPE girl;
DROP TYPE human;
DROP TYPE horse;
DROP TYPE animal;
-- remove type definition
DROP TYPE surrey;
-- remove type definition
DROP TYPE building;

Physical Design Version Nine (9)

The entire physical design creation script is presented below. This is based on the physical design for most types from Nordic (4), with the original design for the GUY type restored, and the physical design for the COURTSHIP type from Nordic (6).

-- BUILDING Type with a single attribute
CREATE OR REPLACE TYPE building AS OBJECT (
  name VARCHAR2(30)
)
FINAL;
/
-- Persistent storage for BUILDING type
CREATE TABLE buildings
  OF building
  (
    CONSTRAINT
      buildings_pk
      PRIMARY KEY (
        name
      )
   )
   OBJECT IDENTIFIER IS PRIMARY KEY
;
-- SURREY Type with a single attribute
CREATE OR REPLACE TYPE surrey AS OBJECT (
  name VARCHAR2(30)
)
FINAL;
/
-- Persistent storage for SURREY type
CREATE TABLE surreys
  OF surrey
  (
    CONSTRAINT
      surreys_pk
      PRIMARY KEY (
        name
      )
   )
   OBJECT IDENTIFIER IS PRIMARY KEY
;
-- Begin the ANIMAL type hierarchy
CREATE OR REPLACE TYPE animal AS OBJECT (
  name VARCHAR2(30),
  MEMBER PROCEDURE uses (
    building_used building
  )
)
NOT FINAL;
/
-- HUMAN type
CREATE OR REPLACE TYPE human UNDER animal (
  MEMBER PROCEDURE drives (
    my_surrey surrey
  ),
  MEMBER PROCEDURE fixes (
    my_surrey surrey
  )
)
NOT FINAL;
/
-- HORSE type
CREATE OR REPLACE TYPE horse UNDER animal (
  MEMBER PROCEDURE pulls (
    my_surrey surrey
  )
)
FINAL;
/
-- GIRL type
CREATE OR REPLACE TYPE girl UNDER human (
)
FINAL;
/
-- Persistence for ANIMAL objects
CREATE TABLE animals
  OF animal
  (
    CONSTRAINT
      animals_pk
      PRIMARY KEY (
        name
      )
   )
   OBJECT IDENTIFIER IS PRIMARY KEY
;
-- Complete definition for COURTSHIP type
CREATE OR REPLACE TYPE courtship AS OBJECT (
  guy_name  VARCHAR2(30),
  girl_name VARCHAR2(30)
)
FINAL;
/
-- Persistent storage for COURTSHIP objects
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
/
-- GUY type
CREATE OR REPLACE TYPE guy UNDER human (
  MEMBER FUNCTION courts (
    my_girl girl
  ) RETURN courtship
)
FINAL;
/
-- Type Implementation for GUY type
CREATE OR REPLACE TYPE BODY guy IS
  MEMBER FUNCTION courts (
    my_girl girl
  ) RETURN courtship AS
    l_courtship
      courtship
        := NULL;
  BEGIN
    IF my_girl IS NOT NULL THEN
      l_courtship := NEW courtship( self.name, my_girl.name );
    END IF;
    RETURN l_courtship;
  END courts;
END;
/
-- COURTSHIPS Trigger
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;
/

As a production DBA, I have seen too many examples of shoddy implementation scripts where errors and warnings are a common occurrence. Here we have a clean back-out script, and a clean implementation script.

Suite of Test Cases

The TRUNCATE command cannot be used on a parent table otherwise the following message will ensue:

TRUNCATE TABLE animals
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

The final version of the test script is:

-- Remove any existing data (child tables first)
TRUNCATE TABLE courtships;
DELETE FROM animals;
COMMIT;
TRUNCATE TABLE surreys;
TRUNCATE TABLE buildings;
-- Load the BUILDINGS table
-- This should work
INSERT INTO buildings VALUES( building( 'Barn' ) );
COMMIT;
-- Load the ANIMALS table
-- These should work
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;
-- Test Suite for Courtships Table
-- 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';
-- All done.  Save the results
COMMIT;
-- Test Member Functions and Procedures
-- This should complete without any errors
DECLARE
  my_guy guy := NEW guy( 'Fred' );
  my_girl girl := NEW girl( 'Wilma' );
  my_courts courtship;
BEGIN
  my_courts := my_guy.courts( my_girl );
END;
/

Test Verification Script

This script is verify the correctness of the test suite.

-- Only one (1) row should appear
SELECT * FROM buildings;
-- Only four (4) rows should appear
SELECT * FROM animals;
-- No rows should appear
SELECT * FROM surreys;
-- Only one (1) row should appear
-- Jim Bob   Mary Lou
SELECT * FROM courtships;

Conclusion

Although the implementation details for some of the types have not been done yet, this is a good place to leave the physical design and move onto other slides in the presentation.

The association class in Oracle is not easy to implement. There were many pitfalls. The base classes were intuitively simple to define and persist.

Advertisements

2 thoughts on “Nordic (7)

  1. Nordic (10) « Yet Another OCM

  2. Nordic (11) « 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