A question about type substitution came type substitution and since the explanation cannot fit into few rows I'll try to describe it shortly here.

Examples below are for illustrative purpose only so don't be fastidious on some details .

Some SQL99 terms:

“A structured type is a named, user-defined data type. A value of a structured type comprises a number of attribute values. Each attribute of a structured type has a data type, specified by an attribute type that is included in the descriptor of the structured type.”

“A structured type may be defined to be a subtype of another structured type, known as its direct supertype. A subtype inherits every attribute of its direct supertype, and may have additional attributes of its own. An expression of a subtype may appear anywhere that an expression of any of its supertypes is allowed (this concept is known as substitutability).”

“One or more base tables can be created based on a structured type. A base table based on a structured type ST can be a subtable of a base table based on a supertype of ST.”

 

You can look at type substitution (as it's in Data Modeler) as constraint, relationship or just a tool allowing you to define precisely what you want to get into target database. Basically it's related to possibility to get instance of subtypes at the place you expect instance of super-type.

Oracle Database and DB2/UDB LUW have a support for such object relational concepts. In terms of Data Modeler we are talking about tables that a based on structured types. Implementation in both databases is different - in Oracle it's a global flag for such table "allow substitution at all levels" or not, while in DB2/UDB you need to build the hierarchy of base tables.

Definition of type substitution at row level is straightforward and it follows SQL99 definition of supertable/subtable hierarchy. First it’s necessary tables to be defined as based on structured type ( tables City_Village and Village) :

before_type_subst.png

Using “New Type Substitution" button, a type substitution link can be created between tables that are based on direct supertype – subtype pair of given structured type. Tables included in supertable/subtable hierarchy show only attributes defined in the type they are based on (inherited attributes are not shown):

after_type_subst.png

With such definition we put a constraint that table City_Village cannot have instances of State_T and Country_T.

Here is the DDL for Oracle Database:

 

CREATE TABLE City_Village
    OF Geopol_Area_T
    SUBSTITUTABLE AT ALL LEVELS
    (
     ID  NOT NULL ,
     Name  NOT NULL
    )
    NOCOMPRESS
    NOCACHE
    NOPARALLEL
    NOROWDEPENDENCIES
    DISABLE ROW MOVEMENT
;
CREATE OR REPLACE TRIGGER OBJ_SUBS_TR_City_Village
BEFORE INSERT ON City_Village
FOR EACH ROW
BEGIN
-- DO NOT DELETE OR MODIFY THE FOLLOWING COMMENTS
-- <cwd_ts_tab table="City" type="City_T" parent_type="Geopol_Area_T" />
-- <cwd_ts_tab table="County" type="County_T" parent_type="Geopol_Area_T" />
-- <cwd_ts_tab table="Village" type="Village_T" parent_type="Geopol_Area_T" />
IF :new.object_value IS NOT OF ( City_T, County_T, Village_T ) THEN
  raise_application_error(-20222,' Only instances of City_T, County_T, Village_T are permitted');
END IF;
END;
/

-- Table City: is part of type substitution hierarchy
-- Table County: is part of type substitution hierarchy
-- Table Village: is part of type substitution hierarchy

And related DDL for DB2/UDB:

 

CREATE TABLE City_Village OF Geopol_Area_T
    (
    ID WITH OPTIONS  NOT NULL ,
    "Name" WITH OPTIONS  NOT NULL ,
    CONSTRAINT City_Village_PK PRIMARY KEY ( My_Oid ) ,
    REF IS My_Oid USER GENERATED
    )
;

ALTER TABLE City_Village ADD CONSTRAINT City_Village_PK PRIMARY KEY (My_Oid)
;

CREATE TABLE City OF City_T UNDER City_Village
    INHERIT SELECT PRIVILEGES
    (
    City_desc WITH OPTIONS  NOT NULL
    )
;

--  ERROR: Table County is based on structured type without attributes
--  ERROR: Table Village is based on structured type without attributes

Moving to area of views implementation in Oracle Database follows definition in SQL99 i.e. hierarchy of views can be built. Here is an example from OE sample schema:

OE_sample.png

And related DDL:

 

CREATE OR REPLACE NOFORCE VIEW OE.OC_CUSTOMERS
OF OE.CUSTOMER_TYP WITH OBJECT IDENTIFIER ( customer_id )   AS
SELECT c.customer_id, c.cust_first_name, c.cust_last_name, c.cust_address,
           c.phone_numbers,c.nls_language,c.nls_territory,c.credit_limit,
           c.cust_email,
           CAST(MULTISET(SELECT o.order_id, o.order_mode,
                               MAKE_REF(oc_customers,o.customer_id),
                               o.order_status,
                               o.order_total,o.sales_rep_id,
                               CAST(MULTISET(SELECT l.order_id,l.line_item_id,
                                                    l.unit_price,l.quantity,
                                             MAKE_REF(oc_product_information,
                                                      l.product_id)
                                             FROM order_items l
                                             WHERE o.order_id = l.order_id)
                                    AS order_item_list_typ)
                         FROM orders o
                         WHERE c.customer_id = o.customer_id)
                AS order_list_typ)
     FROM customers c
;

CREATE OR REPLACE NOFORCE VIEW OE.OC_CORPORATE_CUSTOMERS
OF OE.CORPORATE_CUSTOMER_TYP UNDER OE.OC_CUSTOMERS   AS
SELECT c.customer_id, c.cust_first_name, c.cust_last_name,
              c.cust_address, c.phone_numbers,c.nls_language,c.nls_territory,
              c.credit_limit, c.cust_email,
              CAST(MULTISET(SELECT o.order_id, o.order_mode,
                               MAKE_REF(oc_customers,o.customer_id),
                               o.order_status,
                               o.order_total,o.sales_rep_id,
                               CAST(MULTISET(SELECT l.order_id,l.line_item_id,
                                         l.unit_price,l.quantity,
                                         make_ref(oc_product_information,
                                                    l.product_id)
                                             FROM order_items l
                                             WHERE o.order_id = l.order_id)
                                    AS order_item_list_typ)
                            FROM orders o
                            WHERE c.customer_id = o.customer_id)
              AS order_list_typ), c.account_mgr_id
     FROM customers c
;

 

Type substitution also can be restricted at column level - for columns based on structured type.

 

Philip