Harmonizing Disparate RDBMS Data using Virtuoso's built-in Reasoning and Inference functionality

Given distinct demonstration database schemas associated with the following Relational Database Management Systems such as Oracle, Progress, Informix, Ingres, Virtuoso, and some CSV documents, here is how I use Reasoning and Inference – that’s informed by an Ontology to harmonize real-world entities (e.g., People and Organizations) – across the Relations associated with the aforementioned Data Sources.

Attach each of the Data Sources to Virtuoso via their respective existing ODBC Data Source Names.

Generate RDF Views using Virtuoso’s RDBMS to RDF Views Wizard.

Describe how Entity Relationship Types (Relations) from the various data sources are to be aligned with various Entity Types (Classes) using a specific Database Document (e.g., a Named Graph identified by the IRI urn:rdbms:1:inference:reasoning:demo ) .

SPARQL
INSERT
   {
      GRAPH <urn:rdbms:inference:reasoning:demo:1>
         {
            ### Organization Class Instances (i.e. Organizations) ###

            ## Class derived from Microsoft SQL Server Customers Table
            <http://demo.openlinksw.com/schemas/SQLServer/Customers>
               rdfs:subClassOf   foaf:Organization   .

            ## Class derived from "sybase12."pubs2"."publishers" Table
            <http://demo.openlinksw.com/schemas/sybase12/publishers>
               rdfs:subClassOf   foaf:Organization   .

           ## Class derived from "progress11"."pub"."Customer" Table
	     <http://demo.openlinksw.com/schemas/progress11/Customer>
               rdfs:subClassOf   foaf:Organization   .
	       
	    # Class derived from "progress11"."pub"."Supplier" Table   
	     <http://demo.openlinksw.com/schemas/progress11/Supplier>
	        rdfs:subClassOf   foaf:Organization   .

            ## Class derived from "PrestoDB"."hive"."customer" Table
            <http://demo.openlinksw.com/schemas/PrestoDB/customer>
               rdfs:subClassOf   foaf:Organization   .
	    
	    ## Class derived from "csv"."demo"."companies" Table
	    <http://demo.openlinksw.com/schemas/csv/companies> 
	        rdfs:subClassOf   foaf:Organization   .
	       
             ### Person Class Instances (i.e. People) ###
	    
	    ## Class derived from "progress11"."pubs"."Employee" Table
	    <http://demo.openlinksw.com/schemas/progress11/Employee>
	       rdfs:subClassOf   foaf:Person   .
	    
	    # Class derived from "progress11"."pubs"."Salesrep" Table  
	    <http://demo.openlinksw.com/schemas/progress11/Salesrep>
	       rdfs:subClassOf   foaf:Person   .
		
	    ## Class derived from Virtuoso "School"."DBA"."Person" Table
            <http://demo.openlinksw.com/schemas/School/Person>
	       rdfs:subClassOf   foaf:Person   .
	       
            ## Class derived from "informix"."stores"."customer" Table
            <http://demo.openlinksw.com/schemas/informix/customer>
               rdfs:subClassOf   foaf:Person   .

            ## Class derived from "ingres"."tutorial"."cust_Info" Table
            <http://demo.openlinksw.com/schemas/ingres/cust_info>
               rdfs:subClassOf   foaf:Person   .

            ## Class derived from "Oracle"."HR"."EMPLOYEES" Table
            <http://demo.openlinksw.com/schemas/OracleHR/EMPLOYEES>
               rdfs:subClassOf   foaf:Person   .
         }
   } ;

Include terms from the RDFS Schema Vocabulary to the same Named Graph which ultimately enables Virtuoso understand the nature of Relationship Types such as rdfs:subClassOf.

SPARQL 
ADD <http://www.w3.org/2000/01/rdf-schema#> 
TO <urn:rdbms:inference:reasoning:demo:1> ;

