Comienza la invasión:

The assessment of these standards of the medical record shows no winner.

The contents formats are similar in the concept and in the possibilities, and are based on a two-level modelling approach with a reference model and a set of constraints rules (archetypes, templates) for mapping the clinical data into the reference model. The most advanced standard today is DICOM SR, but it is rather centralized and only accepted in the field of medical imaging. The other standards vary in the progress made in the standardization process. In principle, all seem pertinent to be implemented in the electronic medical record. However, the most likely candidates to offer a comprehensive solution are either the complete architecture of EHRcom with its still under development security features and patterns of exchange, or IHE XDS with a content structured format of CDA and access to the images on WADO. The last solution has been selected by several electronic medical records projects in the United States, Canada and France.

2.2.8 Conclusion

This last decade has seen the emergence and the rapid development of ICT and the explosion of the eHealth and medical informatics fields with works with the goal of providing a better quality of healthcare and increasing the productivity of the health services and reducing its costs. In fact, health professionals recognize the need of more informed decision-making and more cost-effective health information systems for improving the healthcare delivery and reducing the costs. Therefore, medical informatics

has established a presence within many academic and industrial research works and new challenges have emerged.

As we have presented in the state of the art, many initiatives have been taken for promoting the eHealth solutions over the world. However, the interoperability issue is still one of the major challenges in the eHealth domain. Indeed, a complete interoperability solution between health information systems is still far away from being operational due to the diversity of the actors and of their interactions, the multiplicity of disciplines in medicine and the vast variety of medical practices even for the same specialty within a given country. Medicine is not yet an exact science but it is quite an art, where the “truth” cannot be known. In addition, the increase of the mobility of the patients throughout the world and the language differences difficulty are other factors that make the interoperability issue for digital medical data exchange more challenging.

More recently, the global Information Society has started to experience the widespread use of pervasive computing and the development of smart Personal Health Systems (PHS) aimed at improving patient’s health support anywhere and anytime, by means of ICT. For example, the personal ECG monitor (PEM), an intelligent smart device for ubiquitous pHealth (personalized health) decision-support, allows everybody to self-record an ECG anywhere, at anytime, on demand when needed. The PEM has been elaborated within the EPI-MEDICS (Enhanced Personal, Intelligent and Mobile system for Early Detection and Interpretation of Cardiological Syndromes) European project within the framework of the Information Society Technologies Program, and was mainly centered on the design and development of an original, novel ECG-based PHS of professional quality [Rubel et al. 2005], [Fayn and Rubel 2010]. The PEM embeds advanced methods of signal processing and decision-making, and also a personalized medical record of the user of the device which is thus transmitted, via wireless technology, in XML format with the last recorded ECGs to the appropriate call center or referral physician in case of ECG abnormalities detection and alarm generation. Such a medical record including bio-signals and personal health data such as risk factors or biology data should then be automatically and immediately decoded and integrated in the information system of the recipient for enhancing its reuse.

Hence, the need to manage the interoperability and data mediation within health information systems is nowadays a key issue that this thesis is dealing with. eHealth is thus one of the main application domain that should especially benefit from the original research works that we performed in this thesis and that we will detail in the next two chapters.

Ch C h ap a p t t e e r r 3 3 - - X X M M L L ^{AN A N D D} R R ^{E E L L AT A T I I O O N N AL A L} D D ^{AT A T AB A B AS A SE E S S} M M ^{E E D D IA I AT T I I O O N N}

A A ^{UT U T O O MA M AT T I I ON O N}

Enterprises increasingly collaborate via web services by means of XML based data exchange technologies, while their information systems are usually storing data in relational databases which are still the most important type of data storage. Indeed, XML is considered as a key technology for data exchange that enables flexible processing and simple information exchange between heterogeneous applications. Meanwhile, the relational databases are a well-experimented, dominant technology to efficiently store, query and retrieve a huge volume of data. Consequently, a crucial issue in the data management is to bring together the flexibility of the XML model for communication and the performance of the relational model for data storage and retrieval. The main challenges of this issue are both: first, to be able to store the new data correctly into any existing relational database from any XML document and second, to be able to retrieve the required data from any relational database and to transform them into any XML format. This process should be automatic and transparent to the user, and should dynamically support any data structure.

In this chapter, we propose a novel mediation framework for automatic bi-directional data exchange between any XML source or sink and any Relational Database. This chapter presents the core contribution of our dissertation. It is organised into three main parts:

In the first part (section 3.1), we describe the architecture of the proposed generic mediation framework to automate data exchange between any XML file and any relational database.

In the second part (section 3.2), we describe a methodology for the automatic transformation of the relational model into an XML schema. This transformation methodology has been adopted and used in our proposed approach to automatically generate XSD schemas from any relational database model.

