Ontologies are a well-affirmed way of representing complex structured information and they provide a sound conceptual foundation to Semantic Web technologies. On the other hand, a huge amount of information available on the web is stored in legacy relational databases. The issues raised by the collaboration between such worlds are well known and addressed by consolidated mapping languages. Nevertheless, to the best of our knowledge, a best practice for such cooperation is missing: in this work we thus present a method to guide the definition of cooperations between ontology-based and relational databases systems. Our method, mainly based on ideas from knowledge reuse and re-engineering, is aimed at the separation of data between database and ontology instances and at the definition of suitable mappings in both directions, taking advantage of the representation possibilities offered by both models. We present the steps of our method along with guidelines for their application. Finally, we propose an example of its deployment in the context of a large repository of bio-medical images we developed.
Ontology-based knowledge representation systems are well known to be successful in representing complex and heterogeneous information. In particular, recently, Semantic Web tools and systems permit to build and reason over ontologies providing logically founded representations and even increasing possibilities for data size. Moreover, the growing interest and availability of Semantic Web ontologies opens the possibility to reuse known data sources and, above all, to share and integrate information between systems.
On the other hand, the vast majority of data is nowadays stored in
relational databases, so tools and techniques bridging ontology-based repositories
and relational databases are needed in order to effectively deploy the
potential provided by ontology-based representations. Significant efforts have been
made to make possible to provide translations between ontologies and relational
schemas in order to easily publish readily available database data: however, there
is no accepted way on how to use such tools to let cooperate an existing
relational database system with a paired ontology based system. For example, to
the best of our knowledge, there is no method supporting the decision on what
to represent and how to map information in both directions by using already
available mapping languages and tools (such as D2R [
In this work we propose our experiences in the collaboration of an
ontologybased knowledge base and a legacy relational database under a single application.
In particular, believing in the fact that the problems of this setting can be
quite common, we try to generalize the approach that we chose in our case
to a general method for the integration between an ontology and a relational
database schema, when deployed together in a Semantic Web-based application.
We refer to the definition of method provided in the context of knowledge
reengineering [
We point out that we do not aim at defining a novel mapping language between ontologies and relational schemas. Rather we aim at a method for deciding what are, loosely speaking, the relationships occurring between the ontology and the relational database, e.g. to decide what data stored in the relational database may be fruitfully published as RDF or on what data apply the inference tools proper to ontologies, that is, how to distribute data between both repositories in order to take advantage of the capabilities of the two representations. We have deployed our method and guidelines during the implementation of a large image database currently in use at a veterinary institute (namely, Istituto Zooprofilattico Sperimentale della Lombardia e dell’Emilia Romagna, IZSLER for short6) serving a large user base distributed over more than fifteen sites in northern Italy. In the following we will briefly introduce the structure of the system that lead us to the formulation of this method: the Imm@base system, a repository of bio-medical images supporting advanced classification-based functionalities. 1.1
The definitions of the method and of the guidelines described in this paper have been carried out in the context of a project to satisfy the necessity of a major italian veterinary institute, the previously mentioned IZSLER. The requirement was to create a repository of biomedical pictures to be annotated with semantic information from well-known biomedical taxonomies, such as ICTVdb7 and
NCBI8. Moreover, the institute required a further classification of the pictures according to the medical cases they refer to. Such information is stored in a legacy RDB system, called DARWIN, which can not be modified for legal and pragmatic reasons. As an example of the firsts, the information stored in the database is used to quantify the refund which several farmers are entitled of in case of epidemics.
The architecture of the resulting application is shown in Fig. 1. According to the present architecture, the user interacts with the application through a web interface developed in PHP. Such interface provides, in a comprehensive way: (i). a guided procedure to upload new pictures, properly annotated with metadata retrieved from the ontology database, which – we remind – contains both semantic data from the domain ontologies and a semantic technologybased representation of the data contained in the legacy DARWIN system used by the veterinary institute. (ii). a web form for retrieving pictures and medical cases matching complex criteria defined by the user.
All the semantic data is retrieved via both ad-hoc and dynamically composed
SPARQL queries used against a Joseki end-point. The domain ontologies data
and the annotated pictures are stored in a PostgreSQL database while the
DARWIN system uses of a SQLServer database. The first database has been created
using the tools provided by the Jena API while the others are connected by
means of D2RQ [
As it is easy to understand, the proposed architecture asks for the definition of a clear policy of cooperation between the semantic and relational repositories. After summarizing the related works on such cooperations, we introduce our solution, presented as a general method specification. 2
Several works address the issue of generating Semantic Web content from data stored in traditional databases. Such works can be classified, according to the chosen approach, in three categories: (i). Annotation of the data extracted from databases with informations tracking how data have been obtained, (ii). Mapping of the database model to an ontology, (iii). Generation of an ontology related to the relational model of the DB.
The first approach works with the so-called DeepWeb [
The second approach consists in mapping the database models to a given
ontology by means of a mapping language in order to provide access to the
content of the database as if it were a “semantic repository” [
Images Database D2RQ
Immabase (PHP) Server SPARQL (Joseki)
Ontology
Database DataLoader (Jena)
D2RQ 'le'le'le
Domain Ontology
NCBI ICTV ....
DARWIN
such approach are D2RQ [
The third approach consists of the semi-automatic generation of an
ontology from the database schema [
Moreover, several works have been presented with respect to the development
of tools and algorithms to automatically match and merge ontology schemas,
such as [
The method we propose is aimed at guiding the separation of data between relational database (RDB) objects and ontology instances and defining a suitable mapping between the two repositories, in order to let them cooperate consistently. To achieve this, we have to address several issues, namely: – the treatment of consistent references between the two schemas, – the integration in an existing repository of an external data source, – the identification of static and changing data, – the decision on where to store schema instances.
The method defines a mapping table, which specifies, for each conceptual object
(entity or relationship) in both the re-engineered repositories, where to store
the respective instances and whether and how to refer to them. The mapping
table should be sufficient to define a formal mapping between the sources, either
by modifying the representation of conceptual objects in both sides or defining
mapping in both directions e.g. by using mapping languages as D2RQ [
In this section we present the tasks of our method using the following schema,
derived from the definitions in [
The method is composed by two distinct activities: the first one is a reverse
engineering phase on the available information about DB and ontology, while the
second is a forward engineering phase for the definition of the mapping. In the
first activity A1 the method analyzes the available description of the database
and ontology (either the conceptual schema, the requirements or directly the
sources structure) in order to extract a conceptual representation of the
entire system. In T1 and T2, thus, the conceptual schemas of the two sources
is retrieved or generated from the descriptions. The two are combined in T3
by recognizing and merging (possibly automatically [
This structure is coherent with the one presented in [
The outputs of our method are two mapping tables: the entity table and the properties table. The entity table should describe, for each conceptual entity: – Ontology class, DB table: where its instances are stored, – Mapping: logical mapping on classes and DB tables, – ID: property chosen as identifier, – Source: original source.
The properties table should describe, for each relation: – Ontology property, DB column: where its instances are stored, – Mapping: mapping on ontology properties and DB columns, – Domain and range: conceptual entities linked by the property. We present an execution of our method and an example of the resulting documents in the following sections. A1.Conceptual Modeling Activity Reverse engineering on sources to extract complete conceptual schema of the system.
Input: DB and ontology, their conceptual models or requirements Output: Total conceptual model T1. DB schema extraction
Extract conceptual schema from DB.
Input: DB, requirements or original DB schema
Output: DB conceptual schema T2. Ontology schema extraction
Extract conceptual schema from ontology.
Input: Ontology, requirements or original ontology schema
Output: Ontology conceptual schema T3. Total schema definition
Merge previous schemas to obtain a complete system schema.
Input: DB and ontology schemas Output: Total conceptual model
A2. Mapping Definition Activity Forward engineering on extracted model for the definition of mapping tables.
Input: Total conceptual model Output: Complete mapping tables T4. Shared schema extraction
Identify conceptual objects to be shared between schemas.
Input: Total conceptual model
Output: Shared conceptual schema T5. Instances distribution
For every entity, decide where to store its instances.
Input: Shared conceptual schema
Output: Instances table T6. Relationships distribution
For every relationship, decide where to store its instances.
Input: Shared schema, instances table
Output: Properties table T7. Logical modeling
Define actual classes and tables to be mapped.
Input: Shared schema, mapping tables
Output: Complete mapping tables In the following we suggest some guidelines useful for the application of our method and the definition of the mapping between DB and ontology. First of all, the following guidelines may drive the decision on which of the two schemas refer when storing instances of a conceptual object.
– Ontology instances: data can be stored as instance of ontology classes and properties mostly because it is necessary to draw inferences from this data. This can be useful when arranging data in complex taxonomies or meronimies or when it is needed to verify correctness of the data with respect to the ontology logical constraints. Another scenario where this choice is necessary is when one needs to comply with an external (possibly standard) ontology. In general, ontology instances should be treated as fixed and non-changing data, mostly representing “metadata” of the application to be developed. – RDB instances: on the other hand, DB instances should represent the “working data” of the application, that is the data that one expect to be updated and changed the most. Other reasons to leave out such data from the ontology include the fact that only simple queries (and no inferences) over this data are needed, or the fact that they represent only “administrative” data that is uninteresting to map and publish over the ontology.
Note that this means that the actual data would be stored in the DB, while the metadata would be stored as ontology instance. Note also that, in both cases, the choice can be affected by where the original instances were stored, the modifiability of the sources or the impact that these modification can cover. Moreover, as it is clear from the method, not every conceptual object in both parts takes part in the mapping: however, by re-engineering the conceptual schema we can decide to move an object from a schema to the other.
Once the choice on where to store instances has been done, the following guidelines suggest how to map and identify these instances in the two directions, so that they are visible in the other schema.
– Instances in DB, class in ontology: this partly corresponds to the case treated by mapping tools. From DB to ontology, entity instances can be mapped to individuals of a class naming them, e.g. as ClassName ID. To identify in the DB mapped instances once they are retrieved from the ontology, one can map the ID or primary key of the instance as a hasID datatype property value referred to the ontology instance. – Instances in ontology, values in DB: we can suggest different solutions to access or refer to external ontology instances into the DB records. A solution consists in directly using the URI of the referred individual in the DB tuples: however, this solution can be non satisfactory in that one can not check the validity and consistency of the references and can not add information to such individuals in the DB. Another solution is to keep in the DB a table relating the DB tuples to their counterpart individual in the ontology: additional data for the objects (not available in the ontology) can be stored in other columns of such table. A similar solution, but more demanding in terms of updates to the DB schema, consists in using the URI (or a transformation of it) of the ontology instance as the ID of the tuple of their counterpart in the DB. A relaxation of the previous solution consists in defining a transformation from the URI (or other property value) of ontology individuals to the value used as ID in the DB. 4
In the following section we present a simple example for the application of the previously proposed method. More precisely, we present a simplification of the actual integration of the DB schema and ontology mentioned in our motivating scenario. Note that the operations described in the following of the section have been performed manually because of the dimension of the problem. In order to deal with more complex scenarios it will be required to develop tools supporting the tasks described in Section 3. In the case of our example, we assume to already have the ontology conceptual schema (since basically we are adding a new ontology to an existing DB based system) and to be able to extract the conceptual schema from DB tables. Moreover, we assume (by relaxing the situation of our motivating scenario) that we can freely modify both parts. In our example, ideally, the DB mostly contains the data pertaining to the actual files representing medical images, while the ontology stores the relations between the concepts represented in the subject of images.
Given these premises, in the following we proceed trough the tasks of our method, providing examples for the most relevant produced documents. For simplicity, we only represent the properties and relations of the main entities of our system.
After the first two tasks T1 and T2, given the previous assumptions, we obtain the conceptual schema of the DB and of the ontology, which are shown in Fig. 3 and Fig. 4. In particular, note that their structure is slightly different: e.g. the entity Origin (representing the method of acquisition of an image) appears only in the DB schema, while in the ontology schema Image is specialized in MacroImage and MicroImage (actual photographs versus microscopy images) which need to be treated differently in our system. Most notably, only the ontology contains the relations between the entities representing subject properties as in the case of hasPosition for Lesion. In task T3 we merge the two schemas in the total conceptual schema, considering the shared attributes: for example, note the case of the information about gender, in the DB represented as attribute and in the ontology as object property. We do not show this schema, for space and significance reasons. After obtaining the total schema, we can also begin to define the contents of the entity and property tables, mainly by filling in the names of entities and the properties with their specified domain and range.
We can now begin the forward engineering activity: the first task T4 consists in identifying the shared schema in the total schema, which corresponds in picking out the instances that are not to be mapped, following the given guidelines. For example Origin and the attributes as author and notes only belongs to the DB while MicroImage is only to be contained in the ontology. The shared schema obtained in this task is shown in Fig. 5. The next two tasks T5 and T6 consist in separating the instances and relations of DB and ontology and thus defining the direction and the choices for the mapping, as suggested in our guidelines. For example, note that since Protocol and Image represent the data of our system, they are stored in the DB and mapped to their classes in the ontology. On the other hand, the objects actually detailed in the ontology have to be only referred in the DB instances.
In the last task T7, the mapping tables are completed with the actual DB tables and columns to be mapped. The final mapping tables for our example are shown in Table 1. Note that the proposed tables only contain relevant parts of the actual mapping tables for our schemas. We remark that the structure and notations used to present our method are simply suggestions for a manual execution of the method and can be replaced or hidden to the user in case of an implementation. Entity Mapping Onto.Class DB Table ID Source Protocol DB - O Protocol Protocol Protocol.ID O, DB Image DB - O Image Image Image.ID O, DB MacroImage DB - O MacroImage Image Image.ID O MicroImage O MicroImage - URI O Species O - DB Species - URI O, DB Gender O - DB Gender - URI O Organ O - DB Organ - URI O, DB
Origin DB - Origin Origin.ID DB Domain Property Range Type Mapping DB column Image (DB) representsDiagnosis Diagnosis (O) object DB - O Image.diagnosis origin Origin (DB) object DB Image.origin hasImageCode <string> datatype DB - O Image.code image notes <string> datatype DB Image.notes author <string> datatype DB Image.author ... ... ... ... ...
MacroImage (DB) representsOrgan Organ (O) object DB - O Image.organ representsLesion Lesion (O) object DB - O Image.lesion isCarcass <boolean> datatype DB - O Image.carcass Lesion (O) hasPosition Organ (O) object O In this paper we presented a method that allows a relational database and an ontology – as deployed in a Semantic Web application – to collaborate towards a fruitful distribution of data between them. We also provided guidelines in order to support the decisions to be taken in the deployment of our method. We defined the presented method motivated by the scenario of a system for the management of bio-medical images. In such project, semantic technologies are used to relate data from a relational database containing information about images to ontologies containing complex metadata classifying them.
The method we proposed in these pages represents a first step towards the definition of a generally applicable re-engineering process: for its further development, it is certainly necessary to refine and evaluate the proposal with experiences on several real-world applications scenarios. Moreover, the proposed guidelines are not thought to constitute a complete best practice, but they want to draw the attention to some relevant aspects of the cooperation and possibly promote discussion about these issues. Another interesting direction for further developments is the study of the automatization possibilities and the effective implementation for the tasks of our method.
We would like to thank the IZSLER institute for the support and collaboration.