Introduction to Oracle SQL Developer Data Modeler

By Casimir Saternos

Learn how to install/configure Oracle SQL Developer Data Modeler and then perform some common tasks.

Published July 2009

Oracle SQL Developer has quickly become a popular graphical tool for database development. New features have been added in each release, demonstrating Oracle's commitment to continuing to enhance this product. With Oracle SQL Developer, you can browse database objects, run SQL statements and scripts, and write and run PL/SQL programs. You can also run supplied database reports and even create and save your own queries as reports.

However, one feature not included is the ability to create database diagrams. In particular, entity relationship diagrams (ERDs) are a popular way to represent the design of a set of database tables and their relationships in a compact, expressive manner. Oracle recognizes this need and has introduced Oracle SQL Developer Data Modeler to provide a variety of data and database modeling tools and utilities.

Oracle SQL Developer Data Modeler has many features related to designing and implementing databases. It is based on standards including the Zachman Framework, as well as the Object Management Group's MetaObject Facility (MOF) and Common Warehouse Metamodel (CMW) specifications. As such, it streamlines the development process to promote robust, complete, and well-designed databases. Its many capabilities include code generation, import and export to a variety of destinations, formatting functionality, and rule-based validation of models.

This article will show you how to install and configure Oracle SQL Developer, generate an ERD based upon the supplied HR demonstration schema, and export a SQL Data Definition Language (DDL) creation script for the schema.

Prerequisites and Resources

Oracle SQL Developer Data Modeler is platform independent and is compatible with Oracle Database version 9.2.0.1 and later. Currently it is available as a standalone product, but there are plans in the works to make it available as an extension to Oracle SQL Developer. The demonstration provided in this article runs on a Windows OS machine and uses the freely available Oracle Database Express EditionIf you have not done so already, do the following:

  • Download and install Oracle Database 10g Express Edition (XE). More information about installing, configuring, and maintaining Oracle Database XE is available in the online documentation.
  • Download and install the Oracle SQL Developer Data Modeler software. The version of the product being demonstrated in this article is version 2.0.0.57.0, which was released on July 1, 2009. If you are installing a version that does not include a Java runtime environment (JRE), you will be prompted to reference one when the application is started that is at least 1.6.0_04. If in doubt, simply download and install the version that includes the JRE.


Initial Configuration

If you have not already done so, unlock the HR user's account.

Regardless of the installation option you chose, you can start Oracle SQL Developer Data Modeler by double-clicking datamodeler.exe. There is a corresponding bash shell script supplied named datamodeler.sh for Linux users. The main application window will open, providing an interface similar to that used with Oracle SQL Developer. A browser with tree navigation appears on the left, various panels appear on the right, and menus and buttons are available at the top of the screen.

Figure 1

There are a number of options available to import from various sources and formats. Choose the File menu -> Import option to see all of the sources and formats that are available. Choose the Data Dictionary option.

Figure 2

This will open the Data Dictionary Import Wizard, which will take you step-by-step through the process of identifying the resources to be represented in the diagram.

Figure 3

Choose Add to open the Connection Editor.

Figure 4

Enter the following into the Connection Editor: Connection Name: XE
User Name: HR
Password: <password>
Save Password: <set to checked—to enable the Password text field above>
Role: default
Connection Type: Basic
Hostname: localhost
Port: 1521
SID:XE
Service name:

You can then choose Test Connection, and you should see a confirmation of the test as follows:

Figure 5

Click OK to close the Connection Editor.

Figure 6

The new connection is now listed on the Data Dictionary Import Wizard screen. Make sure that this connection is selected, and choose Next to see a list of available schemas that can be imported. Select the HR schema from the list and choose Next.

Figure 7

This screen includes the list of available objects to import. The object types are listed by tab at the bottom of the screen.

Figure 8

Choose the following five options from the five tabs listed below:

Tab

Option

Tables

Select All

Views

Select EMP_DETAILS_VIEW

Users

Select HR

Sequences

Select All

Stored Procedures

Select All

Choose Next after all of these options have been selected, and you will have the opportunity to confirm your selections.

Figure 9

You will notice that a number of other objects are included (TABLESPACE, TEMP TABLESPACE, and ROLE). These objects are considered to be dependent upon the objects selected.

Click Finish and wait a few minutes while files are generated. When the process is complete, a log file will be displayed with the results. Click Close to close the View Log window. The diagram will be displayed in a pane on the right side of the application window.

There are a number of options you can choose for modifying the appearance of the diagram. All of the details of the tables (table name, column names, data types, and keys) are displayed initially. By right-clicking on the diagram and choosing View Details, you can limit what information is displayed. Buttons at the top of the screen allow you to zoom in and out and fit the diagram to the screen. By right-clicking on an object on the diagram and choosing Format, you can further refine the appearance of objects in the diagram (changing color, font, sizing, and so on).

Figure 10

Modifying Diagrams

With a diagram of our database before us, we can modify the diagram in a number of ways. The following actions are available to add more objects and modify the objects already displayed.

Button

Action

Description

Select

Used to select existing objects. Once an object is selected, it can be moved or resized. You can also right-click for additional options.

New Table

Used to create a new database table.

New View

Used to create a new database view.

Split Tables

Used to split a table into two separate tables.

Merge Tables

Used to merge to separate tables into a single table.

New FK Relation

Used to create foreign key relationships between tables.

New Type Substitution

Used to graphically describe situations where subtypes are compatible with the entity (table). It is essentially a subclassing or inheritance mechanism. This functionality can be enforced using triggers in the database.

Whenever one of these options is selected, a window is displayed that provides a list of the available options pertinent to that action. These options allow you to tailor the creation of new objects to the degree of detail available through traditional SQL scripting. You can also add comments and remove objects as needed. You can see the complete list of options by using the buttons at the top of the screen, a menu option ( Object -> Relational), or shortcut keys.


Exporting Diagram Database Creation Scripts

Once you have made modifications to a model, you are able to export the results as SQL DDL. Choose File -> Export -> DDL file (or press Shift+E)to initiate the export. This will bring up the DDL File Editor. Click the Generate button to proceed to the available generation options.

Figure 11

Select Include Comments and click OK to generate the script. The DDL File Editor will be displayed with the text of your script. You can choose to save the file, search the file using the Find option, discard the results of the run, or edit the script manually.

Figure 12

This demonstration highlights only one possible use for Oracle SQL Developer Data Modeler. It has a wide range of capabilities and supports top-down, bottom-up and targeted approaches to modeling. It is a full-featured data modeling and database design tool that provides an environment not only for reverse-engineering schemas but for general-purpose data analysis related to capturing, modeling, managing, and exploiting metadata.


Conclusion

Oracle SQL Developer Data Modeler provides the data architect with the ability to effectively manage, analyze, and implement data models in a well organized and integrated manner. It includes extensive documentation and a tutorial to get you up-and-running quickly. It is an excellent addition to Oracle's software suite, providing powerful tools for data management and architecture tasks.

Casimir Saternos is an Oracle Certified DBA, IBM Certified Enterprise Developer, and Sun Certified Java Programmer based in Allentown, Pennsylvania.