Introducing SQL Developer Data Modeler: Part 2

Oracle SQL Developer 2.1

December 2009


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

Working with diagrams and their components

You can waste away many hours laying out the elements on a diagram. Therefore, this aspect of modeling can be time consuming. However, a model serves as documentation and a communication device. Therefore, taking the time to make sure it is well annotated and clearly designed is important. Most of the controls for the models are on the context menu, allowing you to modify individual aspects of the diagram. The context menu changes depending on whether you have an object or line selected, or you're just clicking in the open space. You can also set general defaults using Tools | General Options | Diagram. In this section, we'll look at the various options available when working with the diagrams.

Formatting the elements

Before moving a relationship line, entity, or table, you can dramatically change the impact and readability of a large diagram just by changing the colors. This is readily demonstrated when importing from two or more schemas. Using the previous example where we imported from two schemas, open one of the subviews and select all of the tables. With the objects selected, invoke the Format Object dialog using the context menu:

Oracle SQL Developer 2.1

If this is the first time you are adjusting the colors, the dialog does not display any colors as you open it. The colors used in the diagram are the default settings. Deselect Use Default Color and click on the Background and Border Color items to select and set the new color. When you are done, click on OK and note the changes applied to the subview. Switch to the main relational model to review the impact there. The color applied to the subview is also applied to the main model as shown. This is very useful when illustrating how tables in different schemas relate to each other. For example, take the HR and OE sample schema, all of the tables related to human resources are maintained in the HR schema, while those related to the order entry system are maintained in the OE schema. You may well have applications designed around the HR schema and others tied to the OE schema, but some may involve both. In the following relational model, the OE tables are now colored green, so we're able to identify them, but we can also see where the schemas link. We can see that a CUSTOMER may deal with one EMPLOYEE and has many ORDERS:

Oracle SQL Developer 2.1

Selecting all of the tables in a model
Select any table and click on Select Neighbors from the context menu. Select All Zones, to select all of the tables. Use this instead of Ctrl+A, which selects all tables and all lines.

Changing the default format settings

Instead of changing individual items or even a set of items, you can change the default color for each of the element types displayed on a diagram. The Tools | General Options | Diagram | Format provides the ability to control the color of each of the elements displayed such as tables, views, and entities:

Oracle SQL Developer 2.1

To edit any of the elements in the dialog, double-click on the object, or select the object and the edit icon. This allows you to adjust the color of the item and to format the font. You can use the font color to highlight mandatory, Unique, or Foreign Keys.

Setting general diagram properties

Use the same Tools | General Options | Diagram to set model properties,which include:

  • Displaying the grid
  • Controlling the background color of the diagram
  • Controlling the Auto Route feature which is on by default

Set display properties for certain items on each of the models, including the control of:

  • The diagram notation for the logical model, which supports the Barker and Bachman notations
  • The display of the relationship names for either the logical or relational models
  • The flow names for process models

For example, to display the relationship names on an Entity Relationship Diagram (as seen below), check the display property on the Tools | General Options | Model | Logical, and ensure that the Relation Cardinality properties for the relationships are also set.

Oracle SQL Developer 2.1

Creating subviews and displays

Adding subviews and displays offers you alternative ways of laying out elements on a diagram and for working with subsets of items. You can create multiple subviews and displays for either logical or relational models, and remove them as easily, without impacting the main models.

Adding subviews to your design

You have already encountered a subview by importing from a number of schemas in the data dictionary. Subviews are not only a reflection of the different schemas in a design, but they can also represent any subset of elements in the design, allowing you to work with a smaller, more manageable set of elements.

You can create a subview from the object browser by selecting:

  • The SubViews node and using the New SubView context menu. In this case, you have a new empty diagram that you can populate by dragging tables or entities (depending on the subview in question) onto the page.
  • Any of the model tabs and then selecting the Create SubView menu. This creates a new and empty subview.
  • An element or elements on an existing model and using the Create SubView from selected context menu on the diagram. In this case, the new subview will contain the tables or entities you selected:

Oracle SQL Developer 2.1

The layout of the subview is not linked to the main model in any way. What is linked is how you format the items on the subview and any structural changes you make to the objects. You can continue to add new items to the subview by dragging them onto the surface from the object browser. When deleting items from the subview, you should choose whether the item is deleted:

  • From the view (Delete View)
  • From the complete design (Delete Object)

Adding displays

A display is an alternative view of a diagram, whether a main model or a subview, and is linked directly to that model. If you delete the model, the display is also deleted. Any items that you add or remove from displays are also automatically added or removed from the main model they are linked to.

To create a new display, select the tab of any model and select Create Display from the context menu. The new display created is, initially, a replica of the model you selected in both layout and items available. All additional displays are listed at the bottom of the model. In the following example, the HR subview has two displays created, as highlighted at the bottom of the screenshot, the main HR display and the new Display_1. The Create Display context menu is also illustrated:

Oracle SQL Developer 2.1

Use the new display to change the layout of the model and to adjust the level of detail displayed on the diagram.

A second display of the same model is useful when you want to show more or less detail on a model. You can, for example, create a display which only displays the entity or table names. Right-click in the space on a diagram and select View Details | Names Only. We'll discuss how to layout the diagram elements later.

Creating a composite view

If you create a number of subviews, create a new diagram showing the composite models of each of these on a single layout. This serves as a useful reminder of the number of subviews or models you have by having a thumbnail of the various layouts. Alternatively, you can add a composite view of one subview and place it on another.

To create a composite view, select the model in the browser and drag it onto the diagram surface. You can drag any model onto any other diagram surface, except its own:

Oracle SQL Developer 2.1

Once you have the composite folder displayed on the diagram, display the composite model within that folder by selecting Composite View from the context menu. If the model you want to view has a selection of displays, then you can also select the display you want to see within that composite.

The following screenshot shows the, subview, displaying the composite models of the HR subview, the main logical model, and both displays of the logical model:

Oracle SQL Developer 2.1

Controlling the layout

When working with a large number of items in a model, it's important to keep the layout organized. A variety of tools to help with the process are explained in the following sections.

Adjusting the level of detail displayed

Change the amount of detail displayed in a table (or entity) using the View Details menu. It is invoked with a right-click on the white space of any diagram. The View Details menu has options for displaying:

  • All Details
  • Names Only
  • Columns
  • Datatype
  • Keys

Adjusting the width and height across the model

If you have a large diagram and want to see how tables or entities relate to each other, you can create a more compact model using a display, without impacting the main model. This can be done by setting the details to display the name only and then resizing and repositioning the objects.

In the following screenshot, we have set the model to display only the name of the tables. Create a more compact diagram by resizing one of the tables to a more fitting set of dimensions, select the rest, and then resize them all to the same width and height:

Oracle SQL Developer 2.1

Controlling alignment

Once you have positioned the items, align them to complete the model. Use the Edit menu with the required items for top and left alignment as shown in the following screenshot:

Oracle SQL Developer 2.1

Resizing and alignment
The first item you select is the one that drives the position for left or top alignment, and the item that controls the width and height of all subsequent items selected.

Working with lines

A clear layout is fundamental to a clear diagram. This includes ensuring that lines do not cross or lie underneath tables. SQL Developer Data Modeler has an Auto Route feature that is on by default (seen in the following screenshot). This prevents you from manually moving any of the relationship lines yourself.

Take manual control of the lines by using a right-click on the white space of the diagram and deselecting Auto Route. You can also set the Auto Route option to off, by default, using the Tools | General Options | Diagram setting.

Oracle SQL Developer 2.1

Once you have control of the lines, you can select any relationship line and use the context menu to:

  • Add Elbow—to reroute a line around other shapes
  • Remove Elbow
  • Straighten Lines 

Managing lines with elbows

For lines that have elbows (angles), you can select any point on the line to move them and change the shape. Moving the line also moves the end points of the line. You cannot select and move the end points themselves. You can also select any of the elbows to change the angles within the lines:

Oracle SQL Developer 2.1

Managing straight lines

