Nordic (5)


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

Problems with Courtships Table

Since there is no PRIMARY KEY constraint on the COURTSHIPS, Jim Bob can court Mary Lou again in a separate courtship. Since this is not logically possible, we need to find a way to restrict the maximum number of courtships between two (2) people to one (1).

We were able to enforce this restriction by having the primary key for the COURTSHIPS table being the names of the parties involved. This was at the cost of the data integrity in which anyone court anyone else irrespective of gender, or species. Since this is Okalahoma, not California, this was not on.

There has to some way to combine these two (2) ways. As we found in the previous article, we cannot use the REF data type in a primary key constraint.

Courtship Design Version Six

We are back at the same design we had in the Design Review of Nordic (4). As usual with type evolution in Oracle, we have to do a clean up first.

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

Type Interface

In this attempt, we are going to use object constructors to enforce the business rules for the COURTSHIP type. Because the attributes for a type are optional, we had to define three (3) different constructors based on the number of parameters passed: zero (0); one (1); or, two (2).

CREATE OR REPLACE TYPE courtship AS OBJECT (
  guy_name  VARCHAR2(30),
  girl_name VARCHAR2(30),
  CONSTRUCTOR FUNCTION courtship 
    RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION courtship (
    guy_name  VARCHAR2
  ) RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION courtship (
    guy_name  VARCHAR2,
    girl_name VARCHAR2
  ) RETURN SELF AS RESULT
);
/

Note that the names of the parameters for the two (2) parameter version of the constructor match the names of the attributes. If we did not do this, we would get the following error message whenever we tried to use a constructor with different parameter names:

ORA-06553: PLS-307: too many declarations of 'COURTSHIP' match this call

The reason for this is that whenever a type is defined, a constructor with the names of the atrributes is also defined. Note how we were able to construct objects earlier without having defined any constructor to do so. Parameter names form part of the function’s signature.

Type Implementation

In the following definitions of the functions, only the two (2) parameter function attempts to set the attribute values to non-null values:

CREATE OR REPLACE TYPE BODY courtship IS
  CONSTRUCTOR FUNCTION courtship (
      guy_name VARCHAR2,
      girl_name VARCHAR2
  ) RETURN SELF AS RESULT
  IS
    CURSOR guy_csr ( p_name VARCHAR2 ) IS
      SELECT
          name
        FROM
          animals ptr
        WHERE
            name = p_name
          AND
            VALUE( ptr ) IS OF TYPE( guy );
    CURSOR girl_csr ( p_name VARCHAR2 ) IS
      SELECT
          name
        FROM
          animals ptr
        WHERE
            name = p_name
          AND
            VALUE( ptr ) IS OF TYPE( girl );
  BEGIN
    FOR l_guy IN guy_csr( guy_name ) LOOP
      self.guy_name := l_guy.name;
    END LOOP;
    FOR l_girl IN girl_csr( girl_name ) LOOP
      self.girl_name := l_girl.name;
    END LOOP;
    RETURN;
  END courtship;
  CONSTRUCTOR FUNCTION courtship (
      guy_name VARCHAR2
  ) RETURN SELF AS RESULT
  IS BEGIN
    self.guy_name  := NULL;
    self.girl_name := NULL;
    RETURN;
  END courtship;
  CONSTRUCTOR FUNCTION courtship 
  RETURN SELF AS RESULT
  IS BEGIN
    self.guy_name  := NULL;
    self.girl_name := NULL;
    RETURN;
  END courtship;
END;
/

Type Persistence

The table definition is the same as in Design Review of Nordic (4).

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
/

Test the Design

Let’s see if true love can bloom!

INSERT INTO courtships VALUES(
  NEW courtship( 'Jim Bob', 'Mary Lou' )
  );
COMMIT;

This works! Great!

Now for the unwholesome data:

INSERT INTO courtships VALUES(
  NEW courtship( 'Billy Joe' )
  );

Because the constructor sets the attributes to NULL, and there are NOT NULL constraints, this fails with:

  NEW courtship( 'Billy Joe' )
      *
ERROR at line 2:
ORA-01400: cannot insert NULL into ("ORDBMS"."COURTSHIPS"."GUY_NAME")

And more unwholesome data:

INSERT INTO courtships VALUES(
  NEW courtship( 'Billy Joe', 'Mabel' )
  );

Because the query for the girl’s name restricts the selection to those animals of type GIRL, a NULL is returned. The NOT NULL constraint causes the INSERT to then fail with:

  NEW courtship( 'Billy Joe', 'Mabel' )
      *
ERROR at line 2:
ORA-01400: cannot insert NULL into ("ORDBMS"."COURTSHIPS"."GIRL_NAME")

This seems pretty good. Can we make it fail? What if we try:

INSERT INTO courtships VALUES(
  'Billy Joe', 'Mabel'
  );

