You can create the tables and sample data using the scripts found under the ./scripts folder of the VirtualTreeNavigator
installation folder, or here.
Tutorial 1. Simple Recursive Structure
Some data models contain recursive data structures, i.e. trees by simple means of connecting every row
in a table to another row, usually referred to as "parent row". This simple data modelling technique
observes the main attributes of a strict taxonomy, that a node can have only one parent.
The figure below illustates such a structure. The PRODUCT table contains a recursive relationship to itself.
To fully traverse the taxonomy defined by the PRODUCT table in VirtualTreeNavigator, the following taxonomy definition is required.
Note, that the two queries are required, one to retrieve the first level of the taxonomy (i.e. products with no "parents") and
another to retrieve the "children" of each product regardless of the product id, name or level in the hierarchy.
Tutorial 2. Nodes with types
Often, one of the most important attributes for a taxonomy node is its "type", a categorisation or grouping
that determines how this product behaves in the taxonomy or what additional data it may contain. The node type
can be one of the node's attributes as illustrated in the figure below:
The difference between the following taxonomy definition and the one above is that the nodetype attributes
of the query node have been replaced with the nodetypecol attribute
which retrieves the type of each node from a column in the result set of the query.
Oracle Script for Sample Tables and Data
-- Oracle script for the creation of sample tables and data
create table PRODUCT (
product_id number(10,0) not null,
product_type_id number(10,0) not null,
product_name varchar2(20) not null,
parent_product_id number(10,0) null,
constraint product_pk primary key (product_id)
);
create table PRODUCT_TYPE (
product_type_id number(10,0) not null,
product_type_name varchar2(20) not null,
constraint product_type_pk primary key (product_type_id)
);
alter table PRODUCT add constraint parent_product_fk
foreign key (parent_product_id) references product (product_id);
alter table PRODUCT add constraint prodtype_product_fk
foreign key (product_type_id) references product_type (product_type_id);
insert into product_type (product_type_id, product_type_name) values (100, 'Group A Products');
insert into product_type (product_type_id, product_type_name) values (200, 'Group B Products');
insert into product_type (product_type_id, product_type_name) values (300, 'Group C Products');
insert into product_type (product_type_id, product_type_name) values (400, 'Group D Products');
insert into product_type (product_type_id, product_type_name) values (500, 'Group E Products');
insert into product (product_id, product_type_id, product_name, parent_product_id) values ( 1, 100, 'Product A', null);
insert into product (product_id, product_type_id, product_name, parent_product_id) values ( 2, 200, 'Product B', null);
insert into product (product_id, product_type_id, product_name, parent_product_id) values ( 3, 300, 'Product C1', 1);
insert into product (product_id, product_type_id, product_name, parent_product_id) values ( 4, 300, 'Product C2', 1);
insert into product (product_id, product_type_id, product_name, parent_product_id) values ( 5, 300, 'Product C3', 1);
insert into product (product_id, product_type_id, product_name, parent_product_id) values ( 6, 400, 'Product D1', 2);
insert into product (product_id, product_type_id, product_name, parent_product_id) values ( 7, 400, 'Product D2', 2);
insert into product (product_id, product_type_id, product_name, parent_product_id) values ( 8, 500, 'Product E1', 6);
insert into product (product_id, product_type_id, product_name, parent_product_id) values ( 9, 500, 'Product E2', 6);
insert into product (product_id, product_type_id, product_name, parent_product_id) values (10, 500, 'Product E3', 7);