Oracle SQL Developer Data Modeler is available as an independent product, providing a focused data modeling tool for data architects and designers. There is also a Data Modeler Viewer extension to SQL Developer, which allows users to open previously created data models and to create read-only models of their database schemas. SQL Developer Data Modeler is a vast tool, supporting the design of logical Entity Relationship Diagrams, and relational models, with forward and reverse engineering capabilities between the two. It supports multi-dimensional, data flow, data type, and physical models, and allows files to be imported from a variety sources and exported to a variety of destinations. It allows users to set naming conventions and verify designs using a set of predefined design rules. Each of these topics is extensive, so in this two-part article by Sue Harper (author of Oracle SQL Developer 2.1)  we’ll review a few of the areas, illustrating how you can use them and highlight a few key features, using the independent, stand alone release of SQL Developer Data Modeler. We’ll include a rief review of the integration points of the Data Modeler Viewer extension to SQL Developer. The product offers support for Oracle and non-Oracle Databases. In the interest of time and space, we have elected to only work with the Oracle database.

Oracle SQL Developer Data Modeler

SQL Developer Data Modeler provides users with a lightweight tool which provides application and database developers a quick and easy way of diagrammatically displaying their data structures, making changes, and submitting the new changes to update a schema. In this article, we will not attempt to teach data modeling (except to provide some generally accepted definitions). Instead, we will discuss how the product supports data modeling and a few of the features provided. There are a variety of books available on the subject, which describe and define modeling best practice.

Feature overview

The Data Modeler supports a number of graphical models and a selection of text-based models. The graphical models are:

• Logical—this is the entity relationship model or Entity Relationship Diagram (ERD), and comprises entities, attributes, and relationships.
• Relational—this is the schema or database model and is comprised of tables, columns, views, and constraints. In SQL Developer Data Modeler, these models are database independent, and need to be associated with the physical model to support database specific DDL.
• Data Types—this is the model that supports modeling SQL99 structured types and for viewing inheritance hierarchies. The data types modeled here are used in both the logical and relational models.
• Multidimensional models—these models support fact, dimension, and summary classifications for multi-dimensional models.
• Data Flow—these models support the definition of primitive, composite, and transformational tasks.

The following support these graphical models:

• Domains—these allow you to define and reuse a data type with optional constraints or allowable values. You can use domains in the Logical and Relational models.
• Physical—this model is associated with a relational model and defines the physical attributes for a specific database and version.
• Business Information—this allows you to model or document the business details that support a design.

Tying these graphical and textual models together are a variety of utilities, which include:

• Forward and reverse engineering between the Logical and Relational models
• Import from various databases
• Export, including DDL script generation, for various databases
• Design Rules for verifying standards and completeness
• Name templates, glossary, and abbreviation files for supporting naming standards

Integrated architecture

SQL Developer Data Modeler is made up of a number of layers, which have a tightly synchronized relationship. The Logical model is thought of as the core of the product, providing the starting point for any design, and feeding details into other models. The following diagram shows an illustration of how the models relate to each other:

The logical ERD provides the basis for one or more relational models, and each of these feeds into one or more physical models, which are in turn used for the DDL generation. You can create separate data types models and use the defined data type in either the logical or relational models. Both relational and logical models can have multiple subviews created, and each subview can have many displays created.

Getting started

SQL Developer Data Modeler is an independent product, and with the exception of the Data Modeler Viewer extension to SQL Developer 2.1, is not packaged with other Oracle tools. You can download it and install it in a directory of your choice, with no impact on other tools. To install, simply unzip the file.

Installing and setting up the environment

Getting started with SQL Developer Data Modeler is straightforward. Follow the links from the Data Modeler site on OTN, http://www.oracle.com/technology/products/database/datamodeler to the download location. You are offered a choice of files to download:

• For Microsoft Windows, a ZIP file with or without the JRE included
• For the Mac OS X, a ZIP file without the JRE included
• For Linux, a ZIP file without the JRE included

For any of these ZIP files, extract the file contents and run the datamodeler.exe, which is in the top-level /datamodeler folder, or in the /datamodeler/bin folder. For Linux, use the datamodeler.sh executable.

If the file you choose does not include a JRE, you will be prompted on startup for the location of your installed JRE. The minimum supported release is JRE 1.6 update 6.0.

Oracle clients and JDBC drivers

If you are designing and building a model from scratch, or have access to the DDL script file for importing models, then you do not need to have access to a database. However, if you want to import from a database, you’ll need to create a database connection. In this case, there is no need for an Oracle client in your development environment because you can use the thin JDBC Driver to connect to the database. SQL Developer Data Modeler also supports the TNS alias. Therefore, if you have access to a tnsnames.ora file, or have other Oracle software installed in your environment, you can access the tnsnames file to make the database connection if and when required.

Creating your first models

The Data Modeler browser starts with empty Logical and Relational models. This allows you to start a new design and build a model from scratch, whether a logical model with entities and attributes, or a relational model with tables and columns. The Data Modeler also supports metadata to be imported from a variety of sources, which include:

• Importing metadata from:
  • DDL scripts
  • Data dictionary
• Importing from other modeling tools:
  • Oracle Designer
  • CA Erwin 4.x
• Importing other formats:
  • VAR file
  • XMLA (Microsoft, Hyperion)

The context menu displaying the choices available is shown in the following screenshot:

Once you have created and saved your models, you can open these or share them with colleagues. To open an existing model, use the menu:

• File | Open—browse to the location of the files, which then opens the full design with all of the saved models
• File | Recent Designs—opens the full design, with all of the saved models, with no need to first search for the location
• File | Import | Data Modeler Design—more granular, offering a choice of models saved in a set of models

Recent diagrams
Use File | Recent Diagrams to display a list of all diagrams you have recently worked on and saved. Using this approach saves you from needing to browse to the location of the stored files.

Importing from the Data Dictionary

There are many ways to start using the tool by just starting to draw any one of the model types mentioned.

In the screenshot shown earlier, we highlighted the File | Import | Data Dictionary option. Using this allows you to import from Oracle 9i, Oracle 10g, Oracle Database 11g, Microsoft SQL Server 2000 and 2005, and IBM DB2 LUW Versions 7 and 8.

Creating a database connection

Before you can import from any database, you need to create a database connection for each database you’ll connect to. Once created, you’ll see all of the schemas in the database and the objects you have access to.

Access the New Database Connection dialog from the File | Import wizard (seen in the following screenshot). If you have no connections, click on Add to create a new connection.

For a Basic connection, you need to provide the Hostname of the database server, the Port, and SID. The connection dialog also supports TNS alias and the advanced JDBC URL.

Before you can add connections for non-Oracle databases, you need to add the required JDBC drivers. To add these drivers, use Tools | General Options | Third Party JDBC Drivers.