Chat now with support
Chat with Support

Toad Data Modeler 6.2 - User Guide

Introduction User Interface Models and Model Objects
Physical Data Model
About Physical Data Modeling Benefits of Physical Data Model Entity Relationship Diagram Objects Basic Database Design Advanced Database Design
Universal Data Model Logical Data Model Working with Model Objects Model Properties Model Statistics
Features and Tools
Application Variables Export/Import DDL Script Generation Graphics Model Actions Print Projects Reports Reverse Engineering Scripting and Customization SQL Templates Tips and Tricks Toad for Oracle Integration Toad Intelligence Central (TIC) Integration Tools Version Control
Options and Configuration Databases
Supported Databases Connection Types by Database DB2 UDB v. 8 (LUW) DB2 v. 9 (LUW) DB2 v. 9.5 (LUW) DB2 v. 9.7 (LUW) DB2 v. 10.1 (LUW) DB2 v. 10.5 (LUW) DB2 z/OS v. 9 DB2 z/OS v.10 DB2 z/OS v. 11 Greenplum 4.1 Greenplum 4.2 Ingres 9.3 Ingres 10.0 Microsoft Access 2000/2002/2003 Microsoft Access 2007/2010 Microsoft Azure SQL Database Microsoft Azure SQL Database V12 Microsoft SQL Server 2000 Microsoft SQL Server 2005 Microsoft SQL Server 2008 Microsoft SQL Server 2012 Microsoft SQL Server 2014 Microsoft SQL Server 2016 MySQL 5.0 MySQL 5.1 MySQL 5.5 MySQL 5.6 MySQL 5.7 Oracle 9i Oracle 10g Oracle 11g Release 1 Oracle 11g Release 2 Oracle 12c Release 1 Oracle 12c Release 2 PostgreSQL 8.1 PostgreSQL 8.2 PostgreSQL 8.3 PostgreSQL 8.4 PostgreSQL 9.0 PostgreSQL 9.1 PostgreSQL 9.2 PostgreSQL 9.3 PostgreSQL 9.4 PostgreSQL 9.5 SQLite 3.7 Sybase ASE 12.5 Sybase ASE 15 Sybase ASE 15.5 Sybase ASE 15.7 SAP ASE 16.0 Sybase IQ 15.2 Sybase SQL Anywhere 11 SAP SQL Anywhere 17 Teradata 13 Vertica Database 8.0
Legal Information

PER - LER Conversion Information

Features and Tools > Model Actions > Model Conversion > PER - LER Conversion Information

See the following basic information on what is converted during PER to LER and LER to PER conversion and how.

Item to Convert Notes: PER - LER Conversion LER - PER Conversion

Data Types

Similar to PER to PER conversion.

The conversion rules should be defined in the Data Types Conversion Settings dialog.

The conversion rules should be defined in the Data Types Conversion Settings dialog.

Self Relationship

In PER model, only non-identifying self relationship is supported.

Self relationship is converted properly.

Identifying self relationship will change to non-identifying self relationship.

Cardinality

In PER model, cardinality of one side of relationship is 1..n.

 

Cardinality is converted properly.

E.g. 2..5 cardinality in LER model is converted to 1..5 in PER model.

Parent Key

Parent key can be defined in LER model.

In LER model, open the Relationship Properties dialog | General tab | Foreign Unique Identifier box.

 

(In PER model, Parent Key is defined in the Relationship properties dialog | Foreign Keys tab.)

Parent key in PER model is different than PK (alternate key, unique attribute or index). -> Parent key defined in PER model is converted to LER model properly.

Selected UI of LER model is converted to PER model (PK and appropriate alternate keys are created).

Foreign Keys

In LER model, keys are not transferred from parent to child entity.

 

No FKs are displayed in child entity in LER model.

FKs that are not displayed in LER model are visible in child entity in PER model.

Primary Keys

 

PK (Primary key) in PER -> PUI (Primary unique identifier) in LER

