Relational Database Reverse Engineering: A Method Based on Query Analysis

This paper introduces a method of reverse engineering for operational relational databases. The conceptual schemas are derived using information extracted from data dictionaries, database extensions and application programs. Its main strength relies on the assumptions made on the a priori knowledge available about the database (only and/or constraints on attribute(s)) as well as the user competence. We argue that most of the knowledge needed to build a conceptual schema, if not described in the Data Description Language, is embedded in application programs under various forms. The method is therefore based on four main steps: firstly, application program analysis is performed and a set of equi-joins is obtained; secondly, a conceptual schema is derived from , from the database extension and from the relational schema; thirdly, this conceptual schema is validated through an interactive dialogue with the expert user who is helped in this task by indications given by the method. Finally, a schema reorganization under user control is achieved to match the user requirements better. We introduce also how other kinds of queries can help the task of semantics discovery. Additionally, we precisely identify the phases when user interaction is needed. This method has been successfully validated on an operational database.