Chat now with support
Chat with Support

Toad Data Modeler 7.3 - 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

Data Types and Dictionary

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.3\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)

User Data Types

In Toad Data Modeler, you can define your own data types (in case the database platform you use supports this feature).

To add a user data type

Select Model Menu | Model Items | User Data Types and click Add in the User Data Types dialog.

Example: User Data Types listed in User Data Types dialog

To edit a user data type

Double-click the selected data type or click Edit in the User Data Types dialog.

Example: The User Data Type Properties dialog (Oracle 10g)

General Tab

Description

Caption

Logical user data type name

Name

Physical user data type name

Schema

Schema selection box

Type

This option is available in Oracle models only. Contains specific types of user defined types.

Generate

Select this option to generate the user data type in final SQL script.

Generate SQL Only

This option is available only for Oracle user data types.

If this checkbox is selected, only the SQL code that you manually entered in appropriate tab (Object Type or Varray Type or Nested Table Type) will be generated in final DDL script.

Note: All changes in your selected user data type will be automatically applied to all attributes of this data type.

To Do Tab

On this tab, you can write some tasks related to the user data type.   

TIP: To see all To Do tasks in your entire model, select Model Menu | To Do.

Used In Tab

All objects where the user data type is used are listed here.

Permissions Tab

On this tab, you can assign a User or User Group permissions for selected user data type.

Notes Tab

Write notes related to the user data type.

Object Type Tab

See example of specification and definition in the following screenshot.

TIP: Click OK+Add to create another user data type.

Example: User Data Type Properties dialog | Object Type tab (Oracle 10g)

To assign a User Data Type to an attribute

  1. Open the Attribute Properties dialog of the selected attribute | General tab.
  2. From the Data Type box, select the required user data type.

TIP: You can reach the User Data Types dialog from the Attribute Properties dialog - see the small icons next to the Data Type box.

Note:

  1. To copy user data types, use CTRL + Drag&Drop techniques.
  2. To move user data types, use Drag&Drop techniques.
    You can copy and move your user data types:
  • In User Data Types dialog (Model Menu | Model Items)
  • Between Model Explorer and User Data Types dialog
  1. To delete user data types:
  • Go to Model Menu | User Data Types, select a user data type and click Delete.
  • In Model Explorer | User Data Types folderRight-click and select Delete Item.

Note: When you delete an user data type, the changes will reflect in all attributes that had this type assigned. There, the user data type will be replaced with data type that it was originally created from. To find out which attributes use a specific user data type, go to User Data Type Properties dialog | Used in tab.

Dictionary Types

In Toad Data Modeler, you can define dictionary types. Dictionary is an alias of data type (must be supported by database).

Note: Oracle databases support user data types only, Microsoft SQL Server supports both user data types and dictionary types.

To add a dictionary type

Select Model Menu | Model Items | Dictionary Types and click Add in the dialog.

To edit a dictionary type

Select Model | Model Items | Dictionary  Types and double-click the selected dictionary type or click Edit.

General Tab

Description

Caption

Logical dictionary type name

Name

Physical dictionary type name

Data Type

Data Type selection box

Note:

  • Other options on the General tab vary according to the current database platform. Options specific for your database can be found in the "Databases" chapter.
  • All changes in your selected dictionary type will be automatically applied to all attributes of the dictionary type.

    • To Do Tab

    You can enter To Do tasks related to the object here.

    Note: To display all To Do tasks, select To Do from Model Menu.

    Used In Tab

    All objects using this dictionary type are listed here.

    Notes Tab

    Tab for notes related to the dictionary type.

    To select a dictionary type for attributes

    1. Open the Attribute Properties dialog of a selected attribute.
    2. In General tab, select the dictionary type from the Data Type dropdown menu.
     

    Note:

    1. You can reach the Dictionary Types dialog also from the Attribute Properties dialog - see the small icons next to the Data Type box.
    2. Click OK+Add in the Dictionary Type Properties dialog to apply changes and create another dictionary type at once.
    1. To copy dictionary types, use CTRL + Drag&Drop techniques.
    2. To move dictionary types, use Drag&Drop techniques.

      You can copy and move your dictionary types within a model and between models of the same and different databases:
    • In the  Dictionary Types dialog (ModelMenu)
    • Between Model Explorer and the Dictionary Types dialog
    1. To delete dictionary types, select:
    • Model | Dictionary Types | Select a dictionary type and click Delete.
    • Model Explorer | Dictionary Types folder | Right-click and select Delete Item.

    Note: When you delete a dictionary type, it will be also removed from all attributes in which it was used and replaced with the data type that it was based on (e.g. deleting dictionary type based on char data type will cause all attributes to revert back to the char data type). To find out which attributes will be changed when you delete a dictionary type, open the Dictionary Type Properties dialog | Used In tab.

    Domains

    Domain is another dictionary item in Toad Data Modeler. Domains only have logical meaning and are not generated. If a domain is used in a attribute, only the values of the domain are transferred to the attribute and are generated.

    To add a domain

    Select Model Menu | Model Items | Domains and click Add in the Domains dialog.

    To edit a domain

    Select Model Menu | Model Items | Domains and double-click the selected domain or click Edit in the Domains dialog.

    Example: The Domain Properties dialog (Oracle 10g)

    General Tab

    Description

    Caption

    Logical domain name

    Name

    Physical domain name

    Data Type

    Data Type selection box

    Note:

    1. Other options on the General tab vary according to the current database platform. Options specific for your database can be found in the "Databases" chapter.
    2. All changes made to your domain will be automatically applied to every attribute which uses the domain.

    Check Constraints Tab

    On this tab, you can add, edit and delete check constraints of a domain.

    To Do Tab

    On this tab, you can write some tasks related to the domain.

    Used In Tab

    All attributes with this domain are listed on this tab.

    Double-clicking any attribute will open its Attribute Properties.

    Notes Tab

    Tab for notes on the domain.

    To select domains for attributes

    1. Open the Attribute Properties dialog of the selected attribute | General tab.
    2. From the Domains box, select a domain.

    Note:

    1. To copy domains, use CTRL + Drag&Drop techniques.
    2. To move domains, use Drag&Drop techniques.

      You can copy and move your domains within a model and between models of the same and different databases:
    • In  Domains dialog (Model menu)
    • In Model Explorer | Domains folder
    • Between Model Explorer and Domains dialog
    1. To delete domains, select:
    • Model | Domains | Select a domain and click Delete.
    • Model Explorer | Domains folder | Right-click and select Delete Item.

    Note: Deleting a domain will remove it from all attributes that are using it. To find out which attributes they are, open the Domain Properties dialog | Used In tab.

    Related Documents

    The document was helpful.

    Select Rating

    I easily found the information I needed.

    Select Rating