Relational database reverse engineering: Algorithms to extract cardinality constraints

doi:10.1016/S0169-023X(98)00017-2

Data & Knowledge Engineering

Volume 28, Issue 2, November 1998, Pages 161-207

https://doi.org/10.1016/S0169-023X(98)00017-2 Get rights and content

Abstract

This paper presents algorithms which build SQL queries to improve the reverse engineering of relational databases. Our process extracts the current cardinality constraints of n-ary relationships through a combination of data dictionary, data schema and data instance analysis. Our results can be applied to semantic data models with cardinalities constraints based either on the ER model or to semantic data models with cardinality constraints on participation constraints (MERISE, ECR, ERC +, OMT, ODMG). The process we propose can also refine conceptual diagrams of commercial tools with reverse-engineering options (AMC^∗Designer^™, ORACLE^TM Designer 2000, etc.). From our algorithms a PRO*C program using dynamic SQL has been implemented.

References (47)

R. Chiang et al.
Reverse engineering of relational databases: Extraction of an EER model from a relational database
Journal of Data and Knowledge Engineering
(1994)
R. Elmasri et al.
The Category concept: An extension to the Entity-Relationship Model
Journal of Data and Knowledge Engineering
(May 1985)
P. Shoval et al.
Database reverse engineering: From the relational to the binary relationship model
Journal of Data and Knowledge Engineering
(1993)
M. Andersson
Extracting an entity relationship schema from a relational database through reverse engineering
C. Batini et al.
Conceptual Database. Design: An Entity Relationship Approach
(1992)
D. Bitton et al.
A feasibility and performance study of dependency inference
H. Briand et al.
From minimal cover to Entity-Relationship diagram
T.A. Bruce
Designing Quality Databases with IDEFIXI Information Models
(1992)
M. Castellanos et al.
Extraction of data dependencies
M. Castellanos
A methodology for semantically enriching interoperable databases

R.G.G. Catell

Object Databases: The ODMG-93 Standard

(1993)

P.P. Chen

The Entity-Relationship model: Towards a unified view of data

ACM Trans. Database Systems

(Mar. 1976)

R. Chiang et al.

Performance evaluation of reverse engineering relational databases into extended entity-relationship models

E.F. Codd

A relational model for large shared data banks

Comms. of the ACM

(1970)

I. Comyn-Wattiau et al.

Reverse engineering of relational database physical schemas.

K.H. Davis et al.

Converting a relational database model into an Entity Relationship model

V. Englebert et al.

DB-MAIN; A database-oriented CASE tool

Engineering of Information Systems

(1996)

G.C. Everest

Database Management: Objectives, Systems Functions and Administration

(1986)

C. Fahrner et al.

Transforming relational database schemas into object-oriented schemas according to ODMG-93

G. Gardarin

Translating relational to object databases

Engineering of Information Systems

(1994)

J.L. Hainaut et al.

Transformation-based database reverse engineering

P. Johanneson

A method for translating relational schemas into conceptual schemas

T.H. Jones et al.

Binary representation of ternary relationships in ER conceptual modeling

Cited by (19)

