Chat now with support
Chat with Support

Toad Data Modeler 7.0 - User Guide

Introduction User Interface Models and Model Objects
Physical Data Model
Entity Relationship Diagram Objects Basic Database Design Advanced Database Design
Universal Data Model Logical Data Model Working with Model Objects
Features and Tools
Application Variables Export/Import DDL Script Generation Graphics Model Actions Print Create New Project Reports Reverse Engineering Scripting and Customization About Templates Tips and Tricks Toad for Oracle Integration Toad Intelligence Central (TIC) Integration Tools Version Control
Options and Configuration Databases
Amazon Redshift 1.0 IBM DB2 LUW 9.5 IBM DB2 LUW 9.7 IBM DB2 LUW 10.1 IBM DB2 LUW 10.5 IBM DB2 LUW 11.1 IBM DB2 z/OS 10 IBM DB2 z/OS 11 Greenplum 4.1 Greenplum 4.2 Ingres 9.3 Ingres 10.0 EDB Postgres Advanced Server 10 Microsoft Access 2007/2010 Microsoft Azure SQL Database V12 Microsoft SQL Server 2005 Microsoft SQL Server 2008 Microsoft SQL Server 2012 Microsoft SQL Server 2014 Microsoft SQL Server 2016 Microsoft SQL Server 2017 MySQL 5.0 MySQL 5.1 MySQL 5.5 MySQL 5.6 MySQL 5.7 MySQL 8.0 Oracle 10g Oracle 11g Release 1 Oracle 11g Release 2 Oracle 12c Release 1 Oracle 12c Release 2 Oracle 18c Specifics - Oracle 12c Release 2 PostgreSQL 9.0 PostgreSQL 9.1 PostgreSQL 9.2 PostgreSQL 9.3 PostgreSQL 9.4 PostgreSQL 9.5 PostgreSQL 10 SQLite 3.7 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
Copyright Legal Notices

PER - PER Conversion Information

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

  • Data Types are converted.
  • Permissions are compared by names.
  • SQL code is copied and commented not to generate any code.

See other details here:

Item to Convert

Model A

(Source Model)

Model B

(Destination Model)

Result

Data Types

 

Supported data type

Supported data type

Same data type

Example*:

Integer

Integer

Integer

 

Supported data type

Equivalent data type

Equivalent data type

Example**:  

Float

Real

Real

Permissions

 

Supported Permission

Supported Permission

Same Permission

Example:

SELECT

SELECT

SELECT

 

Supported Permission

Unsupported Permission

--None--

Example:

DROP (MySQL 5)

--None-- (Microsoft SQL 2005)

--None-- (Microsoft SQL 2005)

 

Unsupported Permission

Supported Permission

Model B Permission

Example:

--None-- (Microsoft SQL 2005)

DROP (MySQL 5)

DROP (MySQL 5)

(DROP preserved in already existing model.)

Deny Permission

 

Deny Permission Supported

Deny Permission Supported

Deny Permission Converted

 

Deny Permission Supported

Deny Permission Unsupported

--None--

 

 

Deny Permission Unsupported

Deny Permission Supported

Model B Deny Permission

Example:

MySQL 5

Microsoft SQL 2005

Microsoft SQL 2005 (Deny permission preserved in already existing model.)

Grantor

 

Grantor Supported

Grantor Supported

Grantor Converted

 

Grantor Supported

 

Grantor Unsupported

 

--None--

 

 

Grantor Unsupported

 

Grantor Supported

 

Grantor is empty.

Example:

MySQL 5

 

Microsoft SQL 2005

 

Microsoft SQL 2005 (Grantor preserved in already existing model.)

Users and User Groups

 

Supported

Supported

Converted successfully

 

Supported

Unsupported

--None--

 

Unsupported

Supported

--None--

Example:

Users unsupported in PostgreSQL

Users supported in Microsoft SQL 2005

--None--

Model A - Currently opened model that you want to convert.

Model B - Model to which you want to convert Model A.

Complete Model A is converted to Model B.

Other Information

  • Conversion between PostgreSQL and Microsoft SQL: Serial and BigSerial data types in PostgreSQL are converted to Identity in Microsoft SQL. Identity in Microsoft SQL is converted to combination of sequence and default "nextval" in PostgreSQL. 
  • Conversion between PostgreSQL and MySQL: Serial data type in PostgreSQL is converted to Autoincrement in MySQL. Autoincrement in MySQL is converted to combination of sequence and default "nextval" in PostgreSQL.
  • Conversion between PostgreSQL and Oracle: Serial and BigSerial data types in PostgreSQL are converted to combination of sequence and a sequence selected for attribute in Oracle. A sequence selected for attribute in Oracle is converted to default "nextval" in PostgreSQL (the sequence is converted automatically).
  • Conversion from Oracle to Microsoft SQL (and Microsoft SQL to Oracle): Automatic conversion of Identity in Microsoft SQL to Sequence in Oracle (and back).
  • Conversion from Oracle to MySQL (and back): Sequence in Oracle are converted to Autoincrement in MySQL (and back).
  • Conversion from Microsoft SQL to MySQL (and back): During conversion of Identity (Microsoft SQL) to Autoincrement (MySQL) and back the new IdentitySeed/Initial Autoincrement is taken into consideration. (In version 3.4, only conversion between checkbox Identity and Autoincrement was possible.)
  • Model Conversion from MySQL to Microsoft SQL and Oracle: MySQL Enum data type is converted to Char data type, a check constraint for the attribute is created, the parameter is preserved (see the SQL tab of the Check Constraint Properties dialog).

* Data types conversion examples - conversion from MySQL 5 model to SQL Server 2005 model.

** See the equivalent data types for conversion in the Settings menu | Data Type Conversion Settings.

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

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

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.
Related Documents