Enterprise Architect inclut un puissant ensemble d’outils d’ingénierie de bases de données. Le nouveau Constructeur de Base de Données améliore considérablement les capacités de modélisation des données existantes d’Enterprise Architect, ce qui rend plus facile que jamais la modélisation, la génération et la synchronisation de modèles de bases de données.
Dans ce webinaire, vous apprendrez à:
| Exemple Modéle: | database-engineering-example-physical-model.eap |  |
| Exemple Firebird Base des Données: | SALES-DATABASE-TEST.FDB | |
| Firebird Query - Afficher Tables: | firebird-select-user-tables.sql | |
| Firebird Query - Afficher Columns: | firebird-select-columns.sql | |
| MySQL Query - Afficher Tableaux: | mysql-show-tables.sql | |
| MySQL Query - Afficher Columns: | mysql-show-table-columns.sql | |
The Database Builder is available in the Corporate edition and above. Desktop, Professional and Lite editions do not have the Database Builder feature.
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.
Yes. You can find out about upcoming webinars from: www.sparxsystems.com/webinar
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.
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.
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.
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.
Changes in the model can be generated to DDL in two ways:
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.
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.
It depends on the target DBMS. If the Support DBMSs 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.
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.
Currently, it is all or nothing, but in a future release you should be able to individually choose which constraint types to generate.
It's straightforward because Enterprise Architect's underlying UML profile for data modeling is largely unchanged.
tables package created by the new model pattern. (The tables package resides under the package that is stereotyped database.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 ([email protected]).
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}.
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.
No. We assume you are referring to user defined types as Domain Classes. These are not currently supported by Enterprise Architect.
Yes. MS SQL IDENTITY is supported via the AutoNum, StartNum and Increment extended properties of a numeric column.
Not currently. This is a new feature in MS SQL Server 2014, which we might support in future.
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.
Not currently.
Yes. Enterprise Architect 12 includes Geometry and Geography in its data types for MS SQLServer 2012.
No.
Yes.
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.
No. Data modeling for OpenEdge is not currently supported.
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.
See the presentation video available on this page, from 6:30 minutes.
This is not currently possible as the Database Builder does not support JDBC connections.
No. We support ODBC because it provides a common interface to all the DBMS products that Enterprise Architect supports.
No.
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.
Yes.
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.
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.
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.
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.
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.
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.
The automation interface can be used against data model elements. The Database Builder, however, is a GUI to make data modeling easier.
Not currently. However this is something we would like to support in future.
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.
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.
The requirement is that an ODBC connection can be made to the database. This is possible with any of the popular RDBMS products.
No. The Entity Relationship Diagram (ERD) model pattern is shown during the presentation when the Motifs Modéle dialog is invoked. This ERD pattern is listed after the DBMS Motifs Modéle and is a different type of pattern.
Yes. Enterprise Architect provides Motifs Modéle for this purpose, as demonstrated in the presentation video, from 2:15 minutes.
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.
Interesting questions... you might like to send us some more details at [email protected] with examples of what you would like to achieve.
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.
