Reading records from a database without a primary key

You may also have cases where you need to retrieve all records from multiple tables and compare record values across tables. In this case, you do not need a primary key. Let’s look at this scenario.

In this example, assume that only one cargo container can be assigned to a flight plan. You need to model a rule that compares the weight of each cargo container with the maximum cargo weight of each Aircraft to determine which cargo-aircraft combinations are valid. As you know, records about different Cargo containers and different Aircraft are stored in database tables. So the rule must retrieve all Cargo records from the Cargo table, retrieve all Aircraft records from the Aircraft table, and compare the cargo weight of each Cargo record with the maximum cargo weight of each Aircraft record. In this case, using a primary key value is not applicable.

Before modeling the rule, let’s add a few more Cargo and Aircraft records to make this example more interesting.

Adding more sample data

  1. In SQL Server Management Studio, create a new SQL script file by selecting File > New > Query with Current Connection.
    media/image38.png
  2. Copy and paste these lines of code into the SQL script editor: 
    INSERT INTO Transportation.DBO.Aircraft 
(aircraftType, maxCargoVolume, maxCargoWeight, tailNumber)
VALUES ('DC-10', 4000, 125000, 'N222B');

INSERT INTO Transportation.DBO.Aircraft 
(aircraftType, maxCargoVolume, maxCargoWeight, tailNumber)
VALUES ('787', 8000, 175000, 'N333C');

INSERT INTO Transportation.DBO.Cargo 
(RflightPlanAssoc_flightNumber, container, manifestNumber, volume, weight)
VALUES (NULL, 'STANDARD', '625C', 4000, 125000);
  3. Click Execute.
    media/image39.png
  4. You should see messages indicating that new rows have been added.
    media/image40.png
  5. Let’s take a quick look at these records. Right-click on Databases > Transportation > Tables > dbo.Aircraft and select Select Top 1000 Rows. You now see the following: Two new Aircraft records were added.
    media/image41.png
  6. Right-click dbo.Cargo and select Select Top 1000 Rows. You now see the following: Two new Cargo records were added.
    media/image42.png

Modeling the rules

Create a Rulesheet named Cargo_Aircraft.ers and model the rules as shown in this Rulesheet:



Here are the steps for this task:

  1. From the Vocabulary, drag and drop the Cargo attribute weight to the condition line 1 panel.
  2. Add > to the expression.
  3. Drag the Aircraft attribute maxCargoWeight to complete the expression.
  4. Select T in column 1 and F in column 2.
  5. Enter the rule statements as shown.
  6. Save the file.

Note that Aircraft.tailNumber and Cargo.manifestNumber are referenced in the rule statements by enclosing each attribute with [{ }]. That will dynamically retrieve those values in the test so that we can see which Cargo record is being compared with which Aircraft record.

When a rule uses a primary key, it retrieves a limited set of data from the database, which is loaded into memory for processing. However, without a primary key, if a rule has to retrieve multiple records, using root-level entities—for example Cargo and Aircraft—ALL records from the tables must be loaded into memory. Since this takes up a lot of memory, retrieving all root-level entity records is disabled by default. To retrieve all records from tables, you must ‘extend’ the entities used in the rule to the database. To extend an entity to the database:

  1. Switch the Rulesheet to Advanced View by selecting Rulesheet > Advanced View.
  2. Right-click Aircraft in the Scope pane and select Extend to Database:
    media/image44.png
  3. The icons of the Aircraft entity and its attributes in the Scope pane change to include the database icon indicating that the entity is extended to the database.
  4. Then, right-click Cargo in the Scope pane and select Extend to Database:
    media/image45.png

Your Rulesheet is now ready for the test we want to run.

Testing the rules

Create a Ruletest named Cargo_Aircraft.ert that uses Cargo_Aircraft.ers as its test subject. The Ruletest must retrieve ALL records from the Cargo and Aircraft tables. So, no input is required.media/image46.png

You only need to run the Ruletest.

Since this Ruletest needs to read records from the database, configure the Database Access setting as Read Only:

media/image47.pngClick Run Test to execute the Ruletest.

You should see the following output:

media/image43.png

As you can see, the nine permutations of the three aircraft and the three manifests were passed through the rules. The value of Cargo.weight of each Cargo record has been compared with the value of Aircraft.maxCargoWeight of each Aircraft record. The rule messages indicate which Cargo-Aircraft combinations are valid – one failed the test and has been flagged through a Violation message.