PUI (Primary unique identifier) in LER -> PK (Primary key) in PER

Alternate Keys

You can select alternate key as a parent key.

AK (Alternate key) in PER -> UI (Unique identifier) in LER

UI (Unique identifier) in LER -> AK (Alternate key) in PER

NN versus M Attributes

NN - Not Null in PER model.

M - Mandatory in LER model.

The values can be displayed in ER diagram.

NN -> M

M -> NN

Inheritance

Inheritance is not supported in PER model.

--

Conversion of inheritance to PER model will be executed by the rules set in the Inheritance dialog | Generation tab.

Valid Values in Attribute

Valid values are supported only in LER model.

They can be defined for the following data types: Bigint, Float, Integer, VarChar.

--

 

(Check constraints from PER model are not converted to LER model.)

Default values in LER model -> Check constraints in PER model.

Defaults for Attributes and Domains

 

Converted properly.

Converted properly.

Rules for Attributes and Domains

In LER model, attributes and domains can have rules.

In PER model, attributes and domains have check constraints and these check constraints can have rules.

Attribute check constraint has rules in PER model. -> Rules for this attribute are converted to LER model.

Attribute has rules in LER model. -> Check constraint with this rule for the attribute is available in PER model.

LER to PER Conversion - Self-Relationship

If there is a self-relationship in LER model, the entity has two columns, both of the same name (primary key), in converted PER model. Other modifications are necessary.

Possible solution: You can define a name for the propagated attributes in LER model before the conversion. Open the Attribute Properties dialog | General tab | enter the name to the Propagated Name box.

If this box is empty, Toad Data Modeler will behave standardly (two columns of the same name in PER model).

Convert Model

Features and Tools > Model Actions > Model Conversion > Convert Model

Toad Data Modeler allows you to convert:

  • Physical models from one database platform to another (e.g. Oracle 10g model can be converted to SQL Server 2008 model)
  • Physical model to Logical model
  • Logical model to Physical model

The conversion can be executed via:

  • Model Actions | Convert Model or Model Menu | Convert | Run
  • Simple Model Conversion in Model Menu | Convert

How to Convert Model

Physical to Physical

  • Select your desired database platform.

  • Pay attention to the Conversion Settings dialog.

Note: Converting between different platforms means that some items, which contain SQL code, may not be converted correctly (e.g. After Scripts, Before Scripts, Procedures...). In this case, it is recommended to check the Comment Out Database Specific Items checkbox. This way, SQL code, that cannot be successfully converted, will be commented out. You can review these parts of code later and fix them on your own.

  • Choose what object types will be converted. You can access full list of Objects and Properties by clicking on Detailed Settings.

  • Check items you want to convert to another model. For easier item management use buttons located on the top.

  • A review dialog shows with some statistics about how many and what kinds of objects will be converted. Once you're done reviewing, click on Finish.

TIP:If you plan to do this action again in the future, you might want to click the Save Action button to save this Action Definition. See Model Actions for more information.

  • The conversion process occurs and your model is now converted to another database platform.

Physical to Logical

  • Select Logical model from the list of available database platforms.

  • Pay attention to the Conversion Settings dialog.

Note: Since Logical Model doesn't have any objects which contain SQL, all your procedures, scripts and functions will be lost.

  • Check items you want to convert to another model. For easier item management, use buttons located on the top.

  • Review the statistic and when you're done, click the Finish button.
  • After a short while your Physical model will be converted to Logical model.

TIP:If you plan to do this action again in the future, you might want to click the Save Action button to save this Action Definition. See Model Actions for more information.

Logical to Physical

Note:

