Transcript Introducing Database Modeling and Design with Oracle

<Insert Picture Here>
Oracle SQL Developer Data Modeler
Kris Rice – Director of Development
Agenda
Technical and Feature Review
• Focus on Developer Productivity
• Introduction to SQL Developer Data Modeler
• Audience
• Technology
• Review Features
• Update on Packaging
Strategic Rationale
Why are we in the Data Modeling Market?
• Oracle has a strong suite of Database Developer Tools
• SQL Developer Data Modeler provides a competitive
data modeling tool relative to best-of-breed niche
providers
• Niche vendors have critical mass
• Niche vendors provide full data lifecycle
development
• Adding data modeling further improves Oracle’s
position in database tools by
• Filling current functionality gaps
• Adding specialized domain expertise
• Displacing market leaders:
• CA Erwin, MS Visual Studio, MS Visio,
Embarcadero ER/Studio, Sybase Power Designer,
IBM Rational Data Architect
What is SQL Developer Data Modeler?
• A visual diagramming and data modeling tool
• A single tool for different users and functionality
• Data Architect to build logical data models
• Database Developer to model
relational models (tables and columns)
• DBA to add tablespaces, partitions to the tables
Why use Oracle SQL Developer Modeler?
• A single tool with different features and for different
audiences and users
• A diagram is a powerful communication tool
•
•
•
•
Logical Model for architects and users
Relational model for developers
Physical model for database administrators
Viewer for all users
• Extend Oracle product suite
• Improve Application Development
• Will have better quality
• Are easier to maintain
• Have shorter Time to Market
Oracle SQL Developer Data Modeler
• Supports a variety of visual models
• Entity Relationship (ERD) - supports Barker and Bachman notation (EAR)
• Relational
• DataTypes (SQL99)
• Multi-Dimensional
• Data Flow
• Records details for
• Physical implementation
• Business information
• Domains
• Imports from various sources
• DDL and Dictionary import from Oracle Database
• Oracle Designer repository
• DDL and data dictionary import from Microsoft SQL Server, IBM DB2/390 and DB2 LUW
• General JDBC based dictionary import for non-Oracle databases
• Ca Erwin Data Modeler
• Exports to various sources
• DDL for Oracle and non-Oracle databases
• Oracle Analytical Workspaces
• Multi-Dimensional XMLA format
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 multidimensional 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
and reverse engineering
Physical Modeling
• One relational model for many
physical models
• Supports
• Oracle Database 9i, 10g and 11g
• Microsoft SQL Server 2000 and
2005
• IBM DB2/390 and DB2 LUW
• DDL file editor supports
•
•
•
•
Design Rules
Object selection
Drop objects
Table scripts
Forward and Reverse Engineering
 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
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
• Data Flow
• External Agents
• Processes
• Primitive
• Composite
• Transformation
• Information Flow
• Information Store
Importing
• Oracle Database 9i, 10g and 11g
• Database catalog
• Scripts
• Oracle Designer repository import
• Generic JDBC based Dictionary
• Examples: MySQL, Terradata
• SQL Server, DB2 and UDB
• Scripts
• Multi-Dimensional
• Cube views
• XMLA
• Other Modeling tools
• CA ERwin
• Bachman
Exporting and Code Generation
• Standard database DDL
scripts
• Oracle
• IBM DB2 and UDB
• MicroSoft SQL Server
• Multi-Dimensional Oracle AW,
Cube Views and XMLA
• CSV export
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)
Formatting and General Appearance
• Granular and general control
• Set per item type
• Synchronize tree
with diagram
• Set notation
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
• Create your own reports
Packaging Choices
• SQL Developer Data Modeler
• Licensed, independent standalone product
• File based
• SQL Developer Data Modeler Viewer
• Free standalone viewer
• SQL Developer Data Modeler Viewer extension
• Integrated into SQL Developer 2.1
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 introduces SQL Developer Data Modeler for
• Logical modeling
• 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
“This presentation is for informational purposes only and may not be incorporated into a contract or agreement.”