Import Database Schema

Analysis of legacy database systems is possible using Enterprise Architects 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.

Notes:

  • Import of stored procedures and views is supported for: DB2; SQL Server; Firebird/Interbase; Informix; Ingres; Oracle 9i, 10g and 11g; MySQL; 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.
  • The ODBC connection should use the ODBC driver available from the DBMS vendor. For example, MySQL's ODBC driver for MySQL, and Oracle's ODBC driver for Oracle. Drivers provided by third-party vendors are not supported - this includes 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.

Import Database Tables and Stored Procedures

To import database tables and stored procedures, follow the steps below:

  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.

Note:

Alternatively you can select the Project | Database Engineering | Import DB Schema from ODBC menu option.

       The Import DB Schema from ODBC Source dialog displays.
 
import_ddl_schema
 

  1. 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.
  2. If importing from Oracle, to restrict the import to a specific owner, type the owner name in the Schema/Owner field. By default, Enterprise Architect inserts the Oracle user name in this field.
     
    For imports from other types of database, leave this field blank.
  3. 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.
  4. When synchronizing existing Classes, select the appropriate checkbox in the Synchronization panel to determine whether the model comments, default values or constraints are to be synchronized with the ODBC tables, or as new objects.

Note:

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.

  1. Click on the Import button to start the import.
  2. Select the tables and - if appropriate - stored procedures to import.

This completes the procedure. See the Imported Class Elements topic.