This works.

1 row created.

SQL> SELECT * FROM courtships;

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

Looks like we are bypassing the constructors. This is serious.

Trigger

The next step in the physical design is to try triggers. We will use the CREATE TRIGGER command to accomplish this.

Set User Privileges

Before we do so, the ordbms user needs to have the CREATE TRIGGER system privilege by means of the GRANT command.

SQL> connect / as sysdba
Connected.
SQL> GRANT CREATE TRIGGER TO ordbms;

Grant succeeded.

SQL> connect ordbms
Enter password:
Connected.

Code the Trigger

Now to create the trigger. This uses the same code as the type constructor above.

CREATE OR REPLACE TRIGGER courtships_pre
  BEFORE INSERT OR
    UPDATE OF guy_name, girl_name
  ON courtships
  FOR EACH ROW
  DECLARE
    CURSOR guy_csr ( p_name VARCHAR2 ) IS
      SELECT
          name
        FROM
          animals ptr
        WHERE
            name = p_name
          AND
            VALUE( ptr ) IS OF TYPE( guy );
    CURSOR girl_csr ( p_name VARCHAR2 ) IS
      SELECT
          name
        FROM
          animals ptr
        WHERE
            name = p_name
          AND
            VALUE( ptr ) IS OF TYPE( girl );
    l_valid_name
       VARCHAR2(30)
        := NULL;
  BEGIN
    FOR l_guy IN guy_csr( :new.guy_name ) LOOP
      l_valid_name := l_guy.name;
    END LOOP;
    :new.guy_name := l_valid_name;
    l_valid_name  := NULL;
    FOR l_girl IN girl_csr( :new.girl_name ) LOOP
      l_valid_name := l_girl.name;
    END LOOP;
    :new.girl_name := l_valid_name;
END courtships_pre;
/

Here we are using two (2) explicit cursors with parameters: GUY_CSR and GIRL_CSR. Each of these explicit cursors is queried by means of a FOR loop, the body of which is executed if data is returned by the cursor query. In other words, the local variable, l_valid_name, is only set to a non-null value if the query returns data.

This trigger is fired for each row during the INSERT command, when an UPDATE command is applied one or both of the GUY_NAME and GIRL_NAME columns.

Test the Trigger

First, clear the COURTSHIPS table of all data by using the TRUNCATE command.

TRUNCATE TABLE courtships;

Now, apply the test data, and see the results:

INSERT INTO courtships VALUES(
  NEW courtship( 'Jim Bob', 'Mary Lou' )
  );
INSERT INTO courtships VALUES(
  NEW courtship( 'Billy Joe' )
  );
INSERT INTO courtships VALUES(
  NEW courtship( 'Billy Joe', 'Mabel' )
  );
INSERT INTO courtships VALUES(
  'Billy Joe', 'Mabel'
  );
SELECT * FROM courtships;

The results are:

SQL> INSERT INTO courtships VALUES(
  2    NEW courtship( 'Jim Bob', 'Mary Lou' )
  3    );

1 row created.

SQL> INSERT INTO courtships VALUES(
  2    NEW courtship( 'Billy Joe' )
  3    );
INSERT INTO courtships VALUES(
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("ORDBMS"."COURTSHIPS"."GUY_NAME")


SQL> INSERT INTO courtships VALUES(
  2    NEW courtship( 'Billy Joe', 'Mabel' )
  3    );
INSERT INTO courtships VALUES(
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("ORDBMS"."COURTSHIPS"."GIRL_NAME")


SQL> INSERT INTO courtships VALUES(
  2    'Billy Joe', 'Mabel'
  3    );
INSERT INTO courtships VALUES(
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("ORDBMS"."COURTSHIPS"."GIRL_NAME")


SQL> SELECT * FROM courtships;

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

Our test data tells us that only good data is being stored in the COURTSHIPS table.

Can we byapss the checks if we change valid data to invalid data by means of the UPDATE command?

UPDATE courtships
  SET girl_name = 'Mabel'
  WHERE girl_name = 'Mary Lou'
/

The good news is that this raises the following error:

  SET girl_name = 'Mabel'
      *
ERROR at line 2:
ORA-01407: cannot update ("ORDBMS"."COURTSHIPS"."GIRL_NAME") to NULL

Problems with Latest Physical Design

There are, at least, two (2) problems with the current physical design:

  1. Obscure error messages.
  2. Duplicate code that is being called twice in some cases.

Obscure Error Messages

What has NULL got to do with anything? Unless one goes into the inner workings of the code, one cannot see where NULL values are coming from.

Duplicate Code

The code for checking the correct type of referenced objects is duplicated in the constructor and in the trigger.

Advertisements

3 thoughts on “Nordic (5)

  1. Nordic (13) « Yet Another OCM

  2. Nordic (6) « Yet Another OCM

  3. Nordic (8) « 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