Reverse Engineering an Existing Database

Go Up to Creating and Working With Data Models

Use the reverse engineering function to create a logical and physical model by extracting information from an existing data source. You can specify a data source to reverse engineer using an ODBC or native connection. Using the Reverse Engineer Wizard and the native connection, you can specify exactly what you want to reverse engineer, selecting from a particular database objects such as: tables, views, indexes, defaults, rules, user datatypes, owners, objects, and dependencies. Using the Reverse Engineer Wizard and an ODBC connection allows you to reverse engineer only tables and views. The information extracted for tables includes constraints, primary keys, foreign keys, and comments. Reverse engineering does not view or access the actual data within the database. Using the Quick Launch feature, you can save a set of reverse-engineering options to a file and retrieve them the next time you need to perform a similar reverse-engineering operation.

To reverse engineer an existing database

1. Choose File > New.
2. Select Reverse-engineer an existing database, and then click Login. ER/Studio Data Architect displays the Reverse Engineer Wizard. Here you can select specific objects and information to include in the reverse engineered model. The pages within the wizard guides you through the rest of the process.

Note: Please note you may need specific database privileges for reverse engineering.

Page 1: New Reverse Engineering Operation

• Connection Type:
  • ODBC: Using an ODBC connection, you can only reverse engineer tables and views. To add a new ODBC Data Source or edit an existing one, click Setup to open the ODBC Data Source Administrator dialog. For more information, see Configuring ODBC Data Source and Target Connections.
  • Native/Direct: Using a native or direct connection, you can reverse engineer all supported objects for the selected platform. For more information, see Using Native or Direct Database Connections.
  • Team Server Data Source: Using an ER/Studio Team Server data source, you can reverse engineer a database/schema whose connection details are retrieved from an ER/Studio Team Server server. You can reverse engineer all supported objects for the selected platform. If you are not logged in to ER/Studio TS, you will be prompted to log in.
• Database Type: When using a native connection, ER/Studio Data Architect can reverse engineer the following databases:
  • Hive
  • IBM DB2 for z/OS
  • IBM DB2 for LUW
  • MongoDB

    Note: When MongoDB database type is selected, an additional Advanced Options button will become available. For details, see MongoDB Advanced Login Options.

  • MS Azure SQL DB
  • MS SQL Server
  • Oracle
  • Sybase ASE

• Datasource: Disabled when ER/Studio Team Server Data Source is selected. This field differs depending on whether you selected an ODBC or a native/direct connection.
  • For an ODBC or ER/Studio Team Server connection, select a data source from the list. Note that for ER/Studio TS data sources, the list may include data sources for platform versions not supported by ER/Studio Data Architect.
  • For a Native/Direct connection, enter the data source connection string. For example:
    • Azure SQL DB or SQL Server: Specify the server machine name on which the Repository database is hosted.
    • Oracle: Connection alias generated with Oracle client.
    • DB2: Connection alias generated with DB2 client.
    • MongoDB: Host name of the MongoDB server. Alternately, users may specify a MongoDB connection URI in standard format.
• Authentication: This field differs depending on which database type you selected.
  • Azure SQL DB: Select SQL Server Authentication or Azure Active Directory - Password.

    Note: When Azure Active Directory - Password authentication is selected, you must have the SQL Server ODBC Driver 17 already installed. For details and a download of this driver, see the Microsoft SQL Docs topic, Download ODBC Driver for SQL Server.

  • SQL Server: Select SQL Server Authentication or Windows Authentication.
  • Hive: Select No Authentication or Kerberos Authentication.
• User Name and Password: These fields let you provide credentials.
• Create ER/Studio Team Server Data Source from settings: Disabled when ER/Studio Team Server Data Source is selected. Checking this option and providing a Name creates a data source definition on the ER/Studio Team Server server as soon as you click Next, using the settings you provided.
• Wizard Quick Launch: In the last step of this page, you can save settings to a file for later use. If you previously saved such a file, you can load it by clicking the ... button next to Select Settings File. Click Go to run the import exactly as before, or click Next to modify the settings. ER/Studio Data Architect saves the Wizard Quick Launch data as an .rvo file. The default location for these files is:

  C:\Users\<user>\AppData\Roaming\IDERA\ERStudio\XML

To change the default directory for Wizard Quick Launch files, click Tools > Options > Directories.

Page 2: Datasource, Database, and Owner

• Database List: If the database platform supports databases, browse and locate one or more databases to reverse engineer.
• Owner List: If the database platform supports owners, browse and locate one or more owners to reverse engineer.
• Include: Select the objects that you want to include in the data model.
• Document Sample Size: (MongoDB Only) Because MongoDB documents come in format, you must query the documents before you can reverse engineer the database. The Document Sample Size option allows you to specify how many documents you want to use for sampling. The default is 100. Note that the reverse engineer fails if you do not sample all documents having a different format.

Page 3: Selected Objects and Capacity Planning

• Object tabs: Displays a tab for every object type selected in the Include list on the previous page. Click the object tab, review the items in the Available Objects list, and then use the arrows to move the objects you want to reverse engineer to the Selected Objects list. By default, all objects are selected.
• Capacity Planning: Selecting any of these options returns information about the database that can help you plan for storage requirements. The options available are database dependent.

Page 4: Primary and Foreign Keys

Referential integrity

ER/Studio Data Architect can infer referential Integrity when none is declared in the database. Click one or more options to create relationships between entities in the reverse-engineered diagram.

• Infer Primary Keys: ER/Studio Data Architect infers primary keys from unique indexes on a table. If more than one unique index exists on a table, ER/Studio Data Architect chooses the index with the fewest columns.
• Infer Foreign Keys from Indexes: ER/Studio Data Architect infers foreign keys from indexes. When inferring foreign keys from indexes, ER/Studio Data Architect looks for indexes whose columns match the names, datatype properties, and column sequences of a primary key. If the child index is a primary key index, it must contain more columns than the parent primary key. In this case, an identifying relationship is created.
• Infer Foreign Keys from Names: If your database contains foreign keys, ER/Studio Data Architect infers foreign keys from names. When inferring foreign keys from names, ER/Studio Data Architect looks for columns matching the names and datatype properties of a primary key. In this case, a non-identifying relationship is created. ER/Studio Data Architect cannot infer relationships where the child column has a role name. Instead, create a Non-Identifying Relationship, and then designate the correct rolename column using the Edit Rolenames function; right-click relationship, and then select Edit Rolenames.
• Infer Domains: ER/Studio Data Architect infers domains from the columns in the database. ER/Studio Data Architect creates a domain for each unique combination of a column name and its associated datatype properties. Duplicate domains with an underscore and number suffix indicate that columns with the same name but different datatypes were found in the database. This can alert you of how standardized the columns are in a database. You can use macros to consolidate domains and preserve the bindings from the inferred domains.

Object dependencies

ER/Studio Data Architect can ensure all objects referenced by those selected for reverse engineering are also included.

• Reverse Engineer View Dependencies: ER/Studio Data Architect includes referenced view dependencies.
• Reverse Engineer Other Dependencies: ER/Studio Data Architect includes referenced dependencies, such as procedures and triggers. Dependent objects that are not otherwise selected are included. For example, a database contains proc1, proc2, and proc3; and proc3 references proc1 and proc2. During reverse engineering, if you selected only proc3 and this option, proc1 and proc2 are also included.

Initial layout

ER/Studio Data Architect allows you to select the initial layout for the data model.

• Circular and Tree: Provide the best performance when reverse engineering large databases. Reverse engineering a large database to create a hierarchical model is often time consuming. For more information about layouts, see Changing Data Model Layout.

Syntax interpreter

ER/Studio Data Architect allows you to select a platform-specific syntax interpreter.

Page 5: Model type, object summary, and Quick Launch save

• Create custom datatypes for unsupported types: If the selected mappings file contains new datatypes because you added them or they were added by a previous reverse-engineering process, then those datatype mappings are used.
• Summary of Selected Objects: Select an object type to display in the Summary of Selected Objects grid, and then review the provided details of all the objects you selected to reverse engineer.

Notes

• You can reverse engineer diagrams created with DT/Designer to create ER/Studio Data Architect models.
• Reverse-engineered diagrams do not display users and roles by default. To enable Users and Roles, select View > Diagram and Object Display Options > Security Objects, and then click Display All Security Objects.
• To create a logical model only from the database, select Tools > Options > Application, and then click Logical Model Only in the Reverse Engineer area.
• To reverse engineer a database, you must have both CONNECT and RESOURCE roles assigned to your user ID for the database you want to access.
• Objects in SQL 2005 are owned by schemas. A schema is similar to a user, and can be treated the same way; however, schemas are not explicitly represented in the explorer tree, nor is there a CREATE SCHEMA DDL function. In the Reverse Engineer Wizard and Compare and Merge Utility, the "owner" field in the object editors of ER/Studio Data Architect represent the schema name for an SQL Server 2005 physical model. The field will be the schema list.
• NUMBER datatype: NUMBER or NUMBER(*) you must set the width and scale to *. A width of 0 is converted to * when generating DDL.
• Width and scale information of unsupported and unmapped datatypes is not retrieved in the reverse-engineering process. If you need this information, create a custom datatype manually through the Datatype Mapping Editor before reverse-engineering.

Specifying Application Options for Reverse Engineering

Using the Application tab of the Options Editor, you can specify whether reverse engineering always produces both logical and physical models or produces logical models only. You can also specify the column sequence for new models that you reverse engineer. Once applied, this customization will apply to all your reverse-engineering projects.

1. Click Tools > Options, and then click the Application tab.
2. Specify your reverse-engineering preferences in the Reverse Engineer and Column Order: Reverse Engineer areas of the Application tab.
3. Click OK to apply your changes to future reverse-engineering projects.

See Also

• Changing Data Model Layout
• Importing a Model