Extracting the extended entity-relationship model from a legacy relational database
The maintenance of an existing database depends on the depth of understanding of its characteristics. Such an understanding is easily lost when the developers disperse. The situation becomes worse when the related documentation is missing. This paper addresses this issue by extracting the extended entity-relationship schema from the relational schema. We developed algorithms that investigate characteristics of an existing legacy database in order to identify candidate keys of all relations in the relational schema, to locate foreign keys, and to decide on the appropriate links between the given relations. Based on this analysis, a graph consistent with the entity-relationship diagram is derived to contain all possible uniary and binary relationships between the given relations. The minimum and maximum cardinalities of each link in the mentioned graph are determined, and extra links within the graph are identified and categorized, if any. The latter information is necessary to optimize foreign keys related information. Finally, the last steps in the process involve(similar to)(when applicable) suggesting improvements on the original conceptual design, deciding on relationships with attributes, many-to-many and n-ary (n greater than or equal to 3) relationships, and identifying is-a links. User involvement in the process is minimized to the case of having multiple choices, where the system does not have the semantic knowledge required to decide on a certain choice. (C) 2002 Elsevier Science Ltd. All rights reserved.
entity-relationship model relational database algorithms database reverse engineering database re-engineering documenting legacy databases