This tutorial shows you how to add a table to an existing database using Oracle SQL Developer Data Modeler.
Approximately 15 minutes
Oracle SQL Developer Data Modeler offers a full spectrum of data and database modeling tools and utilities, including Entity Relationship modeling, Relational (Schema), Data Types or Object Type modeling, and Multidimensional modeling and DDL generation. It includes importing from and exporting to a variety of sources and targets, provides a variety of formatting options and validates the models through a predefined set of Design Rules.
Oracle SQL Developer Data Modeler will be released as an extension to SQL Developer and as a standalone product, for those developers who only want to work with visual data modeling.
In this tutorial, you create an initial relational model by importing an existing script (DDL), add a new table, link the table to an existing table, create a sub view and generate the DDL.
Before starting this tutorial, you should:
Note: This tutorial was created in a Linux environment. If you are using a Windows environment, the screenshots may be slightly different.
In this section, you import the DDL from the HR sample schema to create a relational model. Perform the following steps:
. |
Open Oracle SQL Developer Data Modeler.
|
---|---|
. |
Select File > Import > DDL File.
|
. |
You can add multiple DDL files to be imported at the same time. Click the '+' icon to add a DDL file.
|
. |
Select hr_cre.sql from the directory where you unzipped the files from the Prerequisites and click Open.
|
. |
Click OK.
|
. |
Select Oracle Database 11g and click OK.
|
. |
The Compare Model window appears. You can view the changes that will occur when the DDL file is imported. Expand Tables under hr_cre. You see that the list of tables that will be created. Click Merge.
|
. |
The relational diagram is displayed. You can zoom in and out. click
the Zoom Out
|
. |
You can now see more of the diagram. To examine the domains that were created in the browser tree, expand Domains.
|
. |
Note that domains are used in data type definitions - domains like VARCHAR_0_0_20 or NUMERIC_0_4_0 are created during import of DDL file. Similar data types (used in column definitions) are aggregated into domains. These names can be changed. Double-click NUMERIC_0_4_0.
|
. |
For each domain you can view where it is used through the domain properties dialog. In the left navigator, select Used in.
|
. |
Click the Columns tab.
|
. |
You see the list of columns that use this domain. Click Close. In the next section, you create a new table to store information about an employees dependents.
|
In this section, you create a new table called dependents and add a number of columns to the new table. Perform the following steps:
. |
Select the New Table icon and click the white space of the diagram.
|
---|---|
. |
Enter dependents for the Name and click Columns from the left navigator. Note that you can click Apply on this page so that the header in the window changes from TABLE_8 to dependents.
|
. |
Select the create column
|
. |
Change the name to id. When you import a DDL file, a domain is created for each datatype in the DDL file. You can select one of these domains if it contains the correct format and length. Select NUMERIC_0_6_0 from the list.
|
. |
You want the id column to be the Primary Key. Click the PK check box. Then click the Create Column Enter first_name for the Name, select Domain for Datatype and select VARCHAR_0_0_20. Then click
the Create Column
|
. |
Enter last_name for the Name, select VARCHAR_0_0_25
for Type and click the Create Column
|
. |
Enter birthdate for the Name and select
Date for Type and click the Create Column
|
. |
Enter relation for the Name and select
VARCHAR_0_0_25 for Type and click the Create Column
|
. |
Enter gender for the Name. There is no
character domain with the length of 1 so you can use logical type. Select
Logical for Datatype, select VARCHAR from
the Type drop list, enter 1 for Size and select CHAR
for Units. Then click the Create Column
|
. |
Enter relative_id for the Name, select Domain for Datatype and select NUMERIC_0_6_0 from the drop list. Then click OK.
|
. |
The dependents table was created successfully. In the next section, you add a foreign key between employees and dependents.
|
In this section, you add a foreign key between the employees and dependents tables. Perform the following steps:
. |
Select the New FK Relation
|
---|---|
. |
Select the employees table then the dependents table to create the new FK relation.
|
. |
Not all dependents have an assigned employee, so you want to change the relation to an optional relation. Deselect the Mandatory check box and select Associated Columns in the left navigator.
|
. |
Select relative_id for the Child Column.
|
. |
Click OK.
|
. |
Since you want to use an existing column for the FK instead of the generated column, Click Yes to delete the generated FK column.
|
. |
Notice that the relative_id column now has an F next to it indicating that it is the foreign key column and the foreign key relation is optional (represented by the dotted line). In the next section, you create a subview.
|
In this section, you create a subview with the employees and dependents tables. Perform the following steps:
. |
There are several possible ways to create subset of the tables you are interested in. One way is to create a subview of tables that are related to one another. Right-click the dependents table and select Select Neighbors.
|
---|---|
. |
Accept the default of 1 zone and click OK.
|
. |
Notice that the FK relation and employees table are now selected because they are the neighbors of the dependents table. Right-click the dependents table again and select Create SubView from selected.
|
. |
The subview is created. You can view the list of objects in the subview from the browser window by expanding the objects.
|
In this section, you create and export the DDL for the dependents table. Perform the following steps:
. |
Select File > Export > DDL File. OR, you can select the Generate DDL icon.
|
---|---|
. |
Click Generate.
|
. |
Select the Tables tab.
|
. |
You only want to generate the DDL for dependents. Click the Deselect All icon.
|
. |
Select dependents. Then click the Views tab.
|
. |
Deselect emp_details_view and click the Sequences tab.
|
. |
Click the Deselect All icon and click OK to generate the DDL for your selections.
|
. |
View the contents of the DDL file. You can save the DDL and run it in SQL Developer. In this tutorial, click Close and perform the next tutorial.
|
In this tutorial, you have learned how to:
![]() |
About
Oracle |Oracle and Sun | ![]() |