Oracle sql developer data modeler инструкция

This topic explains menu items that are of special interest for Data Modeler .

File menu

Edit menu

Contains standard Edit menu options related to cutting, copying, pasting, deleting, aligning, and finding objects.

View menu

Team menu

Tools menu

The context menus (right-click menus) in the object browser and diagrams contain commands relevant for the object or objects selected.

In the object browser, if you right-click the logical model or a relational model, the context menu generally includes the following:

• Change Subview Object Names Prefix: Specifies the new prefix to replace a specified current prefix for selected types of objects. Displays the Change Subview Object Names Prefix dialog box.

• Apply Custom Transformation Scripts: Displays the Custom Transformation Scripts dialog box, where you can select scripts to be applied. (For more information about custom transformation scripts, see Transformations.)

• Discover Foreign Keys: Lets you discover foreign key relationships among tables in the relational model, and to create foreign keys. (See Create Discovered Foreign Keys.)

• Engineer to Relational Model (with the logical model selected): Performs forward engineering: generates or updates a relational model from the logical model. You can also specify if the operation creates a subview.

• Engineer to Logical Model (with a relational model selected): Performs reverse engineering: updates the logical model from the selected relational model.

In diagrams, if you right-click outside any displayed object, the context menu generally includes the following:

• Create Discovered Foreign Keys (relational model) Displays discovered hidden foreign key relationships in a relational model. (See Create Discovered Foreign Keys.)

• Remove Discovered Foreign Keys (relational model): Removes any discovered foreign keys from the relational model diagram.

• Create Subview: Creates a subview. (See also Logical Diagram and Subviews and Relational Diagram and Subviews.)

• Create Display: Creates a separate display pane of the view or subview. Displays enable you to represent the same set of objects in different ways. For example, you can create a display, select it, and then experiment with changing the context-menu settings for View Details, Show (Grid, Page Grid, Labels, Legend), and Diagram Color.Displays are in the same window together with related main diagram or subview; however, you can find tabs for displays at the bottom of that window. To remove a display, right-click in it and select Delete Display.

• Auto Route: Toggles the setting of the Line Auto Route option (see Diagram under Data Modeler). You must disable Auto Route before you can adjust lines in diagrams, such as clicking and dragging edges and elbows (vertices) to move them, or Ctrl+clicking and dragging on an edge to create a new elbow. Note: If you then enable Auto Route, any manual adjustments are lost.

• Straighten Lines (available only if Auto Route is disabled): Removes any elbows so that the line connects only the start and end points.

• AutoLayout (relational and data flow diagrams): Rearranges the objects in the diagram to a layout that may be more meaningful and attractive. If you do not like the rearrangement, you can restore the previous layout by clicking Edit, then Undo AutoLayout.

• View Details: Lets you view all available details for objects or only selected details.

• Show: Grid displays a grid in the background, which can help you to align objects vertically and horizontally on the diagram; Page Grid displays where page margins will be in printed PDF output; Labels displays the foreign key names on relationship arrows and the flow names on flow lines in data flow diagrams; Legend displays a legend box (which you can drag to move) containing the diagram name, author, creation date, and other information.

• Resize Objects to Visible: Resizes objects in the diagram so that all are visible in the display area.

• Diagram Color: Displays a dialog box for selecting the color scheme for the background on diagrams.

• Properties: Displays the dialog box for viewing and editing properties of the model.

In diagrams, if you right-click a line connecting two objects, the context menu generally includes the following:

• Delete: Removes the line and deletes the relationship represented by the line.

• Straighten Lines (available only if Auto Route is disabled): Removes any elbows so that the line connects only the start and end points.

• Format: Lets you change the width and color of the line.

• Add Elbow (available only if Auto Route is disabled): Adds an elbow (vertex) at the selected point.

• Remove Elbow (available only if Auto Route is disabled): Removes the selected elbow (vertex).

• Properties: Displays the dialog box for viewing and editing properties of the relationship represented by the line.

In the logical and relational diagrams, if you select one or more entities or tables and right-click one of them, the context menu includes at least the following:

• Create Synonym: Creates a synonym object in the display.

• Create Subview from Selected: Creates a subview containing the selected objects. (See also Logical Diagram and Subviews and Relational Diagram and Subviews.)

• Select Neighbors: Selects objects that are related to the selected object or objects. You can specify the selection direction: All (higher- and lower-level zones), Parent, or Child. You may want to select neighbors before creating a subview from the selection.

• DDL Preview (relational diagrams): Shows the DDL statement that would be generated to create the object. (When the DDL Preview window is displayed, you can click on other objects in the relational diagram to see the DDL statements that would be generated to create those objects.)

• Format: Lets you specify colors and fonts for the selected objects.

• Show/Hide Elements: Lets you hide specified elements in the display.

• Send to Back: Sends the selected objects to the back of the diagram display, which may cause them to be partially or completely covered by other objects.

• Properties: Displays the dialog box for viewing and editing properties of the object.

In data flow diagrams, if you select one or more objects and right-click one of them, the context menu includes at least the following:

• Delete: Deletes the selected object.

• Format: Lets you specify colors and fonts for the selected objects.

• Send to Back (for objects not represented by lines): Sends the selected objects to the back of the diagram display, which may cause them to be partly or completely covered by other objects.

• Properties: Displays the dialog box for viewing and editing properties of the object.

Data Modeler works with one open database design, consisting of one logical model, optionally one or more relational models based on the logical model, and optionally one or more physical models based on each relational model. The database design can also include a data types model, and business information. To work on another database design, close the current design (click File, then Close), and create or import objects for the other database design.

When you save a database design, the structural information is stored in an XML file (with the extension .dmd) in a folder or directory that you specify, and subfolders or subdirectories are created as needed under it. The .dmd file contains pointers to information in these subfolders or subdirectories. For example, for a very basic design named my_db_design, the following hierarchy might be created starting at the folder or directory in which you created it:

my_db_design.dmd
my_db_design
   businessinfo
   datatypes
      subviews
   logical
      entity
      subviews
   mapping
   pm
   rdbms
   rel
      1
         subviews
         table

