SQL Developer Data Modeler: A Top-Down Product Overview: Sue Harper Oracle United Kingdom

SQL Developer Data Modeler:

A Top-Down Product Overview

Sue Harper
Oracle
United Kingdom
Keywords: SQL Developer Data Modeler, ERD, DDL Generation, model, capture, design

Introduction
Oracle SQL Developer Data Modeler is a graphical data modeling tool for creating, browsing and
editing data models, including logical, relational, physical, multi-dimensional, and data type models.
It supports forward and reverse engineering from Oracle Database, Microsoft SQL Server and IBM
DB2 databases and the generation of DDL scripts for these databases. SQL Developer Data Modeler
offers features and utilities that enhance the data modeling experience, improve productivity and
promote the use of standards. SQL Developer Data Modeler is designed for a broad spectrum of
database developers; from business architects to DBAs and from database to application developers,
serving as a powerful communication tool between developers and business users.
In this article we review the main modelers and features in SQL Developer Data Modeler 2.0 starting
with the logical model and progressing through the features to the final DDL script. Often called the
top-down approach, this is not the only flow of work through the product, as indeed, users can start by
importing a data model and reverse engineering the objects to create an initial diagram and
progressing from there. We will refer to various methods of creating models and updating the
database using the product.
Integrated Models
SQL Developer Data Modeler provides facilities to build sets of related and integrated models. Many
consider the core of SQL Developer Data Modeler to be the logical model. It is this logical model that
provides a true implementation-independent view of enterprise information. Using the logical model,
often referred to as the Entity Relational Model (ERD), you can engineer relational models or
multidimensional models. The ERD is also ultimately the source of many different physical
implementations. A logical model or a part of it (subject area or sub view), can be transformed to one
or more relational models. Each relational model can have an unlimited number of physical
implementations in the form of physical models (referred to as RDBMS Sites in SQL Developer Data
Modeler), with each physical model based on a supported database.
Logical Models
The logical model (or ERD) in SQL Developer Data Modeler includes standard logical modeling
facilities, such as drawing entities and relationships. It also supports the choice of Barker or Bachman
notations. The diagram itself provides for box-in-box representation for the display of the super-type
and sub-type hierarchy of entities and you can easily create and display arcs, for the support of
exclusive relationships. The image below provides an example of a logical model using the Barker
notation and the box-in-box representation of super-types and sub-types.
Figure 1: SQL Developer Data Modeler Logical Model
Relational Models
The SQL Developer Data Modeler relational model is an intermediate model between the logical
model and the physical models. Some refer to this as the server diagram or data model as it is made up
of tables, columns and constraints and is a result of the transformation from the logical model. The
relational model supports relational design decisions independent of the constraints of the target
physical platform(s), which means that you can use one design for any of the supported target
databases. All many-to-many relationships and all super-type/sub-types entity hierarchies are resolved
during forward engineering (transformation) of the logical model to a relational model. You can apply
naming standardization to the models during the transformation, by applying a glossary of terms.
Physical Models
It is the physical model that allows you to add implementation-specific detail. Here you add structure
and definition to the objects, such as adding partitions and sub-partition detail, or adding elements not
exposed on a diagram, such as sequences, tablespaces and roles. There is no diagram associated with
the physical model, so all changes and updates are through the property dialogs which you can invoke
from the browser.
Data Type and Structured Type Models

The Data Types model is independent of each of the above models, allowing you to create and
visualize structured types and the inheritance hierarchies of structured types, defining distinct and
collection (array) types on a model and then use these in either the logical or relational models. You
can also add physical detail to these data types through the physical model.
Creating New Models

On opening SQL Developer Data Modeler, two empty model tabs, the logical and relational models,
are automatically opened. To start creating a new model, use the toolbar and click on the appropriate
button, for example the entity or table button, and then click on the model. Each action invokes the
associated dialog, allowing you to create or update the elements on the diagram. Once you have
created the logical model you can forward engineer this to a relational model. The Data Modeler also
supports the reverse process.
Importing Existing Models

You can also import existing logical models, multi-dimensional, or relational models instead of
creating them from scratch. You can create relational models by importing script files (DDL) or by
importing directly from the data dictionary. SQL Developer Data Modeler can also import directly
from the Oracle Designer repository or import CA ERwin Data Modeler export files. Extensive and
wizard-led engineering capabilities allow you to re-engineer a relational model to a logical model or to
engineer a logical model to one or more relational models, where both models can be kept
synchronized.
Importing from the Data Dictionary

While not necessarily a part of the top-down approach, you can also create models by importing or
reverse engineering directly from the database. SQL Developer Data Modeler provides selection lists
for all supported objects and with it the ability to filter out secondary tables. While importing, you can
import the objects from one or more schemas. The Data Modeler creates a single diagram for all
tables and views and a separate subview for each schema imported.
Generating Scripts
Once you have created the models and updated the physical properties for the objects, you can
generate the DDL scripts. SQL Developer Data Modeler supports generation of DDL scripts for
Oracle, DB2 and SQL Server. The DDL generation supports a wide variety of features, by allowing
you to generate interactive DDL scripts, add additional code into the scripts, and even name
substitution facilities to ease database migration tasks. For example, you can have the same set of
database objects exist in different databases (test, development, production environments, etc.) but
with different naming templates.
Updating the Database through DDL Scripts

Once you have established your data model and have generated and executed the DDL, you may also
want to update the database using the Data Modeler. SQL Developer Data Modeler enables safe and
effective through the generated DDL scripts. To update an existing database design, you need to
determine whether it is the database or the model which is to be the “point of truth”. By this we mean
that you might want to use the database to update your model. This is easily done by importing the
objects in the database to the same data model and allowing the tool to update the diagram. A
Compare Model dialog allows you do select the objects you want updated. If, on the other hand, you
want the model to update the database, you need to create the alter database DDL scripts. This is also
achieved through the import menu, by directing the import to “Swap Target Model”.
In the following image, the model, with details listed on the left, includes the CATEGORIES table,
which does not appear in the selected data dictionary schema import, displayed on the right. In
addition, the process reveals that there are additional tables, such as the EMP and DEPT tables, in the
schema selection, which are not on the model. In both cases, the user has the choice of including the
objects in the DDL. The review and save the DDL that updates the database, click “DDL Preview”.
Figure 2: SQL Developer Data Modeler: Compare Model Dialog
Formatting, Sub views and Displays

The easiest way of managing a large set of objects is to split the model into subject areas. SQL
Developer Data Modeler refers to these subject areas as subviews. Typically, a subview is a group of
related objects on a diagram and any changes you make to the object in the subview are reflected in
the main model. On importing multiple schemas, a single relational model is created for all the objects
and a separate subview is automatically created per database. Subviews make it easier to maintain
larger models.
You can also control colors, fonts and the dimensions of the objects on a model.
Diagram Linking
In addition to standard diagram drawing capabilities, SQL Developer Data Modeler supports diagram
nesting or linking. For example, a data flow diagram can be visually nested in a relational. Nested
diagrams can be visualized as icons or in a composite view. If a nested diagram has more than one
display, one of them can be selected to represent the diagram.
Naming Standards
SQL Developer Data Modeler supports naming standard glossaries, and also provides flexible and
unrestricted naming rules through naming templates. These template or name patterns mean that you
can define naming rules for table elements. You can create name patterns for indexes and constraints
(including Primary Key and Unique key) using a combination of predefined variables, e.g. {table},
{child}, {parent}, {column}, {seq_nr}, {model} and alphanumeric constants and combining these,
optionally with the SUBSTR function to restrict the total length of the word. Once created these
templates can be applied to single objects or to a complete model, thus allowing you to standardize or
change the naming convention for a full data model.
Design Rules
SQL Developer Data Modeler provides a set of predefined Design Rules that can be applied at any
stage of the design process. Design Rules help you to identify gaps or inconsistencies in the design,
from as simple a task as finding all tables without columns defined, to finding columns without
domains.
Using the Reporting Repository

SQL Developer Data Modeler supports a reporting repository, which means that you can export your
designs to a separate Oracle schema and then run SQL queries against the schema to gather details of
the designs. A set of predefined reports are available in Oracle SQL Developer, thus providing an
initial set of reports which you can run against the schema and also providing a starting point for
creating your own reports.
Summary
In this paper, we have illustrated many of the key features of Oracle SQL Developer Data Modeler
2.0. Oracle SQL Developer Data Modeler provides a toolset to help anyone involved in the data
modeling and data design process. It supports logical or conceptual modeling, relational database
modeling, detailed physical implementation details and DDL script generation. The product supports a
top-down modelling approach, allowing users to start with a blank logical model and then to design
database agnostic models which are then engineered to relational models, with additional physical
properties added before the generation of the DDL scripts. Additionally, users can import the
definition of the schema from a database data dictionary to create models, update these and then
update the data dictionary in turn.
For more detail and to watch online demonstrations, work through online tutorials and to download the
product, visit http://www.oracle.com/technetwork/developer-tools/datamodeler.
Contact address:
Sue Harper
Oracle Corporation
520 Oracle Parkway
Reading
Berkshire
RG6 1RA
Phone: +44(0)1189245169
Fax: +44(0)1189245169
Email [email protected]

SQL Developer Data Modeler: A Top-Down Product Overview: Sue Harper Oracle United Kingdom

Uploaded by

Copyright:

Available Formats

SQL Developer Data Modeler: A Top-Down Product Overview: Sue Harper Oracle United Kingdom

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

SQL Developer Data Modeler: A Top-Down Product Overview: Sue Harper Oracle United Kingdom

Uploaded by

Copyright:

Available Formats

SQL Developer Data Modeler:

A Top-Down Product Overview

Data Type and Structured Type Models

Creating New Models

Importing Existing Models

Importing from the Data Dictionary

Updating the Database through DDL Scripts

Formatting, Sub views and Displays

Using the Reporting Repository

You might also like