Direct Mapping Tests

The direct mapping defines an RDF Graph [RDF] represention of the data in any relational database. This is called the direct graph. Foreign keys in relational databases establish a reference from any tuple in a relation to exactly one tuple in a (potentially different) relation. The direct graph conveys these references, as well as the values of the attributes in the tuple. Each tuple in the database produces a set of triples with a subject, predicate, and object composed as follows:

shared subject — The tuple generates a unique subject node. If the relation has no primary key, the subject node is a Blank Node. Otherwise, the subject node is an IRI composed of the stem, relation name and attribute name and value for each attribute in the primary key.
reference triples — Non-NULL Foreign keys generate triples with a:
- predicate — an IRI composed of the stem, relation name and attribute name and value for each attribute in the foreign key.
- object which is the subject node created for the referred triple.
literal triples — Non-NULL attributes generate triples with a:
- predicate composed of the stem, relation name and attribute name.
- object is an RDF Literal with an XML Schema datatype corresponding to the SQL datatype of that attribute.
Attributes which are also the sole attribute in a foreign key do not generate a literal triple. (Otherwise, there would be two values for that predicate.)

Primary-is-foreign Key Exception: If the primary key is also a foreign key K to relation R:

The shared subject is the subject node of the referenced tuple in R.
The foreign key K generates no reference triple.
Even if K is a single-attribute foreign key, it generates a literal triple.

The Hierarchical Relations tests shows how this exception is applied.

Following is SQL to create a simple example with two tables with single-attribute primkary keys and one foreign key between them:

CREATE TABLE Addresses (ID INT, city CHAR(10), state CHAR(2), PRIMARY KEY(ID))
CREATE TABLE People (ID INT, fname CHAR(10), addr INT, PRIMARY KEY(ID), FOREIGN KEY(addr) REFERENCES Addresses(ID))
INSERT INTO Addresses (ID, city, state) VALUES (18, "Cambridge", "MA")
INSERT INTO People (ID, fname, addr) VALUES (7, "Bob", 18)
INSERT INTO People (ID, fname, addr) VALUES (8, "Sue", NULL)

People
PK
ID	fname	addr
7	Bob	18
8	Sue	NULL

Addresses
PK
ID	city	state
18	Cambridge	MA

Given a stem of http://foo.example/DB the direct mapping of this data produces a direct graph:

@prefix xsd: <http://www.w3.org/2001/XMLSchema#> .

<http://foo.example/DB/People/ID.7#_> <http://foo.example/DB/People#ID> 7 .
<http://foo.example/DB/People/ID.7#_> <http://foo.example/DB/People#fname> "Bob"^^xsd:string .
<http://foo.example/DB/People/ID.7#_> <http://foo.example/DB/People#addr> <http://foo.example/DB/Addresses/ID.18#_> .
<http://foo.example/DB/People/ID.8#_> <http://foo.example/DB/People#ID> 8 .
<http://foo.example/DB/People/ID.8#_> <http://foo.example/DB/People#fname> "Sue"^^xsd:string .

<http://foo.example/DB/Addresses/ID.18#_> <http://foo.example/DB/Addresses#ID> 18 .
<http://foo.example/DB/Addresses/ID.18#_> <http://foo.example/DB/Addresses#city> "Cambridge"^^xsd:string .
<http://foo.example/DB/Addresses/ID.18#_> <http://foo.example/DB/Addresses#state> "MA"^^xsd:string .

In this expression, each tuple, e.g. (7, "Bob", 18), produces a set of triples with a common subject. The subject is an IRI formed from the concatenation of the stem, relation name (People), primary key name (ID) and primary key value (7). The predicate for each attribute is an IRI formed from the concatenation of the stem, relation name and the attribute name. The values are either RDF literals formed from the lexical form of the attribute value, or tuple identifiers in the case of foreign keys. Note that these tuple identifiers are necessarily identical to the subject of the tuple in the referenced relation.

Multi-attribute Keys

More complex schemas include compound and composite primary keys and potentially incomplete foreign keys. SQL foreign keys reference primary or candidate keys in the referenced relations. In this example, the attributes deptName and deptCity in the relation People reference name and city in the relation Department:

People
PK			→ Department.Key1
ID	fname	addr	deptName	deptCity
7	Bob	18	accounting	Cambridge
8	Sue	NULL	NULL	NULL

Addresses
PK
ID	city	state
18	Cambridge	MA

Department
PK	Key1
ID	name	city	manager
23	accounting	Cambridge	8

Per the People relation's foreign key to Department, we see:

