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

Import Database Schema

The power of model-based engineering is the ability to visualize, analyze and design all aspects of a system. Being able to view the database schemas alongside other models of a system provides great clarity and reduces the chance of error. Enterprise Architect can reverse engineer a DBMS schema and its objects into a model under a number of different standards, including UML, Information Engineering and IDEF 1X. A wide range of database objects are supported including Tables, Views, Procedures, Functions and Sequences. Enterprise Architect communicates with the DBMS via ODBC, can import a schema via this connection, and can synchronize the model with the live database if it is changed.

Once the schema is in Enterprise Architect, the database objects can be traced to other elements, ensuring the integrity of design and architecture. When systems target multiple DBMSs, these can all be reverse engineered into a model and elements and datatypes can be compared between these models. The sophisticated reporting engine can produce high quality documentation, including data dictionaries, diagrams and relationships back to other models such as architecture and information requirements, and ultimately to business goals and drivers.

Database schema information can be imported by two different methods:

  • via the Database Builder or
  • via the Project Browser or diagram

Import Database Schema using the Database Builder

Step

Action

See also

1

Open the Database Builder.

 The Database Builder

2

Load or create a Data Model.

3

Right-click on the loaded Data Model and select 'Import DB schema from ODBC'.

The 'Import DB Schema from ODBC source' dialog displays, showing the details of the current active database connection.

Import Database Schema Using a Diagram

Step

Action

See also

1

Select a Package in the Project Browser, into which to import the database schema.

2

Create a database modeling diagram in this Package.

 Create a Data Model Diagram

3

To import, either:

  • Right-click on the Package and select the 'Code Engineering | Import DB Schema from ODBC' option, or
  • Right-click on the diagram and select the 'Import DB schema from ODBC' option, or
  • Select the 'Package | Database Engineering | Import DB schema from ODBC' menu option

The 'Import DB Schema from ODBC Source' dialog displays.

The Import DB Schema from ODBC Source dialog

Field/Option/Button

Description

See also

Database

This field shows a description of the current ODBC connection, in the format:

     dbms.database_server.database_name

If necessary, click on the button and, on the 'Machine Data Source' tab of the 'ODBC Select Data Source' dialog, select an alternative ODBC connection.

Import to

This field shows the target Package that the new objects will be saved to.

If you want to specify a different Package, click on the button and select an alternative Package.

Only include objects from Schema(s)

If the database type supports multiple schemas (such as SQL Server 2005/2008/2012, Oracle, PostgreSQL and DB2 Express) you can filter objects to be retrieved from the database by schema.

The available schemas are automatically listed in this panel. Select the checkbox against each schema to include in the import.

(You can click on the All button to select all the schemas, or the None button to clear all selected checkboxes.)

If you suspect that the schema list might have changed since you loaded them, you can refresh the list by clicking on the Reload Schemas button.

Filter Options (upper panel)

Select the appropriate checkboxes to import:

  • System Objects - (database Tables)
  • Database (User) Views
  • Table Triggers
  • Oracle Packages
  • Oracle Length Semantics
  • SQL Server Default Constraints (for Legacy systems)

If a checkbox is not selected, the import will ignore all objects of that type.

 Check Constraints Primary Keys Database Functions Foreign Keys Unique Constraints Indexes Database Sequences Database Procedures Table Triggers Oracle Packages Database Tables Database Views

Filter Options (lower panel)

Select the appropriate checkboxes to import:

  • Database Procedures
  • User Database Functions
  • User Database Sequences

Each database Procedure, Function or Sequence can be imported either as a Class or as a set of operations contained under a Class; select the appropriate radio button for each object type.

Import as individual Classes

The selected items are imported as separate Classes.

Import as Operations

The selected items are imported to a single Class as Operations (methods); you can view and edit them through the Database object container 'Properties' dialog.

Synchronization

Select the appropriate radio button to indicate whether the existing Classes are to be updated, 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.

Import To

Select the appropriate radio button to indicate whether to update the Package and currently-open data model diagrams, or just the Package.

If no diagrams are open, the 'Package Only' radio button defaults to selected and the options are disabled; if the open diagrams are in the selected Package, you can select either option.

Import

Click on this button to start the import.

The 'Select Database Objects to Import' dialog displays, listing all the database objects found that match the selection criteria.

Select the checkbox against each schema (or object type) to automatically select all objects in that group or to import each object individually.

Click on the All button to select all types and objects, or on the None button to clear all selected checkboxes.

When you have selected all the objects to import, click on the OK button to continue the import.

Notes

  • 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
  • You can import a suitable ODBC driver for SQLite from http://www.ch-werner.de/sqliteodbc/
  • Due to the limitations of SQLite, round tripping of SQLite Table and column comments is not possible; to retain comments entered in an SQLite data model when importing from ODBC, deselect the 'Overwrite Object Comments' checkbox in the 'Synchronization' section of the 'Import DB Schema from ODBC Source' dialog
  • If setting up an ODBC connection for reverse engineering, the default settings are sufficient
  • If you are importing database schema from an MS Access Jet 4.0 database, check that you have selected the 'Use Jet 4.0' checkbox on the 'General' page of the 'Options' dialog ('Tools | Options'), otherwise the Jet 3.5 routines are loaded; you must restart Enterprise Architect after selecting the checkbox
  • The list of Data Modeling Data types is defined as static data (in each repository), so depending on the age of your repository, there may be additional data types available from the 'Data Modeling Data Types' section of the 'Resources' page on the Sparx Systems website

Learn more

Learning Center topics

  • Alt+F1 | Enterprise Architect | Database Engineering | Import Schema