Extracting entity-relationship diagram from a table-based legacy database
2008, Journal of Systems and Software
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. In a recent DBRE 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. In this paper, we present a process that 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. The primary inputs of our approach are system display forms, table schema and data instance. We utilize screen displays to construct form instances. Secondly, code analysis and data analysis involving comparisons of fields and decomposition of fields are applied to extract attribute semantics from forms and table schemas, followed by the determination of primary keys, foreign keys and constraints of the database system. In the final step of conceptualization, with the processes of table mergence and relationship identification, an extended ER diagram is successfully extracted in a case study.
Extracting generalization hierarchies from relational databases: A reverse engineering approach
2007, Data and Knowledge Engineering
Citation Excerpt :
Past comprehensive approaches on relational database reverse engineering can be found in [2,5,8–13,15,16,21–23,25,28,31].
Relational Data Base Management Systems (RDBMS) are currently the most popular database management systems. The relational model is a simple and powerful model for representing real world applications. However, it lacks the expressiveness of conceptual models. Unlike the latter, the relational model does not offer the generalization abstraction. Therefore, it does not allow the designer to represent directly a large variety of integrity constraints. Moreover, inclusion dependencies formalizing inter-relational constraints cannot directly be represented in the relational model, due to the fact that its basic construct, the relation, is the unique structure. Finally, relational databases do not enable a natural way to represent inheritances. In this paper we describe a reverse engineering method which particularly deals with the elicitation of inheritance links embedded in a relational database, combining heuristic and algorithmic approaches. We provide rules for detecting intersection constraints and inclusion dependencies. Heuristics are proposed for understanding null value semantics. Finally, we present decision rules for detecting existence dependencies and translating them into IS-A hierarchies among entities. An example is used to illustrate our approach.
Generating three-tier applications from relational databases: A formal and practical approach
2002, Information and Software Technology
Citation Excerpt :
Other improvements that are being currently studied include the processing of n-ary relationships and the detection of other possible patterns that were used during the database construction. For both issues, the analysis of actual data instances will be a help, as has been evidenced by Soutou [28] (for example, the detection of a set of records with a certain column set to null could mean that the one inheritance tree, one table pattern has been used). Moreover, several additional modules are being programmed to generate the tiers for other environments and programming languages.
This article describes a method for building applications with a three-tier structure (presentation, business, persistence) from an existing relational database. The method works as a transformation function that takes the relational schema as its input, producing three sets of classes (which depend on the actual system being reengineered) to represent the final application, as well as some additional auxiliary classes (which are ‘constant’ and always generated, such as an ‘About’ dialog, for example). All the classes generated are adequately placed along the three-tiers.
The method is based on (1) the formalization of all the sets involved in the process, and (2) the mathematical formulation of the required functions to get the final application. For this second step, we have taken into account several well-known, widely used design and transformation patterns that produce high quality designs and highly maintainable software.
The method is implemented in a tool that we have successfully used in several projects of medium size. Obviously, it is quite difficult for the obtained software to fulfill all the requirements desired by the customer, but the uniformity and understandability of its design makes very easy its modification.
Modeling relationships in object-relational databases
2001, Data and Knowledge Engineering
We propose an approach for designing an object-relational database. We inspect each case of semantic relationships (one-to-one, one-to-many, many-to-many and n-ary). For each of these cases we list the different solutions for implementation and propose some solutions. We also make a comparison with the relational data model. Our results can be applied to current RDBMS including object extensions (Oracle8, DB2-IBM, Informix…).
Logical Schema Design that Quantifies Update Inefficiency and Join Efficiency
2021, Proceedings of the ACM SIGMOD International Conference on Management of Data
Mining contextual maximum cardinality in knowledge bases
2018, CEUR Workshop Proceedings

View all citing articles on Scopus

View full text

Relational database reverse engineering: Algorithms to extract cardinality constraints

Abstract

Journal of Data and Knowledge Engineering

Journal of Data and Knowledge Engineering

Journal of Data and Knowledge Engineering

Extracting an entity relationship schema from a relational database through reverse engineering

Conceptual Database. Design: An Entity Relationship Approach

A feasibility and performance study of dependency inference

From minimal cover to Entity-Relationship diagram

Designing Quality Databases with IDEFIXI Information Models

Extraction of data dependencies

A methodology for semantically enriching interoperable databases

Object Databases: The ODMG-93 Standard

The Entity-Relationship model: Towards a unified view of data

ACM Trans. Database Systems

Performance evaluation of reverse engineering relational databases into extended entity-relationship models

A relational model for large shared data banks

Comms. of the ACM

Reverse engineering of relational database physical schemas.

Converting a relational database model into an Entity Relationship model

DB-MAIN; A database-oriented CASE tool

Engineering of Information Systems

Database Management: Objectives, Systems Functions and Administration

Transforming relational database schemas into object-oriented schemas according to ODMG-93

Translating relational to object databases

Engineering of Information Systems

Transformation-based database reverse engineering

A method for translating relational schemas into conceptual schemas

Binary representation of ternary relationships in ER conceptual modeling