The People tuple with ("accounting", "Cambridge") references a Department tuple with a primary key of ID=23.
The predicate for this key is formed from stem and "deptName_deptCity".
The node identifier (object of the above predicate) is formed from stem and "ID=23".

@prefix xsd: <http://www.w3.org/2001/XMLSchema#> .

<http://foo.example/DB/People/ID.7#_> <http://foo.example/DB/People#ID> 7 .
<http://foo.example/DB/People/ID.7#_> <http://foo.example/DB/People#fname> "Bob"^^xsd:string .
<http://foo.example/DB/People/ID.7#_> <http://foo.example/DB/People#addr> <http://foo.example/DB/Addresses/ID.18#_> .
<http://foo.example/DB/People/ID.7#_> <http://foo.example/DB/People#deptName> "accounting"^^xsd:string .
<http://foo.example/DB/People/ID.7#_> <http://foo.example/DB/People#deptCity> "Cambridge"^^xsd:string .
<http://foo.example/DB/People/ID.7#_> <http://foo.example/DB/People#deptName_deptCity> <http://foo.example/DB/Department/ID.23#_> .
<http://foo.example/DB/People/ID.8#_> <http://foo.example/DB/People#ID> 8 .
<http://foo.example/DB/People/ID.8#_> <http://foo.example/DB/People#fname> "Sue"^^xsd:string .

<http://foo.example/DB/Addresses/ID.18#_> <http://foo.example/DB/Addresses#ID> 18 .
<http://foo.example/DB/Addresses/ID.18#_> <http://foo.example/DB/Addresses#city> "Cambridge"^^xsd:string .
<http://foo.example/DB/Addresses/ID.18#_> <http://foo.example/DB/Addresses#state> "MA"^^xsd:string .

<http://foo.example/DB/Department/ID.23#_> <http://foo.example/DB/Department#ID> 23 .
<http://foo.example/DB/Department/ID.23#_> <http://foo.example/DB/Department#name> "accounting"^^xsd:string .
<http://foo.example/DB/Department/ID.23#_> <http://foo.example/DB/Department#city> "Cambridge"^^xsd:string .
<http://foo.example/DB/Department/ID.23#_> <http://foo.example/DB/Department#Manager> <http://foo.example/DB/People#ID.8#_> .

Primary keys may also be composite. If the primary key for Department were (name, city), the identifier for that first (only) tuple would be <http://foo.example/DB/Department/name.Accounting_city.Cambridge#_>.

Empty (Non-existent) Primary Keys

As seen above, tuples generate a set of triples with a shared subject. In relations with a primary key, that subject identifier is a URI constructed from the attributes in the primary key. In relations with no primary key, the subject is an RDF blank node.

Tweets
→ People.ID
tweeter	when	text
7	2010-08-30T01:33	I really like lolcats.
7	2010-08-30T09:01	I take it back.

@prefix xsd: <http://www.w3.org/2001/XMLSchema#> .

_:a <http://foo.example/DB/Tweets#tweeter> <http://foo.example/DB/People/ID.7#_> .
_:a <http://foo.example/DB/Tweets#when> "2010-08-30T01:33"^^xsd:dateTime .
_:a <http://foo.example/DB/Tweets#text> "I really like lolcats." .

_:b <http://foo.example/DB/Tweets#tweeter> <http://foo.example/DB/People/ID.7#_> .
_:b <http://foo.example/DB/Tweets#when> "2010-08-30T09:01"^^xsd:dateTime .
_:b <http://foo.example/DB/Tweets#text> "I take it back." .

It is not possible to dereference blank nodes ("_:a" and "_:b" above). Queries or updates may be made to these nodes via SPARQL queries.

Referencing Relations with Empty Primary Keys

Tuples in relations with no primary key may still be refrenced by foreign keys. (Relational theory tells us that these tuples must be unique as foreign keys reference candidate keys and candidate keys are unique across a relation.) References to tuples in relations with no primary key are expressed in direct graphs as RDF triples with blank nodes for objects, where that blank node is the same node used for the subject in the referenced tuple.

Projects
Key1
	Key2
→ People.ID		→ Department.Key1
lead	name	deptName	deptCity
8	pencil survey	accounting	Cambridge
8	eraser survey	accounting	Cambridge

TaskAssignments
PK
	→ Projects.Key2
→ People.ID		→ Departments.Key1
worker	project	deptName	deptCity
7	pencil survey	accounting	Cambridge

@prefix xsd: <http://www.w3.org/2001/XMLSchema#> .
@prefix pencil: <http://foo.example/DB/TaskAssignment/worker.7_project.pencil+survey#>