Also include Relationship that are missing from RDFS Schema e.g., indicating that rdfs:subClassOf is Transitive in nature i.e., the relationship between classes in a class hierarchy are transferable throughout the hierarchy.

SPARQL

INSERT { GRAPH <urn:rdbms:inference:reasoning:demo:1>
           {  rdfs:subClassOf   a   owl:TransitiveProperty   }
} ;

Use the Entity Relationship Descriptions in the Named Graph urn:rdbms:1:inference:reasoning:demo as the basis for a built-in Virtuoso Inference Rule to be invoked when Reasoning and Inference is required.

RDFS_RULE_SET
  ( 'sql-vdb-data-lake' ,
    'urn:rdbms:inference:reasoning:demo:1'
  ) ;
  

Verify that the Inference Built-In Inference Rule has been successfully generated.

SELECT * 
FROM sys_rdf_schema 
WHERE RS_NAME = 'sql-vdb-data-lake' ;

Data Virtualization using Reasoning and Inference Verification

Entity Types

The following links identify faceted browsing pages comprising the description of Entity Types derived from Relations represented as N-Tuples (Tables).

Organizations

People

SPARQL Queries

Each of the following test queries conditionally applies reasoning and inference based on the built-in inference rules created earlier in this post. In all cases, there is a query that operates without or without the the effects of a built-in inference rule that’s informed by an associated Ontology.

Test 1 Inference Enabled

DEFINE input:inference 'sql-vdb-data-lake' 

SELECT  (COUNT(*) AS ?count)  
FROM <urn:demo.openlinksw.com:OracleHR> 
FROM <urn:demo.openlinksw.com:ingres> 
FROM <urn:demo.openlinksw.com:informix> 
FROM <urn:demo.openlinksw.com:School> 
FROM <urn:demo.openlinksw.com:SQLServer> 
FROM <urn:demo.openlinksw.com:progress11> 
FROM <urn:demo.openlinksw.com:sybase12> 
FROM <urn:demo.openlinksw.com:prestoDB> 
FROM <urn:demo.openlinksw.com:csv> 
WHERE {
        ?s a foaf:Person .
      }  

Live Query Results Link .

Test 1 Inference Disabled

# DEFINE input:inference 'sql-vdb-data-lake' 

SELECT  (COUNT(*) AS ?count)  
FROM <urn:demo.openlinksw.com:OracleHR> 
FROM <urn:demo.openlinksw.com:ingres> 
FROM <urn:demo.openlinksw.com:informix> 
FROM <urn:demo.openlinksw.com:School> 
FROM <urn:demo.openlinksw.com:progress11>
FROM <urn:demo.openlinksw.com:SQLServer> 
FROM <urn:demo.openlinksw.com:sybase12> 
FROM <urn:demo.openlinksw.com:prestoDB> 
FROM <urn:demo.openlinksw.com:csv> 
WHERE {
        ?s a foaf:Person .
      }  

Live Query Results Link

Test 2 Inference Enabled

DEFINE input:inference 'sql-vdb-data-lake' 

SELECT  (COUNT(*) AS ?count)  
FROM <urn:demo.openlinksw.com:OracleHR> 
FROM <urn:demo.openlinksw.com:ingres> 
FROM <urn:demo.openlinksw.com:informix> 
FROM <urn:demo.openlinksw.com:progress11>
FROM <urn:demo.openlinksw.com:SQLServer> 
FROM <urn:demo.openlinksw.com:School> 
FROM <urn:demo.openlinksw.com:sybase12> 
FROM <urn:demo.openlinksw.com:prestoDB> 
FROM <urn:demo.openlinksw.com:csv> 
WHERE {
        ?s a foaf:Organization .
      }  

Live Query Results Link

Test 2 Inference Disabled

# DEFINE input:inference 'sql-vdb-data-lake' 

