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.

1. Choose File > New.
2. Select Reverse-engineer an existing database, and then click Login.
3. The Reverse Engineer Wizard displays.

Here you can select specific objects and information to include in the reverse engineered model. The Reverse Engineer Wizard will walk you through the rest of the process.

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

The following describe fields and options on the pages of the wizard that require additional explanation:

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.
  • ER/Studio 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 please see MongoDB Advanced Login Options.

  • MS SQL Server
  • Oracle
  • Sybase ASE

This control is disabled when ER/Studio Team Server Data Source is selected.

• Data Source: This field differs depending on whether you selected an ODBC or a native 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 connection, enter the data source connection string. For example:
    • 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.
• User Name and Password: These fields let you provide credentials.
• Create ER/Studio Team Server Data Source from settings: These controls are disabled if ER/Studio Team Server Data Source is selected. Selecting the check 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.
• Quick Launch: In the last step of the wizard, you can save settings to a file for later reuse. If you have previously saved such a file, you can load it step by clicking the ... button next to Select Settings File on page 1 of the wizard. Then click Go to run the import exactly as before, or click Next to modify the settings. The Wizard Quick Launch data is saved 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 Quick Launch files, choose Tools > Options > Directories.

Page 2

• 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 to include in the data model.
• Document Sample Size: Because MongoDB documents can be in any type of format, the documents need to be queried before the DB can be reverse engineered. The Document Sample Size option on Page 2 of the Reverse Engineer wizard allows you to specify how many documents are used for sampling. The default is 100. Should any documents that have a different format not be sampled, the reverse engineer will fail.

Page 3

• 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.
• Object tabs: Displays a tab for every object type selected in the Include list on the previous page. Click the object tab, review the available objects, and then use the arrows to move the objects you want to reverse engineer to the Selected Objects area.

Page 4

• Infer 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: If selected, 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: If selected, 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 selected and 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 rolenamed column using the Edit Rolenames function; right-click relationship and then select Edit Rolenames.
• Infer Domains: If selected, 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.
• View Dependencies: ER/Studio Data Architect can ensure all objects referenced by those selected for reverse engineering are also included.
• Reverse Engineer View Dependencies: If selected, ER/Studio Data Architect includes referenced view dependencies.
• Reverse Engineer Other Dependencies: If selected, ER/Studio Data Architect includes referenced dependencies such as procedures and triggers. Dependent objects that are not otherwise selected will be 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.
• Select the Initial Layout Option: Select an initial layout for the data model
• Circular Layout and Tree Layout: Provide best performance when reverse engineering large databases. Reverse engineering a large database to create a hierarchical model can be quite time consuming.
  For more information on layout, see Changing Data Model Layout.
• View Parser: Select a platform-specific syntax interpreter.

Page 5

• Summary of Selected Objects: Select an object type to display in the Summary of Selected Objects grid and then review the object type, owner, and object name of all the objects you selected to reverse engineer.
• 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 will be used.

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, choose View > Diagram and Object Display Options > Security Objects, and then select Display All Security Objects.
• To create a logical model only from the database, choose Tools > Options > Application and then select Logical Model Only in the Reverse Engineer area.
• In order 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 will be 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