A methodology for reverse engineering hierarchical databases

10th International Conference on Entity-Relationship …, 1991

Part of the material the lecture is related with the PHENIX research project developed jointly by the University of Namur 1 and the BIKIT 2 . The objective of the project is to develop an expert-system approach to database reverse engineering. This project is supported by IRSIA, a Belgian public research agency, and by industrial partners 3 .

Reverse engineering applied to databases permits to extract a conceptual schema that represents, at a higher level of abstraction, the database implementation. This resulting conceptual schema may be used to facilitate, among others, system maintenance, evolution and reuse. In the last years, the use of object-relational constructs was incorporated into database development. However, reverse engineering techniques for these specific constructs have not been yet provided. In this sense, the main goal of this paper is to present a method that considers these new constructs in the reverse engineering of an existing object-relational database. As a result of the process, our method returns an equivalent conceptual schema specified in UML (extended with a set of OCL integrity constraints) that represents, at a conceptual level, the database schema. We provide a prototype tool that implements our method for the Oracle9i database management system. context Checkpoint inv: Checkpoint.allInstances->isUnique(id) context Person inv: self.age > 10 context Person inv: Person.allInstances->isUnique(name) context Address inv: self.floor->size()<=10 self.town->size() <= 30 context Address inv: self.floor->size() <= 10 context Address inv: self.street->size() <= 40 3 Obtained CS

2008

One important problem in today organizations is the existence of non-integrated information systems, inconsistency and lack of suitable correlations between legacy and modern systems. One main solution is to transfer the local databases into a global one. In this regards we need to extract the data structures from the legacy systems and integrate them with the new technology systems. In legacy systems, huge amounts of a data are stored in legacy databases. They require particular attention since they need more efforts to be normalized, reformatted and moved to the modern database environments. Designing the new integrated (global) database architecture and applying the reverse engineering requires data normalization. This paper proposes the use of database reverse engineering in order to integrate legacy and modern databases in organizations. The suggested approach consists of methods and techniques for generating data transformation rules needed for the data structure normalization.

Object-Oriented and Entity-Relationship Modelling/International Conference on Conceptual Modeling / the Entity Relationship Approach, 1999

Database reverse engineering is a complex activity that can be modeled as a sequence of two major processes, namely data structure extraction and data structure conceptualization. The first process consists in reconstructing the logical - that is, DBMS-dependent - schema, while the second process derives the conceptual specification of the data from this logical schema. This paper concentrates on the

1999

Abstract The problem of choosing a method for the reverse engineering of relational database systems is not trivial. Methods have different input requirements, and each legacy system has its particular characteristics that restrict information availability. In this paper, we propose a classification framework based on the method's input requirements, namely: attribute semantics, attribute name consistency, data instances, applications source code, candidate keys, 3rd normal form (3NF), inclusion dependencies and human input.

… Engineering, 1996., …, 1996

Recovering the semantic description of file and database structures is an important aspect of business application reverse engineering. It includes a particularly delicate activity, namely data structure extraction, i.e. finding the exact data structures and integrity constraints of the database. This process is made more complex than generally expected due to the fact that these structures and constraints often are not explicitly defined, but are translated into implicit constructs, controlled and managed through procedural code or user interface protocol for instance. This paper describes the problem of implicit structure elicitation. It proposes an analysis of this phenomenon, and of the techniques and heuristics that can be used in the elicitation process. It develops a set of efficient techniques and a strategy for the elicitation of one of the most common implicit construct, namely the foreign key. The paper also explains how DB-MAIN, a general-purpose database reverse engineering CASE tool, can help analysts elicit implicit constructs, and specifically foreign keys.

… of the First International Working Session …, 2007

In this paper we describe an experience of Data Reverse Engineering supported by a repository of conceptual schemas.We first introduce a set of integration/abstraction primitives that are used in order to organize a large set of conceptual schemas in a repository. We describe the methodology conceived to produce the repository of schemas of central public administrations in Italy. Then we describe an heuristic methodology, applied in the production of the set of schemas of the public administrations of an italian region. We also compare the former exact methodology and the heuristic one according to their correctness, completeness, and efficiency.

Intl. J. Cooperative Information Systems, 1995

A logical database schema, e.g. a relational one, is an implementation of a specification, e.g. an entity-relationship diagram. Upcoming new data models require a cost-effective method for mapping from one data model into the other. We present an approach where the mapping relationship is divided into three parts. The first part reformulates the source and target data models into a so-called meta model. The second part classifies the input schema into the meta model, yielding a data model independent representation. The third part synthesizes the output schema in terms of the target data model. A prototype has been implemented on top of deductive object base manager ConceptBase for the mapping of relational schemas to entity-relationship diagrams. From this, a C++-based tool has been derived as part of a commercial CASE environment for database applications

