Please note : This help page is not for the latest version of Enterprise Architect. The latest help can be found here.

Import Database Schema

Analysis of legacy database systems is possible using Enterprise Architect's reverse engineering capabilities. By connecting to a live database via ODBC, you can import the database schema into a standard UML model. Subsequent imports enable you to maintain synchronization between the data model and the live database.

Enterprise Architect supports importing database tables from an ODBC data source. Tables are imported as stereotyped Classes with suitable data definitions for the source DBMS.

How To:

To import database schema and objects, follow the steps below

Step

Action

See Also

1

Select any package in the Logical View

 

2

To import into:

  • The package only, right-click on the package to display the context menu, and select the Code Engineering | Import DB Schema from ODBC menu option
  • A diagram, right-click on the diagram in the selected package to open the context menu, and select the Import DB schema from ODBC menu option

The Import DB Schema from ODBC Source dialog displays

 

3

In the Database field, click on the ( ...  )  (Browse) button and select a suitable ODBC data source from the ODBC dialog (ODBC must be installed and configured on your machine for this to work correctly)

When you have selected the data source, the Database field shows the DBMS, the database server ID and the database name, separated by full stops; that is:

          dbms.dbserver.database.

Select a Suitable Data Source

4

You can filter objects to be retrieved from the database by schema or owner

In the Schema/Owner field, type the schema/owner as a single entry, or as a comma-separated list

The filter is useful for databases that support multiple schemas or owners, such as SQL Server 2005/2008, Oracle, PostgreSQL and DB2 Express

By default, when importing database objects from Oracle, Enterprise Architect inserts the Oracle user name in this field to avoid unnecessary retrieval of large numbers of objects not owned by that user

 

5

In the Filter panel, select the appropriate checkboxes for additional items to include in the import

Select the appropriate checkboxes to import system tables and views, user views, triggers and/or Oracle packages

If you select to import User Functions and/or User Sequences as individual Classes, then they are imported as separate elements and the Properties dialog is solely concerned with the Function or Sequence definition; for Stored Procedures, always select this option

If you select to import User Functions and/or User Sequences as Class operations, then they are imported as operations (methods) and you view and edit them through the Operations Properties dialog of the parent Class

 

6

In the Synchronization panel, select the appropriate option to determine whether the existing Classes are to be synchronized, or the database objects imported as new objects

If you select the Synchronize existing classes option, also select the appropriate checkboxes to determine whether model comments, column default values and/or table constraints are to be retained or overwritten with the comments, values and constraints of the imported objects

 

7

Click on the Import button to start the import

 

8

Select the database objects to import

Select Tables

Imported Class Elements

Notes:

  • Import of stored procedures and views is supported for: DB2; SQL Server; Firebird/Interbase; Informix; Ingres; Oracle 9i, 10g and 11g; MySQL; SQLite, PostgreSQL; Sybase Adaptive Server Enterprise (ASE) and Sybase Adaptive Server Anywhere (ASA)
  • If you are importing database schema from an MS Access Jet 4.0 database, please ensure that you have selected the Use Jet 4.0 checkbox on the General page of the Options dialog, otherwise the Jet 3.5 routines are loaded; you must restart Enterprise Architect after selecting the checkbox
  • Enterprise Architect requires 32-bit ODBC drivers to connect to a repository through ODBC; to set up the ODBC configuration on 64-bit clients, run the 32-bit ODBC Data Source Administrator from C:\Windows\SysWOW64\odbcad32.exe
  • The ODBC connection should use the ODBC driver available from the DBMS vendor, such as MySQL's ODBC driver for MySQL, and Oracle's ODBC driver for Oracle; drivers provided by third-party vendors are not supported, including the Microsoft ODBC driver for Oracle
  • If setting up a ODBC connection for reverse engineering, the default settings are sufficient
  • Additional data types are available from the Datamodeling Data Types section of the Resources page on the Sparx Systems website
  • For importation, you can select the Tools | Database Engineering | Import DB Schema from ODBC menu option as an alternative
  • It is only possible to import into a diagram if it is in the selected package - if a diagram from another package is open, a message displays to give the option to cancel the import or to continue importing into the package only; the Import DB Schema from ODBC Source dialog includes checkbox options to import into the diagram and package, or into the package only
  • If no diagram is open, the Package Only radio button defaults to selected and the options are disabled; if the open diagram is in the selected package, you can select either option

Learn More: