Adding a Table to An Existing Database

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 icon.

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.

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 icon.

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 icon to create another column.

Enter first_name for the Name, select Domain for Datatype and select VARCHAR_0_0_20. Then click the Create Column icon again.

Enter last_name for the Name, select VARCHAR_0_0_25 for Type and click the Create Column icon again.

Enter birthdate for the Name and select Date for Type and click the Create Column icon again.

Enter relation for the Name and select VARCHAR_0_0_25 for Type and click the Create Column icon again.

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 icon to create one more 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.

Select the New FK Relation icon.

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.

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.

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.