skip to main content
Corticon Studio: Rule Modeling Guide : Rule scope and context : Scope and perspectives in the vocabulary tree : Technical aside
 

Try Corticon Now
Technical aside

Understanding Rule Associations and Scope as Relationships Between Tables in a Relational Database

Although it is not necessary for the rule modeler or developer to understand database theory, a business or systems analyst who is familiar with it may have already recognized that the preceding discussion of rule scope and context is an abstraction of basic relational concepts. Actual relational tables that contain the data for our Cargo example might look like the following:
Figure 65. Tables in a Relational Database
Each one of these tables has a column that serves as a unique identifier for each row (or record). In the case of the Aircraft table, the tailNumber is the unique identifier for each Aircraft record – this means that no two Aircraft can have the same tailNumber.ManifestNumber is the unique identifier for each Cargo record. These unique identifiers are known as primary keys. Given the primary key, a particular record can always be found and retrieved. A common notation uses an asterisk character (*) to indicate those table columns that serve as primary keys. If a Vocabulary has been connected to an external database using Enterprise Data Connector features, then you may notice asterisks next to attributes, indicating their designation as primary keys. See the Data Integration Guide, Direct Database Access chapter for complete details.
Notice that the FlightPlan table contains columns that did not appear in our Vocabulary. Specifically, tailNumber and manifestNumber exist in the Aircraft and Cargo entities, respectively, but we did not include them in the FlightPlan Vocabulary entity. Does this mean that our original Vocabulary was wrong or incomplete? No - the extra columns in the FlightPlan table are really duplicate columns from the other two tables – tailNumber came from the Aircraft table and manifestNumber came from the Cargo table. These extra columns in the FlightPlan table are called foreign keys because they are the primary keys from other tables. They are the mechanism for creating relations in a relational database.
For example, we can see from the FlightPlan table that flightNumber 101 (the first row or record in the table) includes Aircraft of tailNumber N1001 and Cargo of manifestNumber 625A. The foreign keys in FlightPlan serve to link or connect a specific Aircraft with a specific Cargo. If the database is queried (using a query language like SQL, for example), a user could determine the weight of Cargo planned for Aircraft N1001 – by traversing the relationships from the Aircraft table to the FlightPlan table, we discover that Aircraft N1001 is scheduled to carry Cargo 625A. By traversing the FlightPlan table to the Cargo table, we discover that Cargo 625A weighs 100,000 kilograms. Matching the foreign key in the FlightPlan table with the primary key in the Cargo table makes this traversal possible.
The Corticon Vocabulary captures this essential feature of relational databases, but abstracts it in a way that is friendlier to non-programmers. Rather than deal with concepts like foreign keys in our Vocabulary, we talk about associations between entities. Traversing an association in the Vocabulary is exactly equivalent to traversing a relationship between database tables. When we use a term like Aircraft.tailNumber in a rule, Studio creates a collection of tailNumbers from all records in the Aircraft table. This collection of data is then fed to the rule for evaluation. If however, the rule uses FlightPlan.aircraft.tailNumber, then Studio will create a collection of only those tailNumbers from the Aircraft table that have FlightPlans related to them – it identifies these aircraft instances by matching the tailNumber in the Aircraft table with the tailNumber (foreign key) in the FlightPlan table. If the Aircraft table contains 7 instances of aircraft (i.e., 7 unique rows in the table), but the FlightPlan table contains only 3 unique instances of flight plans, the term FlightPlan.aircraft.tailNumber will create a collection of only 3 tail numbers – those instances from the Aircraft table which have flight plans listed in the FlightPlan table. In database terminology, the scope of the rule determines how the tables are joined.
When FlightPlan is used as the scope for our rule, Corticon Studio automatically ensures that the collection of data contains matching foreign keys. That's why, when we rewrote the rule using proper scope, the rule only fired 3 times – there are only 3 examples of Aircraft-Cargo combinations where the keys match. This also explains why, prior to using scope, the rule produced 6 spurious and irrelevant outcomes – 6 combinations of Aircraft and Cargo that were processed by the rule do not, in fact, exist in the FlightPlan table.
While the differences in processing requirements are not extreme in our simple example, for a large company like Federal Express, with a fleet of hundreds of aircraft and several thousand unique cargo shipments every day, the system performance differences could be enormous.