Before you convert your Logical model to Physical model, you should be aware of the following:

  • Physical model supports only non-identifying self relationship.
  • Inheritance is not supported in PER model. Toad Data Modeler solves this by converting Inheritance object into either Single Table, N-1 Tables or N Tables. See Inheritance.
  • Keys in LER models do not migrate.
  • You can select a linking method in LER model.
  • M:N relationships are supported in both models.
  • Before you start the conversion, you can set up the conversion rules in the Data Type Conversion Settings dialog. This option is available only if Expert Mode is enabled.
  • Cycled relationships will be ignored during LER to PER conversion and will not be converted.  A message informing you about this will be displayed in  Message Explorer Log.

  • Select your desired database platform.

  • Change any of the conversion settings, if necessary.

  • Choose what object types will be converted. You can access full list of Objects and Properties by clicking on Detailed Settings.

  • Check items you want to convert to another model. For easier item management use buttons located on the top.

  • Review the statistic and when you're done, click the Finish button.

  • After a short while your Logical model will be converted to Physical model of your desired database platform.

TIP:If you plan to do this action again in the future, you might want to click the Save Action button to save this Action Definition. See Model Actions for more information.

Selection Tree Overview

Option Description
Import selection from a saved file.
Save selection to a file.
Checks all items.
Unchecks all items.
Opens the Wildcard Dialog where you can define settings for bulk selection/deselection of the Action box of the items listed on page Select Items.

Refresh Necessitated Items

Explanation: Some objects are related together (e.g. entity and domain, entity and relationship). Let's say you uncheck a Domain in Select Object Types dialog. However you keep an Attribute of the Domain type checked for conversion. In the next screen the Domain will be selected for conversion (and highlighted in gray), even if you don't want it to. This is because of its relationship with the Attribute, which cannot exist without the Domain.

Now, if you uncheck the Attribute, the Domain will still be checked for conversion. This is where you use this button. It runs through all checked objects and removes the Domain highlighted in gray since the Attribute is no longer checked. That means the Domain is no longer necessary, since it has no relationships with currently checked objects and you unchecked it in Select Object Types dialog.

Expand All.
Collapse All.
Display options for properties.

Right-click an item to see the following options:

Option Description
Expand All Children Expands all sub-items of the selected item.
Collapse All Children Collapses all sub-items of the selected item.

Simple Model Conversion

Features and Tools > Model Actions > Model Conversion > Simple Model Conversion

This feature allows you to convert your logical models to physical models and your physical models to models of another database system very quickly.

Scenario

You want to convert your Oracle 10g model to Microsoft SQL Server 2005.

  1. Open your Oracle 10g model.
  2. Select  File | Synchronization | Simple Model Conversion.
  3. Define the settings in the Conversion dialog.
Option Description

To Database

Select a target database system - Microsoft SQL Server 2005.

New Model Name

Define a name for the converted model.

More>>

Click this button to see and modify Object Types and Properties for the conversion. No modification is necessary. Object Types and Properties - OTPs

Close after Conversion

Select it to close the Conversion dialog after the process is finished.

Convert

Executes the process of conversion.

Close

Closes the Conversion dialog.

  1.  Click Convert.

Compare Models, Generate Change Report

Features and Tools > Model Actions > Compare Models, Generate Change Report

Toad Data Modeler allows you to view differences between two models and for physical models you can generate change reports in HTML, RTF and PDF formats. Change reports are not available in logical models.

You can compare your models and generate the change report via the Model Actions.

How to Compare Models and Generate Change Reports

  • Click on Compare and Generate Report | Run in Model Menu or Run Compare and Generate Report Action in Model Actions.

  • Select another model for comparison. It can be either a model opened in Toad Data Modeler or a model loaded from Model File.

nOTE: The selected model has to be of the same database platform and version. You cannot compare DB2 10.5 and MySQL 5.1 models, or MySQL 5.1 and MySQL 5.5 models.

  • The next dialog presents you with a couple of setting related to Model Comparison. When you're done editing the options, click on Next.

  • Now you have to choose what types of objects will be included in the comparison. If you wish to select objects in more detail, click on Detailed Settings.

  • You are now presented with Compare Tree dialog displaying all differences between the two given models. Now you have the option to generate a report by clicking on Report button.

  • Check the Review screen which shows you the final statistics and close Model Compare by clicking the Finish button.