In the third part (section 3.3), we describe the automatic SQL generation methodology that has been used in our mediation framework.

3.1 A Generic Mediation Architecture

3.1.1 Introduction

Hereafter, we describe the proposed generic mediation framework which consists in the design of a generic XML-based data mediator for data exchange between any XML source or sink and any relational database.

The proposed mediator shall support an automatic, coherent and transparent updating of any relational database from any XML document [Jumaa et al.

2010b]. In the next section, we first illustrate the three general scenarios for data exchange between both models. Next, we present the architecture of the mediator, its components and its functions. Then, we discuss our proposed architecture.

3.1.2 General data exchange scenarios

For exchanging data between an XML model and an relational model, we can identify three main generic scenarios that are depicted in Figure 3-1.

In the first scenario (Figure 3-1 (a)), the relational data in the legacy systems should be exported or published into an XML format to facilitate the data exchange. In this scenario, the user (or system) receives or retrieves the needed or required data and is able to modify the received data in the XML format. Thus, the changes over the retrieved data should be transported by the mediator to coherently update the original data in the relational database in the legacy system.

In the second scenario (Figure 3-1 (b)), the user in a system issues a query to retrieve some data stored in a relational database (possibly in one or more databases). This query is defined in terms of an XML infoset with some conditions. Thus, the user defines the data he needs as well as the XML format he desires. Then, the mediator should generate a coherent and correct series of SQL queries to retrieve the required data and transform it into the required XML format defined by the user.

Furthermore, if the user updates or makes changes over the retrieved data,

as in the previous scenario, the mediator should support correct update of the relational database from the modified data.

In the third scenario (Figure 3-1 (c)), the data that have been produced by different source systems and/or devices are then exported in an XML format to be exchanged over the network. Thus the relational database in the legacy systems should be able to import the XML data whatever their structure or representation. Therefore, the mediator should provide means to automatically store into and update the relational database from any new XML incoming data, while data in the relational database will continue to be stored and queried efficiently and thus help the legacy system to survive and to be up-to-date. Indeed, we notice that the update operation in the first scenario is a sub case of the import operation where both the insert of new data and the update of old data should be considered.

Figure 3-1 The exchange scenarios between XML files and relational databases

3.1.3 Mediation framework design

To handle the problem of data exchange as described in the previous three generic scenarios we have proposed an XML based mediation framework.

The goal of this framework is to give a generic and automatic solution for the data exchange between any XML document and any existing relational database.

Figure 3-2 shows the overall architecture of the proposed mediation framework. This framework is based on the development of generic components, which will ease the setup of specific interfaces adapted to any source XML and any target relational database. The mediator components shall be independent of any application domain, and thus shall need to be customized only once for each couple of source and target data storage formats. Thus, the role of our XML-based mediator is to automatically store the data embedded in XML documents into a target relational database as well as to retrieve the results of any query over the database and to embed them into XML documents (or messages) structured according to a requested interchange format.

Figure 3-2 Overall architecture of the proposed data interchange XML-based mediator between XML documents and relational database

management systems

The incoming data, of course, should be compliant with a global data model that is known in advance, which is usually the case between cooperative organizations. But the proposed solution shall be also open to any implementation of the global model, even if some data are missing or if the data structure may be dynamically changed. On the other hand, the items in the incoming data must correspond to attributes of the target database, even if the database model can store additional data.

To design this generic XML-based mediation framework, we propose a solution that consists in the interconnection of meta-models of the source and target data sources using the XML language. Meta-models will be specified using the XML Schema Definition (XSD) language.

Indeed, to transfer data between XML documents and a relational database, it is important to match the XML document schema and the database schema. Since the structure of the XML documents may not exactly match the structure of the existing database, we will generate an XML schema of the relational database before carrying out the mapping process.

Specifically, in our framework we will first generate the physical model of the relational database by means of some standard reverse engineering technique. Then, the relational schema will be automatically converted into an equivalent XML schema using an “XSD converter from R schema”

component. Then a “XSD schemata matcher” will perform the correspondence between the XML source schema and the XML schema representing the target database relational schema. Finally, the “XSD schemata matcher” will produce the interconnection between the source and the target XML schemas for each pair of exchanged data sources. This interconnection will consist in building a set of XSLT mapping instances.

Furthermore, our framework will include a “Rule base” for supporting coherent and secure databases updates.

In the next section we detail the components of the proposed mediator architecture.

3.1.4 Mediator’s components

Our proposed architecture consists of four main components as depicted in Figure 3-2. First, the “XSD Generator from XML” generates the meta-model of each XML source as an XML schema that is described by means of the XSD language. Then the second component, the “XSD Converter from R Schema”, generates the meta-model of a relational database as an XML schema that is described in XSD. Third, the “Schemata Manager”