SELECT  (COUNT(*) AS ?count)  
FROM <urn:demo.openlinksw.com:OracleHR> 
FROM <urn:demo.openlinksw.com:ingres> 
FROM <urn:demo.openlinksw.com:informix> 
FROM <urn:demo.openlinksw.com:progress11> 
FROM <urn:demo.openlinksw.com:SQLServer> 
FROM  <urn:demo.openlinksw.com:School>  
FROM <urn:demo.openlinksw.com:sybase12> 
FROM <urn:demo.openlinksw.com:prestoDB> 
FROM <urn:demo.openlinksw.com:csv> 
WHERE {
        ?s a foaf:Organization .
      }  

Live Query Results Link

Test 3 Inference Enabled

DEFINE input:inference 'sql-vdb-data-lake' 

SELECT DISTINCT ?s as ?organization
FROM NAMED <urn:demo.openlinksw.com:OracleHR> 
FROM NAMED <urn:demo.openlinksw.com:ingres> 
FROM NAMED <urn:demo.openlinksw.com:informix> 
FROM NAMED <urn:demo.openlinksw.com:progress11>
FROM NAMED <urn:demo.openlinksw.com:SQLServer> 
FROM NAMED <urn:demo.openlinksw.com:School> 
FROM NAMED <urn:demo.openlinksw.com:sybase12> 
FROM NAMED <urn:demo.openlinksw.com:prestoDB> 
FROM NAMED <urn:demo.openlinksw.com:csv> 
WHERE {
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:OracleHR>  {?s a foaf:Organization . } } LIMIT 1
         } 
		 
		 UNION
		 
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:ingres> {?s a foaf:Organization . } } LIMIT 1
        } 
		
		UNION
		
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:informix> {?s a foaf:Organization . } } LIMIT 1
        } 
		
		UNION
		
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:progress11> {?s a foaf:Organization . } } LIMIT 1
        } 
		
		UNION
		
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:SQLServer> {?s a foaf:Organization . } } LIMIT 1
        } 
		
		UNION
		
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:School> {?s a foaf:Organization . } } LIMIT 1
        } 
		
		UNION
		
		
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:sybase12> {?s a foaf:Organization . } } LIMIT 1
        } 
		
		UNION
		
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:prestoDB> {?s a foaf:Organization . } } LIMIT 1
        } 
		
		UNION
		
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:csv> {?s a foaf:Organization . } } LIMIT 1
        } 
		
      }  

Live Query Results Link

Test 3 Inference Disabled

# DEFINE input:inference 'sql-vdb-data-lake' 

SELECT DISTINCT ?s as ?organization
FROM NAMED <urn:demo.openlinksw.com:OracleHR> 
FROM NAMED <urn:demo.openlinksw.com:ingres> 
FROM NAMED <urn:demo.openlinksw.com:informix> 
FROM NAMED <urn:demo.openlinksw.com:progress11>
FROM NAMED <urn:demo.openlinksw.com:SQLServer> 
FROM NAMED <urn:demo.openlinksw.com:School> 
FROM NAMED <urn:demo.openlinksw.com:sybase12> 
FROM NAMED <urn:demo.openlinksw.com:prestoDB> 
FROM NAMED <urn:demo.openlinksw.com:csv> 
WHERE {
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:OracleHR> {?s a foaf:Organization . } } LIMIT 1
         } 
		 
		 UNION
		 
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:ingres> {?s a foaf:Organization . } } LIMIT 1
        } 
		
		UNION
		
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:informix> {?s a foaf:Organization . } } LIMIT 1
        } 
		
		UNION
		
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:progress11> {?s a foaf:Organization . } } LIMIT 1
        } 
		
		UNION
		
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:SQLServer> {?s a foaf:Organization . } } LIMIT 1
        } 
		
		UNION
		
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:School> {?s a foaf:Organization . } } LIMIT 1
        } 
		
		UNION
		
		
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:sybase12> {?s a foaf:Organization . } } LIMIT 1
        } 
		
		UNION
		
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:prestoDB> {?s a foaf:Organization . } } LIMIT 1
        } 
		
		UNION
		
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:csv> {?s a foaf:Organization . } } LIMIT 1
        } 
		
      }  