_:c <http://foo.example/DB/Projects#lead> <http://foo.example/DB/People/ID.8#_> .
_:c <http://foo.example/DB/Projects#name> "pencil survey"^^xsd:string .
_:c <http://foo.example/DB/Projects#deptName> "accounting"^^xsd:string .
_:c <http://foo.example/DB/Projects#deptCity> "Cambridge"^^xsd:string .
_:c <http://foo.example/DB/Projects#deptName_deptCity> <http://foo.example/DB/Department/ID.23#_> .

_:d <http://foo.example/DB/Projects#lead> <http://foo.example/DB/People/ID.8#_> .
_:d <http://foo.example/DB/Projects#name> "eraser survey"^^xsd:string .
_:d <http://foo.example/DB/Projects#deptName> "accounting"^^xsd:string .
_:d <http://foo.example/DB/Projects#deptCity> "Cambridge"^^xsd:string .
_:d <http://foo.example/DB/Projects#deptName_deptCity> <http://foo.example/DB/Department/ID.23#_> .

pencil:_ <http://foo.example/DB/TaskAssignments#worker> <http://foo.example/DB/People/ID.7#_> .
pencil:_ <http://foo.example/DB/TaskAssignments#project> "pencil survey"^^xsd:string .
pencil:_ <http://foo.example/DB/TaskAssignments#deptName> "accounting"^^xsd:string .
pencil:_ <http://foo.example/DB/TaskAssignments#deptCity> "Cambridge"^^xsd:string .
pencil:_ <http://foo.example/DB/TaskAssignments#deptName_deptCity> <http://foo.example/DB/Department/ID.23#_> .
pencil:_ <http://foo.example/DB/TaskAssignments#project_deptName_deptCity> _:c .

The absence of a primary key forces the generation of blank nodes, but does not change the structure of the direct graph or names of the predicates in that graph. This example SPARQL query would find the leads for person 7's projects regardless of whether the Projects relation had a primary key or not:

PREFIX asgn: <http://foo.example/DB/TaskAssignments#>
PREFIX proj: <http://foo.example/DB/Projects#>
SELECT ?manager
 WHERE {
    ?assignment asgn:worker <http://foo.example/DB/People/ID.7#_> .
    ?assignment asgn:project_deptName_deptCity ?project .
    ?project proj:lead ?manager .
 }

Hierarchical Relations

It is common to express hierarchies as mutiple relations sharing a common primkary key. In such cases, the primary keys of the inherited relations are in turn foreign keys to the relation from which they derive.

People
PK
ID	fname	addr
7	Bob	18

Addresses
PK
ID	city	state
18	Cambridge	MA

Offices
PK
→ Addresses.ID
ID	building	ofcNumber
18	32	G528

ExecutiveOffices
PK
→ Offices.ID
ID	desk
18	oak

In this example, Offices are a specialization of Addresses and ExecutiveOffices are a specialization of Offices. The subject nodes for the triples implied by tuples in Offices or ExecutiveOffices are the same as for the referenced Addresses tuple.

@prefix xsd: <http://www.w3.org/2001/XMLSchema#> .

<http://foo.example/DB/People/ID.7#_> <http://foo.example/DB/People#ID> 7 .
<http://foo.example/DB/People/ID.7#_> <http://foo.example/DB/People#fname> "Bob"^^xsd:string .
<http://foo.example/DB/People/ID.7#_> <http://foo.example/DB/People#addr> <http://foo.example/DB/Addresses/ID.18#_> .

<http://foo.example/DB/Addresses/ID.18#_> <http://foo.example/DB/Addresses#ID> 18 .
<http://foo.example/DB/Addresses/ID.18#_> <http://foo.example/DB/Addresses#city> "Cambridge"^^xsd:string .
<http://foo.example/DB/Addresses/ID.18#_> <http://foo.example/DB/Addresses#state> "MA"^^xsd:string .

<http://foo.example/DB/Addresses/ID.18#_> <http://foo.example/DB/Offices#ID> 18 .
<http://foo.example/DB/Addresses/ID.18#_> <http://foo.example/DB/Offices#building> 32 .
<http://foo.example/DB/Addresses/ID.18#_> <http://foo.example/DB/Offices#ofcNumber> "G528"^^xsd:string .

<http://foo.example/DB/Addresses/ID.18#_> <http://foo.example/DB/ExecutiveOffices#ID> "18"^^xsd:int .
<http://foo.example/DB/Addresses/ID.18#_> <http://foo.example/DB/ExecutiveOffices#desk> "oak"^^xsd:string .

The Primary-is-foreign Key Exception allows the generation of a triple with an RDF literal for the ID attribute in the Offices relation.