Oracle SQL Developer Data Modeler is available as an independent product, providing a focused data modeling tool for data architects and designers. There is also a Data Modeler Viewer extension to SQL Developer that allows users to open previously created data models and create read-only models of their database schemas. SQL Developer Data Modeler is a vast tool, supporting the design of logical entity relationship diagrams and relational models, with forward and reverse engineering capabilities between the two. It supports multi-dimensional, data flow, data type, and physical models and allows files to be imported from a variety of sources and exported to a variety of destinations.
It allows users to set naming conventions and verify designs using a set of predefined design rules. Each of these topics is extensive. So, in this chapter, we'll review a few areas, illustrating how you can use them and highlight a few key features using the independent, standalone release of SQL Developer Data Modeler. We'll include a brief review of the integration points of the Data Modeler Viewer extension to SQL Developer. The product offers support for Oracle and non-Oracle databases. In the interest of time and space, we have decided to work only with the Oracle database.
SQL Developer Data Modeler provides users with a lightweight tool, which provides application and database developers a quick and easy way of diagrammatically displaying their data structures, making changes, and submitting the new changes to update a schema. In this chapter, we will not attempt to teach data modeling except to provide some generally accepted definitions. Instead, we will discuss how the product supports data modeling and a few features that are provided. There are a variety of books available on this subject that describe and define best practice in data modeling.
Data Modeler supports a number of graphical models and a selection of text-based models. The graphical models are as follows:
- Logical: This is the entity relationship model or Entity Relationship Diagram (ERD) and comprises entities, attributes, and relationships.
- Relational: This is the schema or database model and is comprised of tables, columns, views, and constraints. In SQL Developer Data Modeler, these models are database-independent and need to be associated with the physical model to support database-specific DDLs.
- Data Types: This is the model that supports modeling SQL99 structured types and viewing inheritance hierarchies. The data types modeled here are used in both the logical and relational models.
- Multidimensional Models: These models support fact, dimension, and summary classifications for multidimensional models.
- Data Flow: These models support the definition of primitive, composite, and transformational tasks.
The following support these graphical models:
- Domains: These allow you to define and reuse a data type with optional constraints or allowable values. You can use domains in the Logical and Relational models.
- Physical: This model is associated with a relational model and defines the physical attributes for a specific database and version.
- Business Information: This allows you to model or document the business details that support a design.
A variety of utilities tie these graphical and textual models together, which include the following:
- Forward and reverse engineering between the Logical and Relational models
- Importing from various databases
- Exporting, including the DDL script generation, for various databases
- Design rules to verify standards and completeness
- Name templates, glossary, and abbreviation files to support the naming standards
SQL Developer Data Modeler is made up of a number of layers, which have a tightly synchronized relationship. The Logical model is thought of as the core of the product, providing the starting point for any design and feeding details into other models. The following diagram shows an illustration of how the models relate to each other:
The logical ERD provides the basis for one or more relational models, and each of these feeds into one or more physical models, which are in turn used for the DDL generation. You can create separate data types model and use the defined data type in either the logical or relational models. Both relational and logical models can have multiple subviews created, and each subview can have many displays created.