Import Database Schema

Enterprise Architect provides facilities for reverse engineering DBMS schemas, which help you to analyze and remodel existing database systems and then export them to the current DBMS or an alternative DBMS.

By connecting to a live database via ODBC, you can import the database schema into a standard UML model. In subsequent imports you can update your model from the live database.

Enterprise Architect supports importing database Tables, Procedures and Views from an ODBC data source. Database Tables are imported as stereotyped Classes with suitable data definitions for the source DBMS.

Import database schema and objects

Step

Action

See also

1

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

Create a database modeling diagram in this Package.

 

Create a Data Model Diagram

2

To import, either:

·Right-click on the Package and select the Code Engineering | Import DB Schema from ODBC context menu option, or
·Right-click on the diagram and select the Import DB schema from ODBC context menu option, or
·Select the Package | Database Engineering | Import DB schema from ODBC menu option, or
·Open the Database Builder (Tools | Database Builder), right-click on the data base model 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, on the Machine Data Source tab of the ODBC Select Data Source dialog, click on the required data source name and then click on the OK button (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.

 

 

See Notes below, concerning 32-bit ODBC drivers

4

You can filter objects to be retrieved from the database by schema (where the database type supports multiple schemas, such as SQL Server 2005/2008/2012, Oracle, PostgreSQL and DB2 Express). The available schemas are automatically listed in the Only include objects from Schema(s) panel.

Click on the checkbox against each schema to import. 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 might have changed since you loaded them, you can refresh the list by clicking on the Reload Schemas button.

By default, when importing database objects from Oracle, Enterprise Architect inserts the Oracle user name against each schema to avoid unnecessary retrieval of large numbers of objects not owned by a specific user.

 

 

5

Select the appropriate checkboxes to import:

·System Objects (database Tables)
·Database Views
·Table Triggers
·Oracle Packages and/or Length Semantics
·SQL Server Default Constraints (for legacy systems)

 

 

Database Tables

Database Views

Table Triggers

Oracle Packages

6

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 script is imported into separate Classes. The Properties dialog for each of these Classes supports a context sensitive editor for editing the script.

 

Import as Operations

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

 

 

Database Procedures

Database Functions

Database Sequences

7

In the Synchronization panel, 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.

 

See Notes below, concerning SQLite Table and column comments

8

In the Import To... panel, 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.

 

 

9

Click on the Import button to start the import.

The Select Database Objects to Import dialog displays, listing the database objects available in the source database or in the schema selected in the Import DB Schema from ODBC Source dialog.

Select the checkbox for the database or each schema, and for each database object type and object to import.

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 resume the import.

 

The Imported Class Elements

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
·Additional data types are available from the Data Modeling Data Types section of the Resources page on the Sparx Systems website
·An alternative method for importing database schema is to use the Database Builder Show Differences option to bring the 'missing' database elements from the database into the model structure

 

Learn more

·Data Modeling Data Types (Online Resource)

Learning Center topics

·(Alt+F1) | Enterprise Architect | Database Engineering | Import Schema