Subset definition explanation and rules

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

Main concepts

• 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 created diagram on the 'Design surface'.

Figure 2: Explicit relationship, e.g. Relationship between Employees and Departments, 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 on them. When dragging first table from Toolbox to Design surface relationship dialog appears. Table that was dragged from Toolbox is created as 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. 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.

Adding tables to the Design surface to make subset definition

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

Subset relationship cannot be created without 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.

Selecting different relationships from dropdown list

• If in relationship dialog there are multiple database relationships for selected tables, selecting database relationship from dropdown should fill parent-child columns with that relationship data, e.g.: DEPARTMENTS - EMPOLYEES - two database relationships:

  • The Relationship is by default set as DEPT_MGR_FK and the columns are set as EMPLOYEE_ID and MANAGER_ID

  Figure 6: The Relationship is by default set as DEPT_MGR_FK

  • If EMP_DEPT_FK is selected from Relationship dropdown, parent and child colums will be set as DEPARTMENT_ID.

  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

  • The Relationship is by default set as EMP_MANAGER_FK and the columns are set as EMPLOYEE_ID and MANAGER_ID

  Figure 8: The Relationship is by default set as EMP_MANAGER_FK

  • If the parent and child columns swap places, that parent column is now MANAGER_ID and child column is now EMPLOYEE_ID, columns are reversed and on Relationship dropdown it shows EMP_MANAGER_FK – reversed:

  Figure 9: EMP_MANAGER_FK - Reversed

• If parent–child columns are changed to relationship which does not exist in database, in 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. Custom relationship has been created.

  Figure 11: No relationship