manages the meta-models of the XML sources and of the relational databases and produces the mapping for each couple of source and target data in the XSLT language. Each instance of XSLT allows to obtain an XML infoset compliant with a source schema that will be transformed into an XML infoset compliant with the target schema. Fourth, the “SQL generator” generates the correct SQL script to store the XML data into the relational database or to retrieve the needed infoset from the relational database. Hereafter, we detail more each of these components.

3.1.4.1 XSD Generator from XML

The role of the “XSD Generator from XML” component is to automatically create an XML schema in XSD language for each XML source, when such a schema is not already provided by the source. It uses the instance of the XML document to be exchanged with the target relational database in the generation process.

3.1.4.2 XSD Converter from R Schema

The role of the “XSD Converter from R Schema” component is to generate an XML schema from the database relational schema. This component is especially important because it maps the database schema into an equivalent XML representation. This XML representation must be properly created. Therefore we design a new methodology for automating the transformation of the XML schema from the relational model. It is described in details in section 3.2.

Briefly, our methodology to generate the target XML schema from a relational database consists of two main steps (see section 3.2). In the first step, a stratification algorithm is applied to classify the different relations of the relational schema. The latter may be obtained by applying a reverse engineering technique on the relational database. The result of the algorithm is a hierarchical representation of the relational schema into different relation levels according to the existing functional dependencies and to the foreign keys constraints. In the second step, a transformation algorithm is applied to generate the target XML schema according to the classification performed in the first step and based on the use of a set of generic XML schema fragments templates. These templates aim to map each component of the relational schema into its corresponding XML schema fragment. Then a highly nested target XML schema is incrementally built while accurately reproducing the database structure as well as preserving the semantics defined by the integrity constraints.

The resulting XML schema offers many major benefits: (1) it can be used to publish an XML view of relational data; (2) it allows to check the XML document for anomalies or incoherencies before updating the relational database from the XML document; (3) it can take into consideration the hierarchical view of the database tables, which will be very useful for generating the SQL scripts that shall be executed on the database.

3.1.4.3 Schemata Manager

The schema mapping between the exchanged source and target data is a prerequisite for a successful data exchange. This mapping is achieved by

the main component of the mediator, that we call the “Schemata Manager”.

This component includes four sub-components:

● The first sub-component is the “Sources Schemata” that includes the XML sources meta-model, which is specified using the XSD language to describe the layout of the data to be exchanged with each source.

● The second one is the “Target Schemata” that includes the relational database meta-model, which is specified using also XSD to describe the physical model of the relational database.

● The third sub-component is the “XSD schemata matcher”, which is specified using XSLT and describes the interconnections between the fields of the XML document meta-model and the database columns of the database meta-model.

● The last sub-component is a “Rule base”, which contains a set of pre-processing rules that will be used to verify the possible pre-existence of the data to be inserted in each table of the database, and to define the tables’ keys to be reused or created when generating the SQL scripts of the database updates with new data. The rules are mainly associated with the primary keys of the tables. As an example, when the value of a primary key is not known, a set of attributes can be defined to verify the existence of a row in a table. For instance, in the medical domain, the existence of a patient in the Patient table can be verified using the attributes: Last name, First name, Sex and Date of birth.

3.1.4.4 SQL Generator

The “SQL Generator” has two roles:

● The first is to generate the relevant SQL queries for each table’s row to be updated in the database, using the XML-infoset as input data.

The SQL queries are created according to the database meta-model (i.e. the XSD schema converted from the relational database schema), and use the rules specified in the rule base.

● The second role of the SQL generator is to act as an XML publisher of the result of each database query and to generate an XML-infoset that is compliant with the XML schema of the database meta-model.

3.1.5 Mediator functions

Here we present the general functional architecture of our proposed mediation solution between any XML document and any Relational Database. As depicted in Figure 3-3 we identify two main tasks for our mediator: the XML to DB Function which allows inserting or updating the

database from the XML file, and the DB to XML function which allows the extraction of data from the database into a required XML format.

Figure 3-3 Global functional architecture of the mediation solution between XML documents and relational databases

3.1.5.1 XML to DB function

The goal of this functionality is to allow the update of the target database from any XML data whatever its structure and to generate automatically and coherently the corresponding SQL Insert or/and update statements. The Figure 3-4 describes the data flow and management inside the mediator to realize this functionality.

Figure 3-4 XML to DB function

3.1.5.2 DB to XML function

The goal of this functionality is to allow the retrieval of data from the target database according to any required XML format. Therefore, the mediator shall assure the automatic generation of the corresponding SQL Select statements. Figure 3-5 describes the data flow and management inside the mediator to realize this functionality.

Figure 3-5 DB to XML function

3.1.6 Discussion

As already explained, several attempts have been performed to bring

As already explained, several attempts have been performed to bring