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) :
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):
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:
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.