3.3 The Data Modeler Page
The Data Modeler page provides an integrated version of Oracle SQL Developer Data Modeler with basic reporting features. You can create diagrams from existing schemas, retrieve data dictionary information, generate DDL statements, and export diagrams.
To navigate to the Data Modeler page, do either of the following:
-
In the Launchpad page, click Data Modeler.
-
Click Selector
to display the navigation menu. Under Development, select
Data Modeler.
The Data Modeler page consists of the left pane for navigating objects and diagrams, the editor pane for working with relational diagrams, and the right pane for viewing the properties of the object selected in the diagram. These panes are described in the following sections:
Related Topics
3.3.1 Navigating Diagrams and Objects
The Diagrams tab lists the Data Modeler diagrams that have been saved.
When you right-click a diagram, you have options to open, save, delete, and view properties. Figure 3-11 shows a thumbnail preview that is displayed when you click the name of a diagram in the left pane.
Figure 3-11 Diagram Preview in Data Modeler
Description of "Figure 3-11 Diagram Preview in Data Modeler"
-
To create a new diagram, in the Diagrams tab, click New Diagram
.
-
To open an existing diagram, in the Diagrams tab, right-click the diagram and click Open.
Note:
Diagrams are stored in the database, in the Database Actions user schema.
When you use Data Modeler for the first time, a
OSDDMW_DIAGRAMS table is created
in your schema, to store the diagrams.
Oracle SQL Developer Data Modeler can import from or export to the
OSDDMW_DIAGRAMS table if a connection is provided. For more
information, see Sharing Diagrams with SQL Developer Data
Modeler.
The Navigator tab lists the objects that are available for each schema. You can select the schema and object type from the drop-down lists. For a particular schema, if a tables object is selected, the left pane displays all the tables that belong to the particular schema. You can expand a table to view its columns. Figure 3-12 indicates the important elements in the Navigator tab.
In a schema, you can search for objects in the Navigator tab by entering a few characters. You can also search the contents of a saved diagram. The search functionality is not case-sensitive, retrieves all matching entries, and does not require the use of wildcard characters.
The context menu for a table or view consists of:
Add Object to Diagram: Adds the selected object to the selected diagram. Alternatively, you can drag and drop an object into a selected diagram.
Add Object with dependencies to Diagram: Adds the selected object along with parent and child tables related to the object to the selected diagram.
Add Object as Star Schema to Diagram: Adds the selected object to the diagram and searches the data dictionary for foreign keys and implied foreign keys related to the object. The related tables or views are added to the diagram and the star schema layout is applied. See Implied Foreign Keys
Edit, Use as Template: Opens the Table Properties Dialog. Use to edit an existing object (such as a table or view) for a specific schema, or create a new object by using an existing one for the initial content. See The Table Properties Dialog and The View Properties Dialog.
Add/Edit Sequence: Opens the Sequence Properties Dialog. Use to create or edit a sequence for a selected schema. See The Sequence Properties Dialog.
Add Index: Opens the Index Properties Dialog. Use to create an index for a table. See The Index Properties Dialog.
Add Synonym: Opens the Synonym Properties Dialog. Use to create a synonym for a table or view in a selected schema. See The Synonym Properties Dialog
3.3.2 About the Data Modeler Editor
You can create and work with relational diagrams in the editor pane.
When an object is selected in the diagram, you can inspect the properties of the
object in the right pane. For a table, the properties displayed are Columns, Primary
Key, Unique Constraints, Indexes, Foreign Keys, Comments, and Colors. The only
properties that you can edit are background and border color. Click Reset 
to return to the default colors.
Figure 3-13 shows the properties of an object selected in the editor pane.
You can drag the thumbnail viewer in the upper right corner to display the appropriate area in the editor pane. This is useful when you are working with long diagrams in the editor pane.
Figure 3-13 Inspect Properties of Object Selected in Editor Pane
Description of "Figure 3-13 Inspect Properties of Object Selected in Editor Pane"
When creating diagrams, you can do the following:
-
Select an object and then drag to move it around.
-
Adjust or move objects with the relationships intact.
-
Add elbows to relationship lines to avoid intersecting with lines from other objects. Right-click the relationship line and drag to create the elbow. Click Remove vertex
to restore the original shape.
-
Resize objects by dragging the handles that are positioned around the box.
-
Select and then right-click an object for the following options:
-
DDL Preview: Shows the DDL statements for the object.
-
Update: Updates any actions performed on the object.
-
Delete: Deletes the object.
-
Edit: Opens the Table Properties Dialog for editing a table object or the View Properties Dialog for a view object.
-
Implied Foreign Keys: Opens the Implied Foreign Keys Dialog. Use this option to define implied foreign keys for the object. See Implied Foreign Keys
-
View JSON Data Guides: Opens the JSON data guide diagram for the table or view. See About Viewing JSON Data Guides
-
The icons on the toolbar are:
-
Save Diagram: Saves the currently selected diagram. Diagrams are stored in a table that is created in the schema of the user.
-
Print Diagram: Prints the selected diagram.
-
Save to SVG: Saves the currently selected diagram to an image file in SVG format.
-
Add Objects to Diagram: A dialog is displayed where you can select one or more objects of type tables or views from a specific schema into the selected diagram. Type
*to list all the objects of an object type (tables or views) in the schema. You can also search by typing a few characters. -
Add Note: Adds notes to the selected diagram. Select the note to add text and to see the associated properties in the right pane, such as Font Size and Colors.
-
To enter text, expand TEXT and type information. Click Apply.
-
To select the background, border, or text color for the note, click the box and select the required color. To revert to the default color, click Reset
.
-
-
Delete: Deletes the selected object or objects from the diagram. To select multiple objects, press the CTRL key and select the objects.
-
Zoom In: Displays more detail, and potentially fewer objects, in the currently selected diagram.
-
Zoom Out: Displays less detail, and potentially more objects, in the currently selected diagram.
-
Fit Screen: Makes all relevant objects fit in the window for the currently selected diagram, adjusting the sizes of shapes and text labels as needed.
-
Actual Size: Adjusts the shapes and text labels in the currently selected diagram to the default sizes.
-
Layout: Auto Layout: Rearranges the objects in the diagram to a layout that may be more meaningful and attractive. If you do not like the rearrangement, you can restore the previous layout by clicking Undo.
-
Layout: Star Layout: Rearranges the objects in the diagram to a star schema layout, where the fact table is in the center and the associated dimension tables surround the fact table.
-
DDL Preview: Shows the DDL statements for the object. You have the option to save or send the DDL statements to the worksheet. To choose what you want to display, click Options.
-
DDL Preview for Current Schema: Shows the DDL statements for the current schema. You can send the DDL statements to the worksheet.
-
Diagram Report: Generates a data dictionary report of everything in the diagram.
-
Schema Report: Generates a data dictionary report of everything in the schema.
-
Help: Displays the help for the Data Modeling editor.
3.3.2.1 About Viewing JSON Data Guides
A JSON data guide represents the JSON schema for documents in one column with JSON content. A table can have more than one column with JSON content.
The View JSON Data Guides option is available in the context menu for a table or view in a Data Modeler diagram.
This is applicable for tables, views and external tables that have columns with JSON content. Columns with JSON content are identified and data guides are retrieved for each such column. This process is quicker with the existence of a JSON search index.
The JSON schema (JSON Data Guide) is presented visually like an entity-relationship diagram. Arrays are presented as one-to-many relationships, contained objects as one-to-one relationships, and "oneOf" constructs as a box that surrounds possible choices. There is a column selector at the top right part of the page enabling you to select a column with JSON content for diagram presentation.
The following example is a JSON schema and its representation in a data guide diagram.
{
"type" : "object",
"properties" :
{
"User" :
{
"type" : "string",
"o:length" : 8,
"o:preferred_column_name" : "DATA$User"
},
"PONumber" :
{
"type" : "number",
"o:length" : 4,
"o:preferred_column_name" : "DATA$PONumber"
},
"LineItems" :
{
"type" : "array",
"o:length" : 1024,
"o:preferred_column_name" : "DATA$LineItems",
"items" :
{
"properties" :
{
"Part" :
{
"type" : "object",
"o:length" : 256,
"o:preferred_column_name" : "DATA$Part",
"properties" :
{
"UPCCode" :
{
"type" : "number",
"o:length" : 16,
"o:preferred_column_name" : "DATA$UPCCode"
},
"UnitPrice" :
{
"type" : "number",
"o:length" : 8,
"o:preferred_column_name" : "DATA$UnitPrice"
},
"Description" :
{
"type" : "string",
"o:length" : 128,
"o:preferred_column_name" : "DATA$Description"
}
}
},
"Quantity" :
{
"type" : "number",
"o:length" : 4,
"o:preferred_column_name" : "DATA$Quantity"
},
"ItemNumber" :
{
"type" : "number",
"o:length" : 1,
"o:preferred_column_name" : "DATA$ItemNumber"
}
}
}
},
"Reference" :
{
"type" : "string",
"o:length" : 32,
"o:preferred_column_name" : "DATA$Reference"
},
"Requestor" :
{
"type" : "string",
"o:length" : 16,
"o:preferred_column_name" : "DATA$Requestor"
},
"CostCenter" :
{
"type" : "string",
"o:length" : 4,
"o:preferred_column_name" : "DATA$CostCenter"
},
"ShippingInstructions" :
{
"type" : "object",
"o:length" : 256,
"o:preferred_column_name" : "DATA$ShippingInstructions",
"properties" :
{
"name" :
{
"type" : "string",
"o:length" : 16,
"o:preferred_column_name" : "DATA$name"
},
"Phone" :
{
"type" : "array",
"o:length" : 64,
"o:preferred_column_name" : "DATA$Phone",
"items" :
{
"properties" :
{
"type" :
{
"type" : "string",
"o:length" : 8,
"o:preferred_column_name" : "DATA$type"
},
"number" :
{
"type" : "string",
"o:length" : 16,
"o:preferred_column_name" : "DATA$number"
}
}
}
},
"Address" :
{
"oneOf" :
[
{
"type" : "string",
"o:length" : 1,
"o:preferred_column_name" : "DATA$Address"
},
{
"type" : "object",
"o:length" : 128,
"o:preferred_column_name" : "DATA$Address_1",
"properties" :
{
"city" :
{
"type" : "string",
"o:length" : 32,
"o:preferred_column_name" : "DATA$city"
},
"state" :
{
"type" : "string",
"o:length" : 2,
"o:preferred_column_name" : "DATA$state"
},
"street" :
{
"type" : "string",
"o:length" : 32,
"o:preferred_column_name" : "DATA$street"
},
"country" :
{
"type" : "string",
"o:length" : 32,
"o:preferred_column_name" : "DATA$country"
},
"zipCode" :
{
"type" : "number",
"o:length" : 8,
"o:preferred_column_name" : "DATA$zipCode"
}
}
}
]
}
}
},
"Special Instructions" :
{
"type" : "string",
"o:length" : 16,
"o:preferred_column_name" : "DATA$SpecialInstructions"
}
}
}