La première étape des méthodologies de conception de base de données consiste à éliciter les besoins à partir, entre-autres, dŠinterviews des utilisateurs. Ces besoins sont formalisés au sein dŠun schéma conceptuel du domaine dŠapplication, typiquement un diagramme Entité-Relation (ER). Le processus de validation de ces besoins demeure pourtant problématique car la représentation visuelle du modèle ER ne permet pas toujours une bonne compréhension par les utilisateurs finaux. En revanche, des prototypes dŠinterfaces utilisateurs peuvent être utilisés comme un moyen plus efficace pour exprimer, capturer et valider des besoins en termes de données. Considérant ces interfaces comme une vue physique de la base de données à concevoir, il est possible d'en dériver le schéma conceptuel sous-jacent en combinant des techniques de rétro-ingénierie, de transformation et d'intégration de schémas. Cet article présente les fondements d'une approche interactive et outillée mettant en oeuvre ces principes.

IJRET, 2012

The database forensic investigation plays an important role in the field of computer. The data stored in the database is generally stored in the form of tables. However, it is difficult to extract meaningful data without blueprints of database because the table inside the database has exceedingly complicated relation and the role of the table and field in the table are ambiguous. Proving a computer crime require very complicated processes which are based on digital evidence collection, forensic analysis and investigation process. Current database reverse engineering researches presume that the information regarding semantics of attributes, primary keys, and foreign keys in database tables is complete. However, this may not be the case. Because in a recent database reverse engineering effort to derive a data model from a table-based database system, we find the data content of many attributes are not related to their names at all. Hence database reverse engineering researches is used to extracts the information regarding semantics of attributes, primary keys, and foreign keys, different consistency constraints in database tables. In this paper, different database reverse engineering (DBRE) process such as table relationship analysis and entity relationship analysis are described .We can extracts an extended entity-relationship diagram from a table-based database with little descriptions for the fields in its tables and no description for keys. Also the analysis of the table relationship using database system catalogue, joins of tables, and design of the process extraction for examination of data is described. Data extraction methods will be used for the digital forensics, which more easily acquires digital evidences from databases using table relationship, entity relationship, different joins among the tables etc. By acquiring these techniques it will be possible for the database user to detect database tampering and dishonest manipulation of database

Working Conference on Reverse Engineering, 1997

While the database reverse engineering problems and solving processes are getting more and more mastered, the academic community is facing the complex problem of knowledge transfer, both in university and industrial contexts. The paper addresses one efficient support of this transfer, namely academic case studies, i.e., small, clean, self-contained applications exhibiting representative problems and appropriate solutions that can be mastered

Lecture Notes in Computer Science, 2010

The first step of most database design methodologies consists in eliciting part of the user requirements from various sources such as user interviews and corporate documents. These requirements formalize into a conceptual schema of the application domain, that has proved to be difficult to validate, especially since the visual representation of the ER model has shown understandability limitations from the end-users standpoint. In contrast, we claim that prototypical user interfaces can be used as a two-way channel to efficiently express, capture and validate data requirements. Considering these interfaces as a possibly populated physical view on the database to be developed, reverse engineering techniques can be applied to derive their underlying conceptual schema. We present an interactive tool-supported approach to derive data requirements from user interfaces. This approach, based on an intensive user involvement, addresses a significant subset of data requirements, especially when combined with other requirement elicitation techniques.

Many CASE tools for information systems engineering can input a conceptual data model of an application and map this to a logical data model for implementation. Typically this involves mapping an ER (Entity-Relationship) conceptual schema to a relational database schema. Since the graphic notation of ER, or the mapping algorithm itself, fails to capture many constraints and derivation rules, these additional features must be coded up manually. Object-Role Modelling (ORM) provides a simpler and richer notation, enabling most of these additional features to be catered for in the mapping. The most well known version of ORM is NIAM, and a number of CASE tools now support this method. Recently, an extended ORM language called FORML has been developed which is even more expressive, and a complete mapping algorithm has been developed and automated. This paper provides an overview of the mapping algorithm and the use of role-graphs for automation.

Data & knowledge engineering, 1987

This paper presents the system ADDS that has been developed to assist the database designer designing a database schema. A distinction is made between the stage of information structure analysis, in which the information structure of the system is defined according to its user information needs, and the stage of database schema design, in which the record types of the database and the relationships between them are defined. In the first stage a conceptual schema is obtained, represented as an information structure diagram (ISD), and in the later stage the ISD is used to derive the database schema in the form of a data structure diagram (DSD). ADDS automatically creates the database schema out of a conceptual schema which is expressed as an ISD of the binary-relationship data mode. The resulting schema consists of normalized record types, according to the relational model, along with hierarchical/set relationships between ‘owner’ and ‘member’ record types. ADDS applies algorithms to convert the conceptual schema into the relational database schema.

We describe a relational database semantic re-engineering technology and the tools that are available for its implementation. The semantic re-engineering technological process starts with creatinga conceptual data ontology together with its annotations for database schema and user interface mappings. The database schema mappings are implemented in RDB2OWL and D2RQ server thus creating a SPARQL-endpoint for "semantic" access to the relational database contents. The SPARQL endpoint can be explored by the user interface automatically generated by OBIS system, or ViziQuer tool may be used for custom SPARQL query generation in a graphical way. We report on successful application of the approach on the Latvian medical data with the ontology containing 172 OWL classes, 138 object properties, 814 data properties, and about 40 million data level RDF triples.