Nordic (10)


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

Associations vs. Domain Integrity

We are now at slide #57 (Associations vs. Domain Integrity).

Entities

In Nordic (7), we have the following physical entities:

Associations (entity to entity)

Again, in Nordic (7), we used a trigger, two (2) referential integrity constraints, and a table to implement this.

Domain Integrity (attribute lookups)

In Nordic (8), we enforced domain integrity for the ANIMAL_STATE and HUMAN_STATE types through object constructors and through a trigger.

Associations Matrix & StateFlow

We are now at slide #58 (Associations Matrix & StateFlow).

We will use the knowledge gained in Nordic (8) for working with state objects.

SURREY_STATEFLOW Type

First, let’s create the type definition and implementation for the state of the surrey (called SURREY_STATEFLOW). This is similar to that used for ANIMAL_STATE, except that no sub-typing is allowed (enforced by the FINAL keyword). We are using the CREATE TYPE and CREATE TYPE BODY commands. The RAISE_APPLICATION_ERROR procedure is used to raise a user exception.

CREATE OR REPLACE TYPE surrey_stateflow AS OBJECT (
  current_state VARCHAR2(30),
  CONSTRUCTOR FUNCTION surrey_stateflow(
    current_state VARCHAR2
  ) RETURN SELF AS RESULT
)
FINAL;
/
SHOW ERRORS
CREATE OR REPLACE TYPE BODY surrey_stateflow IS
  CONSTRUCTOR FUNCTION surrey_stateflow(
    current_state VARCHAR2
  ) RETURN SELF AS RESULT IS
    BEGIN
    IF current_state IN ( 'Broken', 'Working' ) THEN
      self.current_state := current_state;
    ELSE
      RAISE_APPLICATION_ERROR( -20008, current_state || ' is not a valid surrey state', FALSE );
    END IF;
    RETURN;
  END surrey_stateflow;
END;
/
SHOW ERRORS

The SQL*Plus command SHOW ERRORS displays any errors in the previous command.

Change the SURREY Type

We will add the new attribute, SURREY_STATEFLOW, to the SURREY type through the use of the ALTER TYPE command with the CASCADE option:

ALTER TYPE surrey
  ADD ATTRIBUTE state surrey_stateflow
  CASCADE;

SURREY Persistent Storage

The SURREYS table is empty. There is no need to modify existing data.

However, we need to add a NOT NULL integrity constraint on the STATE as an out of line constraint through the ALTER TABLE command.

ALTER TABLE surreys
  ADD CONSTRAINT surreys_state_nn
    CHECK ( state IS NOT NULL )
/

There is no need to add a trigger because the state transition graph is very simple, and idempotent transitions (“Broken” -> “Broken”) should not be prohibited anyway.

Implement HUMAN type

Back in , we had left the type implementation for the HUMAN type undefined. Now is our chance to rectify this.

CREATE OR REPLACE TYPE BODY human IS
  MEMBER PROCEDURE drives (
    my_surrey surrey
  ) AS
  BEGIN
    NULL;
  END drives;
  
  MEMBER PROCEDURE fixes (
    my_surrey surrey
  ) AS
  BEGIN
    UPDATE surreys
      SET state = NEW surrey_stateflow( 'Working' )
      WHERE name = my_surrey.name;
  END fixes;      
END;
/
SHOW ERRORS

Here the NULL PL/SQL statement defines a dummy body for the DRIVES procedure.

In the FIXES procedure, we are using the UPDATE command.

Testing the Implementation

Let’s check existing data in the SURREYS table by using the OBJECT_VALUE pseudo-column:

SQL> SELECT object_value FROM surreys;

OBJECT_VALUE(NAME, STATE(CURRENT_STATE))
--------------------------------------------------------------------------------
SURREY('Mary Lou''s', SURREY_STATEFLOW('Broken'))

Mary Lou’s surrey is broken. So she asks Billy Joe to fix it by treating him as a guy through the use of the TREAT function. As with all guys, Billy Joe goes through a complicated procedure to do the simplest thing:

DECLARE
  l_guy guy;
  l_surrey surrey;
BEGIN
  SELECT
      TREAT( object_value AS guy )
    INTO
      l_guy
    FROM
      animals
    WHERE
      name = 'Billy Joe';
  SELECT
      object_value
    INTO
      l_surrey
    FROM
      surreys
    WHERE
      name = 'Mary Lou''s';
  l_guy.fixes( l_surrey );
END;
/

And Mary Lou checks up on Billy Joe after he said was done:

SQL> SELECT object_value FROM surreys;

OBJECT_VALUE(NAME, STATE(CURRENT_STATE))
--------------------------------------------------
SURREY('Mary Lou''s', SURREY_STATEFLOW('Working'))

Mary Lou is happy. So she

COMMIT;
Advertisements

2 thoughts on “Nordic (10)

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