Nordic (2)


This series is a commentary of sorts on a PowerPoint™ presentation called Nordic – Object Relational Database Design. The first post is at Nordic (1).

The Animal Type

We have created the BUILDING type and instantiated one (1) building – a barn. Now for the animals who use the building. So, we have an attribute, NAME, and a method, USES for the ANIMAL type. Translated into SQL, this is:

connect ordbms/******** -- password suppressed
CREATE OR REPLACE TYPE animal AS OBJECT (
  name VARCHAR2(30),
  MEMBER PROCEDURE uses (
    building_used building
  )
)
NOT FINAL;
/

These are just definitions. The method USES has not been implemented yet in a TYPE BODY. The method cannot do anything until it has been implemented.

The NOT FINAL clause means that we can create sub-types from this type.

Other Types

These types do not inherit from other types.

Surrey

CREATE OR REPLACE TYPE surrey AS OBJECT (
  name VARCHAR2(30)
);
/

Courtship

CREATE OR REPLACE TYPE courtship;
/

This is an incomplete definition because this type is a relationship between two (2) yet undefined types: BOY, and GIRL.

Types Defined So Far

To find out all of the types (classes) we have created so far, run the following query:

SELECT type_name, final, incomplete FROM user_types;

To produce the following report:

TYPE_NAME                      FIN INC
------------------------------ --- ---
BUILDING                       YES NO
ANIMAL                         NO  NO
SURREY                         YES NO
COURTSHIP                      YES YES

The FIN column indicates that we can create sub-types for only the ANIMAL type.

The INC column indicates that the COURTSHIP type is incomplete. This is used for forward-references in type definitions.

Type Inheritance

The remaining types on slide #12 of the PowerPoint™ Presentation are all sub-types. That is, they inherit attributes and methods from their parents (aka super-types).

The same SQL command is used to create these sub-types, but the AS OBJECT clause is replaced by the UNDER clause. The UNDER clause specifies the super-type.

Human Type

The NOT FINAL clause is specified because we have two (2) sub-types for the HUMAN type: BOY, and GIRL.

CREATE OR REPLACE TYPE human UNDER animal (
  MEMBER PROCEDURE drives (
    my_surrey surrey
  ),
  MEMBER PROCEDURE fixes (
    my_surrey surrey
  )
)
NOT FINAL;
/

If we had not specified the NOT FINAL clause on the CREATE TYPE command for the ANIMAL type, we would get the following error messages:

SQL> show errors
Errors for TYPE HUMAN:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1      PLS-00590: attempting to create a subtype UNDER a FINAL type

Now, if we look at the definition of the HUMAN type, we find:

SQL> describe human
 human extends ORDBMS.ANIMAL
 human is NOT FINAL
 Name                                         Null?    Type
 -------------------------------------------- -------- ----------------
 NAME                                                  VARCHAR2(30)

METHOD
------
 MEMBER PROCEDURE USES
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 BUILDING_USED                  BUILDING                IN

METHOD
------
 MEMBER PROCEDURE DRIVES
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 MY_SURREY                      SURREY                  IN

METHOD
------
 MEMBER PROCEDURE FIXES
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 MY_SURREY                      SURREY                  IN

The HUMAN type has inherited the NAME and the USES method from its super-type, ANIMAL. This is as expected.

Horse Sub-Type

CREATE OR REPLACE TYPE horse UNDER animal (
  MEMBER PROCEDURE pulls (
    my_surrey surrey
  )
);
/

Courtship Type

At this point, we can complete the COURTSHIP type.

CREATE OR REPLACE TYPE courtship AS OBJECT (
  my_boy human,
  my_girl human
);
/

Girl Sub-Type

We are defining the GIRL sub-type ahead of the BOY sub-type because the latter requires the former in a function.

CREATE OR REPLACE TYPE girl UNDER human (
);
/

Boy Sub-Type

CREATE OR REPLACE TYPE boy UNDER human (
  MEMBER FUNCTION courts (
    my_girl girl
  ) RETURN courtship
);
/

If we had left the COURTSHIP type definition incomplete at this point, we would have received the following error messages:


Warning: Type created with compilation errors.

SQL> show errors
Errors for TYPE BOY:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
4/12     PLS-00311: the declaration of "COURTSHIP" is incomplete or
         malformed

Types Defined

To find out what types have been defined so far, we run the following query:

SELECT
    type_name,
    final,
    incomplete,
    supertype_name
  FROM
    user_types
/

To produce the following report:

TYPE_NAME                      FIN INC SUPERTYPE_NAME
------------------------------ --- --- ------------------------------
BUILDING                       YES NO
ANIMAL                         NO  NO
SURREY                         YES NO
COURTSHIP                      YES NO
HUMAN                          NO  NO  ANIMAL
HORSE                          YES NO  ANIMAL
GIRL                           YES NO  HUMAN
BOY                            YES NO  HUMAN
Advertisements

6 thoughts on “Nordic (2)

  1. Nordic (4) « Yet Another OCM

  2. Nordic (7) « Yet Another OCM

  3. Nordic (9) « Yet Another OCM

  4. Nordic (8) « Yet Another OCM

  5. Nordic (3) « Yet Another OCM

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