Diagrams are neater if there are fewer lines crossing and more straight lines. Where possible, remove the elbows and straighten lines. There is an option to straighten lines by removing all elbows on the lines. Straighten Lines will create a horizontal straight line between two tables or entities next to each other, and a diagonal line between elements that are not adjacent. You can only move the endpoints on a straight line and not the entire line.

Moving a straight line
To select and move a straight line, add an elbow. Then, without bending the line, you can select and position it where you choose to without needing to move both endpoints.

Integration with Oracle SQL Developer

SQL Developer 2.1 includes the Data Modeler Viewer extension. This viewer provides users with the option of opening existing SQL Developer Data Modeler diagrams or creating models based on the database connection. To open the Data Modeler in SQL Developer, select Tools | Data Modeler. This opens the Logical and Relational model tabs. Once open, you can open any data model created in the Data Modeler. To review the details of the model in the navigator, select View | Data Modeler | Browser.

You can open any Data Modeler model in SQL Developer 2.1 by selecting File | Open Data Modeler Design. Once you have the model and the Data Modeler browser open, you can browse and review all of the model properties. The model and properties are all read-only:

Oracle SQL Developer 2.1

Creating a new model

The Data Modeler Viewer extension to SQL Developer also allows you to create models of the tables, views, and constraints in the database connections you have access to. To create a new model, expand the database connection in the Connections navigator, and then select the tables you want to include on a diagram. Drag the selection on the relational model (as seen in the following screenshot). You can't save the model, but you can move the objects on the diagram to change the default layout.

Oracle SQL Developer 2.1

Creating and running reports

An important aspect of building a data model is to run reports against the designs, to ascertain if there are:

  • Tables without columns
  • Entities without attributes
  • Columns or attributes without data types set

SQL Developer and the SQL Developer Data Modeler work together to support this functionality. You can use the Data Modeler to create and save the design to the reporting repository, and then use SQL Developer to run the shipped reports against the saved design.

Setting up the reporting schema

Before you can export any designs to the reporting schema, you need to create a user which  will own the tables in the reporting repository. The repository is made up of 42 tables, with columns and associated indexes. You can use either the SQL Worksheet, or the Create User context menu on Other Users, to create a repository owner. Grant the user CONNECT and RESOURCE privileges.

Starting in SQL Developer Data Modeler, create and refine your data model designs. Once they are ready, select Files | Export | To Reporting Schema. This action invokes the Export to Reporting Schema dialog:

Oracle SQL Developer 2.1

The first time you export your design, you'll need to create a connection to the new repository schema. Click on the Add button to invoke the New Database Connection dialog. The repository is built in an Oracle database, so the connection details here are for an Oracle connection. Once you have created the connection you can reuse it on subsequent exports. Select the new connection, and click on OK. The first time you perform the export, the tables for the repository are created in the repository schema. Subsequent exports write new records to the tables.

Having exported the design, open SQL Developer and select the View | Reports tab. Expand the Data Modeler Reports node. There are two categories of reports available, as shown in the following screenshot. These are Design Content and Design Rules:

Oracle SQL Developer 2.1

By default, the latest report for each design is displayed in the report. In order to see all exports of the design, enter FALSE in the Enter Bind Values dialog when running the report, and then click on Apply:

Oracle SQL Developer 2.1

Use the reports to verify the completeness of your design, or create additional reports to extract further detail about your models. In the following screenshot, we have run the Design Rules | Relational Model | Columns report to determine which columns have no data type set. The report also provides the tables these columns belong to:

Oracle SQL Developer 2.1


In this article, you have had an insight into the features offered by SQL Developer Data Modeler.

The subject of data modeling is vast, and this article only introduced some of the many and varied features available in SQL Developer Data Modeler. This article introduced some of the utilities available in SQL Developer Data Modeler. You also learned about the integration points with SQL Developer and the Data Modeler Viewer in SQL Developer.

Books to Consider

Microsoft SQL Server 2008 R2 Administration Cookbook
$ 32.99
What's New in SQL Server 2012
$ 20.99
Microsoft SQL Server 2012 Integration Services: An Expert Cookbook
$ 26.99
comments powered by Disqus

An Introduction to 3D Printing

Explore the future of manufacturing and design  - read our guide to 3d printing for free