Extracting from the entity and relationship database tables
Use the JOIN command to create complex database reports that contain information from both the Entity and Relationship Tables. The JOIN command tells the system to leave the table it is searching and go to the other table and look for the information in the command that follows the JOIN command. In this way, you can extract data from both the Entity Table (see Entity Table overview) and the Relationship Table (see Relationship Table overview) to create a complex database report.
All data selection begins with the Entity Table. If the System encounters a JOIN command, it scans the Relationship Table. The next JOIN automatically returns to the Entity Table.
The structure of the JOIN command is: JOIN. The command must be used in pairs. The first JOIN command tells the system to leave the Entity Table; the second JOIN command tells the system to go back to the Entity Table, and so on. The WHERE command tells the system the relationship row to find in the Relationship Table.
WHERE Class = Definition
WHERE Type = "Data Domain"
JOIN
WHERE Relation = "instantiated by"
JOIN
WHERE Class = Definition
WHERE Type = "Data Element"
Legend
Lines 1 and 2 search the Entity Table class columns for the value definition and type column for the value data domain.
Lines 3 and 4 go to the Relationship Table and find the relationship called instantiated by.
Line 5 goes back to the Entity Table.
Lines 6 and 7 search the Entity Table class column for the value definition and the type column for the value data element.
Note Use a Relation field-identifier with the WHERE command between JOIN commands to identify the relationship to search for in the Relationship Table. That relationship must be at the same level as the WHERE statement prior to the JOIN command. For example, you can not search the Entity Table for diagrams and then to go the Relationship Table to search for a defines relationship because the defines relationship is only valid at the symbol level of the database.
JOIN command indentation and spacing
To separate the results of each JOIN command, each resulting column of printed information is indented 0.50 inches on the report page. Use the INDENT subcommand to override the default indentation.
Note The indentation following a JOIN statement is only dependent on the presence of one or more JOIN statements. The default indentation is 1/2 inch whether there is one JOIN statement or six JOIN statements between SELECT commands.
These commands control the spacing between columns and rows resulting from the JOIN commands used to create a tabular report.
1 The JOIN command is used to move from a level within the Entity Table, to the same level within the Relationship Table. Once in the Relationship Table, the system locates the appropriate relationship row and extracts the information specified using these tabular report commands:
2 Another JOIN command is then used to return to the Entity Table at the level specified by the relationship. Upon returning to the Entity Table, more information can be extracted for printing.
3 The JOIN command should be placed in pairs; a report always begins in the Entity Table, and it should also end in the Entity Table. An even number of JOIN commands within a report specification is strongly recommended.
Example 1, JOIN command
In this example, the Reporting System begins at the diagram level of the Entity Table, and uses the relation contains to move to the symbol level of the Relationship Table. These commands are used to extract the data and compile the report:
REPORT "Process Symbols contained on Diagrams"
▪This report begins at the diagram level in the Entity Table using a SELECT Name, Type, and a WHERE Class = Diagram command statement to extract information on all diagrams in the encyclopedia. That information is placed in the extraction database.
▪The first JOIN command is used to move from the diagram level of the Entity Table to the diagram level of the Relationship Table. The move is based on the unique identity number that is given to each diagram, symbol, and definition in the encyclopedia.
▪The second JOIN command is used to move from the symbol level of the Relationship Table to the symbol level of the Entity Table.
▪At the symbol level of the Entity Table, all symbols that were found to be contained in the diagrams that were located in step 1, are examined. If the new extraction criteria is met, WHERE Type = Process, information on the symbol is added to the extraction database.
Note It is not necessary to specify Class = Symbol because only symbols are located by the contains relation.
The report is then constructed from the data contained in the extraction database. Not all of the information in the initial extraction database is printed in the final report. The diagram name and type containing a process symbol are included in the report.
Example 2, JOIN command
This report produces a Tabular report of all Conditions and Actions (flag symbols) attached to symbols in a State Transition diagram.
REPORT "Conditions and Actions in State Transition Diagrams"
▪The first JOIN command is used to move from the diagram level of the Entity Table to the diagram level of the Relation Table.
▪Once in the Relationship Table, the Reporting System locates the contains relation that correspond to the identity of each State Transition diagram found in the Entity Table (WHERE Relation = "contains"). The Reporting System then picks up the identity number of the symbol that is contained in the diagram.
▪The second JOIN command is used to move from the symbol level of the Relationship Table to the symbol level of the Entity Table.
▪Since additional selection criteria is not specified at the symbol level of the Entity Table, all symbols that were found to be contained in the diagrams that were located in step 1, are added to the extraction database.
▪The third JOIN command is used to move from the symbol level of the Entity Table, to the same level in the Relationship Table.
▪Once in the Relationship Table, the Reporting System locates the qualified by relation that correspond to the identity of each symbol that was found to be contained in each State Transition diagram. The Reporting System then picks up the identity number of the flag symbol that qualifies the symbol.
▪The fourth JOIN command is used to move from the flag symbol in the Relationship Table to the flag symbol in the Entity Table.
▪Once in the Entity Table, all flag symbols that were found to qualify symbols that were contained in a State Transition diagram are added to the extraction database.
Note The specification of Type = Condition, Action is optional in this report, since these are the only types of flag symbols in a State Transition diagram.
In this example, the Reporting System begins at the diagram level of the Entity Table, and uses the relations contains, connects, and qualified by to locate the flag symbols in the Entity Table. The report is then constructed from the data contained in the extraction database.
