Chat now with support
Chat with Support

Toad Data Modeler 7.2 - 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 Microsoft SQL Server 2019 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 Oracle 19c PostgreSQL 9.0 PostgreSQL 9.1 PostgreSQL 9.2 PostgreSQL 9.3 PostgreSQL 9.4 PostgreSQL 9.5 PostgreSQL 10 PostgreSQL 11 PostgreSQL 12 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

Entities

Toad Data Modeler allows you to design Entity Relationship Diagrams of specific database platforms, convert physical model from one database platform to another, create an ER Diagram directly from your database (Reverse Engineering feature), update physical models, generate DDL/SQL scripts and Change Scripts, create Dictionary Types, Views, Triggers, Functions, generate detailed documentation to your model (in HTML, RTF, PDF, XSLT formats) and much more.

This chapter describes features and functions related to Physical Data Modeling. Look around each section to get the information you need.

Note: See the sample physical model Videorental (Oracle 10g db) that is included in the installation package of Toad Data Modeler. Default location is: C:\Program Files (x86)\Quest Software\Toad Data Modeler7.2\Samples.

Benefits of Physical Data Model

  • Detailed definition of database structure, including database specific items, for example:
    • Stored procedures
    • Functions
    • Triggers
    • Views
    • Materialized views
    • Sequences (auto increments) etc.
  • Possibility to synchronize local model with existing database.
  • Possibility to specify logical names for objects (captions for tables, attributes and other objects).
  • Detailed database specific information can be exported to HTML/RTF/PDF or XML/XHTML/CSV reports.
  • Automatic generation of SQL code for selected objects (SQL code generation is not available in Logical and Universal Model)
  • Automatic migration of PK attributes to child entities (Attributes don't migrate to child entities in Logical Model)

Create Entities

In Toad Data Modeler, there are several ways how to create entities - on the Workspace, via Model Explorer and in the Entities dialog (Model | Model Items | Entities). Create entities directly on the Workspace.

Scenario

Create entity Customer on the Workspace in your Videorental model.

  1. Click on the toolbar (also CTRL+E) and then click anywhere on the Workspace.
  2. Double-click the entity to edit it.
  3. Define the entity caption and name



Caption

Logical entity name - Customer.

Name Physical entity name - T_CUSTOMER.
  1. Define other properties on tab General and other tabs (e.g. Notes, Comments etc.). To save the changes simultaneously and leave the form open, click Apply.

Edit Entities

To edit entities:

  • Double-click the entity on the Workspace.

or

  • Edit the entity in Model Explorer | Entities folder | double-click the selected entity (or right-click | Edit).

Note: You can also edit entities in the Entities dialog (Model Menu | Model Items |Entities | Edit).

Example: The Entity Properties form (Oracle 10g db). Object navigator collapsed.

Example of tab Attributes, Object Navigator expanded.

Option Description
Object Navigator Dropdown Menu

All entities in your model are listed here. It allows you to edit entities quickly and comfortably from one place.

Tip: After you finish editing an entity, click Apply to confirm changes and select another entity from the Object Navigator box.

Buttons Previous and Next for quick navigation among entities.

General Tab

General properties of entity

Caption

Logical name of entity

Name

Physical name of entity

About Naming Conventions

Tip: You can choose if you want to display objects physical names, logical names, or full names by selecting an option from dropdown menu on Display Toolbar.

Schema

Schema selection box. Click the small button on the right to access the Schema dialog.

(User/Schema stands only for Oracle models.)

Category

Category selection box. To see a list of Categories of your model, click the small button on the right.

Generate

Select it to generate the entity in final SQL (DDL) script. (It is selected by default.)

Note: Objects of your model that have the Generate checkbox disabled in their Properties dialogs are displayed in Model Explorer this way:

Note: Other options on the General tab vary depending on a target database. See the specific options for your database  in the "Databases" chapter.

Attributes, Keys, Indexes, Check Constraints, Triggers, Permissions Tabs

On these tabs, you can add, edit and delete particular items. More details on each of them can be found in appropriate topics that follow.

To Do Tab

On this tab, you can write some tasks on the selected entity.   

To see all To Do tasks, select Model | To Do.

Before Script Tab

Whatever you write into the Before Script section, it will be generated before the Entity definition.

After Script Tab

Whatever you write into the After Script section, it will be generated after the Entity definition.

Notes Tab

Write notes on the entity on this tab. The text will display in a pop-up box when you point your mouse cursor at the entity name in ER diagram.

SQL Preview Tab

Click at the bottom of this tab to see the part of SQL code for the entity.

Select the WordWrap checkbox to wrap the code on this tab.

Relationships Tab

Information on parent and child entities, relationships and key attributes.

Double-click the selected object to open the Properties dialog.

Physical Properties Tab

Storage characteristics of table can be defined on this tab.

Table Properties Tab

Other table characteristics can be defined here.

Comment Tab

Write comments on the entity on this tab.

Note: CTRL+A, CTRL+C, CTRL+X and CTRL+V functions are available on this tab.

Buttons:

- opens the Application Variables form

OK -  confirms all changes and closes the form

Cancel - cancels the changes you have made and closes the form

Apply - confirms the changes (the form will remain opened for further edit)

Help - opens Help file

Copy Entities

In Toad Data Modeler there are several methods to copy entities. You can select from the following options.

TIP: If you find yourself copying objects too much, consider using the Gallery feature. See Gallery for more information.

A. Drag&Drop techniques + CTRL on Workspace

Where to use: On a Workspace

Example:

  1. Click an entity on Workspace and hold the mouse button down.
  2. Press CTRL key.
  3. Drag the entity to another place.
  4. Release the mouse button and the key.

B. Copy&Paste functions: Edit | Copy/Paste (CTRL+C, CTRL+V)

Where to use:

  • On a Workspace
  • Between Workspaces
  • Between Models

Example: Copying an entity to another Workspace

  1. Select an entity.
  2. Press CTRL+C. Activate the Workspace in which the entity copy will be created. It can be in your current model or in another model.
  1. Press  CTRL+V
  2. A copy of the entity is created on the Workspace you selected.

C. In the Entities dialog: Drag&Drop techniques + CTRL

Where to use:

  • In the Entities dialog itself (Model Menu | Model Items | Entities)
  • Between the Entities dialog and a Workspace
  • Between the Entities dialog and Model Explorer | Entities folder
  • Between the Entities dialogs of two different models

Example: An entity in the Entities dialog of Model A needs to be copied to Model Explorer of Model B:

  1. Open the Entities dialog in Model A in Model Menu | Entities.
  2. Open Model Explorer in Model B.
  3. Select an entity in the Entities dialog of Model A, hold down CTRL key.
  4. Drag the entity over the Entities folder in Model Explorer and drop.

D. In Model Explorer:  Drag&Drop techniques + CTRL

Where to use:

  • In Model Explorer itself
  • Between Model Explorer and a Workspace
  • Between Model Explorer and the Entities dialog (in a different model as well)
  • Between Model Explorer and Entities dialog of two different models

 

Example 1: Making a copy of an entity in Model Explorer.

  1. Unfold the Entities folder in the Model Explorer tree.
  2. Click an entity and hold the mouse button down.
  3. Press CTRL.
  4. Drag the entity to the Entities folder and drop.

A copy of the selected entity is listed in the Model Explorer tree and the entity appears on all the Workspaces of your model where the Auto Complete option is enabled.

 

Example 2: Copying an entity from Model Explorer to a Workspace.

  1. Unfold the Entities folder in the Model Explorer tree.
  2. Click an entity and hold the mouse button down.
  3. Press CTRL.
  4. Drag the entity to a Workspace.
 

Example 3: Creating an entity copy between Model Explorers of two models. The entity will be copied from Model A to Model B:

  1. Open Model Explorers in both models. You may want to undock at least one of the Model Explorer windows.
  2. Click an entity in Model Explorer A and hold the mouse button down.
  3. Press CTRL.
  4. Drag the entity to the Entities folder of the Model Explorer B an drop.

Note:

  • A copied entity has the same properties as its source.
  • In some cases, entities cannot be copied between models. It's because they contain specific properties, that do not exist in the target mode. For example, copying two entities from Microsoft SQL 2005 model to MySQL 5.1 model. Entity A has Partition Schema, Entity B has Fulltext Catalog. Since these features do not exist in MySQL 5.1, the entities will not be copied.
  • Even though it is possible to copy and paste objects to a different model of different database platform or version, it is encouraged to use Model Convert function, even for single objects. The copy-pasting method is faster, but more error-prone, while the Model Convert method is slower, but more robust.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating