Enhanced Database Engineering with Enterprise Architect

Webinar February 2016
Learn how to:
  • Model useful database concepts, including foreign keys and views
  • Customize DDL schema generation to suit your needs
  • Reverse engineer existing schemas from live databases

Would you like help to model some common database concepts?
Do you need to ensure that auto-generated DDL scripts align with your project standards?
Can you quickly visualize legacy database schemas, to facilitate maintenance or redesign?

In this follow-up webinar, we take a closer look at Enterprise Architect's powerful Database Engineering toolset that empowers our global users with a DBMS-centric view for engineering physical models and connecting to and querying live databases!

Ben Constable
Ben Constable

Explore the Example Database (Physical) Model

We have provided the example Enterprise Architect model used in the webinar. The model also contains the SQL queries used.
Example Model: database-engineering-example-physical-model.eap Download the Enterprise Architect Database Engineering Example Model

Questions from the Audience

Click on a question to see the answer.

General

What edition of Enterprise Architect supports the database engineering tools?

Corporate and higher editions. Also note: The Database Builder and template-based DDL generation were introduced with Enterprise Architect version 12. Import from ODBC and non-template-based DDL code generation were released in earlier versions of Enterprise Architect.

I have challenges connecting to an Oracle database. Which is the correct ODBC driver I need to install?

You need to install the ODBC driver for Oracle. You can access the driver via Oracle's ODBC driver download web page.

I can't find a MySQL ODBC connector for Windows. Do I have to add that?

Yes, if you want to reverse engineer or synchronize a MySQL database, you need to download and install the ODBC driver. You can download the driver from MySQL Connector/ODBC web page.

How can you generate a schema, that is create/drop schemas?

Connect to any live database via Enterprise Architect's Database Builder. Then you can directly execute SQL queries via the SQL Scratchpad built into the Database Builder. Your queries can include SQL's CREATE and DROP syntax.

Data Models and Physical (Database) Models

How do we create a Model Pattern for a new database type and register it, so it is supported by the Database Builder?

You can add your own Model Pattern by creating one in an MDG Technology. However the Model Pattern will not show up in the same Database Technology that is built into Enterprise Architect – that is a separate MDG Technology built into Enterprise Architect and is not editable by users. Also, note: The Database Builder will not automatically recognize a new (unsupported) DBMS product based on a new Model Pattern, as the compare, merge and DDL generation functions require additional inputs, such as code templates for generating DDL.

For more information on user defined model patterns in Enterprise Architect, see:

How do you create views or synonyms across databases?

Normally it is just a matter of specifying the fully qualified view name. Views, like procedures, functions and sequences, are simply SQL objects. Whatever is in their definition will be added to the generated DDL.

Can you convert a UML class model into a database model? If so, how do you map UML attribute types to database column types?

Yes. Enterprise Architect provides model-driven architecture (MDA) transformations to automatically convert UML class models into database models. The UML Attributes should are mapped to an appropriate DBMS type as part of the transformation. For example a UML attribute of type Integer would map to INTEGER when you target a MySQL physical data model. Note: At the time of writing, there is a known issue where string attributes types were not being mapped to VARCHAR in the target database. This has been corrected for a future release however.

For more information on converting pure data models to physical models using MDA, see our Data Modeling paper available from our community site. In particular see figures 5, 7, 9 & 10.

Also see for a short description on how to generate DDL via an MDA Transformation, see page 5 for of our paper on Enterprise Architect's Database Builder.

What do you recommend for logical diagrams rather than physical diagrams? I find ERD cumbersome and resorted to class diagrams...

UML Class diagrams are often used to define logical diagrams and pure (platform-independent) data models.

What is the best way to layout your ERD if you have hundreds of tables?

Modular diagrams tend to work best. Rather than placing tables on a single diagram and trying to find an optimal routing for all relationships, try to focus each diagram on a particular aspect of the data model. If you need to create large "poster" diagrams for reference purposes, use Diagram Frames. This is an effective way to build a large diagram from smaller ones. For an example, see our recent webinar on Large-scale Geodatabase Modeling Techniques. The presentation discusses Diagram Frames from 7:53 minutes.

Can you still use Crow's foot (Information Engineering) notation?

Yes. When we released the Database Builder in Enterprise Architect, we also included a set of database model patterns, as used in this presentation. Based on feedback from users, IDEF1X notation was used to display relationships in those model patterns. For any diagram, however, you can switch between Crow's foot, IDEF1X and UML notations. For details, see our Help topic on Data Modeling Notations.

Is it possible to model objects like packages and stored procedures in an Enterprise Architect Project (*.eap) file?

Yes. Oracle packages are supported, as are stored procedures for all supported DBMS product that use stored procedures. You can model these database constructs irrespective of the type of model repository you use (*.eap or *.feap files, or RDBMS repository).

Can I assign a particular tablespace also for index structures?

