In order to integrate relational databases into Semantic Web applications, relational databases need to be mapped to RDF. The W3C RDB2RDF Working Group is in the process of ratifying two standards to map relational databases to RDF: Direct Mapping and R2RML mapping language. Through our experience as implementors of two RDB2RDF systems: Ultrawrap and Morph, and as authors of R2RML mappings, we have observed mappings that are reusable in order to solve a commonly occurring problem. In this paper, we have compiled these mappings and present a non-exhaustive list of RDB2RDF Mapping Patterns. We aspire that the mapping patterns in this paper are considered as a starting point for new mapping patterns.
Assuming the RDB2RDF standards are widely adopted, we expect the mapping patterns to increase. We aspire that the mapping patterns in this paper are considered as a starting point for new mapping patterns. 2
We present an example that motivates the need of RDB2RDF mapping patterns.
Due to lack of space, we do not present an overview of R2RML. We refer the
reader to the R2RML spec [
We observe a One to One mapping between tables and ontology classes and a One to One mapping between attributes and ontology properties.
Now assume that the table Student has a new attribute, mobilephone, which we would also like to map to foaf:phone. This means that we would need to have a Many to One mapping between attributes and an ontology property. The previous mapping could be augmented by adding rr:objectMap [rr:column "mobilephone"]; to the existing rr:predicateObjectMap that has foaf:phone as a predicate. Another solution would be to repeat the entire rr:predicateObjectMap, but with a rr:column of mobilephone. This type of pattern impacts query performance. The following SPARQL query: SELECT ?s ?o WHERE f?s foaf:phone ?og would get translated to the following SQL query: SELECT id, homephone FROM Student UNION SELECT id, mobilephone FROM Student. If we were to increase the amount of attributes mapped to the same ontology property, the size of the SQL query would increase. This example suggests that there is a tradeo between mapping patterns and query performance. In order to further study tradeo s and design decisions of RDB2RDF mappings, it is important to understand the di erent types of mapping patterns. In the next section, we present fourteen mapping patterns which we have observed as implementors of RDB2RDF systems and authors of R2RML mappings. 3
A RDB2RDF mapping pattern is a reusable mapping that solves a commonly occurring problem. We present four type of mapping patterns: Attribute Mapping Patterns, Table Mapping Patterns, Join Mapping Patterns and Value Translation Patterns. Each pattern consists of a name, a question that de nes the problem that is being addressed, description of the context, description of the solution in R2RML, an example R2RML mapping, a discussion and related patterns. Some mapping patterns may consist of di erent R2RML solutions. 3.1
Context: An application would like to map a tables to an ontology class. Moreover, every record of the tables is mapped to an instance of the corresponding ontology classes. For example, the table student is mapped to foaf:Person. Solution: Create a TriplesMap for the table and specify the rr:logicalTable whose value corresponds to the table name. In the TriplesMap, create a rr:subjectMap with a rr:template to de ne the URI template for each row. Finally, the rr:subjectMap will have a rr:class corresponding to the ontology class for that table.
<TriplesMapStudent> a r r : TriplesMap ; r r : l o g i c a l T a b l e [ r r : tableName " student " ] ; r r : subjectMap [ r r : c l a s s f o a f : Person ; r r : template " http : // example . com/ r e s o u r c e / Student /f s i d g" ] .
Discussion: This is the simplest pattern for table mapping. This is the case of the Direct Mapping, which automatically generates a unique ontology class for each table. However, a user has the option to specify a particular ontology class.
Context: An application would like to map a table to many ontology classes. Moreover, every record of the table is mapped to an instance of the corresponding ontology classes. For example, the table student is mapped to foaf:Person and ex:Student.
Solution: Create a TriplesMap for the table and specify the rr:logicalTable whose value corresponds to the table name. In the TriplesMap, create a rr:subjectMap with a rr:template to de ne the URI template for each row. Finally, the rr:subjectMap will have multiple rr:class that correspond to the ontology classes for that table.
<TriplesMapStudent> a r r : TriplesMap ; r r : l o g i c a l T a b l e [ r r : tableName " student " ] ; r r : subjectMap [ r r : template " http :/ / example . com/ r e s o u r c e / Student /f s i d g"; r r : c l a s s f o a f : Person ; r r : c l a s s ex : Student ] .
Discussion: This pattern extends Pattern 1 by adding multiple rr:class.
3Except for the case when the table represents a many-to-many relationship
Context: An application would like to map many tables to an ontology class. Moreover, every record of these table are mapped to an instance of the corresponding ontology class. For example, the tables student and professor are both mapped to foaf:Person.
Solution: Repeat the solution in pattern 1 for each table to be mapped. Example R2RML Mapping <TriplesMapStudent> a r r : TriplesMap ; r r : l o g i c a l T a b l e [ r r : tableName " student " ] ; r r : subjectMap [ r r : c l a s s f o a f : Person ;
r r : template " http : / / example . com/ r e s o u r c e / Student /f s i d g" ] . <TriplesMapStudent> a r r : TriplesMap ; r r : l o g i c a l T a b l e [ r r : tableName " p r o f e s s o r " ] ; r r : subjectMap [ r r : c l a s s f o a f : Person ;
r r : template " http : / / example . com/ r e s o u r c e / P r o f e s s o r /f s i d g" ] .
Discussion: This pattern extends Pattern 1 and it is used when instances of an ontology class may be distributed over several tables.
Context: An application would like to map a table to several ontology classes. Additionally, the application would like to map an ontology class to several tables.
Solution: Repeat solution of pattern 2 for each table to be mapped.
<Trrrip: lseusbMjeacptSMtuapden[ t>rr rr a:: tcerl mars:psTlartfipoelae"fsh:MPtateppr s:;/on/r re; x: arlomrg:piclcelaa.lcsTosma/bexrlee: sAo[cuardrcreem:/tiacSbtlu]edNe.anmt /ef s "idstgu"d; ent " ] ; <Trrrip: lseusbMjeacptPMraopf>[ a rr rrr r:: t:ceTlmarsippsllaetfsoeMaa"fph:Pt;teprrrs:/o:n/l oe; xg aircmra:plclTel aa. cbsoslme /exr[ e: sArorcu:atrdacebeml/eicPNra]omf e.e s s o"rp/rfosf ieds sgo"r; " ] ; Discussion: This pattern extends Pattern 3. In addition to instances of an ontology classes are distributed over several database tables, each row of the database table produces multiple ontology instances.
Related Patterns: Pattern 2, Pattern 3 3.2
Attributes of tables are mapped to ontology properties. In R2RML, a TripleMap can have zero or more rr:predicteObjectMap, which in turn speci es a predicateobject pair. The following patterns de ne di erent ways that an attribute can be mapped to an ontology property.
Context: An application would like to map an attribute to an ontology property. For example, the attribute rstname is mapped to foaf:givenName. Solution: Given a TripleMap, create a rr:predicateObjectMap for the attribute, which has only one rr:predicate for the ontology property and a rr:objectMap for the attribute.
<TriplesMapStudent> a r r : TriplesMap ; r r : l o g i c a l T a b l e [ r r : tableName " student " ] ; rr rr rr:: rrspur::beotdejbemijcceptacMlttaMeatOpaebpj"[eh[crtttrMp:ac:/pol/ue[mxnar mr":pfplierr.escdtonimcaa/mtreee s"of]uo; arcf] e: /g.iSvteundNeanmt /ef; s i d g"; ] ; Discussion: This is the simplest pattern for attribute mapping. This is the case of the Direct Mapping, which automatically generates a unique ontology property for each attribute.
Context: An application would like to map an attribute to several ontology properties. For example, the attribute lastname is mapped to foaf:familyName and ex:apellido.
Solution 1: Given a TripleMap, create a rr:predicateObjectMap for the attribute, which has a rr:predicate for each ontology property and a rr:objectMap for the attribute.
<Trrrrripr::lrspeur:sbeoMdjbeaijcceptacSMtttMeuaOpadbpejn[et[c>rrtrrMa::actpoerlmru[:pmTlnrarrtipe":llpea"rssheMtdtnatiappcma:;/et/er"re]x;f: aol oma]gfpi:lcfeaa.mlcToimlay/bNrlaeems oe[u; rrcrrer: /t:aSpbtrlueeddNeiacnmat /teef s "eidxstg:ua"dp;een]lt;l i"d o] ;; Solution 2: Repeat the solution for Pattern 5 for the attribute to be mapped but having the rr:predicate speci c to each ontology property
<Trrrrrrriprr:::lrrsppeurr::sbeeppMddjrreaiieecccpddtaaSMiittccteeuaaaOOpdttbbeeejjn[eetefcc>xorttra:MMaf:aaat:pppefreamrlm[[l:piTildlayrotiNpe;alem"rsrehM;:tatoppbrrj:;e/:c/ortbreMxj:eaalcpomtgMpi[lcaerapr.lc:Toc[moarl/rbur:lmeceonslo[uu"mlrrancrse:t"/tnaSlaabtmsluetedNne"aan]mmt; e/ef"].s] "i;dstg]u;"d; en] t; " ] ; Discussion: Solution 1 is a short cut for Solution 2.
Context: An application would like to map several attributes to an ontology property. For example, the attribute homephone and mobilephone is mapped to foaf:phone.
Solution 1: Given a TripleMap, create a rr:predicateObjectMap, which has only a rr:predicate for the ontological property and a rr:objectMap for each attribute.
<TriplesMapStudent> a r r : TriplesMap ; r r : l o g i c a l T a b l e [ r r : tableName " student " ] ; r r : subjectMap [ r r : template " http : / / example . com/ r e s o u r c e / Student /f s i d g"; ] ; r r : predicateObjectMap [ r r : p r e d i c a t e f o a f : phone ;
r r : objectMap [ r r : column "homephone " ] ; r r : objectMap [ r r : column " mobilephone " ] ; ] . Solution 2: Repeat the solution for Pattern 5 for each attribute to be mapped Example R2RML Mapping for Solution 2 <TriplesMapStudent> a r r : TriplesMap ; r r : l o g i c a l T a b l e [ r r : tableName " student " ] ; r r : subjectMap [ r r : template " http : / / example . com/ r e s o u r c e / Student /f s i d g"; ] ; r r : predicateObjectMap [ r r : p r e d i c a t e f o a f : phone ;
r r : objectMap [ r r : column "homephone " ] ; ] ; r r : predicateObjectMap [ r r : p r e d i c a t e f o a f : phone ;
r r : objectMap [ r r : column " mobilephone " ] ; ] .
Discussion: Solution 1 is a short cut for Solution 2. As described in the motivating example of Section 2, this pattern impacts performance on queries that select on the ontology property.
Context: An application would like to map an attribute to several ontology predicates. Additionally, the application would like to map an ontology predicate to several attributes.
Solution 1: Combine Solution 1 of Pattern 6 with Solution 1 of Pattern 7.
<TriplesMapStudent> a r r : TriplesMap ; r r : l o g i c a l T a b l e [ r r : tableName " student " ] ; r r : subjectMap [ r r : template " http : / / example . com/ r e s o u r c e / Student /f s i d g"; ] ; r r : predicateObjectMap [ r r : p r e d i c a t e f o a f : phone ; r r : p r e d i c a t e ex : t e l e f o n o ; r r : objectMap [ r r : column "homephone " ] ; r r : objectMap [ r r : column " mobilephone " ] ; ] . Solution 2: Combine Solution 2 of Pattern 6 with Solution 2 of Pattern 7. Example R2RML Mapping for Solution 2 the result to an ontology property. For example, concatenate the attributes rstname and lastname and map it to foaf:name.
Solution 1: Given a TripleMap, create a rr:predicateObjectMap which has a rr:predicate for the ontology property and the rr:objectMap as a rr:template. The concatenation is represented as a template.
<TriplesMapStudent1> a r r : TriplesMap ; r r : l o g i c a l T a b l e [ r r : tableName " student " ] ; r r : subjectMap [ r r : template " http :/ / example . com/ r e s o u r c e / Student /f s i d g" ] ; r r : predicateObjectMap [
r r : p r e d i c a t e f o a f : name ; r r : objectMap [ r r : template "f f i r s t n a m e g f lastname g" ] ; ] . Solution 2: Create a new TripleMap with an R2RML view which consists of a rr:logicalTable that has a rr:sqlQuery which includes the concatenation. Additionally, create a rr:predicateObjectMap which has a rr:predicate for the ontology property and the rr:objectMap for the attribute which represents the concatenation in the SQL query.
Discussion: Consider the SPARQL query SELECT ?s ?fullname WHERE f?s foaf:name ?fullname g. Solution 1 would produce a SQL query and concat the name in the SELECT clause. Solution 2 does the concatenation operation as a SQL query as speci ed in rr:sqlQuery and then use this query as a subquery. S o l u t i o n 1 SQL : SELECT sid , f i r s t n a m e j j ' ' j j lastname as fullname FROM student S o l u t i o n 2 SQL : SELECT sid , fullname
FROM (SELECT sid , f i r s t n a m e j j ' ' j j lastname AS fullname
FROM student ) Note that these queries are semantically equivalent. From a query performance perspective, they should be equal unless the RDBMS does not have optimizations for subqueries.
Foreign Key relationships among tables can be mapped to ontology properties. The following patterns de ne di erent ways that foreign key relationships can be mapped to an ontology property.
Context: An application would like to map a table to an ontology class. However, some of the property values are stored in another table. Therefore, it is necessary to perform a join to get those values.
Solution: Given two tables, one table will be considered the child and the other the parent. Create a TripleMap for each table. Given the child TripleMap, create a rr:predicateObjectMap which will have, in addition to the rr:predicate, a rr:objectMap which has a rr:parentTripleMap and a rr:joinCondition. The rr:parentTripleMap will point to the parent TripleMap and the rr:joinCondition will have a rr:child and rr:parent which represent the join attributes in the child and parent table respectively.
Discussion: This pattern describes an R2RML mapping that joins two tables. This mapping can also be represented using Pattern 11. However, if a join involves more than two tables, then Pattern 11 must be used. The addition of another property that involves a join in Pattern 10 means that the user has to specify a new parent TriplesMap and then refer this parent TriplesMap in a rr:objectMap for the new property. On the other hand, the rr:logicalTable value stays the same and no changes needed.
Context: An application would like to map a table to an ontology class. However, some of the property values are stored in other tables. Therefore, it is necessary to perform a joins to get those values.
Solution: Create a TripleMap with an R2RML view which consists of a rr:logicalTable that has a rr:sqlQuery which includes a SQL query that represent explictly the join(s).
<TriplesMapStudent> a r r : TriplesMap ; r r : l o g i c a l T a b l e [ r r : sqlQuery """ rr rrWSr::ErspHLuEr:EbeRoCdjbEeTijccestacs.Mttc.MeasoOpaiubdpnj[etA[crSrtyrrMrs:oait:fdpetmeb,m[iprcpltra.lhrcateot:=eup"nr"chethdtr.tyctiptcopca:u/ot:n//det/eerxeeyAxxamaSi:dmcpocpleuo"leun."o.nt"rrot]gyrr;gO/y/rfceBroseidosroutehurFcr;RecOe/M/SCtusotduuendntetryn/f/tfs sicdi,dgcg"o";u; n];t] r;y ] c . Discussion: If the join is between two tables, then Pattern 10 can be used. However, if the join is between more than two tables, then Pattern 10 can not be used and the SQL query must be made explicit. Unlike Pattern 10, Pattern 11 does not require to have additional TriplesMap needed in order to map the property that needs a join. An addition of a new property that involves a join does not require to create additional TriplesMap instance, but the user has to modify the SQL query.
Context: A many-to-many table represents a relationship between two entities. For example, the table StudentSport records the relationship of which Students play a speci c Sport. Several students can play a sport and several sports can be played by a student. An application would like to map the many-to-many table to an ontology property.
Solution: Create a TriplesMap for the many-to-many table. Specify the rr:logicalTable whose value corresponds to the table name of the many-to many table. In the TriplesMap, create a rr:subjectMap with a rr:template to de ne the URI template for one of the tables of the many-to-many relationship. Create an instance of rr:predicateObjectMap which has a rr:predicate for the ontology property. Finally, create a rr:objectMap with a rr:template to de ne the URI template for the other table of the many-to-many relationship.
<TriplesMapStudentSport> a r r : TriplesMap ; r r : l o g i c a l T a b l e [ r r : tableName " StudentSport " ] ; rr rr :: spurbedjeicctaMteaOpbj[ecrtrM:atpem[plrarte: p"rhe dttipc a:/t/e exexa m:ppllaey. sor; g / r e s o u r c e / Student /f studentid g"; ] ; r r : objectMap [ r r : template " http :// example . org / r e s o u r c e / Sport /f s p o r t i d g" ] ; ] . Discussion: This mapping can also be represented through Pattern 11 given that it consists of a join between three tables (Student, StudentSport and Sport).
It is common that speci c values in the database are code values which need to be translated to URIs. R2RML relies on SQL's CASE statement for the translation. The following patterns de ne di erent ways that values can be translated.
Context: An application would like to map values in the data to IRIs. For example, if the value in a job attribute is engineer, then a special IRI needs to be generated. A translation using rr:template is not possible because templates can only use the same values from the database.
Solution: Create a new TripleMap with an R2RML view which consists of a rr:logicalTable that has a rr:sqlQuery. Represents the translation in the SQL query by using SQL CASE statement.
<#TriplesMap1> a r r : TriplesMap ; r r : l o g i c a l T a b l e [ r r : sqlQuery """
SELECT EMP. , (CASE JOB
WHEN 'CLERK' THEN ' general o f f i c e ' WHEN 'NIGHTGUARD' THEN ' s e c u r i t y '
WHEN 'ENGINEER' THEN ' engineering ' END) ROLE FROM EMP """ ] ; rr rr :: spurbedjeicctaMteaOpbj[ecrtrM:atpem[plrarte: p"rhe dttipc a:/t/e deaxta: .r eoxl ea m;ple . com/ employee /fEMPNOg"; ] ; r r : objectMap [ r r : template " http :// data . example . com/ r o l e s /fROLEg";
r r : termType r r : IRI ; ] ; ] .
Discussion: The R2RML language does not have the expressivity to represent such value translation. Therefore, translating values has been pushed into SQL using the CASE statement. Query performance depends on the optimizations that a RDBMS has for the CASE statement.
Context: An application would like to map a table to an ontology class. The table has a foreign key that references another table. The referenced table contains columns whose values need to be translated into URIs.
Solution 1: Combine Pattern 11 and Pattern 13.
<TriplesMapStudent> a r r : TriplesMap ; r r : l o g i c a l T a b l e [ r r : sqlQuery """ SELECT s . s i d AS sid , a . aid AS aid , (CASE a . a r t i c l e t y p e
WHEN ' ppr ' THEN ' Paper ' WHEN ' ths ' THEN ' Thesis ' ) AS ArticleType
FROM student s , a r t i c l e a WHERE s . s i d=a . author """ ] ; r r : subjectMap [ r r : template " http :/ / example . com/ r e s o u r c e / Student /f s i d g"; ] ; r r : predicateObjectMap [ r r : p r e d i c a t e ex : isAuthorOf ;
r r : objectMap [ r r : template " http : // example . com/ r e s o u r c e /f ArticleType g/f aid g " ; ] ; ] . Solution 2: Use Pattern 10 Example R2RML Mapping for Solution 2 new article type, and also the SQL needed to execute the mappings.
In solution 1, to add a new article type, a user just need to modify the SQL query, appending the corresponding WHEN THEN pair statement. In solution 2, a new (parent) TriplesMap instance is needed for each article type. Then, in the child TriplesMap, every article type needs a predicateObjectMap property.
Now consider the following SPARQL query: Solution 1 is the following: SELECT ?s ?o WHERE f ?s ex:isAuthorOf ?o g. The resulting SQL query for SELECT sid , ArticleType , aid FROM (
SELECT s . s i d AS sid , a . aid AS aid , (CASE a . a r t i c l e t y p e
WHEN ' ppr ' THEN ' Paper ' WHEN ' ths ' THEN ' Thesis ' ) AS ArticleType FROM student s , a r t i c l e a WHERE s . s i d=a . author )
The resulting SQL query for Solution 2 is the following: SELECT sid , aid
FROM student S , (SELECT author , aid FROM a r t i c l e WHERE a r t i c l e t y p e ='ppr ' ) P WHERE S . s i d = P. author UNION SELECT sid , aid FROM student S , (SELECT author , aid FROM a r t i c l e WHERE a r t i c l e t y p e =' ths ' ) T WHERE S . s i d = T. author
Note that the generated SQL queries are very di erent and may have an impact on query performance depending on the RDBMS.
Related Patterns: Pattern 10, Pattern 11, Pattern 13
In the (Object-Oriented) software community, patterns are used to describe
software design structures that can be used over and over again in di erent systems.
They provide a general solution that has to be applied in a particular context,
in which the design considerations serve to decide whether the pattern is
useful and how it could be implemented best [
In the Semantic Web community, the Ontology Design Pattern portal4 has
been created in order to help in the design and quality of ontologies. Additionally,
Dodds and Davis presents a pattern catalogue for modeling, publishing and
consuming Linked Data [
In this paper, we have introduced fourteen RDB2RDF mapping patterns5, which we have observed as reusable mapping throughout our experience as RDB2RDF system developers and R2RML mapping authors. As previously mentioned, this is a non-exhaustive list of mapping patterns and we aspire that the mapping patterns presented in this paper serve as a starting point. We foresee new mapping patterns in areas such as Named Graphs, Blank Nodes for anonymous or sensitive data, Metadata, Languages, Datatypes. We hope that this work encourages the Semantic Web community to further extend the RDB2RDF mapping patterns.
In certain patterns, we have identi ed two R2RML mapping solutions. These solutions may or may not have performance issues. As future work, we will 4http://ontologydesignpatterns.org/ 5The mappings are stored at http://mappingpedia.linkeddata.es/pattern/ DataPatterns/ thoroughly study the tradeo between mapping patterns and query performance. Additionally, we will investigate the overlap between ontology design patterns, linked data patterns, the feature set of Hert et al. and the RDF to RDF mapping patterns of Rivero et al. with RDB2RDF mappings in general.
Finally, we have to see how to align the proposed RDB2RDF mapping patterns with Re-engineering Patterns category in the ODP Portal. Acknowledgments: This work has been supported by the PlanetData, BabelData, and myBigData projects. Juan F. Sequeda was supported by the NSF Graduate Research Fellowship. We would like to kindly thank all W3C RDB2RDF Working Group members. We thank Richard Cyganiak for discussions.