Database Engineering with Enterprise Architect 12

Webinar March 2015
In this webinar you will learn how to:
  • Model a database from scratch using predefined patterns
  • Connect to live databases, run queries and compare differences
  • Synchronize your live database schema based on model changes

Enterprise Architect includes a powerful new database engineering toolset. The new Database Builder greatly enhances Enterprise Architect's existing data modeling capabilities – making it easier than ever to model, generate and synchronize database designs.

Ben Constable
Ben Constable

Explore the Example Database (Physical) Model

We have provided the example Enterprise Architect model and associated resources used in the webinar.
Example Model: database-engineering-example-physical-model.eap Download the Enterprise Architect Database Engineering Example Model
Example Firebird Database: SALES-DATABASE-TEST.FDB Download the Example Firebird Database
Firebird Query - Show Tables: firebird-select-user-tables.sql Download the Example Firebird Query to Show User Tables
Firebird Query - Show Columns: firebird-select-columns.sql Download the Example Firebird Query to Show Columns
MySQL Query - Show Tables: mysql-show-tables.sql Download the Example MySQL Query to Show Tables
MySQL Query - Show Columns: mysql-show-table-columns.sql Download the Example MySQL Query to Show Columns for a Table

Questions from the Audience

Click on a question to see the answer.


 I have Enterprise Architect Corporate edition. Are the features you showed available in all editions of Enterprise Architect 12?

The Database Builder is available in the Corporate edition and above. Desktop, Professional and Lite editions do not have the Database Builder feature.

 What version of Enterprise Architect was used for the presentation?

Enterprise Architect 12, Build 1210.

Please note: Some minor updates that relate to Database Engineering will be released with Build 1211 of Enterprise Architect. One of the updates ensures that a semicolon is added at the end of a generated DDL file for MySQL when the DefaultOwner tag is set. This facilitates execution of the DDL statements outside of Enterprise Architect for that specific scenario.

Another update ensures that the Database Compare tool ignores the Database name to prevent false positives when comparing the physical data model to the live database. This improves comparisons involving MySQL, Access, Firebird and SQLite databases.

 Do you have any further webinars planned?

Yes. You can find out about upcoming webinars from:

 Will there be a dedicated section on the Enterprise Architect Product Demonstrations web page for the Database Builder?

That's a fine suggestion. We will add such a category that links to this webinar and further Database Engineering webinars that we conduct in future.

 Does this replace the functionality of the DBMode plug-in?

The Database Builder in Enterprise Architect 12 was not designed as a replacement for the DBMode plug-in. It was designed and developed independently of DBMode, so there might be some overlap of functionality. Looking at the DBMode plug-in description on the Sparx Systems website, however, DBMode contains features that are not part of the Database Builder, such as export to MS-Excel documents, a model checker, a CRUD matrix and support for Domains.

 Can you show how these data entities can be referred to in Business Process Model?

You might be interested in a response posted to a similar question in our recent BPMN webinar. The response by the presenter discusses how to associate elements in your physical data model with your business process model.

Physical Data Modeling and DDL Generation

 When updating a table with alter statements, is the create statement updated?

At any time while data modeling you have the choice to Generate DDL (which will generate Create statements to generate the objects as they are now) or compare modeled objects to a live database and generate Alter DDL statements that represent the differences.

 Do changes in model result in real ALTER Statements in generated DDL?

Changes in the model can be generated to DDL in two ways:

  • Generate DDL (Create statements)
  • Compare to live database (Alter statements)

In the second case, the modeled object is compared to the object in a live database. Any differences can then produce Alter DDL statements (that are valid for the current DBMS) to adjust the live DB to match the modeled object.

 Can we create auto increment primary keys with Enterprise Architect?

Yes. Use the AutoNum, StartNum and Increment extended properties of columns that support auto numbers. To access these properties, use the menu: Tools | Database Builder | Tables | [select your table] | Columns tab. The appropriate extended properties appear in the lower left corner.

 The DDL generation from the Compare tool appeared to have dropped the existing column and recreated it when Ben changed the column width in the Firebird example... wouldn't that have lost your existing data? Shouldn't you do an alter table column command?

Because Firebird has limited support for altering columns compared to other DBMS products, Enterprise Architect will drop Firebird columns and recreate them rather than attempting to make all the changes via alter statements. If you performed the same change to the MySQL example model, you would find that the Compare Tool generates alter column statements instead, thereby preserving existing data in many cases. When changing column lengths, you would still need to take care to avoid issues related to data type incompatibility or truncation.

In the case of Firebird there are possible workarounds to retain existing data, such as modifying Enterprise Architect's DDL code generation templates and/or moving the data to a temporary table first.

 If I rename a column, will the generated DDL retain existing data?