Live Query Results Link

Test 4 Inference Enabled

DEFINE input:inference 'sql-vdb-data-lake' 

SELECT DISTINCT ?s 
FROM NAMED <urn:demo.openlinksw.com:OracleHR> 
FROM NAMED <urn:demo.openlinksw.com:ingres> 
FROM NAMED <urn:demo.openlinksw.com:informix> 
FROM NAMED <urn:demo.openlinksw.com:progress11>
FROM NAMED <urn:demo.openlinksw.com:SQLServer> 
FROM NAMED <urn:demo.openlinksw.com:School> 
FROM NAMED <urn:demo.openlinksw.com:sybase12> 
FROM NAMED <urn:demo.openlinksw.com:prestoDB> 
FROM NAMED <urn:demo.openlinksw.com:csv> 
WHERE {
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:OracleHR> {?s a foaf:Person .  } } LIMIT 1
         } 
		 
		 UNION
		 
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:ingres> {?s a foaf:Person .  } } LIMIT 1
        } 
		
		UNION
		
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:informix> {?s a foaf:Person .  } } LIMIT 1
        } 
		
		UNION
		
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:progress11> {?s a foaf:Person .  } } LIMIT 1
        } 
		
		UNION
		
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:SQLServer> {?s a foaf:Person .  } } LIMIT 1
        } 
		
		UNION
		
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:School> {?s a foaf:Person .  } } LIMIT 1
        } 
		
		UNION
		
		
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:sybase12> {?s a foaf:Person .  } } LIMIT 1
        } 
		
		UNION
		
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:prestoDB> {?s a foaf:Person .  } } LIMIT 1
        } 
		
		UNION
		
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:csv> {?s a foaf:Person .  } } LIMIT 1
        } 
		
      }  

Live Query Results Link

Test 4 Inference Disabled

DEFINE input:inference 'sql-vdb-data-lake' 

SELECT DISTINCT ?s 
FROM NAMED <urn:demo.openlinksw.com:OracleHR> 
FROM NAMED <urn:demo.openlinksw.com:ingres> 
FROM NAMED <urn:demo.openlinksw.com:informix> 
FROM NAMED <urn:demo.openlinksw.com:progress11>
FROM NAMED <urn:demo.openlinksw.com:SQLServer> 
FROM NAMED <urn:demo.openlinksw.com:School> 
FROM NAMED <urn:demo.openlinksw.com:sybase12> 
FROM NAMED <urn:demo.openlinksw.com:prestoDB> 
FROM NAMED <urn:demo.openlinksw.com:csv> 
WHERE {
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:OracleHR> {?s a foaf:Person .  } } LIMIT 1
         } 
		 
		 UNION
		 
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:ingres> {?s a foaf:Person .  } } LIMIT 1
        } 
		
		UNION
		
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:informix> {?s a foaf:Person .  } } LIMIT 1
        } 
		
		UNION
		
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:progress11> {?s a foaf:Person .  } } LIMIT 1
        } 
		
		UNION
		
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:SQLServer> {?s a foaf:Person .  } } LIMIT 1
        } 
		
		UNION
		
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:School> {?s a foaf:Person .  } } LIMIT 1
        } 
		
		UNION
		
		
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:sybase12> {?s a foaf:Person .  } } LIMIT 1
        } 
		
		UNION
		
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:prestoDB> {?s a foaf:Person .  } } LIMIT 1
        } 
		
		UNION
		
        { 
         SELECT DISTINCT ?s { GRAPH <urn:demo.openlinksw.com:csv> {?s a foaf:Person .  } } LIMIT 1
        } 
		
      }  

Live Query Results Link

Related

1 Like