Designing an Entity Relation Diagram

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

1. Select Window>Open Perspective>MyEclipse Database Explorer.
2. In the DB Browser, right-click the database or schema for which to create an ER diagram, and select New ER Diagram.
   
   Creating an entity-relation diagram from a database
3. Select the project and enter a name for 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 is empty and you must cancel the process, create a project, and then resume this process.
   
   Selecting new ER diagram location
4. Click OK to initiate the database analysis and diagram creation process.
5. Select a table on the left, and click the Add button to include it in the diagram. Click Finish after adding the tables you want in the diagram.
   
   Selecting tables to include in the diagram

Reverse engineering an ER diagram from a database requires that the ER-Designer download and analyze the database’s metadata. Depending on 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 might choose to run the process in the background by clicking the Run in Background button in the progress window.

Progress window while accessing database metadata

Upon completion of the process, the new HR.mer file has been created. Notice that the REGIONS table, which was left unselected above, is not present in either the diagram or Outline view.

Workbench displaying HR.mer file

2. Refreshing Table Data in the Diagram

An ER diagram can be refreshed (i.e., synchronized) 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.

Select one or more tables to refresh, right-click, and select Refresh Table from the menu. The ER-Designer interacts with the Database Explorer to retrieve the metadata for the selected tables and updates the diagram.

To refresh an entire diagram, select View>Refresh Diagram. The Refresh ER Diagram window appears and allows you to add and remove tables from your diagram. Tables not in the Selected Tables list are removed from your diagram if they are currently in it; conversely, tables not currently in your diagram but are present in this list are added to your diagram. The ER-Designer interacts with the Database Explorer to retrieve the metadata for the selected tables and updates the diagram.

Refreshing an ER diagram

3. Adding Tables to an Entity Relation Diagram

1. In the DB Browser, select the table(s) you want to add to the diagram.
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 is refreshed.
   
   Adding tables to an ER diagram

Note: Tables can also be added by selecting View>Refresh Diagram from the menu as mentioned above.

4. Deleting Tables from an Entity Relation Diagram

Select the table or group of tables you want to delete, and press the DEL key. Alternatively, you can select Edit>Delete from the menu, or right-click a table, and select Delete.

Deleting a table from the diagram

The selected tables are removed from your diagram, along with the corresponding imported and exported relationships.

Note: To Undo table deletion, right-click the diagram, and select Undo. Complete Undo and Redo support is available.

5. Routing and Rerouting Relationships

Manually route relationship connections 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’ background color changes to the selected color
• Bend-points (small black knobs) appear

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 you want to return to auto-layout of a relationship.

To automatically reroute a relationship, right-click the relationship that was manually rerouted, and select Reroute. The Layout Engine reroutes the relationship using its optimization algorithm.

Rerouting a relationship

Relationship following the automated reroute

6. Locating and Selecting Tables

To view and select a table using the Outline view do the following:

1. If the Outline view is not already open, select Window>Show View>Outline.
2. Scroll the Outline view to the desired table and select it. This causes the corresponding ER Diagram table to be highlighted and scrolled into view.

The image below shows the JOB_HISTORY table selected in the outline view and the corresponding ER diagram table symbol highlighted and visible.

To use the Table Locator search, do the following:

1. Click the Table Search icon in the Outline view toolbar.
   
   
   Opening the Table Locator from Outline view
   
   
   Table Locator window
   
2. If your table is not visible in the list, enter the first few characters of the table name, and the list filters to the set table names containing a prefix of the text you entered.
3. Select the table you want to view, and click OK. The Table Locator closes, and the ER-Designer scrolls the selected table into view and highlights it.

7. Exporting an Entity Relation Diagram

To export an ERD, right-click the ER-Designer, and select Export As JPEG from the menu. This opens a file selection dialog for you to specify the location of the JPEG image.

Note: A large ERD can easily require 100s 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.

Exporting as a JPEG