SQL Developer Data Modeler is an independent product and in the previous releases of SQL Developer, only the Data Modeler Viewer extension was readily available along with the SQL Developer download. But in version 4.1, the Data Modeler is integrated with SQL Developer 4.1 by default as a full product and not as a mere read-only extension.
If you are designing and building a model from scratch or have access to the DDL script file to import models, then you do not need to have access to a database. However, if you want to import from a database, you'll need to create a database connection. In this case, there is no need for an Oracle client in your development environment because you can use the thin JDBC driver to connect to the database. SQL Developer Data Modeler also supports the TNS alias. Therefore, if you have access to a tnsnames.ora file or have other Oracle software installed in your environment, you can access the tnsnames file to make the database connection if and when required.
The Data Modeler browser starts with empty Logical and Relational models. This allows you to start a new design and build a model from scratch, which can be either a logical model with entities and attributes, or a relational model with tables and columns. The Data Modeler also supports metadata to be imported from a variety of sources, which include:
- Importing metadata from:
- DDL scripts
- Data Dictionary
- Importing from other modeling tools, such as the following:
- Oracle Designer
- CA Erwin 4.x
- Importing other formats, such as the following:
- VAR file
- XMLA (Microsoft, Hyperion)
The context menu that displays the available choices is shown in the following screenshot:
Once you have created and saved your models, you can open these or share them with your colleagues. To open an existing model, use the menu, as follows:
- File | Open: Browse to the location of the files, which then opens the full design with all the saved models
- File | Recent Designs: This opens the full design with all the saved models with no need to first search for the location
- File | Import | Data Modeler Design: This is more granular, offering a choice of models saved in a set of models
There are many ways to start using this tool by just starting to draw any one of the model types mentioned. Later in the chapter, we'll start with an ERD and work our way down to the DDL scripts. In this first section, we'll import the details from an existing schema. It's a useful place to start, as the process quickly creates a model that you can investigate and then use to learn more about the tool. This may also be one of the most frequently used features in the tool, allowing anyone to quickly see a model of the data structures that underpin their database-based application.
In the screenshot shown earlier, we highlighted the File | Import | Data Dictionary option. Using this allows you to import from Oracle 9i, Oracle 10g, Oracle 11g, Oracle 12c, Microsoft SQL Server 2000 and 2005, and IBM DB2 LUW Versions 7 and 8.
Before you begin to import from a database, you need to create a database connection for each database that you'll connect to. Once it's created, you'll see all the schemas in the database and the objects that you have access to.
Access the New Database Connection dialog by navigating to File | Import, as shown in the following screenshot. If you have no connections, click on Add to create a new connection:
For a Basic connection, you need to provide the Hostname of the database server, Port, and SID. The connection dialog also supports the TNS alias and the advanced JDBC URL.
Once you have a connection created, select the connection and continue using the dialog by clicking on Next. You can select more than one schema during an import. In the following example, we have selected two schemas, which results in three diagrams: one central model with all the imported tables and views, and two subviews. When importing, a separate subview is created for each schema that you select, and all the imported objects are displayed in that schema (this is not the only role for subviews, and we'll expand on this later in the chapter):
In the Import Wizard that's displayed, there is a set of checkboxes and buttons below the listed schemas. By selecting All Selected, the screens that follow will have all instances of all the objects that were automatically selected. Depending on what you are planning to import, it is often easier to keep that deselected and then use the Select All button on each object type tab:
Once you have selected the schemas, you can select the individual objects. Tables and Views are placed on the central Relational model and onto the respective subviews for each schema. All other objects, such as Roles, Users, and TableSpaces, are maintained in the Physical model listed in the object browser.
A summary of the objects that need to be imported is displayed, and you are offered the choice of the model destination. The default destination is HR_11g_System. The choice given here is needed for second or any subsequent imports. For these, you need to decide whether you'll want to merge the objects into an existing model or create a new model.
To create the initial model, complete the dialog and select Finish. Each time you import a set of database objects, a log file of the completed activity is created, which tallies with the number of statements and errors. It is worth saving the log files so that they can be used for troubleshooting later.
Once the import is complete, the main Relational diagram opens:
The preceding screenshot shows a few tables imported into the Relational model. To see the initial subviews created for each schema that was made, expand the Relational Models node and then the SubViews node. Right-click on one of the schema subviews listed and select Show Diagram.