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 the cargo example might look like the following:

Figure 1. Tables in a relational database

Each one of these tables has a column that is 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 (*) to indicate those table columns that are primary keys. If a Vocabulary is connected to an external database using Datasource Configuration features, then you may notice asterisks next to attributes, indicating their designation as primary keys. See How Datasource information is viewed in the Vocabulary for complete details.

Notice that the FlightPlan table contains columns that did not appear in the Vocabulary. Specifically, tailNumber and manifestNumber exist in the Aircraft and Cargo entities, respectively, but you did not include them in the FlightPlan Vocabulary entity. Does this mean that your original Vocabulary was wrong or incomplete? No, the extra columns in the FlightPlan table are 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, flightNumber 101 (the first row or record in the FlightPlan 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), then a user could determine the weight of Cargo planned for Aircraft N1001 by traversing the relationships from the Aircraft table to the FlightPlan table, you see that Aircraft N1001 is scheduled to carry Cargo 625A. By traversing the FlightPlan table to the Cargo table, you can see 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 the Vocabulary, there are “associations” between entities. Traversing an association in the Vocabulary is equivalent to traversing a relationship between database tables. When a term like Aircraft.tailNumber is used 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 creates 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 (7 unique rows in the table), but the FlightPlan table contains only 3 unique instances of flight plans, the term FlightPlan.aircraft.tailNumber creates a collection of only 3 tail numbers—those instances from the Aircraft table that 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 the rule, Corticon Studio automatically ensures that the collection of data contains matching foreign keys. That is why, when 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 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 this simple example, for a large company with a fleet of hundreds of aircraft and several thousand unique cargo shipments every day, the system performance differences could be enormous.