NORDIC Schema for TopLink Demonstration (1)

Overview

This document details the SQL needed to establish the NORDIC schema in an Oracle XE database for the demonstration of the use of TopLink which is included in Oracle JDeveloper 11g (Build 5188). This SQL is based on the NORDIC series of articles on this blog.

Object views are used to replace object tables because TopLink cannot import Object Tables into the Offline database.

Schema Creation

An entire new schema is to be created to replace the original ORDBMS schema used in the original series. This is the minimum required to get the SQL to work.

Use the Run SQL Command Line program from the Oracle Database 10g Express Edition program group to run the following commands:

/* Become the super user. */
CONNECT / AS SYSDBA
/* Create the owner of the database objects */
CREATE USER nordic
  IDENTIFIED BY some_password
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;
/* Grant minimal privileges to create the database objects */ 
GRANT
  CREATE SESSION,
  CREATE TABLE,
  CREATE TYPE,
  CREATE VIEW,
  CREATE PROCEDURE,
  CREATE TRIGGER
    TO nordic;
/* Connect as the database object owner to continue */
CONNECT nordic/some_password

Connection Between JDeveloper and XE Database

In JDeveloper,

1. Create a new application called Oklahoma via File then New
2. Create a new project called Intro. The default package is au.id.yaocm.nordic.
3. Create a new database connection. The name of the connection is XE_Nordic. Other details are:

Connection Type	Oracle (JDBC)
Username	NORDIC
Password	some_password
Driver	thin
Host name	localhost
JDBC Port	1521
SID	XE

4. Within that project, created a new Offline Database. The name was Nordic_database, and the database to emulate is Oracle10g Express Edition Release 2

Buildings

The building attributes are simple. There is only one (1): the name of the building. This name also uniquely identifies the building. There is no ambiguity in this data model.

The external predicate for this object view is

buildings( name ) := Building is called name.

CREATE TABLE building_base (
  name  VARCHAR2( 30 )
    CONSTRAINT building_base_pk
      PRIMARY KEY
  );
CREATE TYPE building AS OBJECT (
  name VARCHAR2( 30 )
) FINAL;
/
CREATE VIEW buildings
  OF building
  WITH OBJECT IDENTIFIER( name )
  AS
    SELECT name FROM building_base;

Surreys

The surrey attributes is more complex, as state is now introduced. There are only two (2) attributes: the name of the surrey, and the state of the surrey. This name also uniquely identifies the surrey. There is no ambiguity in this data model.

The external predicate for this object view is:

surreys( name, current_state ) := Surrey is called name and is currently current_state.

CREATE TABLE surrey_base (
  name  VARCHAR2( 30 )
    CONSTRAINT surrey_base_pk
      PRIMARY KEY,
  current_state VARCHAR2(30)
    CONSTRAINT surrey_base_current_state_nn
      NOT NULL
    CONSTRAINT surrey_base_current_state_ck
      CHECK (
        current_state IN ( 'Broken', 'Working' )
      )
  );
CREATE OR REPLACE TYPE surrey_stateflow AS OBJECT (
  current_state VARCHAR2(30)
)
FINAL;
/
CREATE TYPE surrey AS OBJECT (
  name VARCHAR2( 30 ),
  state surrey_stateflow
) FINAL;
/
CREATE VIEW surreys
  OF surrey
  WITH OBJECT IDENTIFIER( name )
  AS
    SELECT
        name,
        surrey_stateflow( current_state )
          AS state
      FROM 
        surrey_base;

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.
4. Schema should be NORDIC. Click Query.
5. Select the following objects by highlighting them and clicking the blue right-pointing single arrow (>) and then click Next.
1. BUILDING
2. BUILDINGS
3. BUILDING_BASE
4. SURREY
5. SURREYS
6. SURREY_BASE
7. SURREY_STATEFLOW
6. 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 Buildings.
2. Drag the following objects from the Application Navigator:
• BUILDING
• BUILDINGS
• BUILDING_BASE

The diagram should look like the following:

JDeveloper Database diagram for the buildings object

Doing the same for the surrey objects,

JDeveloper Database diagram for the surrey type