Import Database Schema
Enterprise Architect provides facilities for reverse engineering DBMS schemas, enabling you to analyze and remodel legacy database systems and then export them to the existing or an alternative DBMS.
By connecting to a live database via ODBC, you can import the database schema into a standard UML model. Subsequent imports enable you to update your model from the live database.
Enterprise Architect supports importing database tables, stored procedures and views 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
|
|
|
|
1
|
Select a package in the Project Browser, into which to import the database schema.
Create a data 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 Tools | Database Engineering | 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
See Notes below, concerning 32-bit ODBC drivers.
|
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
|
Select the appropriate checkboxes to import system tables and views, user views, triggers and/or Oracle packages.
|
Tables and Columns
Views
Triggers
|
6
|
Select the appropriate checkboxes to import:
These items can be imported as a Class for each Stored Procedure, User Function or User Sequence. Alternatively, all Stored Procedures can be imported as a set of operations contained under a Class. A similar Class can be created for all User Functions and User Sequences.
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.
|
Stored Procedures
|
7
|
In the Synchronization panel, select the appropriate option to determine 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.
|
|
8
|
Click on the Import button to start the import.
|
|
9
|
Select the database objects to import.
|
Select Tables
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 |
| • | If setting up a ODBC connection for reverse engineering, the default settings are sufficient |
| • | 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 |
| • | Additional data types are available from the Data Modeling Data Types section of the Resources page on the Sparx Systems website |
| • | 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
Learning Center topics
| • | | Database Engineering | Import Schema |
The material ("material" includes all pages, documents and on-line graphics) on this web site is protected by copyright law. (C) 1998-2011 Sparx Systems Pty Ltd.
