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.
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
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.
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
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.
Using the import wizard
Once you have a connection created, select the connection and continue using the dialog by clicking on Next. You can select more than one schema during import. In the example below, we have selected two schemas, which results in three diagrams, one central model with all of the imported tables and views, and two subviews. When importing, a separate subview is created for each schema you select and all of the imported objects are displayed in that schema (this is not the only role for subviews).
In the Import Wizard displayed, there is a set of checkboxes and buttons below the listed schemas. By selecting All Selected, the screens that follow will have all instances of all objects automatically selected. Depending on what you are planning to import, it is often easier to keep that deselected and then use the Select All button on each object type tab:
Once you have selected the schemas, you can select the individual objects. Tables and Views are placed on the central Relational model and onto the respective subviews for each schema. All other objects, such as Roles, Users, and TableSpaces, are maintained in the Physical model listed in the object browser.
A summary of objects to be imported is displayed and you are offered the choice of the model destination. The default destination is Relational_1. The choice given here is needed for second or any subsequent imports. For these you need to decide whether you'll want to merge the objects into an existing model or create a new model.
To create the initial model, complete the dialog and select Finish. Each time you import a set of database objects, a log file of the completed activity is created, which tallys the number of statements and errors. It is worth saving the log files to use for troubleshooting later.
Importing multiple schemas from the Data Dictionary creates a central Relational model and a subview for each of the schemas imported.
Reviewing the results
Once the import is complete, the main Relational diagram opens:
The previous screenshot shows a few of the tables imported into the Relational model. To see the initial subviews created for each schema created, expand the Relational Models node and then the SubViews node. Right-click on one of the schema subviews listed and select Show Diagram.
In the following screenshot, we have opened the HR subview and are preparing to open the OE subview:
As you work, it is wise to save the design you are working on. It is prudent, in case something goes wrong, and necessary if you are going to close and reopen the tool later. Initially, a design is untitled. You can change this by saving the design. Once saved, the new name appears in the browser, and a set of folders and XML files are saved on your file system:
When you save a design, a folder and a file of the same name are created. The previous screenshot shows Windows Explorer with a folder of a saved design. Here, the top-level folder is called MyFirstModel, as is the top-level XML file. It is this top-level file that you select when opening a design. All of the files are XML and can be read. Do not edit these as doing so can corrupt your design.