Oracle SQL Developer Data Modeler - Feature List

January 2011

Introduction Subject Area Management
Logical Models Impact Analysis
DataTypes Models Naming Standards
Relational Models Design Rules
Dimensional Models Importing
Data Flow Diagrams Reporting
Physical Models (Database specific) Integrated Version Support
Domains Preferences


Oracle SQL Developer Data Modeler provides a model driven approach for database design and generation, implemented by integrated set of models – Logical, Data types, Dimensional, Relational, Data Flow diagrams and Physical models for supported Oracle Databases, Microsoft SQL Server 2000 and 2005, IBM DB2/390 and DB2 LUW V7 and V8 representing different aspects or level of details and vendor specifics of the modeled information system.

Below is a high-level list of the features the product supports. To review the new functionality added to SQL Developer Data Modeler 3.0, see the separate New Features document. For more detailed information and product usage, please use the product help or the documentation. There are also online demonstrations and tutorials about many of the features listed.

Logical Models
  ER diagramming with support for Barker and Bachman notation. Partial support for Information Engineering notation.
  Entity hierarchies with three strategies for engineering to relational models
  Box-in-box presentation for hierarchies (supertypes and subtypes)
  Support for mutually exclusive relationships (arcs)
  Volumetric information
  Structured types used in addition to classical ER diagramming
  Engineering to and from relational models (create, compare and synchronize changes)

Import from

  • Oracle Designer’s repository
  • CA Erwin Data Modeler 4.x models
  • Bachman Enterprise model
  • XMLA scripts
  • Cube Views metadata
  Compare and merge with logical model in another design
DataTypes Models
  Support (create and visualize) for structured, collection and distinct types in SQL99
  Support for inheritance, references and containment of structured types
  Support for specific implementation of the above for Oracle and IBM DB2/UDB
  Definitions used in Logical and Relational model
Relational Models
  Vendor agnostic implementation of concepts in Logical model
  Define sensitive and personally identifiable information
  Define UI defaults at column level
  Volumetric information
  Mutually exclusive (arcs), mandatory (optional) and transferable foreign keys
  Automatic propagation and synchronization of foreign key columns
  Support for Oracle spatial properties

Visual representation of type substitution on relational diagrams. There is specific implementation for object tables in Oracle and IBM DB2/UDB.

  • Oracle - Defines (restricts) instances of which subtypes can be accommodated by table
  • IBM DB2/UDB - Defines exact hierarchies of typed tables that corresponds to hierarchies of structured types
  Define (restrict) type substitution at column level
  Import DDL scripts or import from database dictionary

Import from

  • Oracle Designer repository
  • CA Erwin Data Modeler 4.x and CA Erwin Data Modeler 7.x models
  • Bachman DBA design
  • XMLA scripts
  • Cube Views metadata
  Compare and/ or merge to models in other designs, status of database dictionary or status in DDL scripts
  Added support for auto increment and identity columns in the Relational model. This is supported with sequences and triggers in the Oracle physical model.
  Support for dynamic properties – can be created and modified using UI and/or transformation scripts.
Dimensional Models
  Dedicated full featured Dimensional model – star and snowflake schema easily can be built and expressed on detailed and compact diagrams
  Dimensions – support for merging (level can belongs to more than one dimension), shared, fact and role playing dimensions
  Hierarchies – value based hierarchies (parent-child), and regular and ragged level based hierarchies
  Measures – fully, semi and none additive; different aggregation functions on different dimensions ; fact dimension; calculated measures
  Query wizard allows Select statements to be generated from the dimensional model
  Support for Oracle OLAP. This includes specifics like cube partitioning, sparse dimensions, and compressed measures
  Built-in wizards help to define all required object types and view definitions that enable SQL access to dimensional data in Oracle AW (using the OLAP_TABLE interface)
  Bidirectional integration with Oracle physical model - Dimensional model can be created using SQL dimension definitions in physical model or the definitions can be created from the dimensional model
Data Flow Diagrams
  Based on the logical model
  Primitive and composite processes with unlimited levels of decomposition, reusable transformation tasks, triggering events, information stores, external agents
  Simple and component flow; information structures that define transferred data elements (defined in logical model)
  Source – target mapping of data elements processed in primitive processes
CRUD dependencies between primitive process and data elements
  Supports domains with validation rules in form of list of values, range of values and check constraint
  Domains can be managed at installation and design level – sets of domains can be created and used in different designs depending on modeled area
  Easy exchange and synchronization of domains using import of domains functionality
  Domains can be assigned to group of attributes and columns in different models
  Set “Default value” property at domain level. This can be updated at column/attribute level. A List of values defined at domain, column or attribute level is used to pick-up default value.
  Use domains definitions in Data types model.
Subject Area Management
  Subviews can be used to represent objects related to given subject area
  Subviews can be nested(linked) thus allowing to build network (or hierarchy) of related subviews – navigation between linked subviews is supported

User defined classification types (and related to them color and name prefix) can be used to highlight objects belonging to different subject areas

Impact Analyses
  For Logical and Relational models – unlimited tracking of dependencies of objects, show correspondence (mapping) between tables and entities, columns and attributes, their usage in dimensional model and data flow diagrams, usage inside the model element belongs. This tracking is not limited to first level of dependencies only. For example, the PK column can have dependent foreign key columns and their impact analyses is part of initial dependencies tracking. You can see the usage of the FK column in the definition of the view column, and that column can be used in definition of another view and so on.
  Structured, collection and distinct types – shows their usage in definition of other modeled elements – columns, attributes, tables, entities, structured types and collection types
  Domains – their usage in definition of columns and attributes
  Usage of object in diagrams can be seen using “Go To diagram functionality
Naming Standards
  Glossary for permitted prime, class, modifier and qualifier words
  Name structure for elements in Logical and Relational models
  Model level restrictions for – name length, possible characters, used letter case
  Name translation during engineering between logical and relational models
  Naming templates for table constraints and indexes
  Prefix management
Physical Models
  Initial status of new model can be cloned from existing compatible model
  • Oracle
Support for following objects – clusters, contexts, dimensions (including levels and hierarchies), directories, disk group, role and user with their privileges and permissions, external tables, materialized views, bitmap join indexes, procedures, packages, functions, triggers and instead of triggers, LOB storage settings, object types and tables, VARRAY and nested tables, partitioned tables, global and local partitioned indexes, subpartition templates, sequences, synonyms, tablespaces (including temporary and undo), rollback segments
  Templates can be defined for storage settings and used for group of objects
  Default templates for tables and indexes can be defined
  Schema controlled generation of DDL
  Generate DDL for database changes
  Generate advanced self controlled DDL script with logging, error masking, and execution window
  • MS SQL Server
Support for following objects – database, login, user and roles with their privileges and permissions, default, rule, user defined type, computed and identity columns, partitioned tables using partition function and partition scheme, primary and secondary XML indexes, triggers on tables and views, stored procedures, indexes on views, synonyms
  Domains can be created as user defined types
  Extended properties for each object can be defined and generated in DDL
  Database and schema controlled generation of DDL
  • IBM DB2/390
Support for following objects – alias, aux table, database, buffer pool, owner with privileges and permission settings, volumes, storage groups and related VCATs, triggers, stored procedures, synonyms, sequences, identity columns, index (v7) and table controlled partitioning, tablespaces, summary tables
  Domains can be created as distinct types
  Storage templates can be defined and used for group of objects
  Space calculation – estimates storage capacity needed
  Control files for loading DB2 data (exported) into Oracle can be generated
  Database and schema (owner) controlled generation of DDL
Support for following objects – alias, synonym, buffer pool, database, stored procedures, triggers, distinct and structured types, object tables and hierarchy of tables, sequence, schema
  Domains can be created as distinct types
  Schema controlled generation of DDL
Design Rules

Sets of predefined design rules can be used to validate correctness of information in all models.

  Create user-defined Design Rules.
  Group collections of design rules in Design Rule sets.
  Build libraries of Design Rules.
  Manage custom design rules and transformation scripts through version control.
  JDBC import from database dictionary and full support in physical models for Oracle, IBM DB2/390, IBM DB2/UDB and MS SQL Server
  Generic JDBC and ODBC/JDBC import
  DDL Scripts for Oracle, IBM DB2/390, IBM DB2/UDB V7 and V8, and Microsoft SQL Server 2000 and 2005
  Dimensional metadata in XMLA and Cube Views files
  Oracle Designer repository
  CA Erwin Data Modeler 4.x models saved as XML
  Information in Logical and Relational models (including diagrams in PDF format) and the use of entities and attributes in data flow diagrams can be exported into reporting schema
  Repository reporting supports data types and process models
  Each export creates new versions of the current design
  Predefined design rules and reports can be run using SQL Developer
  Integrated local reports: Run reports from the tool itself. Reports are word xml documents and as such can be opened in MS Word. These reports include entities tables, domains and glossary.
Version Support
  Integrated version control using Subversion for complete design.
  Allows you to compare and merge objects.
  Designs added to versioned folders are placed under version control.
  Users can see pending incoming and outgoing changes.
  Data Modeler recognizes versioned designs.
  Changes made to a versioned design and saved, are revealed in the pending changes dialog.
  Collaborative access and support is provided through tight integration with Subversion.
  Custom rules and transformation scripts are stored in a single file. This file can be managed under version control
  Set of preferences to control default properties for models.
  Table to View wizard creates views based on tables in a selected relational model.
View to Table wizard creates tables based on view definitions a selected relational model.
Foreign Key discovery helps find possible foreign keys in a relational model, based on selected criteria.