Use your business data to your advantage with the help of Syncfusion’s new data science offerings. Discover how a custom big data solution can provide your company with valuable predictions about key market trends.

Introducing SQL Developer Data Modeler: Part 1

Packt Publishing

Design and Develop Databases using Oracle SQL Developer and its feature-rich, powerful user-extensible interface with this book and eBook

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:

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.

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.

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 diagramsUse 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.

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:

Saving designs

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.

Alerts & Offers

Series & Level

We understand your time is important. Uniquely amongst the major publishers, we seek to develop and publish the broadest range of learning and information products on each technology. Every Packt product delivers a specific learning pathway, broadly defined by the Series type. This structured approach enables you to select the pathway which best suits your knowledge level, learning style and task objectives.

Learning

As a new user, these step-by-step tutorial guides will give you all the practical skills necessary to become competent and efficient.

Beginner's Guide

Friendly, informal tutorials that provide a practical introduction using examples, activities, and challenges.

Essentials

Fast paced, concentrated introductions showing the quickest way to put the tool to work in the real world.

Cookbook

A collection of practical self-contained recipes that all users of the technology will find useful for building more powerful and reliable systems.

Blueprints

Guides you through the most common types of project you'll encounter, giving you end-to-end guidance on how to build your specific solution quickly and reliably.

Mastering

Take your skills to the next level with advanced tutorials that will give you confidence to master the tool's most powerful features.

Starting

Accessible to readers adopting the topic, these titles get you into the tool or technology so that you can become an effective user.

Progressing

Building on core skills you already have, these titles share solutions and expertise so you become a highly productive power user.