Toad Data Modeler 7.3

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

Features and Tools > Graphics > Autolayout

There are three autolayout methods available in Toad Data Modeler.

Top to Bottom
Left to Right
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

Features and Tools > Graphics > 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:

Right-click the object and select Arrange | Settings.
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.

製品を選択してください:

サービス向上のために、「Purpose of your Chat（チャットの目的）」を記入してください。

お客様の不具合に推奨されるソリューション

Toad Data Modeler 7.3 - User Guide

Example of Generated Script

Graphics

Autolayout

Arrange Objects in Layers

Z-Order Box