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
- Open the Attribute Properties dialog of the selected attribute | General tab.
- 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:
- To copy user data types, use CTRL + Drag&Drop techniques.
- 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
- 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
- Open the Attribute Properties dialog of a selected attribute.
- In General tab, select the dictionary type from the Data Type dropdown menu.
|
Note:
- You can reach the Dictionary Types dialog also from the Attribute Properties dialog - see the small icons next to the Data Type box.
- Click OK+Add in the Dictionary Type Properties dialog to apply changes and create another dictionary type at once.
- To copy dictionary types, use CTRL + Drag&Drop techniques.
- 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
- 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:
- 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 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
- Open the Attribute Properties dialog of the selected attribute | General tab.
- From the Domains box, select a domain.
|
Note:
- To copy domains, use CTRL + Drag&Drop techniques.
- 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
- 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. |