NORDIC Schema for TopLink Demonstration (2)


Following on from NORDIC Schema for TopLink Demonstration (1), the more complex Animal type is now tackled.

Animals

Because the animals involve a complex hierarchy of types, the type information needs to be exposed in tables.

Animal Subtype

The ANIMAL_SUBTYPE table is built on a surrogate key and a candidate key for the name of the subtype with a flag to determine if the subtype can be instantiated. The subtype name is required to be in uppercase.

The external predicate for this table is:

animal_subtype( id, name, instantiable ) := Animal subtype #id is called name and can be instantiated when instantiable = ‘Y’.

CREATE TABLE animal_subtype (
  type_id
    NUMBER
    CONSTRAINT animal_subtype_pk
      PRIMARY KEY,
  name
    VARCHAR2( 30 )
    CONSTRAINT animal_subtype_name_nn
      NOT NULL
    CONSTRAINT animal_subtype_name_uk
      UNIQUE
    CONSTRAINT animal_subtype_name_ck
      CHECK (
        name = UPPER( name )
      ),
  instantiable
    CHAR( 1 )
    CONSTRAINT animal_subtype_instant_nn
      NOT NULL
    CONSTRAINT animal_subtype_instant_ck
      CHECK (
        instantiable IN ( 'Y', 'N' )
      )
);
/* Load the initial data */
INSERT INTO animal_subtype
  ( type_id, name, instantiable )
  VALUES( 1, 'ANIMAL', 'N' );
INSERT INTO animal_subtype
  ( type_id, name, instantiable )
  VALUES( 2, 'HORSE', 'Y' );
INSERT INTO animal_subtype
  ( type_id, name, instantiable )
  VALUES( 3, 'HUMAN', 'N' );
INSERT INTO animal_subtype
  ( type_id, name, instantiable )
  VALUES( 4, 'GUY', 'Y' );
INSERT INTO animal_subtype
  ( type_id, name, instantiable )
  VALUES( 5, 'GIRL', 'Y' );
COMMIT;

Animal State Subtype

The animal has two (2) attributes: name and state. In the original design, the state was a type hierarchy. Again, a table is used to define the possible subtypes in this hierarchy.

Each subtype has two (2) attributes: type_id and name. Both are candidate keys. The type_id is chosen as the primary key because it is immutable. The name is always in uppercase.

The external predicate for this table is:

animal_state_subtype( type_id, name ) := The animal state subtype #type_id is called name.

CREATE TABLE animal_state_subtype (
  type_id
    NUMBER
    CONSTRAINT animal_state_subtype_pk
      PRIMARY KEY,
  name
    VARCHAR2( 30 )
    CONSTRAINT animal_state_subtype_name_nn
      NOT NULL
    CONSTRAINT animal_state_subtype_name_uk
      UNIQUE
    CONSTRAINT animal_state_subtype_name_ck
      CHECK (
        name = UPPER( name )
      )
);
/* Load the initial data */
INSERT INTO animal_state_subtype
  ( type_id, name )
  VALUES( 1, 'ANIMAL_STATE' );
INSERT INTO animal_state_subtype
  ( type_id, name )
  VALUES( 2, 'HUMAN_STATE' );
COMMIT;

Animal State Values

The animal state is part of a state machine. Each state has a unique name (which has initial capitals) and is associated with an animal subtype. A surrogate key is used because the candidate key, name, is mutable.

The external predicate is:

animal_state( state_id, name, type_id ) := Animal state #state_id is called name and is associated with animal subtype #type_id and its subtypes.

CREATE TABLE animal_state (
  state_id
    NUMBER
    CONSTRAINT animal_state_pk
      PRIMARY KEY,
  name
    VARCHAR2( 30 )
    CONSTRAINT animal_state_name_nn
      NOT NULL
    CONSTRAINT animal_state_name_uk
      UNIQUE
    CONSTRAINT animal_state_name_ck
      CHECK (
        name = INITCAP( name )
      ),
  type_id
    NUMBER
    CONSTRAINT animal_state_type_id_nn
      NOT NULL
    CONSTRAINT animal_state_type_id_fk
      REFERENCES animal_state_subtype( type_id )
);
/* Load the initial data */
INSERT INTO animal_state
  ( state_id, name, type_id )
  VALUES( 1, 'Preborn', 1 );
INSERT INTO animal_state
  ( state_id, name, type_id )
  VALUES( 2, 'Healthy', 1 );
INSERT INTO animal_state
  ( state_id, name, type_id )
  VALUES( 3, 'Ill', 1 );
INSERT INTO animal_state
  ( state_id, name, type_id )
  VALUES( 4, 'Deceased', 1 );
INSERT INTO animal_state
  ( state_id, name, type_id )
  VALUES( 5, 'In Love', 2 );
INSERT INTO animal_state
  ( state_id, name, type_id )
  VALUES( 6, 'Married', 2 );
COMMIT;

Animal State Transition

Because the animal state is a finite state machine, a table can be used to implement the valid state transitions. In constrast to the original physical design, idempotent transitions are not permitted. This table is a subset of the Cartesian Join on the primary key of the ANIMAL_STATE table with itself. This new table consists only of a composite primary key whose components are all foreign keys on the base table.

The external predicate for the table is:

ANIMAL_STATE_TRANSITION( old_state, new_state ) := Animal state #old_state can change into animal state #new_state.

CREATE TABLE animal_state_transition(
  old_state
    NUMBER
    CONSTRAINT animal_state_transition_old_nn
      NOT NULL
    CONSTRAINT animal_state_transition_old_fk
      REFERENCES animal_state( state_id ),
  new_state
    NUMBER
    CONSTRAINT animal_state_transition_new_nn
      NOT NULL
    CONSTRAINT animal_state_transition_new_fk
      REFERENCES animal_state( state_id ),
  CONSTRAINT animal_state_transition_pk
    PRIMARY KEY(
      old_state,
      new_state
    )
);
/* Load the initial data */
INSERT INTO animal_state_transition( old_state, new_state )
  VALUES( 1, 2 ) /* Preborn to healthy */ ;
INSERT INTO animal_state_transition( old_state, new_state )
  VALUES( 1, 3 ) /* Preborn to ill */ ;
INSERT INTO animal_state_transition( old_state, new_state )
  VALUES( 1, 4 ) /* Preborn to deceased */ ;
INSERT INTO animal_state_transition( old_state, new_state )
  VALUES( 1, 5 ) /* Preborn to in love */ ;
INSERT INTO animal_state_transition( old_state, new_state )
  VALUES( 2, 3 ) /* Healthy to ill */ ;
INSERT INTO animal_state_transition( old_state, new_state )
  VALUES( 2, 4 ) /* Healthy to deceased */ ;
INSERT INTO animal_state_transition( old_state, new_state )
  VALUES( 2, 5 ) /* Healthy to in love */ ;
INSERT INTO animal_state_transition( old_state, new_state )
  VALUES( 3, 2 ) /* Ill to healthy */ ;
INSERT INTO animal_state_transition( old_state, new_state )
  VALUES( 3, 4 ) /* Ill to deceased */ ;
INSERT INTO animal_state_transition( old_state, new_state )
  VALUES( 3, 5 ) /* Ill to in love */ ;
INSERT INTO animal_state_transition( old_state, new_state )
  VALUES( 5, 2 ) /* In love to healthy */ ;
INSERT INTO animal_state_transition( old_state, new_state )
  VALUES( 5, 3 ) /* In love to ill */ ;
INSERT INTO animal_state_transition( old_state, new_state )
  VALUES( 5, 4 ) /* In love to deceased */ ;
INSERT INTO animal_state_transition( old_state, new_state )
  VALUES( 5, 6 ) /* In love to married */ ;
INSERT INTO animal_state_transition( old_state, new_state )
  VALUES( 6, 2 ) /* Married to healthy */ ;
INSERT INTO animal_state_transition( old_state, new_state )
  VALUES( 6, 3 ) /* Married to ill */ ;
INSERT INTO animal_state_transition( old_state, new_state )
  VALUES( 6, 4 ) /* Married to deceased */ ;
COMMIT;

Animal Base Table

Having defined all of the tables required to support the animal type, the base table can now be defined. Again, a surrogate key is used instead of the mutable candidate key, name.

The external predicate is:

ANIMAL_BASE( animal_id, name, type_id, state_type_id, state_value ) := Animal #animal_id is called name and is of type #type_id. It has a current state of type #state_type_id and value of state_value.

CREATE TABLE animal_base (
  animal_id
    NUMBER
    CONSTRAINT animal_base_pk
      PRIMARY KEY,
  name
    VARCHAR2( 30 )
    CONSTRAINT animal_base_name_nn
      NOT NULL
    CONSTRAINT animal_base_name_uk
      UNIQUE,
  type_id
    NUMBER
    CONSTRAINT animal_base_type_id_nn
      NOT NULL
    CONSTRAINT animal_base_type_id_fk
      REFERENCES animal_subtype( type_id ),
  state_type_id
    NUMBER
    CONSTRAINT animal_base_state_type_id_nn
      NOT NULL
    CONSTRAINT animal_base_state_type_id_fk
      REFERENCES animal_state_subtype( type_id ),
  state_value
    NUMBER
    CONSTRAINT animal_base_state_value_nn
      NOT NULL
    CONSTRAINT animal_base_state_value_fk
      REFERENCES animal_state( state_id )
);

Trigger code removed.

Copy the Objects into JDeveloper

Open the Oklahoma application in JDeveloper.

  1. Right click on Nordic_database to bring up the context menu. Choose Copy to project. Source is from database and the connection is called XE_Nordic. Click Next.
  2. The project is Intro.jpr and the offline database is Nordic_database. Click Next.
  3. Schema should be NORDIC. Click Query.
  4. Select the following objects by highlighting them and clicking the blue right-pointing single arrow (>) and then click Next.
    1. ANIMAL_BASE
    2. ANIMAL_STATE
    3. ANIMAL_STATE_SUBTYPE
    4. ANIMAL_STATE_TRANSITION
    5. ANIMAL_SUBTYPE
  5. Choose the defaults. Click Finish

Create the Database Diagrams

In the Intro project of the Oklahoma application in JDeveloper,

  1. Create a new Database Diagram called Animals.
  2. Drag the following objects from the Application Navigator:
    • ANIMAL_BASE
    • ANIMAL_STATE
    • ANIMAL_STATE_SUBTYPE
    • ANIMAL_STATE_TRANSITION
    • ANIMAL_SUBTYPE

The diagram should look like the following:

JDeveloper Database diagram for partial animal type

JDeveloper Database diagram for partial animal type

And the complexity is starting to worry me. Have I over-complicated things?

Advertisements

One thought on “NORDIC Schema for TopLink Demonstration (2)

  1. NORDIC Schema for TopLink Demonstration (3) « 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