When updating a legacy data model, say as part of a transformation or modernization project, it can be beneficial to start redesigning from the logical or conceptual level. This also applies when the data model is used as the basis for other aspects of project development, such as generating a class model, or deriving user interface or form designs.

A common starting point is a physical data model perhaps derived from reverse engineering a database. With all it's platform-specific details, you'll want to automatically derive an abstract (platform independent) representation.

In this post we provide two MDA transformation templates for Enterprise Architect that can help you do this:

• The Physical to Logical transform

This takes a Physical level model of any DBMS, and transforms it to the Logical level – eliminating Primary Keys, Foreign keys, Identifiers and simplifying the Field types to standard UML typing.

• Logical to Conceptual transform

This simplifies the Logical diagram by removing the Attributes to give the simple Entities and their relationships.

Also attached is an update for the Logical to Physical DDL transform built into Enterprise Architect. The update:

• Allows for modeled Enumerations to be generated to script for MySQL
• Sets a Primary Key in the Join tables created by the transformation.

Set up

To use the scripts unpack the attached zip folder.

To load the transform scripts into a model, select from Enterprise Architect's main menu:

Project | Model Import Export | Import Reference Data

Using the Select File button, choose the XML file to import. Then under Select Datasets to Import, choose the template name. The templates provided in the attached zip are:

• DDL to Logical (select “DDL to Logical_Transform_Template”)
• Logical to Conceptual (select “Logical to Conceptual_Transform_Template”)
• Updated DDL Transform ( select “DDL_Transform_Template”)

To check that the transforms have been loaded, select from the main menu: 

Settings | Transform Templates

Enterprise Architect's list of transformations should now include the additional templates “Logical to Conceptual” and “DDL to Logical”.

Usage

It is suggested that a new package is created to contain the target/transformed model before performing the transformation.

To run a transform:

• Select in the Project Browser the Package with the existing Physical database model
• Right-click and select Transform Current Package (Ctrl+Shift+H) from the context menu
• In the Model Transformation window, select the newly imported ‘DDL to Logical’ Transform Script under Transformations
• This will open a dialog to select a package in which to place the generated transform
• Select the package you created for this purpose (as suggested above)
• Click on the Do Transform button (this executes the transformation process)

Once the transformation completes, you can navigate to the generated package and view the automatically created class Diagram!

For help on using these templates see additional information in Enterprise Architect's User Guide under the topic Transform Elements.