How to define subsets and which results to expect is based on Oracle HR or MSSQL schema with the database tables and primary/foreign key relationships displayed below.
Figure 1: Database tables
|Table of contents|
|Adding tables to the Design surface to make subset definition|
|Selecting different relationships from the dropdown list|
A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables.
A primary key is a specific choice of a minimal set of attributes (columns) that uniquely specify a tuple (row) in a relation (table).
Explicit relationship are all visible relations of tables of the created diagram on the 'Design surface'.
Figure 2: Explicit relationship, e.g. Relationship between Employees and Departments, the relationship between Departments and Locations.
Implicit relationships are all foreign key relationships from tables in the diagram where the diagram table has a foreign key of another table.
Root tables are found in the Subset definition at the root level. No relation enters the root table, they don't have any parent tables related to them. When dragging the first table from Toolbox to the Design surface relationship dialog appears. The table that was dragged from Toolbox is created as the root Subset table.
Figure 4: Root table
A single or more tables can be added multiple times from the Toolbox to the empty Design surface at the root level. The relative order of the root level tables must be known and maintained. Figure 4: Root tables
Table with the foreign key is called the Child table. The foreign key in the child table will generally reference a primary key in the parent table.
When the root table is created, we can drag the next table to it and create a subset definition. Pop-up window ‘Subset relationship’ between those two tables will be open for entering Subset relationship details
Figure 5: Subset relationship
A subset relationship cannot be created without a Subset table.
Subset parent-child relationship A zero, one or more tables can be added to any existing subset table as a child subset table by creating a subset parent-child relationship. A subset parent-child relationship defines the hierarchical order of two tables.
If in the relationship dialog there are multiple database relationships for selected tables, selecting a database relationship from the dropdown should fill parent-child columns with that relationship data, e.g.: DEPARTMENTS - EMPLOYEES - two database relationships:
Figure 6: The Relationship is by default set as DEPT_MGR_FK
Figure 7: The Relationship is by default set as EMP_DEPT_FK
In relationship dialog, there is one database relationship, but two different variants of relationships for selected tables, e.g.: EMPLOYEES - EMPLOYEES - one database relationships
Figure 8: The Relationship is by default set as EMP_MANAGER_FK
Figure 9: EMP_MANAGER_FK - Reversed
If parent-child columns are changed to the relationship which does not exist in the database, in the Relationship field (dropdown list) ‘Custom relationship’ will be displayed:
Figure 10: Custom relationship
If there is no relationship from the database between the tables, a warning message is shown: 'There are no imported relationships for these tables. A custom relationship has been created.
Figure 11: No relationship
BizDataX Documentation © Built by Ekobit. All rights reserved.