In the context of allowing table indexes to be defined in different tablespaces, the current release of Enterprise Architect (Build 1226) supports this. The value is stored as a Tagged Value against an index. Currently this is only available for Oracle because that is the only DBMS we know of that supports it.

For stored procedures, is the content (text) part of the model? Will stored procedures be in the generated DDL?

Yes. The complete DDL definition of a stored procedure is stored in the model. This definition is included in the generated DDL.

Can you link a physical model (tables) to the logical model (classes) defined by analysts?

Yes. A simple approach is to draw dependency connectors between the relevant packages or specific elements in the physical and logical models. This can be done via diagrams in the physical or logical model, or a completely separate model within the same Enterprise Architect project. For more information about tracing between model elements, you may be interested in our webinar on traceability in Enterprise Architect.

Can you discuss how stored procedures are modeled in a component diagram?

Enterprise Architect supports modeling each stored procedure as either a UML Operation or a UML Class. Although these approaches are equivalent when generating DDL scripts, there are different benefits from a modeling perspective. For example, if you have a large database with dozens of stored procedures, it might be convenient to model them as operations. Because they will all be grouped together in one element, it is easier to create and manage diagrams that include them. On the other hand, modeling your stored procedures one per UML Class can provide greater flexibility. For example, you could reuse individual stored procedures more easily in different models. You could also add test cases, requirements and other information against each stored procedure element.

For details on how to model stored procedures, see the Help topic Database Procedures.

Database Builder

In which Enterprise Architect edition is the Database builder available?

Corporate and higher editions, Enterprise Architect version 12 or later.