Additional subfolders or directories may be automatically created later, for example, dataflows under pm if you create any data flow diagrams in the process model.

Related Topics

Working with Data Modeler

Data Modeler Concepts and Usage

Data Modeler supports supertypes and subtypes in its logical model, but it also provides the data types model, to be CWM (Common Warehouse Metamodel) compliant and to allow modeling of SQL99 structured types, which can be used in the logical model and in relational models as data types.

Structured types are supported as named user-defined composite types with the possibility of building a supertype/subtypes inheritance hierarchy. You can create and visualize structured types and the inheritance hierarchies of structured types, defining distinct and collection (array) types.

Both logical and relational models can use definitions from the data types model to specify the data type for attributes and columns or to define that a table (entity) is of a certain structured type.

You can build the data types model in one or more of the following ways:

• Manually in Data Modeler

• By importing from Oracle Designer repository. See Importing an Oracle Designer Model.

The data types model in Data Modeler combines two kinds of data:

• One data types diagram, plus an optional set of subviews and auxiliary displays, each associated with the appropriate diagram/subview

• Data type object definitions

Subviews are considered as independent diagrams of the data types model, created to represent different subject areas.

The data types model enables you to create and manage object definitions of distinct, structured, collection, and logical types.

All data type model objects (except logical types) are displayed in the object browser tree, but only structured type objects and their interrelations are represented graphically on data types diagrams.

1.3.2.1 Data Types Diagram and Subviews

1.3.2.2 Distinct Types

1.3.2.3 Structured Types

1.3.2.4 Collection Types

1.3.2.5 Logical Types

The process model represents a functional area of an information structures system. The process model, embodied graphically in one or more data flow diagrams, is an analysis technique used to capture the flow of inputs through a system (or group of processes) to their resulting output. The model shows the flow of information through a system, which can be an existing system or a proposed system.

All necessary elements for data flow diagramming are supported in the Data Modeler process model: primitive processes, composite processes with unlimited levels of decomposition, reusable transformation tasks, triggering events, information stores, external agents, record structure for describing external data elements, source-target mapping of data elements, and CRUD (create, read, update, delete) dependencies between primitive process and data elements.

The following are important concepts for the process model:

• A process is an activity or a function that is performed for some specific reason. Ultimately each process should perform only one activity.

  A primitive process is a standalone process.

  A composite process consists of multiple outer processes. The data flow model allows you to drill down to child processes through a composite process. This means that a top-level process can drill down to another full data flow model.

• A trigger is something that happens which initiates the execution of a process.

• A data flow reflects the movement of single piece of data or logical collection of information. Flows describe the sequence of a data flow diagram. (For more information, see Data Flow Diagrams.)

• A data store is a collection of data that is permanently stored.

• An external agent is a person, organization, or system that is external to the system but interacts with it. External agents send information to and receive information from processes.

• An information store is a passive object that receives or stores information as entities and attributes in the data model. Ultimately, an information store corresponds with one or more entities of the data model.

• A transformation task, including input and output parameters, is an execution unit that communicates with surrounding environment that will execute it. An input parameter might be a date for which processing should be done. An output parameter might be a code that indicates whether the operation was successful or not. Transformation itself might involve reading, transforming, and saving information, some of which may not be directly tied to the input and output parameters. (For more information, see Transformation Processes and Packages.)

• A role is a set of defined privileges and permissions. Primitive processes connected to information stores (processes that create, read, update, and delete data elements) can be attached to a defined role, thus defining collaboration between roles and data elements. Later, role definitions can be transferred to any particular physical model such that appropriate database roles with defined Select, Insert, and Update permission will be created.

1.3.3.1 Data Flow Diagrams

1.3.3.2 Transformation Processes and Packages

At the core of Data Modeler is the logical model (also called the entity-relationship diagram). It provides an implementation-independent view of enterprise information and acts as the mediator that maps definitions in the dimensional and process models to different physical implementations. A logical model, or a part of it (subject area, subview), can be transformed to one or more relational models.

You can build the logical model in any of the following ways:

• Manually in Data Modeler

• By importing models from a VAR file, such as those created by at least these versions of the following products: Sterling COOL:DBA V2.1 or Sterling Bsnteam V7.2, Cayenne Bsnteam V7.2

• By importing an existing model created by Data Modeler

• By reverse engineering from an imported relational model

The logical model combines two kinds of data:

• One logical diagram, plus an optional set of subviews and auxiliary displays, each associated with the appropriate diagram or subview

• Logical model object definitions

Subviews are considered as independent diagrams of the logical model, created to represent different subject areas.

The logical model enables you to create and manage object definitions for entities, logical views, attributes, unique identifiers, inheritances, relations, and arcs.

All logical model objects are displayed in the object browser tree.

1.3.4.1 Logical Diagram and Subviews

1.3.4.2 Entities

An entity is an object or concept about which you want to store information. The structure of entity can be defined as collection of attributes or as based on structured type from the data types model. An entity may have candidate unique identifiers, one of which can be defined as primary unique identifier. Usually, an entity is mapped to table in the relational model.

1.3.4.3 Attributes

A data attribute (property, data element, field) is a characteristic common to a particular entity. The data type of an attribute can be based on a logical data type, a domain, a distinct type, a collection type, or a structured type, or it can be a reference to structured type. If it a reference to a structured type, a scope entity can be defined. An attribute is mapped to a column in the relational model.

1.3.4.4 Unique Identifiers (UIDs)

An entity unique identifier can be composed of one or more attributes. For each entity, you can define one primary unique identifier that uniquely identifies each entity occurrence. You can also specify one or more foreign unique identifiers, each of which points to (that is, must contain a value found in) a unique identifier in another entity.

1.3.4.5 Inheritances

1.3.4.6 Relations

1.3.4.7 Arcs

1.3.4.8 Type Substitution

1.3.4.9 Views

A relational model describes a database in terms of SQL tables, columns, and joins between tables. Each entity that you choose from the logical model is represented as a table in the relational model. Each row in a table represents a specific, individual occurrence of the corresponding entity. Each attribute of an entity is represented by a column in the table.

You can build a relational model in any of the following ways:

• Manually in Data Modeler

