<Insert Picture Here> Introducing Data Modeling and Design with Oracle SQL Developer Data Modeler

<Insert Picture Here>

Introducing Data Modeling and Design with Oracle SQL Developer Data Modeler

Sue Harper

The following is intended to outline our general

product direction. It is intended for information

purposes only, and may not be incorporated into any

contract. It is not a commitment to deliver any

material, code, or functionality, and should not be

relied upon in making purchasing decisions.

The development, release, and timing of any

features or functionality described for Oracle’s

products remains at the sole discretion of Oracle.

Agenda

•

_{What is SQL Developer Data Modeler?}

•

_{Introduction to SQL Developer Data Modeler}

– _Audience – _Technology – _{Feature Review}

•

_Packaging

•

_{Future Direction}

•

_{Customer Feedback}

•

_Summary

What is SQL Developer Data Modeler?

•

_{A diagramming and data modeling tool}

•

_{A single tool for different users and functionality}

– _{Database Developer models}

relational models (tables and columns) – _{DBA adds tablespaces, partitions}

•

_{Use data models to}

– _{Verify accuracy and completeness} of data requirements and

business rules with customers – _{Build standards-driven DDL scripts}

Oracle SQL Developer Data Modeler

• _{Supports a variety of visual models including}

– _{Entity Relationship (ERD)} – _Relational

– _{DataTypes (SQL99)} – _{Multi-Dimensional}

• _{Records details for}

– Physical implementation

– Domains

• _{Imports from various sources}

– _{DDL and Dictionary import from Oracle and non-Oracle databases} – _{Oracle Designer repository}

– _{Ca Erwin Data Modeler}

• _{Exports to various sources}

Data Modeler and the Audience

• _{Analysis (Logical – Platform independent)}

– _{Gather data requirements without implementation detail} – _{Create and transform ERD to one or more relational designs} – _{Supports data analysts, enterprise and data architects}

• _{Design (Physical)}

– _{Create new or update engineered relational model}

– Create one or more physical implementations from the relational model

– Generate DDL for implementation

– _{Supports database administrators}

• _{Read-only Viewer}

– _{Print diagrams and consult model properties}

– Supports application developers, business and end users

Technology

• _{Technology and Architecture}

– _{Java based}

– _{Independent standalone product}

• _{Database support}

– _{Oracle 9i, 10g, and Oracle Database 11g} – _{Third-party databases}

• _{Microsoft SQL Server 2000 and 2005} • IBM DB2/390 and DB2 LUW

• _{Platform support}

– _Windows – _Linux – _{Mac OS X}

Logical Modeling

• _{Model entities, attributes and} relations

• _{Support for}

– _{Super type} – _{Sub types}

• _{Transform one logical to many} relational and

multi-dimensional models • _{Support for configurable}

forward and reverse engineering

Relational Modeling

• _{Model tables, columns and} FKs

• _{Create one logical for one or} more relational models • _{Support configurable forward}

Forward and Reverse Engineering (Transformation)

Each logical model to maps to

one or more relational models

Each relational model maps to

one logical model

Each relational model maps to

one or more physical models

Each physical model maps to

one relational model • _{Engineering options}

– General

– _Compare/copy – Synchronization

Physical Modeling

• _{One relational model for many physical}

models

• _{Add support for}

– Tablespaces

– _Users – _Roles

– Stored procedures • Propate properties

– _{Apply properties to many elements at once}

• Supports

– _{Oracle 9i, 10g, and Oracle Database 11g} – Microsoft SQL Server 2000 and 2005

Modeling DataTypes

• _{Support for SQL99 (Object} Relational Modeling)

– _{Distinct Types}

– _{(Predefined) Structured Types} – _{(Predefined) Collection Types}

• _{Used in logical models} • _{Used in relational models} • _{Included on import} • _{Generated in DDL}

Multi-Dimensional Modeling

• _{Modeling of Cubes,} Dimensions, Levels and Hierarchies, Measures and slices

• _{Start from ROLAP, XMLA or} from scratch

• _{Generate Oracle Analytical} Workspaces

Working with Data Flow Diagrams

Formatting and General Appearance

• _{Granular and general control} • _{Set per item type}

• _{Synchronize tree} with diagram • _{Set notation}

Controlling the Design Environment

• _{Selection of tools available}

– _{Domain definition (data types)} – _{Name abbreviation in the}

relational model (Customer to CUST)

– Compare and merge facilities

– _{Design Rules}

• _{Tools Options}

– _{Naming standards} – Specify default database

– _{Controlling constraints} – _{Physical properties}

– _{Notations (Barker, Bachman)}

Exporting and Code Generation

• _{DDL file editor supports}

– _{Design Rules} – _{Object selection} – _{Drop objects} – Table scripts • _{Standard database DDL} scripts – Oracle

– IBM DB2 and UDB

– _{Microsoft SQL Server}

• _{Multi-Dimensional Oracle AW,} Cube Views and XMLA • _{CSV export}

Importing Metadata

• _{Oracle Database} • _{Microsoft SQL Server} • _{IBM DB2 and UDB} • _{Generic JDBC based}

dictionary

– Examples: MySQL, Teradata • _{Oracle Designer repository} • _{Other Modeling tools}

– _{CA ERwin} – _Bachman • _{Multi-Dimensional} – Cube views – XMLA

Reporting Repository

• _{Create repository user} • _{Export design to repository}

– _{Initial export creates repository} – _{Exports initial version to}

repository

• _{SQL Developer support}

– Browse repository

– _{Import reports} – Run shipped reports

Packaging Choices

•

_{SQL Developer Data Modeler}

– _{Licensed, independent standalone product} – _{File based}

•

_{SQL Developer Data Modeler Viewer}

•

_{SQL Developer Data Modeler Viewer extension}

Available for download and evaluation from OTN

Future Direction

•

_{Provide support for}

– _{Additional database support: MySQL, Sybase} – _{Incremental Oracle Database 11g features} – _{Updated diagramming and layout}

– _{Erwin 7}

– _{Increased database support: SQL Server 2008}

•

_{Ongoing enhancement requests and minor feature}

updates

<Insert Picture Here>

“Having taught data modeling to others for many years,

most class time is spent learning a tool. It's just the

opposite with Oracle SQL Developer Data Modeler. It's

so intuitive to use that we can focus class time on the

actual data model, its content and meaning, and not the

'how to' of the tool.”

Maggie Tompkins

Development Branch Head,

United States Marine Corps

Feedback from an Oracle Designer Customer

•

_{From an Oracle Designer perspective}

– _{$$$$ and 15 years in Oracle Designer data models} – _{Migration path from Designer to Data Modeler}

• _{Complete and accurate}

– _{Logical transition for Designer folks}

•

_{Getting to know the Data Modeler & favorite features}

– _{Logical and relational models can be kept in sync} – _{Save models in Subversion - XML files}

– _{Model driven database design and generation (DDL)} – _{OTN documentation is excellent}

Finding More Detail

www.oracle.com/technology/products/database/datamodeler

•

_{SQL Developer Data Modeler on OTN}

– _{White papers,Oracle by Example (OBE) online demos, models} and scripts

– _{www.oracle.com/technology/products/database/datamodeler}

•

_{SQL Developer Exchange}

– _{Add feature requests: http://sqldeveloper.oracle.com}

•

_Forums

– _{SQL Developer}

forums.oracle.com/forums/forum.jspa?forumID=260

Summary

•

_{Oracle SQL Developer Data Modeler provides}

– _{Relational modeling} – _{Physical modeling}

– _{Forward and reverse engineering} – _{Data types modeling}

– _{Multi-dimensional modeling} – _{Data flow diagrams} – _{Importing and exporting}

– _{Providing control of the design environment} – _{Exporting design for reporting}

For More Information

search.oracle.com

SQL Developer Data Modeler

or