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!
了解如何:
| Example Model: | database-engineering-example-physical-model.eap |  |
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.
You need to install the ODBC driver for Oracle. You can access the driver via Oracle's ODBC driver download web page.
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.
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.
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:
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.
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.
UML Class diagrams are often used to define logical diagrams and pure (platform-independent) data models.
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.
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.
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).
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.
Yes. The complete DDL definition of a stored procedure is stored in the model. This definition is included in the generated DDL.
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.
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.
Corporate and higher editions, Enterprise Architect version 12 or later.
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.
No. You need Corporate edition or higher.
Use the menu Tools | Database Builder. As mentioned previously, ensure you have the right edition and version of Enterprise Architect.
See the video at 2:42 minutes. The Database Builder reflects the contents of the �Database� stereotyped package in Enterprise Architect's Project Browser.
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.
Enterprise Architect's database engineering tools are designed for use with relational databases - OODBMS products and schema-less databases are not yet supported.
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.
Yes. There macros are listed separately for database objects (such as tables), columns, constraints. For details, see the Help topic DDL 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.
Yes. Enterprise Architect provides an equivalent set of Database Engineering DDL templates for Oracle and other support DBMS products.
Yes. To export your custom templates:
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.
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.
Currently partitioning is not supported.
No. JDBC is not supported.
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:
No. Import from DDL is not yet supported.
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.
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.)
Enterprise Architect only supports import from ODBC, as it provides a common interface and a set of APIs that describe the database definition.
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.
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.
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.
The tools shown in this webinar are specifically for relational DBMS products.
No. Amazon relational databases are not currently supported.
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.
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.
There are no immediate plans to support these technologies.
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.
No. Enterprise Architect supports physical data modeling for relational and geospatial databases.