• By forward engineering from the logical model or a subview of the logical model

• By importing models from a VAR file, such as those created by at least these versions of the following products: Sterling COOL:DBA V2.1 or Sterling Bsnteam V7.2, Cayenne Bsnteam V7.2

• By importing an existing model created by Data Modeler

• By importing an Oracle Designer model

• By importing DDL files based on an existing database implementation

• By importing from the data dictionary of a supported database type and version

A relational model combines two kinds of data:

• One relational diagram, plus an optional set of subviews and auxiliary displays, each associated with the appropriate diagram or subview

• Relational model object definitions

Subviews are considered as independent diagrams of the relational model, created to represent different subject areas.

A relational model enables you to create and manage object definitions for tables, views, columns, indexes, and foreign keys, and optionally to associate certain relational model objects with database schemas. A relational model can contain one or more physical models.

All relational model objects are displayed in the object browser tree.

1.3.5.1 Relational Diagram and Subviews

1.3.5.2 Tables

A table is an object in which you want to store information. The structure of table can be defined as a group of columns or as based on structured type from data types model. A table may have candidate keys, one of which can be defined as primary key. Usually, a table is mapped to entity from the logical model.

1.3.5.3 Columns

A table column is a characteristic common to a particular table. The data type of a column can be based on a logical data type, a domain, a distinct type, a collection type, or a structured type, or it can be a reference to structured type. If it is a reference to a structured type, a scope table can be defined. Usually, the columns in a table are mapped to the attributes of the corresponding entity from the logical model.

1.3.5.4 Indexes

An index is an object that consists of an ordered set of pointers to rows in a base table. Each index is based on the values of data in one or more table columns. Defining indexes on frequently searched columns can improve the performance of database applications.

1.3.5.5 Relations

1.3.5.6 Relational Views

A physical model describes a database in terms of Oracle Database objects (tables, views, triggers, and so on) that are based on a relational model. Each relational model can have one or more physical models. The following shows a database design hierarchy with several relational and physical models:

Database design
   Logical model
      Relational model 1
         Physical model 1a
         Physical model 1b
         . . . (other physical models)
      Relational model 2
         Physical model 2a
         Physical model 2b
         . . . (other physical models)
      . . . (other relational models)

Each physical model is based on an RDBMS site object. An RDBMS site is a name associated with a type of database supported by Data Modeler. Several RDBMS sites are predefined (for example, for Oracle 11g and Microsoft SQL Server 2005). You can also use the RDBMS Site Editor dialog box to create user-defined RDBMS sites as aliases for supported types of databases; for example, you might create sites named Test and Production, so that you will be able to generate different physical models and then modify them.

When you export to a DDL file, you specify the physical model to be applied. The generated DDL statements include clauses and keywords appropriate for features specified in that physical model (for example, partitioning for one or more tables).

Physical models do not have graphical representation in the work area; instead, they are displayed in the object browser hierarchy. To create and manage objects in the physical model, use the Physical menu or the context (right-click) menu in the object browser.

The rest of this topic briefly describes various Oracle Database objects, listed in alphabetical order (not the order in which they may appear in an Oracle physical model display).

1.3.6.1 Clusters

1.3.6.2 Contexts

A context is a set of application-defined attributes that validates and secures an application.

1.3.6.3 Dimensions

A dimension defines a parent-child relationship between pairs of column sets, where all the columns of a column set must come from the same table. However, columns in one column set (called a level) can come from a different table than columns in another set. The optimizer uses these relationships with materialized views to perform query rewrite. The SQL Access Advisor uses these relationships to recommend creation of specific materialized views.

1.3.6.4 Directories

1.3.6.5 Disk Groups

A disk group is a group of disks that Oracle Database manages as a logical unit, evenly spreading each file across the disks to balance I/O. Oracle Database also automatically distributes database files across all available disks in disk groups and rebalances storage automatically whenever the storage configuration changes.

1.3.6.6 External Tables

An external table lets you access data in an external source as if it were in a table in the database. To use external tables, you must have some knowledge of the file format and record format of the data files on your platform.

1.3.6.7 Indexes

An index is a database object that contains an entry for each value that appears in the indexed column(s) of the table or cluster and provides direct, fast access to rows. Indexes are automatically created on primary key columns; however, you must create indexes on other columns to gain the benefits of indexing.

1.3.6.8 Roles

A role is a set of privileges that can be granted to users or to other roles. You can use roles to administer database privileges. You can add privileges to a role and then grant the role to a user. The user can then enable the role and exercise the privileges granted by the role.

1.3.6.9 Rollback Segments

A rollback segment is an object that Oracle Database uses to store data necessary to reverse, or undo, changes made by transactions. Note, however, that Oracle strongly recommends that you run your database in automatic undo management mode instead of using rollback segments. Do not use rollback segments unless you must do so for compatibility with earlier versions of Oracle Database. See Oracle Database Administrator’s Guide for information about automatic undo management.

1.3.6.10 Segments (Segment Templates)

A segment is a set of extents that contains all the data for a logical storage structure within a tablespace. For example, Oracle Database allocates one or more extents to form the data segment for a table. The database also allocates one or more extents to form the index segment for a table.

1.3.6.11 Sequences

A sequence is an object used to generate unique integers. You can use sequences to automatically generate primary key values.

1.3.6.12 Snapshots

A snapshot is a set of historical data for specific time periods that is used for performance comparisons by the Automatic Database Diagnostic Monitor (ADDM). By default, Oracle Database automatically generates snapshots of the performance data and retains the statistics in the workload repository. You can also manually create snapshots, but this is usually not necessary. The data in the snapshot interval is then analyzed by ADDM. For information about ADDM, see Oracle Database Performance Tuning Guide.

1.3.6.13 Stored Procedures

A stored procedure is a schema object that consists of a set of SQL statements and other PL/SQL constructs, grouped together, stored in the database, and run as a unit to solve a specific problem or perform a set of related tasks.

1.3.6.14 Synonyms

A synonym provides an alternative name for a table, view, sequence, procedure, stored function, package, user-defined object type, or other synonym. Synonyms can be public (available to all database users) or private only to the database user that owns the synonym).

1.3.6.15 Structured Types

A structured type is a non-simple data type that associates a fixed set of properties with the values that can be used in a column of a table. These properties cause Oracle Database to treat values of one data type differently from values of another data type. Most data types are supplied by Oracle, although users can create data types.

1.3.6.16 Tables

A table is used to hold data. Each table typically has multiple columns that describe attributes of the database entity associated with the table, and each column has an associated data type. You can choose from many table creation options and table organizations (such as partitioned tables, index-organized tables, and external tables), to meet a variety of enterprise needs.

1.3.6.17 Tablespaces

1.3.6.18 Users

A database user is an account through which you can log in to the database. (A database user is a database object; it is distinct from any human user of the database or of an application that accesses the database.) Each database user has a database schema with the same name as the user.

1.3.6.19 Views

Business information objects define business-oriented information about model objects, such as responsible parties and information about how to contact them, and identification of relevant offline documentation.

A model object can have zero or more business information objects associated with it, and a business information object can be associated with zero or more model objects. For example, a single document can be used to describe many different entities and attributes, or a single person can be the responsible party for multiple events.

There can also be many-to-many relationships among business objects. For example, a responsible party can have multiple sets of contact information (contact objects), and a contact object can be associated with multiple responsible parties. Similarly, one or more telephone, email, location, and URL objects can be associated with multiple contact objects.

The Data Modeler business information model is based on the Object Management Group (OMG) business information package, which is described in the OMG Common Warehouse Metamodel™ (CWM™) Specification, V1.1 as follows: «The Business Information Metamodel provides general purpose services available to all CWM packages for defining business-oriented information about model elements. The business-oriented services described here are designed to support the needs of data warehousing and business intelligence systems; they are not intended as a complete representation of general purpose business intelligence metamodel. Business Information Metamodel services support the notions of responsible parties and information about how to contact them, identification of off-line documentation and support for general-purpose descriptive information.»

The rest of this topic briefly describes business information objects, listed in alphabetical order (not the order in which they appear in the object browser under Business Information).

1.3.7.1 Contacts

A contact object groups the various types of related contact information. Each contact object can be associated with multiple email, location, URL, and telephone objects. Conversely, each email, location, URL, and telephone object can be associated with many contact objects. (See also Contact Properties.)

1.3.7.2 Documents

A document object represents externally stored descriptive information about some aspects of the modeled system. A document object can be associated with one or more model objects. (See also Document Properties.)

1.3.7.3 Emails

An email object identifies a single electronic mail address. Through the use of a contact object, you can associate an email address with one or more responsible parties. The sequence of email objects for a contact might be used to represent the order in which to try email addresses in attempting to communicate with a contact. (See also Email Properties.)

1.3.7.4 Locations

A location object identifies a single physical location. Through the use of a contact object, you can associate a location with one or more responsible parties. The sequence of contact objects for a location might be used to represent the order in which to try contacting a person or group associated with a location. (See also Location Properties.)

1.3.7.5 Resource Locators

A resource locator object provides a general means for describing a resource whose location is not defined by a traditional mailing address. For example, a resource locator could refer to anything from a Web address (such as «www.example.com») to a location within a building (such as «Room 317, third file cabinet, 2nd drawer»). (See also Resource Locator Properties.)

1.3.7.6 Responsible Parties

A responsible party object represents a person, role, or organization that has a responsibility for, or should receive information about, one or more model objects. The precise meaning of the «responsibility» of a responsible object depends on the specific system being implemented. (See also Responsible Party Properties.)

1.3.7.7 Telephones

Top-down modeling gathers information about business requirements and the internal environment, and proceeds to define processes, a logical model of the data, one or more relational models, and one or more physical models for each relational model. The steps and information requirements can range from simple to elaborate, depending on your needs. Top-down modeling can involve the following steps, but you can abbreviate or skip steps as appropriate for your needs.

1. Develop the business information.

   1. Create documents. In the object browser, right-click Logical and select Properties, then click Documents and add items as appropriate.

   2. Create responsible parties with contacts, email addresses, locations, telephone numbers, and locations. In the object browser, right-click Logical and select Properties, then click Responsible Parties and add items as appropriate.

   3. Define any other information. In the object browser, right-click Logical and select Properties, then modify other properties (Naming Options, Comments, Notes) as needed.

2. Develop the process model, using a data flow diagram. In the object browser under Process Model, right-click Data Flow Diagrams and select New Data Flow Diagram.

   1. Create processes. For each process, click the New Process icon, click in the data flow diagram window, and enter information in the Process Properties dialog box.

   2. Create external agents. For each external agent, click the New External Agent icon, click in the data flow diagram window, and enter information in the External Agent Properties dialog box.

   3. Create information stores. For each process, click the New Information Store icon, click in the data flow diagram window, and enter information in the Information Store Properties dialog box.

   4. Create flows with information structures. For each flow, click the New Flow icon, click the starting object (such as a process) in the data flow diagram window, and click the ending object for the flow; then double-click the flow arrow and modify information (as needed) in the Flow Properties dialog box

3. Develop the logical model.

   1. Create entities, and for each entity its attributes and unique identifiers. You can create all entities first and then the attributes and unique identifiers for each, or you can create the first entity with its attributes and unique identifiers, then the second, and so on.

      To create an entity, click the Logical tab, click the New Entity icon, click in the logical model window, and enter information in the Entity Properties dialog box. You can also enter attributes and unique identifiers using the appropriate panes in this dialog box.

   2. Create relations between entities. For each relation, click the desired icon: New M:N Relation (many-to-many), New 1:N Relation (one-to-many) , New 1:N Identifying Relation (one-to-many, identifying), or New 1:1 Relation (one-to-one). Click the entity for the start of the relation, and click the entity for the end of the relation; then double-click the relation line and modify information (as needed) in the Relation Properties dialog box.

   3. Apply design rules to the logical model. Click Tools, then Design Rules, and use the Design Rules dialog box to check for and fix any violations of the design rules.

   4. Forward engineer the logical model to a relational model. Right-click the logical model in the navigator, then select Engineer to Relational Model, and use the Engineering dialog box to generate a relational model reflecting all or a specified subset of objects from the logical model.

4. Develop the multidimensional model, if needed.

   1. Create cubes.

   2. Create levels.

   3. Create dimensions.

   4. Create links.

   5. Apply design rules for the multidimensional model.

   6. Export the multidimensional model, as needed.

5. Develop one or more relational models, doing the following for each as needed.

   1. Split tables. To split one table into two, select the table on the relational model diagram, and click the Split Table button

   2. Merge tables. To merge a table into another table (removing the merged table), click the Merge Tables button. Then, in the relational model diagram, first the table into which to merge columns from the other table, and next select the other table whose columns are to me merged. (After the merge, the second table will be removed.)

   3. Check design rules for the relational model. Click Tools, then Design Rules.

6. Develop one or more physical models for each relational model, doing the following for each.

   1. Open a physical model.

   2. Check design rules for the physical model. Click Tools, then Design Rules.

   3. Generate DDL code, which can be used to generate the actual database objects. Click View, then DDL File Editor, and then use the DDL File Editor dialog box to select a physical model, generate DDL code, and save the code to a script file.

Bottom-up modeling builds a database design based on either metadata extracted from an existing database or a file with DDL code that implements an existing database. The resulting database is represented as a relational model and a physical model, and you reverse engineer the logical model from the relational model. Bottom-up modeling can involve the following steps, but you can abbreviate or skip some steps as appropriate for your needs.

1. Generate the relational model in either of the following ways:

   • Extract metadata directly from an existing database: click File, then Import, then Data Dictionary; then follow the instructions for the wizard (see Data Dictionary Import (Metadata Extraction)).

   • Import DDL code that reflects an existing database implementation. Click File, then Import, then DDL File.

2. As needed, modify the relational model and create additional relational models.

3. As needed, denormalize the relational model or models. Perform the following steps iteratively, as needed, on each model.

   1. Split or merge tables, or do both.

      To split one table into two, select the table on the relational model diagram, and click the Split Table button. Use the Split Table wizard to copy or move source foreign keys and columns to the target table (the new table to be created).

      To merge a table into another table (removing the merged table), click the Merge Table button. Then, in the relational model diagram, first click the table whose columns are to be merged into the other table, and next click the table into which to merge the columns from the first table that you clicked. (After the merge, the first table that you clicked will be removed, and the remaining table will include its original columns plus the columns that had been in the first table.)

   2. Check the design rules for the model. To view the design rules, click Tools, then Design Rules; select the desired relational model; and use the Design Rules dialog box.

4. Reverse engineer the logical model from a relational model. Click the Engineer to Logical Model icon, or right-click the relational model, then select Engineer to Logical Model.

5. As needed, modify the logical model.

6. Check design rules for the logical model. Click Tools, then Design Rules.

7. Save the design.

8. Generate DDL code, and use it to create the database implementation. Click View, then DDL File Editor. In the DDL File Editor dialog box, select the physical model and click Generate. Specify any desired DDL Generation Options, then click OK.

Targeted modeling involves maintaining an existing database by adapting it to new requirements.

Depending on the kind of changes necessary, you can start with the logical model, one or more relational models, or one or more physical models, and then forward engineer or reverse engineer as appropriate.

To start with changes to the logical model:

To start with changes to a relational model:

The Environment pane contains options that affect the startup and overall behavior and appearance of Data Modeler. You can specify that certain operations be performed automatically at specified times, with the trade-off usually being the extra time for the operation as opposed to the possibility of problems if the operation is not performed automatically (for example, if you forget to perform it when you should).

For example, changes to the undo level (number of previous operations that can be undone) and navigation level (number of open files) values may cause slight increases or decreases in system resource usage.

Automatically Reload Externally Modified Files: If this option is checked, any files open in Data Modeler that have been modified by an external application are updated when you switch back to Data Modeler, overwriting any changes that you might have made. If this option is not checked, changes that you make in Data Modeler overwrite any changes that might have been made by external applications.

Silently Reload When File Is Unmodified: If this option is checked, you are not asked if you want to reload files that have been modified externally but not in Data Modeler. If this option is not checked, you are asked if you want to reload each file that has been modified externally, regardless of whether it has been modified in Data Modeler.

Environment: Dockable Windows

Environment: Log

The Data Modeler pane contains options that affect the startup and overall behavior and appearance of Data Modeler.

Default Designs Directory: The default directory or folder from which to open a design or in which to create a design.

Default Import Directory: The default directory or folder from which to import files.

Show Log After Import: Controls whether a Log window is displayed after an import operation. The window contains informational messages and any warning or error messages.

Default Save Directory: The default directory or folder in which to save files.

Default System Types Directory: The default directory or folder for storing type definition files.

Show «Select Relational Models» Dialog: Controls whether the dialog box for selecting relational models to be included is displayed when you open a Data Modeler design. If this option is disabled, all relational models are included by default when you open a Data Modeler design.

Show Properties Dialog on New Object: Controls whether the Properties dialog box for objects of that type is displayed when you create a new model object.

Use OCI/Thick Driver: For Oracle Database connections, controls whether the oci8 (thick) driver is used by default if it is available, instead of the JDBC (thin) driver.

Reload Last State: Controls whether the last open design is reloaded when Data Modeler is restarted. (Regardless of the setting of this option, you can see any recently open designs by clicking File > Recent Designs.)

Import: Lets you import Data Modeler preferences and other settings that had previously been exported, as explained in Exporting and Importing Preferences and Other Settings.

Export: Saves Data Modeler preferences and other settings to an XML file, so that you can later import the information, as explained in Exporting and Importing Preferences and Other Settings.

Other Data Modeler preferences are grouped into the following categories:

• DDL

• Diagram

• Model

• Reports

• Search

• Third Party JDBC Drivers

1.5.2.1 DDL

1.5.2.2 Diagram

1.5.2.3 Model

1.5.2.4 Reports

1.5.2.5 Search

1.5.2.6 Third Party JDBC Drivers

The Format pane controls how statements are formatted in SQL scripts that are generated. The options include whether to insert space characters or tab characters when you press the Tab key (and how many characters), uppercase or lowercase for keywords and identifiers, whether to preserve or eliminate empty lines, and whether comparable items should be placed on the same line or on separate lines.

The Advanced Format subpane lets you specify more detailed formatting options. It also includes these options:

• Preview with Current Settings: You specify changes on the left side, and the preview area on the right side reflects the changes.

• Auto-Detect Formatter Settings: You paste code with the desired formatting into the preview pane on the right side, and SQL Developer adjusts the settings on the left side to reflect what you pasted. (It automatically detects, or autodetects, your setting preferences.)

You can export these settings to a code style profile XML file, and can import settings from a previously exported code style profile file.

The Custom Format subpane lets you further customize your formatting settings. You can also export these settings to a custom formatter program XML file, and you can import settings from a previously exported custom formatter program file.

Related Topics

User Preferences for Data Modeler

The Global Ignore List pane specifies filters that determine which files and file types will not be used in any processing.

New Filter: A file name or file type that you want to add to the list of files and file types (in the Filter box) that Data Modeler will ignore during all processing (if the filter is enabled, or checked). You can exclude a particular file by entering its complete file name, such as mumble.txt, or you can exclude all files of the same type by entering a construct that describes the file type, such as *.txt.

Add: Adds the new filter to the list in the Filter box.

Remove: Deletes the selected filter from the list in the Filter box.

Restore Defaults: Restores the contents of the Filter box to the Data Modeler defaults.

Filter: Contains the list of files and file types. For each item, if it is enabled (checked), the filter is enforced and the file or file type is ignored by Data Modeler; but if it is disabled (unchecked), the filter is not enforced.

Related Topics

User Preferences for Data Modeler

The Mouse Actions pane specifies text to be displayed on hover-related mouse actions over relevant object names.

Popup Name: The type of information to be displayed: Data Values (value of the item under the mouse pointer, such as the value of a variable), Documentation (documentation on the item under the mouse pointer, such as Javadoc on a method call), or Source (source code of the item under the mouse pointer, such as the source code of a method).

Activate Via: Use action with the mouse cursor to activate the display: Hover, or Hover while pressing one or two specified modifier keys.

Description: Description of the associated Popup Name entry.

Smart Enabled: If this option is checked, then the text for the relevant type of information is displayed if Smart Popup is also checked.

Smart Popup: If this option is checked, the relevant text for the first smart-enabled popup is displayed for the item under the mouse pointer.

Related Topics

Data Modeler

The Shortcut Keys pane enables you to view and customize the shortcut key (also called accelerator key) mappings for Data Modeler.

Hide Unmapped Commands: If this option is checked, only shortcut keys with mappings are displayed.

More Actions:

• Export: Exports the shortcut key definitions to an XML file.

• Import: Imports the shortcut key definitions from a previously exported XML file.

• Load Keyboard Scheme: Drops all current shortcut key mappings and sets the mappings in the specified scheme. (This option was called Load Preset in previous releases.) If you have made changes to the mappings and want to restore the default settings, select Default.

Category: Lists commands and shortcuts grouped by specific categories (Code Editor, Compare, and so on), to control which actions are displayed.

Command: An action relevant to the specified category. When you select an action, any existing shortcut key mappings are displayed.

Shortcut: Any existing key mappings for the selected action. To remove an existing key mapping, select it and click Remove.

New Shortcut: The new shortcut key to be associated with the action. Press and hold the desired modifier key, then press the other key. For example, to associate Ctrl+J with an action, press and hold the Ctrl key, then press the j key. If any actions are currently associated with that shortcut key, they are listed in the Current Assignment box.

Conflicts: A read-only display of the current action, if any, that is mapped to the shortcut key that you specified in the New Shortcut box.

Related Topics

User Preferences for Data Modeler

SSH preferences are related to creating SSH (Secure Shell) connections.

Use Known Hosts File: If this option is checked, specify the file of known hosts to be used.

Related Topics

User Preferences for Data Modeler

In SQL Developer, Data Modeler, and some other applications, the Usage Reporting user preference and a related dialog box ask for your consent to Oracle usage reporting. If you consent, automated reports can occasionally be sent to Oracle describing the product features in use. No personally identifiable information will be sent and the report will not affect performance. You can review Oracle’s privacy policy by clicking the privacy policy link.

Allow automated usage reporting to Oracle: Determines whether you consent to usage reporting.

Related Topics

User Preferences for Data Modeler

Versioning preferences affect the behavior of the version control and management systems that you can use with Data Modeler. For information about using versioning with Data Modeler, see Using Versioning.

Versioning: Subversion

The Subversion pane specifies the Subversion client to use with Data Modeler.

Versioning: Subversion: Comment Templates

Problem Description (with bug ID if any):
Fix Description:

Versioning: Subversion: General

Versioning: Subversion: Version Tools

The Web Browser and Proxy settings are relevant only when you use the Check for Updates feature (click Help, then Check for Updates), and only if your system is behind a firewall.

Web Browsers

Proxy Settings

You can choose no proxy, system default proxy settings, or manually specified proxy settings for Check for Update operations. For manually specified settings, check your Web browser options or preferences for the appropriate values for these fields.

Internet Files

Importing a DDL files enables you to create a relational model based on an existing database implementation. DDL files can originate from any supported database type and version. The file to be imported usually has the extension .ddl or .sql.

The import process creates a new relational model with the name of the imported DDL file and opens a physical model reflecting the source site.

Importing from a data dictionary enables you to create a relational model and a physical model based on an existing database implementation. The data dictionary can be from any supported database type and version.

In the wizard for importing from a data dictionary, you must either select an existing database connection or create (add) a new one, and then follow the instructions to select the schema or database and the objects to be imported.

After you import from a data dictionary, you can edit the relational and physical models as needed, and you can reverse engineer the logical model from the relational model.

Importing an Oracle Designer model enables you to create a relational model and a physical model based on an existing Oracle Designer model. You can create a connection to an Oracle Designer repository and import the entities, tables, and domains from a workspace in Designer.

In the Import Oracle Designer Model wizard, you must either select an existing database connection or create (add) a new one, and then follow the instructions to select the work areas, application systems, and objects to be imported. (Note that you cannot import Oracle Designer dataflow diagrams.)

After you import the Oracle Designer model, you can edit the relational and physical models as needed, and you can reverse engineer the logical model from the relational model.

If you have made changes to Data Modeler preferences but want to restore all to their original default values, you can follow these steps:

1. If you are running Data Modeler, exit.
2. Delete the folder or directory where your Data Modeler user information is stored. The default location is a build-specific directory or folder under the following:

   • Windows: C:\Documents and Settings\<user-name>\Application Data\Oracle SQL Developer Data Modeler

   • Linux or Mac OS X: ~/.datamodeler

   For example, if the current build-specific folder is named system3.1.0.678, delete C:\Documents and Settings\<user-name>\Application Data\Oracle SQL Developer Data Modeler\system3.1.0.678.

3. Start Data Modeler.

   This creates a folder or directory where your user information is stored (explained in step 2), which has the same content as when Data Modeler was installed.

If you have made changes to the shortcut key (accelerator key) mappings, you can restore the mappings to the defaults for your system by clicking Tools, then Preferences, then Shortcut Keys, then More Actions, then Load Keyboard Scheme, and then selecting Default.

You can save individual reports as RTF (a Microsoft Word format), HTML, or PDF files, and view each report when it is opened automatically on creation and open the files for viewing later. For HTML, several separate files are generated. The files are stored in the location specified or defaulted for Default Reports Directory under Data Modeler preferences.

Data Modeler ensures unique names for each file; for example, if you generate a report on all tables and if AllTablesDetails_1.rtf already exists, AllTablesDetails_2.rtf is created. (If you generate report files from the reporting repository in the reporting schema, the file names include _rs, for example, AllTablesDetails_1_rs.rtf.)

You can generate report files using either of the following approaches:

• Generate reports based on currently loaded designs. (This approach does not involve creating or using a reporting schema and reporting repository.)

• Generate reports based on information in the reporting repository in the reporting schema (which are explained in Using the Reporting Repository and Reporting Schema).

To generate and view a report stored in RTF, HTML, PDF, XLS, or XLSX format, follow these steps:

1. Click File, then Reports.

2. In the General tab, for Available Reports, select one of the types of objects for which to report information: Tables, Entities, Domains, Glossaries, and so on.

3. In Output Format, select HTML, PDF, or RTF for standard templates and HTML, PDF, XLS, or XLSX for custom templates.

4. In JVM Options, specify the memory allocation for PDF reports. The default value is -Xmx768M.

5. For Report Title, enter a name for the report.

6. For Report File Name, enter a name for the report file.

7. For Options, see Reports.

8. Under Templates, optionally, select a report template to use. You can use a report template to customize the types of objects to be included in a report. The following tabs are available:

   • Standard: Leave blank to use the default standard report format for the report type, or select an available modified standard format (if any exist). To create and manage standard report types, click Manage to display the Report Templates Management dialog box.

   • Custom: Lets you specify highly customized report formats. To create a new customized report format or to edit a selected existing one, click Manage to display the Report Templates Management dialog box.

     For Custom reports, Replace Boolean Values let you select nondefault True and False values.

9. Click the Objects tab.

10. Click one of the following tabs (if the desired tab is not already selected):

    • Loaded Designs, to generate a report based on one or more currently loaded Data Modeler designs

    • Reporting Schema, to generate a report based on designs in the reporting repository in the reporting schema

11. For Available Designs, select the desired Data Modeler design.

12. For Available Models, select the desired model. (The list of models reflects the type of objects for the report.)

13. For Report Configurations, you can select a configuration to limit the report to specific subviews or specific objects of the selected Available Report type, or you can leave it blank to select all subviews (if any) and all objects of the selected Available Report type.

    You can add, delete, or modify configurations by clicking Manage to display the Standard Reports Configurations dialog box.

14. Click Generate Report.

    A message is displayed with the location and name of the file for the report.

15. Go to the file and open it.

Related Topics

Data Modeler Reports

The Data Modeler reporting repository is a collection of database schema objects for storing metadata and data about Data Modeler designs. The schema in which the reporting repository is stored is called the reporting schema.

To set up the Data Modeler reporting schema, perform these steps outside the Data Modeler interface:

1. Create or specify a database schema to hold the reporting repository.

   It is recommended that you create a separate database user for the Data Modeler reporting repository, and use that schema only for the reporting repository. For example, create a user named DM_REPORT_REPOS, and grant that user at least CONNECT and RESOURCE privileges. (You can create the reporting repository in an existing schema that is also used for other purposes, but you might find that more confusing to keep track of.)

   If you want to continue using a reporting repository from an earlier version of Data Modeler, see the Reporting_Schema_Upgrade_readme.txt file in the datamodeler\datamodeler\reports folder.

2. Edit and run the Reporting_Schema_Permissions.sql script file, which is located in the datamodeler\datamodeler\reports folder.

   Before running the script, edit the file to replace <USER> and <OS DIRECTORY> with desired values:

   • <USER> is the schema to hold the reporting repository.

   • <OS DIRECTORY> is a temporary file system folder (directory) on the computer where the database is running. The script will create the definition in the database, but you must create this folder after you run the script.

Then, start Data Modeler and perform these steps:

1. Click File, then Export, then To Reporting Schema.

2. In the Export to Reporting Schema dialog box, click the Add Connection (+) icon.

3. In the New/Update Database Connection dialog box, enter a name for the connection (for example, dm_reporting_repos_conn), as well as the other information for the connection , including the user name and password for the database user associated with the reporting schema.

4. Optionally, click Test to test the connection. (If the test is not successful, correct any errors.)

5. Click OK to create the connection and to close the New/Update Database Connection dialog box.

6. Select (click) the connection name in the list of connections near the top of the dialog box.

7. Click OK to create the reporting repository in the schema associated with the selected connection, and to have the information about the selected models exported to that repository.

To see the Data Modeler reports, use SQL Developer, as explained in Using SQL Developer to View Exported Reporting Schema Data.

To delete an existing reporting repository, follow these steps in Data Modeler:

1. Click File, then Export, then To Reporting Schema.

2. Select the connection for the schema associated with the reporting repository to be deleted.

3. In the Export to Reporting Schema dialog box, click the Maintenance tab.

4. Click Drop Repository, then confirm that you want to drop the reporting repository.

   If you only want to deleted selected designs within the repository and not the entire repository, click Delete Designs and select the designs to be deleted.

For glossaries, you can perform the following operations using the Glossary tab of the Export to Reporting Schema dialog box:

• Export Glossary: Enables you to specify a Data Modeler glossary file, to have its information exported to the reporting repository.

• Delete Glossary: Enables you to select a glossary in the reporting repository, to have its information deleted from the repository.

Related Topics

Data Modeler Reports

You can use the reports feature in Oracle SQL Developer to view information that has been exported to the Data Modeler reporting repository. To export the information about a design to the reporting repository, follow the instructions in Using the Reporting Repository and Reporting Schema.

To view the reports in SQL Developer, you must do the following:

1. In SQL Developer, check to see if the Reports navigator already includes a child node named Data Modeler Reports. If it does include that node, go to the next step; if it does not include that node, install the Data Modeler Reports extension, as follows:

   Click Help, then Check for Updates. In the Check for Updates wizard, specify Install From Local File, and specify the following local file in the location where you installed Data Modeler: datamodeler\reports\oracle.sqldeveloper.datamodeler_reports.nn.nn.zip (Windows systems) or datamodeler/reports/oracle.sqldeveloper.datamodeler_reports.nn.nn.zip (Linux systems), where nn.nn reflects a build number.

2. In SQL Developer, open the Reports navigator, expand the Data Modeler Reports node, plus nodes under it as desired.

For each report that you want to view:

1. Double-click the node for the report name.

2. Select the database connection that you used for the reporting repository.

3. Complete the Bind Variables dialog information, and click OK. For the bind variables, the default values represent the most typical case: display all available information for the most recent version of the design.

   The bind variables enable you to restrict the output. The default value for most bind variables is null, which implies no further restrictions. To specify a bind variable, select the variable name and type an entry in the Value field. Any bind variable values that you enter are case insensitive. Bind variable values can contain the special characters % (percent sign) to mean any string and _ (underscore) to mean any character.

Data Modeler reports are grouped in the following categories:

Design Content reports list information about the design content (objects in the design).

Design Rules reports list information about the design rules as they apply to the logical and relational models.

1.8.3.1 Design Content reports

1.8.3.2 Design Rules reports

Before you can work with a Subversion repository through Data Modeler, you must create a connection to it. When you create a local Subversion repository, a connection to it is automatically created, and this can be seen in the Versions navigator. You can subsequently edit the connection details.

Existing files must be imported into the Subversion repository to bring them under version control. Files are then checked out from the Subversion repository to a local folder known as the «Subversion working copy». Files created in Data Modeler must be stored in the Subversion working copy.

Files newly created within Data Modeler must be added to version control. Changed and new files are made available to other users by committing them to the Subversion repository. The Subversion working copy can be updated with the contents of the Subversion repository to incorporate changes made by other users.

1.9.1.1 Pending Changes

To use Subversion with a Data Modeler design, you must have the following:

• A folder or directory on your local system to serve as the working directory for the design. You create the design in this working directory, save the design to this working directory, and open the design from this working directory.

• A Subversion repository to which you can connect, and in which you can create under branches a branch for the initial version of the design (and later any subsequent versions).

The following are suggested basic steps. They are not the only possible steps or necessarily the «best» steps for a given project. These steps reflect the use of the Versions navigator and the Import wizard within Data Modeler to perform many actions; however, many actions can alternatively be performed using a separate SVN repository browser (such as the TortoiseSVN browser) and using SVN commands on your local system.

1. On your local system, create a directory or folder to serve as the parent for design-specific working directories. For example, on a Windows PC create:

   C:\designs
   

2. On your local system, create a directory or folder under the one in the preceding step to serve as the working directory for the design you plan to create. For example, for a design to be named library, create:

   C:\designs\library
   

3. In Data Modeler, create the design (for example, the library design in Data Modeler Tutorial: Modeling for a Small Database), and save the design to the working directory that you created. For example, save the design to:

   C:\designs\library
   

   Saving the design causes the .dmd file and the related directory structure to be created in the working directory. (The .dmd file and the directory structure are explained in Database Design.)

4. Close the design. (Do not exit Data Modeler.)

5. Create an SVN connection to the repository that you want to use.

   1. In the Versions navigator, right-click the top-level node (Subversion) and select New Repository Connection.

   2. In the Subversion: Create/Edit Subversion Connection dialog box, complete the information. Example repository URL: https://example.com/svn/designs/

6. Create a branches directory under the repository path.

   1. In the Versions navigator, right-click the repository path and select New Remote Directory.

   2. In the Subversion: Create Remote Directory dialog box, complete the information, specifying the Directory Name as branches.

7. Create a project-specific branch under the branches directory.

   1. In the Versions navigator, right-click the branches directory and select New Remote Directory.

   2. In the Subversion: Create Remote Directory dialog box, complete the information. Example Directory Name: library

      For example, if you plan to create the library design in Data Modeler Tutorial: Modeling for a Small Database, the URL in the repository for this branch might be:

      https://example.com/svn/designs/branches/library
      

8. Use the Subversion: Import to Subversion wizard to import the design files into the repository. Click Team, then Import Files, and complete the wizard pages as follows.

   1. Destination: Specify the SVN connection and the repository path into which to import the files. Example: root/branches/library

   2. Source: Specify the source directory from which to import the files (that is, the directory containing the .dmd file and the design-specific folder hierarchy). Example: C:\designs\library

   3. Filters: Accept the defaults and click Next.

   4. Options: Accept the defaults and click Next.

   5. Summary: View the information and click Finish.

      The SVN Console Log shows the progress as files are added. After the files are added, the Handle New Files dialog box is displayed.

   6. In the Handle New Files dialog box, select Do Not Open Files and click OK.

   7. To see the files that have been added, click the Refresh icon in the Versions navigator tab.

For subsequent work on the design, follow the usual workflow for Subversion-based projects (SVN Update, SVN Lock, modify files, SVN Commit).