MyEclipse ER Designer Tutorial

Outline

1. Preface

This document was written using MyEclipse and a demonstration Oracle9i server. All screenshots are based upon the default user interface settings for MyEclipse and Windows XP. If you experience difficulty with the instruction of this document, please see the User Feedback section for how to provide feedback to the MyEclipse documentation team.

2. Requirements

Below is a list of requirements for this Tutorial:

1. MyEclipse (see Resources for a download link)
2. A database server that is supported by the MyEclipse Database Explorer (like the included MyEclipse Derby Server)
3. The MyEclipse Database Explorer configured to browse a sample database

3. Introduction

The MyEclipse ER-Designer is a maturing feature set that will ultimately enable developers to visually manage the full life-cycle of a relational database from design, through implementation, and maintenance. The MyEclipse ER-Designer is the initial public version and its features are limited to providing a visual model of existing databases. The ER-Designer utilizes the database connectors of the Database Explorer to access a database's metadata as it reverse engineers the database's entity-relationship (ER) model. From that, the ER-Designer renders the model using the Information Engineering (IE) System for ER as an entity-relation diagram (ERD). At any time the ER-Designer can resynchronize an ERD with its database to accurately reflect the current state of the database. The ER-Design includes a sophisticated table and relationship layout engine that enables it to quickly layout/relayout an ERD.

Figure 1. MyEclipse Database Explorer Perspective

The ER-Designer provides numerous tools and features that enable you to create, reorganize and customize an ER, and to export the ER as a JPEG image. This Tutorial will demonstrate how to use these tools to accomplish the following tasks:

1. Reverse engineer an entity-relation diagram (ERD) from a database
2. Reposition and resize tables and reroute relationships
3. Automatic layout of an ERD table and nodes
4. Export an ERD diagram as a JPEG image
   
5. Resynchronize individual tables or an entire ERD with its source database
6. Add and remove individual tables from the ERD
   
7. Customize the ER-Designer's look and feel

4. Creating an ER Diagram from a Database

This section demonstrates how to create ERD from an existing database. The example presented uses the Oracle 9i Human Resources (HR) example database.

1. Open the Database Explorer perspective. From the menubar select Window > Open Perspective > Other... > MyEclipse Database Explorer
2. From the Database Browser view select the database or schema for which to create an ER diagram
3. From the context-menu (right-click) select the Create ER Diagram action (see Figure 2).

Figure 2. Creating an entity-relation diagram from a database

4. From the New ER Diagram File Wizard, select the project and specify the name of the new ER diagram file. Note that the ".mer" is the default file extension used by the ER Designer . If your workspace has no projects, the Projects list will be empty and you will need to cancel the process, create a project, and then resume this process.

Figure 3. New ER diagram location selection

5. In the New ER Diagram wizard select OK to initiate the database analysis and diagram creation process.
6. The Create ER Diagram dialog will allow you to select the tables in your schema which you want to appear in the diagram.
   We leave out the REGIONS table but choose all the others.

   
   Figure 4. Table selection dialog

Reverse engineering an ER diagram from a database requires that the ER-Designer download and analyze the database's metadata.
Depending upon the size of your database, the number of tables you have selected and the performance of the connection to the DB server, the metadata retrieval process could be time consuming. Therefore you may choose to run the process in the background by selecting the Run in Background action in the ERD creation monitor (see Figure 5).

Figure 5. Progress Monitor while accessing database meta

Upon completion of the process the new HR.mer file has been created. Notice that the REGIONS table is not present in the diagram and Outline view.

Figure 6. Workbench with newly created HR.mer file

5.  Working With An ER Diagram

This section demonstrates use of the basic ER-Designer features with the HR.mer ERD file created in Section 4.

5.1 Notation and Presentation Basics

The ER-Designer supports the Information Engineering for ER notation, a.k.a., crows feet notation. During the reverse engineering process only the cardinality of the child entity of a parent-child relationship is depicted. Figure 6 depicts an annotated relation. Notice that the details compartment of a table defines each table column, the column's type, and its role, such as a primary or foreign key. Table 1 defines the meaning of the icons and text styles used in the table details compartment.

Tables

Tables may be repositioned to any location on the ER-Designer by selecting a table and dragging it to a new location. Any relocation operation may be reversed using the Eclipse UNDO command or its equivalent keyboard short-cut (e.g., ctrl-z). When a table is selected it is presented with a distinct highlighted background color. In Figure 7, the JOBS table appears as a selected table.

Figure 7. Example entity relationship

Table 1. ER-Designer Table Index

Example Table	Symbol/Text Style	Value
	underlined text +   or	Primary Key
	or	Foreign Key
	bold text	Not NULL constraint
	normal text	NULL-able
	/	Expand/Collapse details compartment

Relationships

A relationship is depicted as a directed, labeled connection between 2 database table entities. The connection's label is based on the pattern: parentTable(column1,...)= childTable(column1,...) mapping. Next to the relationship label is a direction arrow that points from the parent table to the child table (->). When you fly-over a connection, the foreign key constraint name is displayed. For example the JHIST_JOB_FK constraint is depicted in Figure 6 just below the relation in bright blue text.

Table 2. ER-Designer Relationship Annotations

Example Schema	Symbol	Description
		One and only one
		One or more
		Zero or more
		Zero or one

5.2 Managing Tables and ER Diagram Data

An ER diagram can be refreshed (i.e., resynchronized) with the current state of the database from which it was derived. The refresh process involves retrieving database metadata and reconciling it with the state of the ER diagram.
A selected table, groups of tables, or the entire ER diagram can be refreshed as desired.

Refreshing Table Data

1. Select 1 or more tables to refresh
2. From the context-menu (right-click menu) select the Refresh Table action
3. The ER-Designer will interact with the Database Explorer to retrieve the metadata for the selected tables and update the diagram

Figure 8. Refresh Table context menu action

Refreshing the Entire ER Diagram

1. From the menubar select View > Refresh Diagram
2. The Refresh ER Diagram dialog box will allow you to add and remove tables from your diagram.
   Tables which are not in the Selected tables list will be removed from your diagram if they are currently in it; conversely, tables which are not currently in your diagram but are present in this list will be added to your diagram.
   The ER-Designer will interact with the Database Explorer to retrieve the metadata for the selected tables and update the diagram.

   
   Figure 9. Refreshing an ER Diagram

Adding Tables to the ER Diagram

1. Select the table(s) you wish to add in the DB Browser view.
2. Drag the table(s) into the ER Diagram and drop them at the desired position.
   If a table is already in the diagram, it will be refreshed.

   
   Figure 11. Adding tables to an ER Diagram

   Note: Tables can also be added using View > Refresh Diagram as mentioned above.

Deleting Tables from the ER Diagram

1. Select the table or group of tables you wish to delete.
2. Press the DEL key, choose Edit > Delete or bring up the context menu and choose the delete action.

   
   Figure 10. Deleting a table

3. The selected tables will be removed from your diagram, along with all the corresponding imported and exported relationships.
   Note: To Undo table deletion, use the Undo action from the diagram's context menu or the eclipse toolbar.
   Complete Undo and Redo support is available.

5.3 Relationship Routing and Rerouting

Relationship connections may be manually rerouted by selecting the target relationship and then dragging bend-points on the connection. When a relationship is selected, the diagram changes as follows: 

• The relationship line color changes to red
• The relationship tables are background color changes to the selected color
• Bend-points (small black knobs) are displayed.

Figure 12 depicts a selected relationship that has been manually routed.

Figure 12. Selected relationship showing its bend-points

A relationship that has been manually routed can be automatically rerouted using the MyEclipse Layout Engine. This feature is useful when manually routing produces undesirable results and the user wishes to return to auto-layout of a relationship. To do so, select the relationship that has been manually rerouted. Then from the context-menu (right-click menu) select the Reroute action  (see Figure 13). The Layout Engine will reroute the relationship using its optimization algorithm.

Table 13. Invoking relationship reroute action

Figure 14. Relationship following rerouting action

5.4 Visual Grid, Labels, Table Alignment and Table Resizing

The ER-Designer provides a visual grid, grid alignment and table sizing tools to help you manually layout tables and relationship routing in a uniform and aesthetically pleasing style.

Grid - To toggle the visual Grid feature on/off do the following:

1. Select the ER-Designer for HR.mer
2. From the menubar select View > Grid

Figure 15. Toggling the visual grid on and off

Snap To Grid   - The Snap To Grid feature facilitates the manual alignment of tables and relationships by constraining their placement to align with vertical and horizontal grid lines. The Grid is NOT required to be visible for this feature to be activated. To toggle the Snap to Grid feature on/off do the following:

1. Select the ER-Designer for HR.mer
2. From the menubar select View > Snap To Grid 

Figure 16. Toggling the Snap to grid on and off

Relationship Labels - Labels for relationships between tables are shown by default. To toggle label visibily, do the following:

1. Select the ER-Designer for HR.mer
2. From the menubar select View > Relationship Labels 

Figure 17. Display relationship between tables

Horizontal and Vertical Alignment Tools - The ER-Design contributes table alignment tools to the MyEclipse toolbar. To align 1 or more tables do the following:

1. Select 2 or tables that you wish to align
2. From the MyEclipse toolbar select the alignment style to apply to the selected tables. The toolbar alignment styles are shown in Figure 18.

Figure 18. Toolbar table alignment styles

Table Resizing Tools - The ER-Designer contributes table resizing tools to the MyEclipse toolbar. To resize 1 or more tables do the following:

1. Select 2 or tables that you wish to resize
2. From the MyEclipse toolbar select the resizing style to apply to the selected tables. The toolbar resize styles are shown in Figure 19.

Figure 19. Toolbar table resize styles

5.5 Locating and Selecting Tables

Browsing a large ER diagram to locate 1 or more tables can be tedious. To simplify this process the ER-Designer provides 2 table location features. The first is an Outline View that depicts the database tables in tree list. The second is a Table Section Dialog that provides fast table name search and location. Once you have located the tables of interest you may select and manipulate them.

To view and select a table from the outline view do the following:

1. If the Outline View is not already opened, then from the menubar select Window > Show View > Basic > Outline
2. Scroll the Outline View to the desired table and select it. This will cause the corresponding ER-Diagram table to be highlighted and scrolled into view.

For example Figure 20 shows the JOB_HISTORY table selected in the outline view and the corresponding ER digram table symbol highlighted and visible.

Table Locator Dialog

In addition to using the Outline View to locate your tables of interest, the ER-Designer provides the Table Locator, a pop-up dialog with a fast text search feature. The Table Locator is launched by from either the ER-Designer's context-menu, the Navigate menubar, the Outline toolbar, or by pressing Alt + F3. This example will use the Outline toolbar action to launch the Table Locator. 

1. From the Outline toolbar select the search icon (see Figure 21)

Figure 21. Launching Table Locator from Outline View

2. The Table Locator Dialog will appear (see Figure 22)

Figure 22. Table Locator Dialog

3. If your table is not in view of the dialog type in the first few characters of its name and the list will quickly filter to the set table names that have a prefix of the string that you entered
4. Select your table of interest
5. Select OK to complete the search process

The Table Locator Dialog will close and the ER-Designer will scroll the selected table into view and highlight it in a manner similar to the Outline View location.

6. Exporting a Diagram

The ER-Designer enables you to export ERDs in the JPEG image format. To export an ERD, from the context-menu select the Export As JPEG... action. This will open a file selection dialog for you to specify the location of the new JPEG image. The export action can be seen in the context-menu of Figure 23.

Note: A large ERD can easily require 100's of megabytes to represent it in image format. In some cases this may result in failure of the export operation as the image is too large to manage in version 3.8.3. A future version of the ER-Design will optimize the image export memory requirements.

Figure 23. Export As JPEG image action

7. Configuring the ER-Designer

You may customize the look and feel of the ER-Designer through its MyEclipse preference page. To access the ER-Designer preference page, select from the menubar Window > Preferences > MyEclipse > Database Explorer > ER-Designer.

Figure 24. ER-Designer Preference page

8. Summary

This concludes the introduction to the MyEclipse ER-Designer features and their use. Additional Tutorialt documents are available that introduce working with Hibernate along with the Database Explorer, as well as working Web Projects, editing HTML, application server configuration, and enterprise application projects. For more information visit the MyEclipse Application Developer Help topic.

9. User Feedback

If you have comments or suggestions regarding this document please submit them to the MyEclipse Documentation Forum.

10. References

• Introduction to Data Modeling
• Download MyEclipse.
• Visit the MyEclipse Tutorial Library for more MyEclipse tutorials.