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

Example of Generated Script

Videorental Model

Oracle 10g database/* 
Created: 15.3.2007
Modified: 6.12.2013
Project: Videorental Project
Model: Videorental
Company: Quest Software Inc.
Author: Jan Novak
Database: Oracle 10g
        */
        -- Create user data types section -------------------------------------------------
        CREATE TYPE SCOTT.Phone_List_TypeAS VARRAY(5) OF VARCHAR2(25)/
        CREATE TYPE SCOTT.Cust_Address_TypeAS OBJECT
        ( street_address     VARCHAR2(40)
        , postal_codeVARCHAR2(10)
        , cityVARCHAR2(30)
        , state_province     VARCHAR2(10)
        , country_idCHAR(2)
        , phonePhone_List_Type
        )/
        CREATE TYPE SCOTT.Price_TypeAS OBJECT (price NUMBER(10,2),
        MEMBER FUNCTION total_price_VAT (vat number) RETURN NUMBER)/
        CREATE TYPE BODY SCOTT.Price_TypeISMEMBER FUNCTION total_price_VAT (vat NUMBER)
        RETURN NUMBER ISBEGINRETURN (price*((vat+100)/100));
        END;
        END;/
        -- Create sequences section -------------------------------------------------
        CREATE SEQUENCE SCOTT.SeqExemplarINCREMENT BY 1START WITH 1NOMAXVALUENOMINVALUENOCACHE/
        -- Create tables section -------------------------------------------------
        -- Table SCOTT.T_CUSTOMERCREATE TABLE SCOTT.T_CUSTOMER(
        customer_id Integer NOT NULL,
        name Varchar2(20 CHAR),
        address SCOTT.Cust_Address_Type
        )/
        -- Create indexes for table SCOTT.T_CUSTOMERCREATE INDEX i_name ON SCOTT.T_CUSTOMER (name)/
        -- Add keys for table SCOTT.T_CUSTOMERALTER TABLE SCOTT.T_CUSTOMER ADD CONSTRAINT pk_T_CUSTOMER PRIMARY KEY (customer_id)/
        -- Table and Columns comments sectionCOMMENT ON COLUMN SCOTT.T_CUSTOMER.name IS 'Name column can contain First and Middle name. Surname must be in different column.'/
        -- Table SCOTT.T_GENRECREATE TABLE SCOTT.T_GENRE(
        genre_id Integer NOT NULL,
        name Varchar2(20 CHAR)
        )/
        -- Add keys for table SCOTT.T_GENREALTER TABLE SCOTT.T_GENRE ADD CONSTRAINT pk_T_GENRE PRIMARY KEY (genre_id)/
        -- Table SCOTT.T_MEDIUMCREATE TABLE SCOTT.T_MEDIUM(
        medium_id Integer NOT NULL,
        medium_type Varchar2(20 BYTE)
        )/
        -- Add keys for table SCOTT.T_MEDIUMALTER TABLE SCOTT.T_MEDIUM ADD CONSTRAINT pk_T_MEDIUM PRIMARY KEY (medium_id)/
        -- Table SCOTT.T_EXEMPLARCREATE TABLE SCOTT.T_EXEMPLAR(
        exemplar_id Integer NOT NULL,
        film_id Integer NOT NULL,
        medium_id Integer NOT NULL,
        price_per_day Integer
        )/
        -- Add keys for table SCOTT.T_EXEMPLARALTER TABLE SCOTT.T_EXEMPLAR ADD CONSTRAINT pk_T_EXEMPLAR PRIMARY KEY (exemplar_id)/
        ALTER TABLE SCOTT.T_EXEMPLAR ADD CONSTRAINT ak_exemplar_ppd UNIQUE (exemplar_id,price_per_day)/
        -- Table SCOTT.T_FILMCREATE TABLE SCOTT.T_FILM(
        film_id Integer NOT NULL,
        title Varchar2(50 CHAR) NOT NULL,
        director Varchar2(30 CHAR) NOT NULL,
        production_company Varchar2(50 CHAR),
        genre_id Integer NOT NULL,
        min_age Integer,
        film_ID_episodes Integer
        )/
        -- Add keys for table SCOTT.T_FILMALTER TABLE SCOTT.T_FILM ADD CONSTRAINT pk_T_FILM PRIMARY KEY (film_id)/
        ALTER TABLE SCOTT.T_FILM ADD CONSTRAINT ak_title_director UNIQUE (title,director)/
        -- Table and Columns comments sectionCOMMENT ON COLUMN SCOTT.T_FILM.production_company IS 'Company name must contain also information about company type - LTD, Inc. and so on.'/
        -- Table SCOTT.T_BORROWINGCREATE TABLE SCOTT.T_BORROWING(
        exemplar_id Integer NOT NULL,
        customer_id Integer NOT NULL,
        start_date Date DEFAULT sysdate,
        end_date Date,
        total_price SCOTT.Price_Type,
        VAT Number(4,2) DEFAULT 19,
        CONSTRAINT check_end_after_start CHECK ((end_date>start_date) or (end_date is null))
        )/
        -- Add keys for table SCOTT.T_BORROWINGALTER TABLE SCOTT.T_BORROWING ADD CONSTRAINT pk_T_BORROWING PRIMARY KEY (exemplar_id)/
        -- Create triggers for table SCOTT.T_BORROWINGCREATE TRIGGER SCOTT.tri_BORROWINGBEFORE INSERTON SCOTT.T_BORROWINGFOR EACH ROWdeclare price number(10,2);
        beginselect price_per_dayinto pricefrom T_EXEMPLARwhere T_EXEMPLAR.exemplar_id = :new.exemplar_id;
        :new.total_price := Price_Type((:new.end_date-:new.start_date)*price);
        end;/
        CREATE TRIGGER SCOTT.tru_BORROWINGBEFORE UPDATEON SCOTT.T_BORROWINGFOR EACH ROWdeclareprice number(10,2);
        total_price_old number(10,2);
        end_d date;
        start_d date;
        beginselect e.price_per_dayinto pricefrom T_EXEMPLAR ewhere e.exemplar_id = :new.exemplar_id;
        total_price_old := :new.total_price.price;
        end_d := :new.end_date;
        start_d := :new.start_date;
        if (total_price_old is null) thentotal_price_old := 0;
        end if;
        if ((end_d-start_d)*price != total_price_old) then
        :new.total_price := Price_Type((end_d-start_d)*price);
        end if;
        end;/
        -- Table and Columns comments sectionCOMMENT ON TABLE SCOTT.T_BORROWING IS 'Borrowed items'/
        -- Table SCOTT.T_ORDER_RECORDCREATE TABLE SCOTT.T_ORDER_RECORD(
        customer_id Integer NOT NULL,
        film_id Integer NOT NULL,
        order_date Date
        )/
        -- Add keys for table SCOTT.T_ORDER_RECORDALTER TABLE SCOTT.T_ORDER_RECORD ADD CONSTRAINT pk_T_ORDER_RECORD PRIMARY KEY (customer_id,film_id)/
        -- Table and Columns comments sectionCOMMENT ON TABLE SCOTT.T_ORDER_RECORD IS 'All records are stored in list of records. It will be possible to book a movie.'/
        -- Table SCOTT.T_CUSTOMER_RATINGCREATE TABLE SCOTT.T_CUSTOMER_RATING(
        title Varchar2(50 CHAR) NOT NULL,
        director Varchar2(30 CHAR) NOT NULL,
        rating Integer DEFAULT 3CONSTRAINT check_rating CHECK (rating > 0)
        )/
        -- Add keys for table SCOTT.T_CUSTOMER_RATINGALTER TABLE SCOTT.T_CUSTOMER_RATING ADD CONSTRAINT pk_T_CUSTOMER_RATING PRIMARY KEY (title,director)/
        -- Table and Columns comments sectionCOMMENT ON TABLE SCOTT.T_CUSTOMER_RATING IS 'Movie ratings (by customers)'/
        -- Create procedures section -------------------------------------------------
        CREATE PROCEDURE SCOTT.p_Customer_Has_Num_Film(cid IN integer, num OUT integer)
        ASBEGINSELECT count(*)
        INTO numFROM T_CUSTOMER c, T_BORROWING b, T_EXEMPLAR e, T_FILM fWHERE c.customer_id=cidand c.customer_id=b.customer_idand b.exemplar_id=e.exemplar_idand e.film_id=f.film_id;
        END;/
        -- Create views section -------------------------------------------------
        CREATE VIEW SCOTT.v_Customer_Has_Film ASSELECT DISTINCT c.name, c.address.city AS city, f.title, f.directorFROM SCOTT.T_CUSTOMER c, SCOTT.T_BORROWING b, SCOTT.T_EXEMPLAR e, SCOTT.T_FILM fWHERE c.customer_id=b.customer_idand b.exemplar_id=e.exemplar_idand e.film_id=f.film_id/
        -- Create functions section -------------------------------------------------
        CREATE FUNCTION SCOTT.f_Customer_Has_Num_Film(cid IN integer)
        RETURN integerISsol integer;
        BEGINp_Customer_Has_Num_Film(cid,sol);
        RETURN (sol);
        END;/
        -- Trigger for sequence SCOTT.SeqExemplar for column exemplar_id in table SCOTT.T_EXEMPLAR ---------
        CREATE OR REPLACE TRIGGER SCOTT.ts_T_EXEMPLAR_SeqExemplar BEFORE INSERTON SCOTT.T_EXEMPLAR FOR EACH ROWBEGINSELECT SCOTT.SeqExemplar.nextval INTO :new.exemplar_id FROM DUAL;
        END;/
        CREATE OR REPLACE TRIGGER SCOTT.tsu_T_EXEMPLAR_SeqExemplar AFTER UPDATE OF exemplar_idON SCOTT.T_EXEMPLAR FOR EACH ROWBEGINRAISE_APPLICATION_ERROR(-20010,'Cannot update column exemplar_id in table SCOTT.T_EXEMPLAR as it uses sequence.');
        END;/
        -- Create relationships section -------------------------------------------------
        ALTER TABLE SCOTT.T_BORROWING ADD CONSTRAINT makes FOREIGN KEY (customer_id) REFERENCES SCOTT.T_CUSTOMER (customer_id)/
        ALTER TABLE SCOTT.T_BORROWING ADD CONSTRAINT is_related_to FOREIGN KEY (exemplar_id) REFERENCES SCOTT.T_EXEMPLAR (exemplar_id)/
        ALTER TABLE SCOTT.T_ORDER_RECORD ADD CONSTRAINT places FOREIGN KEY (customer_id) REFERENCES SCOTT.T_CUSTOMER (customer_id)/
        ALTER TABLE SCOTT.T_EXEMPLAR ADD CONSTRAINT is_available_on FOREIGN KEY (medium_id) REFERENCES SCOTT.T_MEDIUM (medium_id)/
        ALTER TABLE SCOTT.T_ORDER_RECORD ADD CONSTRAINT is_required_by FOREIGN KEY (film_id) REFERENCES SCOTT.T_FILM (film_id)/
        ALTER TABLE SCOTT.T_FILM ADD CONSTRAINT is_of FOREIGN KEY (genre_id) REFERENCES SCOTT.T_GENRE (genre_id)/
        ALTER TABLE SCOTT.T_EXEMPLAR ADD CONSTRAINT has FOREIGN KEY (film_id) REFERENCES SCOTT.T_FILM (film_id)/
        ALTER TABLE SCOTT.T_CUSTOMER_RATING ADD CONSTRAINT is_rated FOREIGN KEY (title, director) REFERENCES SCOTT.T_FILM (title, director)/
        ALTER TABLE SCOTT.T_FILM ADD CONSTRAINT has_more_episodes FOREIGN KEY (film_ID_episodes) REFERENCES SCOTT.T_FILM (film_id)/
        -- Grant permissions section -------------------------------------------------
        insert into T_GENRE values (1,'crime')/
        insert into T_GENRE values (2,'western')/
        insert into T_GENRE values (3,'drama')/
        insert into T_GENRE values (4,'biography')/
        insert into T_GENRE values (5,'comedy')/
        insert into T_MEDIUM values (1,'CD - DivX')/
        insert into T_MEDIUM values (2,'CD - VideoCD')/
        insert into T_MEDIUM values (3,'DVD')/
        insert into T_MEDIUM values (4,'Videotape')/
        insert into T_FILM values (1,'The Shawshank Redemption','Frank Darabont','Castle Rock Entertainment',1,15, null)/
        insert into T_FILM values (2,'The Godfather','Francis Ford Coppola','Paramount Pictures',1,15,null)/
        insert into T_FILM values (3,'The Godfather: Part II','Francis Ford Coppola','Paramount Pictures',1,15,2)/
        insert into T_FILM values (4,'The Good, the Bad and the Ugly','Sergio Leone','PEA',2,15,null)/
        insert into T_FILM values (5,'Pulp Fiction','Quentin Tarantino','Miramax Films',1,18,null)/
        insert into T_FILM values (6,'12 Angry Men','Sidney Lumet','Orion-Nova Productions',3,15,null)/
        insert into T_FILM values (7,'Schindler''s List','Steven Spielberg','Universal Pictures',4,15,null)/
        insert into T_FILM values (8,'One Flew Over the Cuckoo''s Nest','Milos Forman','Fantasy Films',3,15,null)/
        insert into T_EXEMPLAR values (1,1,3,3)/
        insert into T_EXEMPLAR values (2,1,3,3)/
        insert into T_EXEMPLAR values (3,1,3,3)/
        insert into T_EXEMPLAR values (4,1,4,2)/
        insert into T_EXEMPLAR values (5,1,4,2)/
        insert into T_EXEMPLAR values (6,2,3,3)/
        insert into T_EXEMPLAR values (7,2,4,2)/
        insert into T_EXEMPLAR values (8,3,3,3)/
        insert into T_EXEMPLAR values (9,4,4,2)/
        insert into T_EXEMPLAR values (10,5,3,3)/
        insert into T_EXEMPLAR values (11,6,4,2)/
        insert into T_EXEMPLAR values (12,6,4,2)/
        insert into T_EXEMPLAR values (13,7,4,2)/
        insert into T_CUSTOMER values (1,'Audrey',Cust_Address_Type('92A Campton Avenue','60021','Fox River Grove',null,'us',null))/
        insert into T_CUSTOMER values (2,'Simon',Cust_Address_Type('12 Hillpeak Street','60006','Arlington Heights',null,'ca',null))/
        insert into T_CUSTOMER values (3,'Dave',Cust_Address_Type('31D South Avenue','60001','Alden',null,'us',Phone_List_Type('541 123 456')))/
        insert into T_CUSTOMER values (4,'Chris',Cust_Address_Type('5 Quiet Street','60061','Vernon Hills',null,'us',Phone_List_Type('541 123 456','596 815 641')))/
        insert into T_CUSTOMER values (5,'Elen',Cust_Address_Type('47 My Avenue','60083','Beach Park','Illinois','us',null))/
        insert into T_BORROWING (exemplar_id,customer_id,start_date,end_date) values (11,1,to_date('01-01-2010','DD-MM-YYYY'),to_date('03-01-2010','DD-MM-YYYY'))/
        insert into T_BORROWING (exemplar_id,customer_id,start_date,end_date) values (12,2,to_date('02-01-2010','DD-MM-YYYY'),to_date('10-01-2010','DD-MM-YYYY'))/
        insert into T_BORROWING (exemplar_id,customer_id,start_date) values (1,2,to_date('06-01-2010','DD-MM-YYYY'))/
        insert into T_BORROWING (exemplar_id,customer_id,start_date) values (6,4,to_date('07-01-2010','DD-MM-YYYY'))/
        insert into T_BORROWING (exemplar_id,customer_id,start_date) values (7,3,to_date('07-01-2010','DD-MM-YYYY'))/
        insert into T_BORROWING (exemplar_id,customer_id) values (9,3)/
        insert into T_ORDER_RECORD values (1,2,to_date('01-01-2010','DD-MM-YYYY'))/
        insert into T_ORDER_RECORD values (1,1,to_date('01-01-2010','DD-MM-YYYY'))/
        insert into T_ORDER_RECORD values (2,2,to_date('03-01-2010','DD-MM-YYYY'))/
        insert into T_ORDER_RECORD values (3,3,to_date('06-01-2010','DD-MM-YYYY'))/
			

Graphics

Autolayout

There are three autolayout methods available in Toad Data Modeler.

  1. Top to Bottom
  2. Left to Right
  3. Alphabetic

To re-arrange objects on your Workspace automatically

Click any of the autolayout icons on the Layout Toolbar (these options are also available in Layout Menu).

Autolayout is especially handy when you:

  • Reverse engineer a database with a large number of items
  • Add a large number of model objects to a new Workspace

To configure properties of Autolayout

Go to Settings Menu | Options | Graphics | Autolayout.
You can configure horizontal and vertical distance coefficients (the distance between objects) and Alphabetic Autolayout sorting property.

 

Arrange Objects in Layers

Toad Data Modeler allows you to rearrange/order objects on the Workspace in different layers.

To re-arrange the selected object on the Workspace

Right-click the object | Arrange and select any of the following options:

  • Bring to Front – Brings the object on the top layer.
  • Bring Forward – Brings the object one layer up.
  • Send Backward – Sends the object one layer down.
  • Send to Back – Sends the object to the lowest layer.

Z-Order Box

To specify exact layer for an object on Workspace:

  1. Right-click the object and select Arrange | Settings.
  2. In the Object Format dialog, change the Z-Order value. Object with greater Z-Order value are placed on top of objects with lower Z-Order value.
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating