| Oracle® SQL Developer Data Modeler User's Guide Release 2.0 Part Number E13677-01 |
|
|
View PDF |
| Oracle® SQL Developer Data Modeler User's Guide Release 2.0 Part Number E13677-01 |
|
|
View PDF |
SQL Developer Data Modeler is a data modeling and database design tool that provides an environment for capturing, modeling, managing, and exploiting metadata. It is based on the Zachman framework and the Object Management Group (OMG) MetaObject Facility (MOF) and Common Warehouse Metamodel (CMW) specifications.
SQL Developer Data Modeler is a for-cost option of SQL Developer that requires a separate license. However, a free viewer (read-only) version is also available, as explained in Section 1.8.
This chapter contains the following major sections:
Section 1.1, "Installing and Getting Started with SQL Developer Data Modeler"
Section 1.2, "Data Modeler User Interface"
Section 1.3, "Working with SQL Developer Data Modeler"
Section 1.4, "Approaches to Data Modeling"
Section 1.5, "Data Modeler Options (User Preferences)"
Section 1.6, "Saving, Opening, Exporting, and Importing Designs"
Section 1.8, "Read-Only Data Modeler Viewer"
Section 1.9, "Additional Resources for Data Modeling"
To install and start SQL Developer Data Modeler, the process is similar to that for SQL Developer: you download a .zip file and unzip it into a desired parent directory or folder, and then type a command or double-click a file name. You should read any Data Modeler release notes or "readme" file before you perform the following steps.
Unzip the Data Modeler kit into a directory (folder) of your choice. This directory location will be referred to as <datamodeling_install>. For example, on a Windows system you might want to choose C:\Program Files as this location.
Unzipping the Data Modeler kit causes a directory named datamodeling to be created under the <datamodeling_install> directory. It also causes many files and folders to be placed in and under that directory.
To start Data Modeler, go to the datamodeling directory under the <datamodeling_install> directory, and do one of the following:
On Linux and Mac OS X systems, run sh datamodeling.sh.
On Windows systems, double-click datamodeling.exe.
If you are asked to enter the full pathname for java.exe, click Browse and find java.exe. For example, on a Windows system the path might have a name similar to C:\Program Files\Java\jdk1.6.0_06\bin\java.exe.
If you want to become familiar with data modeling concepts before using the interface, read the rest of this chapter before proceeding to the next step.
Do the short tutorial in Chapter 2, "Tutorial: Data Modeling for a Small Database". (For more advanced tutorials and other materials, see Section 1.9, "Additional Resources for Data Modeling".)
The SQL Developer Data Modeler window generally uses the left side for navigation to find and select objects, and the right side to display information about selected objects.
Figure 1-1 shows the main window.
The menus at the top contain some standard entries, plus entries for features specific to SQL Developer Data Modeler (see Section 1.2.1, "Menus for Data Modeler"), as shown in the following figure.
You can use shortcut keys to access menus and menu items: for example Alt+F for the File menu and Alt+E for the Edit menu; or Alt+H, then Alt+C for Help, then Contents. You can also display the File menu by pressing the F10 key.
Icons under the menus perform actions relevant to what is currently selected for display on the right side of the window, such as the logical model, a relational model, or a data flow diagram. For example, for a relational model the icons include New Table, New View, Split Table, Merge Tables, New FK Relation, and Generate DDL. To see the name of any icon, hover the pointer over the icon. The actions for the icons are also available from the Object menu.
The left side of the Data Modeler window has an object browser with a hierarchical tree display for data modeling objects, as shown in the following figure.
To select an object in the object browser, expand the appropriate tree node or nodes, then click the object.
The right side of the Data Modeler window has tabs and panes for objects that you select or open, as shown in the following figure, which displays information about a deliberately oversimplified relational model for library-related data (the model developed in Chapter 2, "Tutorial: Data Modeling for a Small Database").
To switch among objects, click the desired tabs; to close a tab, click the X in the tab. If you make changes to an object and click the X, you are asked if you want to save the changes.
This topic explains menu items that are of special interest for Data Modeler .
File menu
Open: Opens a SQL Developer Data Modeler design that had been saved or exported. For more information, see Section 1.6, "Saving, Opening, Exporting, and Importing Designs".
Close: Closes the current design without exiting SQL Developer Data Modeler.
Import: Lets you import models from a variety of sources. For more information, see Section 1.6, "Saving, Opening, Exporting, and Importing Designs".
Export: Lets you export models to files that can be imported into a variety of data modeling tools. For more information, see Section 1.6, "Saving, Opening, Exporting, and Importing Designs".
Print: Prints the currently selected diagram.
Print Diagram: Saves the currently selected diagram to an image file of the type associated with the file extension that you specify (.png or.jpg), to a PDF file, or to a scalable vector graphics (.svg) file.
Recent Designs: Lets you open a SQL Developer Data Modeler design that you recently worked on.
View menu
Contains options that affect what is displayed in the SQL Developer Data Modeler interface.
Browser: Displays the object browser, which shows data modeling objects in a hierarchical tree format.
Navigator: Displays a graphical thumbnail representation of the view that is currently selected in the right side of the window.
Log: Displays the Log pane with a record of SQL Developer Data Modeler actions.
View Details: Controls the level of detail in displays.
Logical Diagram Notation: Controls whether Barker or Bachman notation is used to display the logical model.
DDL File Editor: Lets you generate DDL statements for a selected physical model. Displays the DDL File Editor dialog box. (This command is equivalent to clicking the Generate DDL icon when a relational model is selected, or clicking Object, then Relational, then Generate DDL.)
Zoom In (and corresponding icon): Displays more detail, and potentially fewer objects, in the currently selected diagram.
Zoom Out (and corresponding icon): Displays less detail, and potentially more objects, in the currently selected diagram.
Fit Screen (and corresponding icon): Makes all relevant objects fit in the window for the currently selected diagram, adjusting the sizes of shapes and text labels as needed.
Find: Displays a dialog box for finding objects in the currently selected diagram. Useful for finding objects in large, complex diagrams.
Design menu
Contains options for generating models.
Engineer to Relational Model: 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: Performs reverse engineering: updates the logical model from the selected relational model.
Object menu
Contains commands for performing the actions that are available for the currently selected diagram (for example, for the logical model, a relational model, or a data flow diagram). The icons are displayed under the menus and above the tabs for selecting diagrams to view.
Physical menu
Contains commands for opening, closing, and saving a physical model.
Tools menu
Invokes SQL Developer Data Modeler tools and lets you set certain options (user preferences).
Domains Administration: Lets you view, modify, add, and delete domains. Displays the Domains Administration dialog box.
Types Administration: Lets you view, modify, add, and delete logical types. Displays the Types Administration dialog box.
RDBMS Site Administration: Lets you view RDBMS sites (names associated with supported types of databases), and to add your own names (aliases) for convenience in creating physical models. Displays the RDBMS Site Editor dialog box.
Table to View Wizard: Lets you create views based on tables in a selected relational model. Displays the Table to View wizard.
Name Abbreviations: Specifies a .csv file with strings to be changed in names of relational model objects (for example, to ensure the use of standard abbreviations or spellings). Displays the Name Abbreviations dialog box.
Glossary Editor: Lets you create a new glossary file (if you specify a file name that does not exist) or edit an existing glossary file. Displays a file selection dialog box, and then the Glossary Editor dialog box.
Design Rules: Lets you check your current design for violations of Data Modeler design rules. Displays the Design Rules dialog box.
Engineering Status: Displays the Engineering dialog box.
Compare/Merge Models: Lets you open a design file, compare a relational model from the file with a relational model in the current design, and merge objects from one model into the other. After you select the design file, the Relational Models dialog box is displayed.
General Options: Lets you customize the behavior of SQL Developer Data Modeler. Displays the Data Modeler Options (User Preferences) dialog box.
Help menu
Displays help about SQL Developer Data Modeler.
Contents: Displays the help window.
About: Displays version-related information about SQL Developer Data Modeler.
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:
Set Classification Types: For entities or tables in a multidimensional model, lets you set the classification type for each: Fact, Dimension, Logging, Summary, or Temporary. (You can also specify colors to be used in diagrams for each classification type in the Diagram: Classification Types user preference.)
Apply Naming Standards to Keys and Constraints (relational models): Applies naming standardization rules specified for Relational - Others in the Naming Standard preferences to keys and constraints in the selected relational model.
Change Object Names Prefix: Specifies the new prefix to replace a specified current prefix for selected types of objects. Displays the Change Object Names Prefix dialog box.
In diagrams, if you right-click outside any displayed object, the context menu generally includes the following:
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.
Auto Route: Toggles the setting of the Line Auto Route option (see Diagram under Data Modeler Options (User Preferences)). 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.
View Details: Lets you view all available details for objects or only selected details.
Model Properties: Displays the dialog box for viewing and editing properties of the model.
In diagrams, if you right-click a line connecting two objects object, 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 contains 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 Subview from Selected: Creates a subview containing the selected objects. (See also Section 1.3.4.1, "Logical Diagram and Subviews" and Section 1.3.5.1, "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.
Format: Lets you specify colors and fonts for the selected objects.
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.
You can use SQL Developer Data Modeler to create, edit, and delete objects at different hierarchy levels in different kinds of models. Many objects have similar properties, and the methods for performing operations are usually consistent and intuitive. To perform operations on objects (create, edit, delete), you can often use the context menu in the object browser or the toolbar or the Object menu after selecting a diagram.
To perform an operation on an object using the object browser, right-click the appropriate node (or click the node and press Shift+f10) in the hierarchy, and select the command for the desired operation.
For example, to edit an entity, expand the Logical display so that all entities are visible, right-click the name of the entity to be edited, and select Properties.
To perform an operation using a diagram, select the tab for the diagram, and use either the toolbar icons or the Object menu commands.
For example, to create an entity, select the Logical tab; either click the New Entity toolbar icon or click Object , then Logical, then New Entity; then define the entity in the Entity Properties box. To edit an entity, either double-click its box in the diagram or right-click the box and select Properties.
Context Menus (right-click menus) in diagrams contain commands relevant for either the diagram generally or the object or objects currently selected.
For conceptual and usage information about specific kinds of objects, see the following topics:
SQL Developer 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 in a folder or directory that you specify, and subfolders or subdirectories are created as needed under it. The XML file contains pointers to information in these subfolders or subdirectories. For example, for a very basic design named my_db_model, the following hierarchy might be created starting at the folder or directory in which you created it:
my_db_model.xml
my_db_model
businessinfo
datatypes
subviews
logical
entity
subviews
mapping
pm
rdbms
rel
1
subviews
table
Additional subfolders or directories may be created later, for example, dataflows under pm if you create any data flow diagrams in the process model.
SQL Developer Data Modeler supports supertypes and subtypes in its logical model, but it also provides the data types model, to be CWM 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 SQL Developer Data Modeler
By importing from Oracle Designer repository. See Section 1.6.6, "Importing an Oracle Designer Model".
The data types model in SQL Developer 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.
The data types diagram contains graphical representations of structured data types and links between them, as shown in the following figure.
A structured type box contains the name of the object, its defined attributes, and its methods (if any). Diagram links represent various kinds of attributes with a structured data type.
When you are working with a complicated data types model, you may want to create subviews, with each subview describing only a section of that model. You can define several data types subviews for a single data types model, and you can assign a structured type to more than one subview. However, links (references) between two structured types are displayed on the complete data types model and only on subviews to which both types have been assigned.
There is no difference between performing changes in a subview or in the complete data types model. Any changes made are immediately reflected in the complete model and any relevant subviews. However, you can remove a structured type from a subview without deleting it from the data types model.
A user-defined distinct type is a data type derived from an existing logical type, defined in Types Administration dialog box. A distinct type shares its representation with an existing type (the source type), but is considered to be a separate and incompatible type.
A distinct type object can be accessed only in the Distinct Types subfolder of the Data Types folder.
You can create new distinct types or edit the properties of existing distinct types.
Structured types are user defined data types have attributes and methods. They also can be part of a supertype and subtype inheritance hierarchy. A structured type can be defined based on a basic data type, a distinct type, another structured type, or a reference to structured type, or it can be defined as a collection type.
A table or entity can be defined as based on a structured type. Type substitution enables you to describe (graphically on a diagram) instances of which subtypes can be accommodated by the table (entity).
Table column or entity attributes can be defined as based on a structured type, a reference to structured type, a collection type, a distinct type, and basic data types. Type substitution can be defined for a column based on a structured type, and a scope table can be defined for a column based on a reference to a structured type.
A structured type also includes a set of method specifications. Methods enable you to define behaviors for structured types. Like user-defined functions (UDFs), methods are routines that extend SQL. In the case of methods, however, the behavior is integrated solely with a particular structured type.
The expanded structured types subfolder lists all structured type objects, with the hierarchy of attributes and methods for each.
The Oracle Spatial SDO_GEOMETRY type is predefined as a structured type. In addition, you can create new structured types or edit the properties of existing structured types.
Collection types represent arrays or collections of elements (basic type, distinct type, structured type, or another collection) and are mapped to the Oracle VARRAY and nested table types.
You can create new collection types or edit the properties of existing collection types.
Logical types are not actual data types, but names that can be associated with native types or with domains. The presupplied logical types include several from Oracle Multimedia (names starting with ORD); however, ORDIMAGE_SIGNATURE is deprecated and should not be used for new definitions.
You can create logical types and edit their mappings to native types (see Section 3.94, "Types Administration"), and you can associate a domain with a logical type (see Section 3.25, "Domains Administration").
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.
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 Section 1.3.3.1, "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 Section 1.3.3.2, "Transformation 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.
A formal, structured analysis approach employs the data flow diagram (DFD) to assist in the functional decomposition process. A data flow diagram consists of the following components:
External interactors, which are represented by rectangles
Data stores, which are represented by open rectangles (two or three sides)
Processes, which are represented by any rounded object (circle, oval, or square with rounded corners)
A process can represent a system function at one of various levels, from atomic through aggregate.
Data flows, which are represented by arrows, and optionally with labels indicating their content.
A transformation package is a package as defined in the Object Management Group (OMG) Common Warehouse Metamodel™ (CWM™) Specification, V1.1. This specification introduces transformation packages as follows:
"A key aspect of data warehousing is to extract, transform, and load data from operational resources to a data warehouse or data mart for analysis. Extraction, transformation, and loading can all be characterized as transformations. In fact, whenever data needs to be converted from one form to another in data warehousing, whether for storage, retrieval, or presentation purposes, transformations are involved. Transformation, therefore, is central to data warehousing.
"The Transformation package contains classes and associations that represent common transformation metadata used in data warehousing. It covers basic transformations among all types of data sources and targets: object-oriented, relational, record, multidimensional, XML, OLAP, and data mining.
"The Transformation package is designed to enable interchange of common metadata about transformation tools and activities."
At the core of SQL Developer 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 SQL Developer Data Modeler
By importing models in a VAR file, such as those created by Sterling COOL:DBA V2.1 or Sterling Bsnteam V7.2, Cayenne Bsnteam V7.2, Rational Rose, TogetherJ, JDeveloper, MEGA, or PowerDesigner v.12
By importing an existing model created by SQL Developer 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.
The logical model diagram contains graphical representations of entities, views, and links (relations and inheritances) between them.
When you are working with a complex logical model, you may want to create subviews, each describing only a section of that model. You can define several logical subviews for a single logical model, and you can assign entities and views to more than one subview. Links (relations) between two entities are displayed on the complete logical model and on logical subviews to which both referenced entities have been assigned.
There is no difference between performing changes in one of the subviews or in the complete logical model. Any changes made are immediately reflected in the complete logical model and any relevant subviews. However, you can remove entities and views from a subview without deleting them from the complete logical model.
To create a subview containing specific entities, you can select the desired entities in the logical model diagram, right-click, and select Create Subview from Selected.
Data Modeler supports the following alternatives for logical model diagramming notation:
Bachman notation
Barker notation
Detailed explanations and examples of each notation style are widely available in textbooks and on the Web. You can set the default notation type for new logical diagrams in the Data Modeler Options (User Preferences) (General Options, Diagram, Logical).
To switch from one notation type to the other (and to see the differences for a diagram), select the logical model diagram and click View, then Logical Model Notation, then the notation that is not the current one.
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.
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.
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.
Inheritance defines a hierarchy of entities based on supertypes and subtypes. The supertype and subtype entities represent part of a system that has a recognizable subset of occurrences of an existing entity type. The subsets) are referred to as entity subtypes, with the original entity type being the supertype.
All attributes and relationships of the supertype must belong to all of its subtypes. However, some attributes and relationships of the subtype are added to those of the supertype. Subtypes are usefully defined where an identifiable group of entity occurrences has attributes in addition to those of the supertype.
A relation (data relationship) is a natural association that exists between two or more entities. Cardinality defines the number of occurrences of one entity for a single occurrence of the related entity.
The relationship can be identifying or not identifying, and with a cardinality of 1:1 (one-to-one), 1:N (one-to-many), or N:M (many-to-many). A relationship with N:M cardinality is mapped to a reference table in the relational model. An identifying relationship indicates that the relationship is a component of the primary identifier for the target entity.
An arc is an exclusive relationship group, which is defined such that only one of the relationships can exist for any instance of an entity. For example, a seminar may be able to be taught by a staff member or an external consultant, but not by both. As examples, a seminar for new employees can be taught only by a corporate staff member, while a seminar in using Product XYX can be taught only by an external consultant with special qualifications.
All relations included in an arc should belong to the same entity and should have the same cardinality Any foreign unique identifier (foreign UID) attributes belonging to relationships in an arc should be transferred as Allow Nulls during forward engineering. The meaning of mandatory relationships in an arc is that only one relationship must exist for a given instance of an entity.
To create an arc, do so after creating all the relationships to be included. Select the entity box, select all relationship lines to be included (hold Shift and click each line), and click the New Arc button in the toolbar or click Object, then Logical, then New Arc.
Type substitution is a subclassing mechanism that complements inheritance. Type substitution on the entity level take place only if the following are defined:
Supertype/subtype inheritance between two structured types
Entities based on the structured types which form a data type inheritance hierarchy (supertype/subtype inheritance)
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 is 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 SQL Developer Data Modeler
By forward engineering from the logical model or a subview of the logical model
By importing models in a VAR file, such as those created by Sterling COOL:DBA V2.1 or Sterling Bsnteam V7.2, Cayenne Bsnteam V7.2, Rational Rose, TogetherJ, JDeveloper, MEGA, or PowerDesigner v.12
By importing an existing model created by SQL Developer 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. A relational model can contain one or more physical models.
All relational model objects are displayed in the object browser tree.
The relational diagram contains graphical representations of tables, views, and links between them.
When you are working with a complex relational model, you may want to create subviews, each describing only a section of that model. You can define several relational subviews for a single relational model, and you can assign tables and views to more than one subview. Links (relations) between two tables are displayed on the complete relational model and on relational subviews to which both referenced tables have been assigned.
If you import from the data dictionary and select more than one schema to import, a relational model is created for all the schemas and a subview is created for each schema.
There is no difference between performing changes in one of the subviews or in the complete relational model. Any changes made are immediately reflected in the complete relational model and any relevant subviews. However, you can remove tables and views from a subview without deleting them from the complete relational model.
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.
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.
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.
A relation (data relationship) is a natural association that exists between two or more tables. Relationships are expressed in the data values of the primary and foreign keys. Cardinality defines the number of occurrences in one table for a single occurrence in the related table.
An identifying relationship indicates that the relationship is a component of the primary identifier for the target table.
An exclusive relationship (arc) specifies that only one of the relationships can exist for a given instance in the table. For example, a seminar may be able to be taught by a staff member or an external consultant, but not by both. As examples, a seminar for new employees can be taught only by a corporate staff member, while a seminar in using Product XYX can be taught only by an external consultant with special qualifications.
All relationships in an arc should belong to the same table, and should have the same cardinality. Any foreign key (FK) attributes belonging to relationships in an arc should be transferred as Allow Nulls during forward engineering. The meaning of mandatory relationships in an arc is that only one relationship must exist for a given instance in the table.
To create an arc, do so after creating all the relationships to be included. Select the table box, select all relationship lines to be included (hold Shift and click each line), and click the New Arc button in the toolbar or click Object, then Relational, then New Arc.
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.
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).
A cluster is a schema object that contains data from one or more tables.
An index cluster must contain more than one cluster, and all of the tables in the cluster have one or more columns in common. Oracle Database stores together all the rows from all the tables that share the same cluster key.
In a hash cluster, which can contain one or more tables, Oracle Database stores together rows that have the same hash key value.
A context is a set of application-defined attributes that validates and secures an application.
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.
A directory is an alias for a directory (called a folder on Windows systems) on the server file system where external binary file LOBs (BFILEs) and external table data are located.
You can use directory names when referring to BFILEs in your PL/SQL code and OCI calls, rather than hard coding the operating system path name, for management flexibility. All directories are created in a single namespace and are not owned by an individual schema. You can secure access to the BFILEs stored within the directory structure by granting object privileges on the directories to specific users.
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.
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.
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.
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.
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.
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.
A sequence is an object used to generate unique integers. You can use sequences to automatically generate primary key values.
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.
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.
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).
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.
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.
A tablespace is an allocation of space in the database that can contain schema objects.
A permanent tablespace contains persistent schema objects. Objects in permanent tablespaces are stored in data files.
An undo tablespace is a type of permanent tablespace used by Oracle Database to manage undo data if you are running your database in automatic undo management mode. Oracle strongly recommends that you use automatic undo management mode rather than using rollback segments for undo.
A temporary tablespace contains schema objects only for the duration of a session. Objects in temporary tablespaces are stored in temp files.
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.
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, e-mail, 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).
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 Section 3.12, "Contact Properties".)
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 Section 3.23, "Document Properties".)
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 Section 3.26, "Email Properties".)
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 Section 3.54, "Location Properties".)
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 Section 3.74, "Resource Locator Properties".)
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 Section 3.75, "Responsible Party Properties".)
A telephone object represents telephone contact information. A telephone object can be associated with one or more contacts. (See also Section 3.89, "Telephone Properties".)
When modeling data, you can choose an approach best suited to the nature of the work to be done. The approaches to data modeling include the following: designing a new database, developing a design for an existing database, or performing maintenance on an existing database design
Top-Down Modeling: for designing a new database
Bottom-Up Modeling: for creating a database based on extracting metadata from an existing database or using the DDL code obtained from an implementation of an existing database
Targeted Modeling: for adapting a database to new requirements
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.
Develop the business information.
Create documents. In the object browser, right-click Logical and select Properties, then click Documents and add items as appropriate.
Create responsible parties with contacts, e-mail 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.
Define any other information. In the object browser, right-click Logical and select Properties, then modify other properties (Naming Options, Comments, Notes) as needed.
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.
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.
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.
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.
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
Develop the logical model.
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.
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.
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.
Forward engineer the logical model to a relational model. Click Design, then 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.
Develop the multidimensional model, if needed.
Create cubes.
Create levels.
Create dimensions.
Create links.
Apply design rules for the multidimensional model.
Export the multidimensional model, as needed.
Develop one or more relational models, doing the following for each as needed.
Split tables. To split one table into two, select the table on the relational model diagram, and either click the Split Table button or click Object, then Relational, then Split Table.
Merge tables. To merge a table into another table (removing the merged table), either click the Merge Table button or click Object, then Relational, then Merge Tables. 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.)
Check design rules for the relational model. Click Tools, then Design Rules.
Develop one or more physical models for each relational model, doing the following for each.
Open a physical model.
Check design rules for the physical model. Click Tools, then Design Rules.
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.
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.
As needed, modify the relational model and create additional relational models.
As needed, denormalize the relational model or models. Perform the following steps iteratively, as needed. on each model.
Split or merge tables, or do both.
To split one table into two, select the table on the relational model diagram, and either click the Split Table button or click Object, then Relational, then Split Table.
To merge a table into another table (removing the merged table), either click the Merge Table button or click Object, then Relational, then Merge Tables. 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.)
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.
Reverse engineer the logical model from a relational model. Click the Engineer to Logical Model icon, or click Design, then Engineer to Logical Model.
As needed, modify the logical model.
Check design rules for the logical model. Click Tools, then Design Rules.
Save the design.
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.
Note:
Maintaining a database with SQL Developer Data Modeler requires that the design and the actual database implementations be fully synchronized. If you are not sure if this is the case, you should consider the designs outdated and perform the procedures in Section 1.4.2, "Bottom-Up Modeling".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:
For each logical model object (entity, attribute, relation, and so on) that you want to modify, modify its properties. For example, to add an attribute to an entity:
Double-click the entity's icon in the Logical diagram (or right-click the entity name in the object browser and select Properties).
In the Entity Properties dialog box, click Attributes.
Click the Add (+) icon and specify the attribute properties.
When you are finished modifying the logical model, forward engineer the changes to the relational model or models by clicking the Logical pane and clicking Design, then Engineer to Relational Model.
In the Engineering dialog box, specify any desired filtering, then click Engineer.
To start with changes to a relational model:
For each relational model object (table, column, and so on) that you want to modify, modify its properties. For example, to add a column to a table in a relational model:
Double-click the table's icon in the diagram for the relational model (or right-click the table name in the object browser and select Properties).
In the Table Properties dialog box, click Columns.
Click the Add (+) icon and specify the column properties.
When you are finished modifying the relational model, reverse engineer the changes to the logical model by clicking the pane for the relational model and clicking Design, then Engineer to Logical Model.
In the Engineering dialog box, specify any desired filtering, then click Engineer.
You can customize many aspects of SQL Developer Data Modeler by clicking Tools, then General Options.
Many preferences are self-explanatory, and this topic explains only those whose meaning and implications are not obvious. The preferences are grouped in the following categories:
The General pane contains options that affect the startup and overall behavior and appearance of SQL Developer 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 designs.
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.
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.
The Model pane contains options that apply to several types of models.
Default RDBMS Type: Default database type.
Default RDBMS Site: Default site within the default database type.
Columns and Attributes Defaults: Nulls Allowed: Controls whether new columns and attributes are allowed to have null values. If this option is disabled, new columns and attributes are by default mandatory (value required).
Model: Logical
Contains options that apply to the logical model.
Relation Cardinality: Source Optional: Controls whether the source entity in a relationship must, by default, contain one or more instances. If this option is enabled, source instances are not required for all relationship types; if this option is disabled, one or more source instances are required for all relationship types.
Relation Cardinality: Target Optional: Controls whether the target entity in a relationship must, by default, contain one or more instances. If this option is enabled, target instances are not required for all relationship types; if this option is disabled, one or more target instances are required for all relationship types.
Use and Set First Unique Identifier as Primary Key: Controls whether, by default, the first unique identifier attribute is set as the primary unique identifier when you create an entity.
Foreign UID Attribute Name Synchronization: Keep as the Name of the Originating Attribute: Controls whether the supertype or referenced attribute must be used in foreign unique identifier naming. To be able to specify some other name, deselect this option.
Model: Relational
Contains options that apply to a relational model.
Delete FK Columns Strategy: Specifies what Data Modeler should do when you attempt to delete a table that has one or more generated foreign key columns (columns in other tables) pointing to it: delete the foreign key columns, do not delete the foreign key columns, or ask to confirm the foreign key column deletions.
For example, using the relational model in Chapter 2, "Tutorial: Data Modeling for a Small Database", if you delete the Books table, the Transactions table contains the book_id foreign key column that refers to the primary key of the Books table. Your choice for this option determines what happens to the Transactions.book_id column if you delete the Books table.
Default Foreign Key Delete Rule: Specifies what happens if a user tries to delete a row containing data that is involved in a foreign key relationship:
No Action causes an error message to be displayed indicating that deletion is not allowed; the deletion is rolled back.
Cascade deletes all rows containing data that is involved in the foreign key relationship.
Set Null sets the value to null if all foreign key columns for the table can accept null values.
Model: Physical
Contains options that apply to a physical model. Different options apply to each supported type of database.
The Diagram pane contains general options that affect the appearance of model diagrams.
General: Show Grid: Controls whether a grid is displayed in the background on diagrams. Seeing the grid can help you to align objects vertically and horizontally on the diagram.
General: Synchronize with Tree: Controls whether the focus on an active diagram is automatically moved to reflect the selection of objects under that model in the object browser.
General: Diagram Color: Displays a dialog box for selecting the color scheme for the background on diagrams.
General: Line Auto Route: Controls whether lines representing relations, foreign key relations, inheritances, flows, and other relationships are automatically drawn on diagrams. If you deselect this option, you determine how these lines are drawn; for example, you may want to add or move break points manually, in order to enhance the clarity of your models. For more information about Auto Route and drawing lines, see the explanation of the Auto Route command in Section 1.2.2, "Context Menus".
Process Model: Show Flow Name: Controls whether the flow name is displayed in a box on each flow line in data flow diagrams.
Diagram: Logical Model
Contains options that apply to the diagram of the logical model.
Notation Type: Notation type: Barker (sometimes called "crow's foot") or Bachman.
Show Source/Target Name: Controls whether the Name on Source and Name on Target values (in the Cardinality pane of the Record Structure Properties dialog box) are displayed. If they are displayed, you can format the text and move the boxes.
Box-in-Box Presentation for Entity Inheritances: Displays subtypes in a box inside their supertype's box.
Domains Presentation: Specifies what is displayed as the data type for an attribute based on a domain: Domain Name causes the domain name to be displayed; Used Logical Type causes the logical type used in the domain definition to be displayed.
Diagram: Relational Model
Contains options that apply to a diagram of a relational model.
Foreign Key Arrow Direction: Controls whether arrowhead points toward the primary key or the foreign key in foreign key relationship arrows.
Show Foreign Key Name: Controls whether a text box containing the foreign key name is displayed on the foreign key relationship arrow.
Specifies colors for the display of different classification types in a multidimensional model.
Diagram: Format
Specifies options for default fonts and colors for objects and default widths and colors for types of lines.
The Naming Standard pane lets you implement naming standardization: you can view, add, and modify naming standards for logical and relational model objects and for domains. These standards will be checked when you apply Design Rules, and any violations of the standards will be reported as errors or warnings.
Do not confuse naming standardization with using the Name Abbreviations dialog box, which makes immediate name changes to enforce consistency in spellings and abbreviations, and which is limited to relational model name strings.
Logical, Relational, Domains
For logical model entities and attributes, relational model tables and columns, and domains, you can add, rearrange, and make optional or mandatory any of the following components of object names: prime word, class word, modifier, and qualifier. The acceptable values of these components are specified in the glossary file or files that you specify in the Glossary pane.
Title Case (Separator option): Refers to capitalizing each "word" and not including spaces: for example, GovernmentAccounts. (Title case is sometimes called CamelCase.)
Abbreviated Only: If this option is enabled, non-abbreviated words cannot be used in relational model object names (that is, only abbreviated words can be used).
For explanations of other terms, see Section 3.39, "Glossary Editor". For an excellent discussion of naming standards, see the United States Coast Guard Data Naming Element Standards Guidebook.
Relational - Others
For various kinds of constraints in relational models, you can edit the format string and add variable string elements.
Example: To see a sample name in a currently specified format, select the desired constraint type (for example, Foreign Key).
Glossary
You can add one or more glossary files to be used in naming standardization. (For more information about glossaries, see Section 3.39, "Glossary Editor".)
The DDL pane contains general options for Data Definition Language (DDL) statements in code to be generated.
Statement Termination Character for DB2 and UDB: Termination character for DDL for IBM DB2 and UDB databases.
Create Type Substitution Triggers for Oracle and UDB: Controls whether triggers are created for type substitutions in Oracle and IBM UDB physical models.
Create Triggers for FK Arc Constraint: Controls whether triggers are created in generated DDL code to implement foreign key arc constraints.
Create Triggers for Non Transferable FK: Controls whether triggers are created for non-transferable foreign key relationships. (Whether a foreign key relationship is transferable is controlled by the Transferable (Updateable) option in the Foreign Key Properties dialog box.)
Show CHAR/BYTE Unit for Oracle Varchar2 and Char Types: Controls whether, for attributes of Oracle type CHAR or VARCHAR2, the unit (CHAR or BYTE) associated with the attribute length is included for columns based on the attribute in relational model diagrams and in generated CREATE TABLE statements.
Use 'Data Type Kind' Property in Compare Functionality: Controls whether the data type kind (such as domain, logical type, or distinct type) should be considered to prevent types of different kinds from generating the same native data type (for example, preventing a domain and a logical type from resulting in Number(7,2)).
DDL: DDL/Migration
Lets you specify one or more pairs of string replacements to be made when DDL statements are generated. Each pair specifies the old string and the new string with which to replace the old string.
Selected: Controls whether the specified replacement is enabled or disabled.
Case Sensitive: Controls whether the replacement is done only if the case of the old string in the DDL exactly matches the case specifies for the old string.
The Third Party JDBC Drivers pane specifies drivers to be used for connections to third-party (non-Oracle) databases. SQL Developer Data Modeler needs to use a JDBC driver for some operations, such as obtaining metadata from the third-party database.
Oracle does not supply non-Oracle drivers. To access any non-Oracle databases that require the use of drivers other than ODBC/JDBC (which are included in Java), you must download the files for the necessary drivers, and then add them using this pane. To download drivers, use the appropriate link at the third-party site. For example:
For Microsoft SQL Server 2000: http://www.microsoft.com/downloads/details.aspx?familyid=07287B11-0502-461A-B138-2AA54BFDC03A&displaylang=en (Be sure to install all three .jar files: msbase.jar, mssqlserver.jar, and msutil.jar.)
For Microsoft SQL Server 2005: http://www.microsoft.com/downloads/details.aspx?familyid=C47053EB-3B64-4794-950D-81E1EC91C1BA&displaylang=en
For IBM DB2/LUW, the IBM Data Server Driver for JDBC and SQLJ at: http://www-01.ibm.com/software/data/db2/express/additional-downloads.html
For each driver to be added, click the Add (+) icon and select the path for the driver. Third-party databases and their required driver files include:
Microsoft SQL Server 2000: msbase.jar, mssqlserver.jar, and msutil.jar
Microsoft SQL Server 2005: sqljdbc.jar
IBM DB2/UDB: db2jcc.jar
To store a design (or parts of a design) that you are working on, you can save or export it.
Saving a design enables you to save all elements of the design: the logical model, relational models, physical models, process model, and business information. An XML file and a directory structure (described in Section 1.3.1, "Database Design") are created for a new design or updated for the existing design, which is stored in SQL Developer Data Modeler format.
To save a design, click File, then Save. If the design was not previously saved, specify the location and XML file name. To save a design in a different file and directory structure. click File, then Save As.
Exporting a design enables you to save parts of the design (logical model, relational models but no physical models, and data types model) to a file. You can export in a variety of formats, both non-Oracle and Oracle. Thus, exporting provides flexibility in output formats, but saving enables you to save more design objects if you only need SQL Developer Data Modeler output.
To export a design, click File, then Export, then the output format.
To use a design that had been saved, you can open it by clicking File, then Open. Opening a design makes all models and objects in the saved design available for you to work on. Any saved physical models are not initially visible in the object browser; however, you can make a physical model visible by right-clicking Physical Models under the desired relational model, selecting Open, and then specifying the database type (such as Oracle 11g).
To use a design that had been saved by SQL Developer Data Modeler, or exported or saved by another data modeling tool, you can import it by clicking File, then Import, then the type of design to be imported. Usually, you specify a file, and then use a wizard that enables you to control what is imported.
Any text file that you open or import must be encoded in a format supported by the operating system locale setting. For information about character encoding and locales, see Oracle Database Globalization Support Guide.
The rest of this section contains information about importing from specific types of files and other sources.
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 Cube Views metadata enables you to create a multidimensional model based on an existing implementation, as reflected in a specified XML file.
Importing from Microsoft XMLA enables you to create a multidimensional model stored in the Microsoft XMLA file format.
Importing an ERwin file enables you to capture models from the ERwin modeling tool. Specify the XML file containing definitions of the models to be imported.
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.
In the wizard for importing an Oracle Designer model, 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.
Importing a SQL Developer Data Modeler design enables you to capture the logical model and any relational and data type models from a design previously exported from SQL Developer Data Modeler.
Importing a domain enables you to change and extend the existing domain definitions. In the Import Domains dialog box, select the domains to be imported and deselect (clear) the domains not to be imported.
You can export the information about a Data Modeler design to a database schema (called the reporting repository), and you can then use the reports feature in SQL Developer to view reports about that Data Modeler design.
The reporting repository is a collection of schema objects for storing metadata and data about Data Modeler designs. 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.)
To export the information about a design to the reporting repository, click File, then Export, then To Reporting Schema.
To view the reports in SQL Developer, you must do the following:
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.
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:
Double-click the node for the report name.
Select the database connection that you used for the reports repository.
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.
Design Content reports list information about the design content (objects in the design).
Data Types Model: Contains reports related to the Data Types Model.
Logical Model: Contains reports related to the Logical Model.
Relational Model: Contains reports related to the Relational Models.
Design Rules reports list information about the design rules as they apply to the logical and relational models. (See the information about the Design Rules dialog box.)
Logical Model: Contains reports related to the Logical Model.
Relational Model: Contains reports related to the Relational Models.
SQL Developer Data Modeler Viewer is a free read-only version of SQL Developer Data Modeler that enables you to open, import, and view a database design, and to save it separate from the original design. However, you cannot create, modify, or delete any objects.
The online help is the same for both SQL Developer Data Modeler and the free viewer; thus, the help includes information about features that are not supported in the viewer.
See the following for more information, including advanced materials, about data modeling:
SQL Developer home page (OTN), which includes links for white papers, viewlets (screen demonstrations), Oracle by Example (OBE) tutorials, and other materials: http://www.oracle.com/technology/products/database/sql_developer/
Object Management Group (OMG) site (http://www.omg.org/), especially the MetaObject Facility (MOF) and Common Warehouse Metamodel (CMW) specifications
United States Coast Guard Data Naming Element Standards Guidebook (http://www.uscg.mil/directives/ci/5000-5999/CI_5230_42A.pdf), especially concepts and recommendations relating to naming standards
|
 Copyright © 2008, 2009, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|