TIP:If you plan to do this action again in the future, you might want to click the Save Action button to save this Action Definition. See Model Actions for more information.

  • Select Format of the generated report.
  • There are two options available during the entire process of generating a report. Hide Log hides the bottom part of the dialogs which otherwise displays information related to the generation. Checking Close after Execution closes the dialog window after you're done creating your report.

Generating HTML Reports

  • After you select HTML from previous dialog and click on Next, you have the option to change Report Caption and Language of the report.

nOTE: You can generate Reports in many languages provided that you have the appropriate Dictionaries installed. For more information see Dictionaries.

  • You are able to define your own path where the generated report will be saved, if you need to.

  • In this dialog there are several options for customizing the look of the report.
Select Layout Page Description

Report Layout

  • Frameless (for Internet Explorer 7 and FireFox 2 users. Reports with Frameless layout is not displayed in older browsers correctly.)
  • Frames - Top menu
  • Frames - Left menu

Note: For large models, Frames - top menu or Frames - left menu options are recommended. (Frameless report layout is not recommended as it uses Java script that goes through all objects, which takes too much time if your model is large.)

CSS Style

Available styles for HTML report:

  • Default
  • Others (select the most convenient for you)

TIP:You can also create your own report styles using CSS. To integrate a custom style into Toad Data Modeler, you have to create a .txs file referencing the style resources and save it to Style folder.

  • Now is the time for you to decide what kind of information should the generated report contain. If you want to get a complete report, check all boxes. For easier management use buttons located on the top.

  • The final dialog where you can set a few last options.
  • Click on Execute to generate the report. A dialog window will inform you when the process is done.

Generating RTF and PDF Reports

  • On this dialog you are able to change the report caption and its language.

nOTE: You can generate Reports in many languages provided that you have the appropriate Dictionaries installed. For more information see Dictionaries.

  • You are able to define your own path where the generated report will be saved, if you need to.

  • A few options regarding the report look are available here. Also note the option to insert text or image watermark.

  • Now is the time for you to decide what kind of information should the generated report contain. If you want to get a complete report, check all boxes. For easier management use buttons located on the top.

  • A few final options are available here. For example, you can decide between generating Summary only, Details only, or both.
  • Click on Execute to generate the report. A dialog window will inform you when the process is done.

What to Report Dialog Buttons

Option Description

Selection Menu

Allows you to save time by loading saved selection.

For example, you may decide that you only want your report to cover Entities, some properties of Relationships and After Scripts. Selecting these items every time you generate a report would be frustrating. That's why you have the option to check these items once and then save the selection and load it every time you generate another report.

Save Selection

Saves the current selection. If a selection is chosen in the Selection Menu, it will be rewritten.

Load Selection

Load the selection chosen in the Selection Menu.

Save as New

Saves the current selection as a new selection. You will be prompted to enter a name.

Delete Deletes the selection chosen in the Selection Menu.
Set as Default Sets the selection chosen in the Selection Menu as default. Such selection is marked by an asterisk in Selection Menu.
Select All Selects all items.
Deselect All Deselects all items.
Inverse Selection Inverts the selection.
Auto Check

On: Checking/Unchecking an item will also check all its sub-items.

Off: Checking/Unchecking an item will not check its sub-items.

Compare Tree Overview

Option Description
Generate a Change Report.
Expand All.
Collapse All.
Go to next object in the tree.
Go to previous object in the tree.
Display options for objects.
Display options for properties.

Source Model

The source model to which the second model is compared to.
Model to Compare The model which is compared to the source model.

Compare Tree Icons

Difference between Source model and Compared model.

Right-click an item to see the following options:

Option Description
Expand All Children Expands all sub-items of the selected item.
Collapse All Children Collapses all sub-items of the selected item.
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating