Database reverse engineering tools

Database Reverse Engineering

December 1, 2022 – 08:46 am

Choose an appropriate DBMS (in this case Oracle8) In this set of notes we introduce the concept of database reverse engineering and walk through three different software tools that can be used to carry out database reverse engineering.

In many cases, we have an existing information system and database but no original E-R diagram or physical model.

Reverse Engineering is the process of reconstructing a physical and/or ER model from an existing schema.

Reverse Engineering can be used to:

Provide documentation on an existing application

Provide applications developers with a better overall picture of the schema

Provide a better guideline when performing changes to the schema.

This is not a trivial process since during the initial conversion from ER to Relational model, some information is lost.
For example, a Many to Many (M:N) relationship is represented as an intersection relation.
Also, the names of the relationships are lost.

Reverse engineering is extremely useful for tracking Foreign Key relationships throughout a schema.

Several CASE tools offer reverse engineering facilities.

In versions prior to 2013, Microsoft Visio provided this functionality but confusingly they have removed it in the 2013 version.

Reverse Engineering using Sybase PowerDesigner

Sybase PowerDesigner has the ability to reverse engineer a database schema into a physical model. What follows is an example data warehouse schema that exists in an Oracle database. Note that this example is from an older version of PowerDesigner, however the steps should still apply to newer versions.

Choose an ODBC data source. In this case, an Oracle8 ODBC driver is selected.
An account name and password (warehouse in this case) is also selected.
Finally, a physical level database diagram is displayed. Notice the indications pk and fk indicating which columns participate as primary keys and foreign keys.

Choose “Reverse Engineering Database” form the Database Menu

Source: holowczak.com