Re-engineering Your Database Using Oracle SQL Developer Data Modeler 3.0

Install Oracle Database 11g with Sample Schema.

Unlock the HR user. Login to SQL*Plus as the SYS user and execute the following command:
alter user hr identified by hr account unlock;

Unzip the Oracle SQL Developer Data Modeler and Oracle SQL Developer software to your system.

Open Oracle SQL Developer Data Modeler.

To import tables from the data dictionary, select File > Import > Data Dictionary.

The Data Dictionary Import Wizard opens. To create a connection to the database, click Add.

A New / Update Database Connection dialog opens. Enter the connection details as follows and click Test Connection.

Connection Name: hr_orcl
User Name: hr
Password: hr (Select the Save Password checkbox)
SID: orcl (or your own SID)

You get a 'Connection established successfully' message. Click OK.

Click OK in the New/ Update Database Connection dialog to create the connection.

In the Data Dictionary Import Wizard, select the hr_orcl connection you just created and click Next.

You want to import only the HR schema. Select the HR schema and click Next.

Select the DEPARTMENTS and EMPLOYEES tables and click Next.

Review the objects to be imported and click Finish to generate the design.

Review the log file created and click Close.

The EMPLOYEES and DEPARTMENTS tables are successfully imported into SQL Developer Data Modeler.

Click the New Table icon () from the Oracle SQL Developer Data Modeler toolbar.

Move the cursor to the white space of the diagram and click.

A Table Properties - TABLE_<n> dialog opens. Enter PROJECTS for Name and click Columns on the left-navigator.

To add a column, click the Create Column icon ().

Enter PROJECT_ID for Name. Select Logical for Datatype and NUMERIC for Type.

Enter 6 for Precision. You want the PROJECT_ID column to be the Primary Key. Select the PK checkbox and click the Create Column icon to add another row.

Enter PROJECT_NAME for Name. Select Logical for Datatype and VARCHAR for Type.

Enter 25 for Size and select CHAR for Units. Click the Create Column icon to add another column.

Enter PROJECT_START_DATE for Name. Select Logical for Datatype and Date for Type. Click the Create Column icon to add another column.

Enter PROJECT_END_DATE for Name. Select Logical for Datatype and Date for Type. Click OK to create the table.

The table is created successfully.

Click the New FK Relation icon () from the toolbar.

To create a foreign key in the PROJECTS table, select the DEPARTMENTS table and then the PROJECTS table.

A ForeignKey Properties dialog opens. Select Associated Columns from the left-navigator.

Confirm that DEPARTMENTS_DEPARTMENT_ID is selected for Child Column and click OK.

Select the Arrow in the toolbar. The FK Relation is created. The 'F' against DEPARTMENTS_DEPARTMENT_ID in the PROJECTS table identifies the column as a foreign key.

To make the FK optional, right-click on the line joining the two tables and select Properties.

Deselect the Mandatory checkbox. and click OK.

Click Yes.

The FK is successfully created.

In the diagram, double-click the EMPLOYEES table.

Select Columns from the left-navigator.

To create a new column, click the Create Column icon.

Enter MOBILE_NUMBER for Name. Select Logical for Datatype and VARCHAR for Type.

Enter 25 for Size and Select CHAR for Units. Click OK to create the column.

The MOBILE_NUMBER column is successfully created in the EMPLOYEES table.

Select File > Import > Data Dictionary.

Select the previously created hr_orcl connection and click Next.

Select HR schema. The data model that you modified is your point of truth. So you must select Swap Target Model. Click Next.

Select the EMPLOYEES and DEPARTMENTS table and click Next.

Click Finish.

The Compare Models dialog opens. Nodes where changes have been made are marked with a triangle symbol (). In this case, the tables node is marked. Expand the Tables node to see the new table.

Confirm that the PROJECTS table is created and the checkbox next to the node is selected. Expand the EMPLOYEES node to see the new column created.

Expand Columns.

Confirm that the MOBILE_NUMBER column is added and the checkbox next to it is selected.

To generate the DDL for the updates you have made, click DDL Preview.

The code generated is displayed. Review the CREATE and ALTER statements and click Save to save the code to a file. Note the location where you save the file as you will open it shortly.

Click Close.

Click Cancel to exit the Compare Models window..

Open Oracle SQL Developer.

To execute the script file, you must have a connection to the HR schema. To create the connection, right-click Connections in the Connections navigator and select New Connection.

The New / Select Database Connection dialog opens. Enter the connection details as follows and click Test.

Connection Name: hr_orcl
User Name: hr
Password: hr (Select the Save Password checkbox)
SID: orcl (or your own SID)

If the Status is Success, click Connect.

To open the script file, select File > Open.

Browse to the location where you saved the file and and select it. Click Open.

The file opens in a new SQL Worksheet. Select the hr_orcl connection for the Worksheet.

Click the Run Script icon ().

Confirm that the script ran successfully.

Expand the hr_orcl connection. All the HR schema objects are listed. Expand the Tables node. Click PROJECTS to view its description.

Expand the EMPLOYEES node. The MOBILE_NUMBER column is created.