It depends on the target DBMS. If the DBMS supports column renames, then Enterprise Architect will make use of the functionality. For example, renaming a SQL Server column will generate DDL that makes use of the system procedure SYS.SP_RENAME. For PostgreSQL, the generated DDL will use ALTER TABLE {table} RENAME COLUMN. For MySQL, it will be ALTER TABLE {table} CHANGE COLUMN.

 How can I add a foreign key between both tables and synchronize with the database?

We will show you an example in our next database engineering webinar. In the meantime, you might like to read the Help topic Foreign Keys.

 Can we now choose which Indexes or Constraints to include when generating DDL, or is it still 'all or nothing'?

Currently, it is all or nothing, but in a future release you should be able to individually choose which constraint types to generate.

 How easily can we migrate Enterprise Architect version 10 models to version 12?

It's straightforward because Enterprise Architect's underlying UML profile for data modeling is largely unchanged.

  1. Use Enterprise Architect 12 to open the model you created in version 10.
  2. Add a new Physical Data Model pattern appropriate for your target DBMS.
  3. Use the Project Browser to drag your table elements into the tables package created by the new model pattern. (The tables package resides under the package that is stereotyped database.

DBMS Support

 Are you planning to support SQL Server 2014?

We are likely to add support for SQL Server 2014. Currently, SQLServer 2014 schemas can be modeled as SQLServer 2012, obviously without the new features 2014 now supports. If there are specific SQL Server 2014 features you currently need to model, please send us your feedback (

 Is it possible to use the packages as schema-definition in PostgreSQL databases (<schema>.<table>) by exporting the DDL?

It is possible to define a schema in the way you have described, but it is achieved by applying an Owner tag to each table element. You can add this tag via the Table Properties dialog, in the Table Detail tab. Once an Owner tag is applied to an element, the Database Builder will refer to it as {owner}.{tablename}.

 How did you create the Firebird database?

The Firebird database was created as a file 'on-the-fly', during the process of connecting to a database. See the presentation video at 6:30 minutes.

 Does Enterprise Architect accommodate Domain Classes? For example: I want to create a domain called "Cde" of type Varchar(50) with a default value, which can be applied to any columns to ensure consistency.

No. We assume you are referring to user defined types as Domain Classes. These are not currently supported by Enterprise Architect.

 Does it support Database specific stuff like MS SQL IDENTITY columns?

Yes. MS SQL IDENTITY is supported via the AutoNum, StartNum and Increment extended properties of a numeric column.

 Is it possible to model very specific database objects like clustered column stores indexes in SQL Server 2014?

Not currently. This is a new feature in MS SQL Server 2014, which we might support in future.

 Can the Database Builder tie into source control? All database objects we have are in TFS.

No. Enterprise Architect can integrate with TFS for the purpose of version controlling the data model, but the Database Builder does not forward or reverse engineer databases stored as version controlled objects in TFS.

 Is there a version compatible with

Not currently.

 Are native spatial types supported, for example MS SQL Server Geometry and Geography?

Yes. Enterprise Architect 12 includes Geometry and Geography in its data types for MS SQLServer 2012.

 Are there any hard limits for number of rows, columns, tables, and connections?


 Is PostgreSQL supported?


 Is there support for the PostgreSQL/PostGIS module? (This relates to the geometry data types).

The PostGIS data types needed to model PostgreSQL/PostGIS databases are included in the latest Base Model (EABase.eap file) that comes with Enterprise Architect 12.

 Do you have DDL templates for the Progress Open Edge Database?

No. Data modeling for OpenEdge is not currently supported.

 Is there non-SQL database support, for example MongoDB?

There is currently no support for MongoDB. Regarding other "non-SQL" databases, however, we do support Esri's ArcGIS geospatial database. You can find out more about that on the ArcGIS web page.

Database Connectivity

 Could you please send steps to connect to file?

See the presentation video available on this page, from 6:30 minutes.

 How do I connect to a database using a JDBC connection?

This is not currently possible as the Database Builder does not support JDBC connections.

 We use the native connection tools instead of ODBC – is there a way to connect Enterprise Architect using these?

No. We support ODBC because it provides a common interface to all the DBMS products that Enterprise Architect supports.

 Are OLE DB connections supported too?


 How can we connect to multiple databases?

Use the connections folder in the Database Builder. You can create multiple connections to various databases, but you have only one active at any point in time.

Model Transformations

 When going to MySQL you changed properties for tables individually. When you have lots of tables is there a bulk change facility?


  1. Right-click your package
  2. Choose Reset DBMS Options
  3. Use the Convert DBMS Type checkbox and its associated options.

 How can I synchronize a logical data model with physical data model?

To do this automatically, you can use Enterprise Architect's Model-Driven Architecture (MDA) Engine.

This sounds like a good topic for a future webinar. In the meantime, for more details, you can refer to the Help topic on MDA. Our whitepaper on Data Modeling also describes how to use a DDL transformation to create a DBMS-specific physical model.

 I already have a logical data model in Enterprise Architect and I want to base my database design on this. How do I do this, or do I have to build my tables from scratch?

As mentioned in the previous response, MDA or scripting would be required. See our whitepaper on Data Modeling for a recipe on how to transform your logical data model into a DBMS-specific physical model.

 Anything to make modeling across disparate SQL environments easier? For example Microsoft SQL Server (MSSQL) and Oracle – I've modeled the MSSQL version and now the boss says he needs this for Oracle too... annoying differences in data types.

As mentioned for other questions, there are some possibilities with MDA. You can transform your platform-specific model (PSM) to a platform independent (logical) model, then perform a DDL model transformation to another target DBMS. For a transformation template that accomplishes this see the Sparx Systems Community site post on Logical and Conceptual MDA transforms.

We are also considering expanding the Manage DBMS Options screen to give users more control over what gets changed and to visually highlight what elements will change.

 Can you link rather than copy the schema for tables from one DBMS type to another?

No. Each table element stores numerous properties that are specific to a single target DBMS. For example, each column stores a data type, which is valid for a specified DBMS type.

 After modeling and generating the database, are there tools to model and generate database tests that can be executed against the live database?

Enterprise Architect does have numerous test management features. I am not aware of any, however, that would automate the modeling/generation of tests specifically for databases. You might like search the Help topics Testing and Testpoint Management.

 Is it possible in Enterprise Architect 12 to model table field maps? It means linking from attribute to attribute to model for example migration or data warehouse processes. (In version 11, this is possible only on a diagram.)

As in previous versions, Enterprise Architect 12 does not use the attribute-to-attribute links database engineering. You might like to submit a feature request that expands on your idea, so that we can consider implementing it in future releases.

 Is there an automation interface for the Database Builder?

The automation interface can be used against data model elements. The Database Builder, however, is a GUI to make data modeling easier.

Import and Reverse Engineering

 Can I reverse engineer from DDL Script file? (So I would only require a script file that defines the tables, rather than an ODBC connection)

Not currently. However this is something we would like to support in future.

 Is it possible to import a table definition into the Database Builder from an external file like a CSV and then apply stereotypes, such as Primary Key once imported?

Not directly. Although Enterprise Architect does support CSV import, it only imports data at the element level (so attributes would be missed). Instead, you might find a user-contributed script helpful for importing elements and attributes into an Enterprise Architect model from Excel. Please see the Sparx Systems Community Site post Simple VBA Excel to EA importer. You could stereotype elements and attributes with the CSV/Excel file or after import.

Where possible, the recommended approach is to import your table definitions directly from within Enterprise Architect via an ODBC connection.

SQL Scratch Pad

 After building the database and populating it with data, can the SQL scratch pad be used to run SQL SELECT statements?

Yes. You can also take advantage of two handy shortcuts: In the Database Builder, right-click a table and choose either View Top 100 rows or View Top 1000 rows.

 Does the SQL Scratch Pad support all types of databases?

The requirement is that an ODBC connection can be made to the database. This is possible with any of the popular RDBMS products.

Model Patterns

 It looked like Entity model was supported as a database. Is that correct?

No. The Entity Relationship Diagram (ERD) model pattern is shown during the presentation when the Model Patterns dialog is invoked. This ERD pattern is listed after the DBMS model patterns and is a different type of pattern.

 Are there any features (or planned features) to help establish an initial relational database model from a UML Class Model?

Yes. Enterprise Architect provides Model Patterns for this purpose, as demonstrated in the presentation video, from 2:15 minutes.

Team Support

 Can two users change the data model at same time?

Yes, if they are using a shared model repository. In such a scenario, you might want to use Enterprise Architect's built-in role-based (user) security to prevent accidental modification and avoid conflicting changes. More details are available in the Help topic User Security.

 Do you support the continuous work of teams during object oriented modeling, when analysts have a PIM, the PSM is done and an ER diagram is needed as well as a database (physical) model based on modeled Entities? How to simplify the transformation of entity classes to an ER model, support of ORM in Sparx 12?

Interesting questions... you might like to send us some more details at with examples of what you would like to achieve.

Database Administration

 Do you have the ability to carry out specific DBA type activities such as creating indexes in Oracle or SQL Server?

It is possible in Enterprise Architect to create indexes on a table, however there is no specific tool for DBAs to determine whether an index is required – that analysis would still be done outside of Enterprise Architect. Once the proposed changes were tested, the new properties/settings and objects could be synchronized to the model.