Is this module part of my Enterprise Architect? (We have version 10.0.1004 and I don't see the Database Builder)

No. As mentioned in the previous question, you need Enterprise Architect 12 or later. Also check that you have an appropriate edition of Enterprise Architect, using the menu Help | About EA.

Are there plans to bring Database Builder at other editions than Enterprise edition?

No. You need Corporate edition or higher.

How exactly do I open the Database Builder?

Use the menu Tools | Database Builder. As mentioned previously, ensure you have the right edition and version of Enterprise Architect.

I missed where this sits within the structure of Enterprise Architect's Project Browser – would you show that again?

See the video at 2:42 minutes. The Database Builder reflects the contents of the «Database» stereotyped package in Enterprise Architect's Project Browser.

When invoking the Database Builder, does it automatically reference the model that is currently loaded?

If you have selected a package stereotyped «Database» in the Project Browser, when you invoke the Database Builder, it will automatically attempt to load this package. Apart from that, you can choose which database model to load, and the Database Builder will ensure your model edits are reflected in the corresponding model hierarchy.

How would the Database Builder and import tools work with schema-less databases or an object-oriented database management system (OODBMS)?

Enterprise Architect's database engineering tools are designed for use with relational databases – OODBMS products and schema-less databases are not yet supported.

Is Enterprise Architect's version control functionality aligned with the merging feature shown in the webinar? For example, when you create a change in the model and then update the database, will version control show the additional DDL?

No, not automatically. To associate the newly generated DDL with a particular version of the model, you could save the DDL to file and store that script file as an artifact element in the model. Then, using Enterprise Architect's Model Baseline feature to create a revision, your DDL would be stored with that revision of the model. Alternatively, you might generate the DDL to file and version it separately, outside of the model.

DDL Generation & Templates

Is there a list of available code template macros supported by Enterprise Architect?

Yes. There macros are listed separately for database objects (such as tables), columns, constraints. For details, see the Help topic DDL Macros.

In the Template Editor, how do you edit/write macros?

You type the any of the predefined database engineering macros (see the lists mentioned in the previous response) and enclose the name with % markers. For details, see the Help topics Edit DDL Templates and DDL Template Syntax.

Are Oracle DDL Script templates formatted the same way as the MySQL templates?

Yes. Enterprise Architect provides an equivalent set of Database Engineering DDL templates for Oracle and other support DBMS products.

Can you back-up and restore the original/custom templates so you can reset changes for building a separate set? Different rules for different projects...

Yes. To export your custom templates:

  1. Use the menu Project | Data Management | Export Reference Data
  2. Check the option Code, DDL, Transformation & CSV Templates
  3. Click Export and choose a target file when prompted.
To import your custom templates to a project:
  1. Use the menu Project | Data Management | Import Reference Data
  2. Choose the target XML file containing your custom templates.
  3. Choose a dataset and click Import

Does Enterprise Architect have the capability to generate “Alter Table” statements, while preserving existing data? For example adding new columns that need to deactivate referential integrity.

Generally, Enterprise Architect does not consider data in the table because it would significantly complicate the generated DDL, which is often not desirable. It is possible, however, for users to modify the DDL templates to retain data.

That said, where the DBMS supports doing so, Enterprise Architect will use commands that allow a column to be changed without dropping it. For example, when adding or removing a Default value on a MySQL column Enterprise Architect will produce DDL using the SET DEFAULT and DROP DEFAULT commands. Not all DBMS products, however, support this syntax.

Can I access my Tagged Values in the DDL templates? I need the Tagged Value information specifically for partitioning, but I cannot make this work from the templates.

Enterprise Architect currently loads a preset list of Tagged Values, which is then shared with the template engine for DDL generation. User defined Tagged Values are not loaded with that preset list and therefore not accessible by the templates. In a future release, however, we plan to load all user defined Tagged Values into a separate collection that will allow the templates to access the values via a new function macro.

How can I configure partitioning options to be generated in DDL?

Currently partitioning is not supported.

Reverse Engineering & ODBC Import

Is it possible to reverse engineer a database via a JDBC driver?

No. JDBC is not supported.

Is there support for Microsoft Excel (not OBDC) for import/export?

Yes. Enterprise Architect provides an import/export capability for .csv files. This is more limited than the database engineering support via ODBC and DDL. However Sparx Systems also provides a dedicated tool for working with Microsoft Office documents, including Excel.

For more details:

If you don't have direct access to an ODBC source, can you import from DDL?

No. Import from DDL is not yet supported.

If there are changes in the database, how do I sync the data model in Enterprise Architect for the only selected tables?

In the compare screen, you are given a list of elements that differ from the database. For any change item, or difference, on an element, you can elect to take no action.

Previously reverse engineering a "real" database has resulted in a diagram containing hundreds of tables. Any new tools or tips for partitioning that into a set of diagrams with reasonable contents?

As mentioned in the presentation, you should first use one of the Database Model Patterns to create a placeholder structure for your schema in the Project Browser. Although this won't change the contents of the auto-generated diagram, it ensures the imported elements are partitioned in the model hierarchy according to the kind of database construct, such as table, view or stored procedure.

After the import, you can create more modular diagrams in a semi-automated manner, by using such tools as Insert Related Elements. This is demonstrated in detail in one of our webinars on diagram tools in Enterprise Architect. (See the presentation from about 7:25 minutes.)

Could you import only from ODBC? What about OLEDB?

Enterprise Architect only supports import from ODBC, as it provides a common interface and a set of APIs that describe the database definition.

Why don't we have the possibility to check the two cases 'attributes' and 'connectors' when reverse engineering an XSD (XML Schema)?

It is because we don't want models with a large amount of redundant model data in them (considering that attributes and association connectors are equivalent in the XSD model).

Consider an XSD snippet as follows :

<xsd:element name="Directions">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="NE" type="xsd:string"/>
<xsd:element name="NS" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>

If we allow importing the XSDelements as both attributes and connectors then we will be importing “NE” and "NS" twice – once as UML Attribute and once as UML Connector.

The generated XSD would be:

<xsd:element name="Directions">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="NE" type="xsd:string"/>
<xsd:element name="NE" type="xsd:string"/>
<xsd:element name="NS" type="xsd:string"/>
<xsd:element name="NS" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>

This output would be incorrect.

DBMS Platforms

Does Enterprise Architect support SQL Server Databases in Azure?

Though not officially supported, if you can connect to your Azure database via the Microsoft SQL Server ODBC driver then it should be possible to reverse engineer your Azure database.

I noticed that you don’t have Microsoft SQL Server 2014 listed as a type of database, are you looking to add this?

The SQL Server 2012 datatypes are compatible with 2014 and there are no model-level differences required between the two versions. Therefore we are have not released a separate set of datatypes, code templates and DBMS type for SQL Server 2014. So you can create a physical model that targets SQL Server 2014, simply by using the SQL Server 2012 DBMS type in Enterprise Architect.

How would this work with schema-less databases or OODBs?

The tools shown in this webinar are specifically for relational DBMS products.

Can I import databases held in Amazon Web Service (AWS) cloud?

No. Amazon relational databases are not currently supported.

Can you please demonstrate an Oracle database-model compare operation?

We plan to post a video showing the Database Builder connected to an Oracle database and comparing the live database schema with the physical model.

Do you support DB2 on z/OS for DDL generation and reengineering?

Enterprise Architect has limited supported for DB2 on z/OS. As we do not maintain a dedicated z/OS server environment for testing purposes, we welcome feedback from users on any issues that arise.

Are you planning to support Hive (Hadoop SQL DB), NoSQL or IBM Netezza DDL?

There are no immediate plans to support these technologies.

Would Enterprise Architect be an appropriate tool to engineer NoSQL databases for use with 'big data' applications?

You can model the NoSQL database, but as mentioned in the previous response, Enterprise Architect currently does not support forward or reverse engineering of NoSQL objects.

Does Enterprise Architect support dimensional models?

No. Enterprise Architect supports physical data modeling for relational and geospatial databases.