< Previous | Next >

Using a wizard to reverse engineer an existing database schema

In this exercise, you will create a physical data model by reverse engineering the model from an existing database schema using the Derby SAMPLE database that is included with this product.

There are several ways to create physical data models in the workbench. You can follow the steps in this exercise to reverse engineer a physical data model from a database, or you can use one of these other methods:
Use the New Physical Model wizard to reverse engineer from a DDL file
This option is useful if you want to use a DDL file instead of a live database connection as the source for the physical data model. To use this method, follow the steps below, but specify a DDL file in the New Physical Data Model wizard.
Drag and drop a schema or table from the Database Explorer to a data design project
This is a quick way to create a physical data model, but it does not allow you to specify options for the new physical data model, such as a name for the file, or whether to also create an overview diagram. To use this method, drag a database, schema, or table from the Database Explorer to a project folder or a Data Models folder in the Data Project Explorer.
Transform a logical data model into a physical data model
This method requires you to start with a logical data model. In this tutorial, you are interested in making changes to an existing database, so this method is not useful. To use this method, highlight a logical data model in the Data Project Explorer and click Data > Transform > Physical Data Model from the main menu.

To use a wizard to reverse engineer an existing database schema:

  1. Select the EMPLOYEE project in the Data Project Explorer.
  2. From the main menu, click File > New > Physical Data Mdoel. The New Physical Data Model wizard opens.
  3. On the Model File page, specify the following settings:
    • Ensure that the EMPLOYEE project is specified in the Destination folder field.

      If the EMPLOYEE project is not already specified, click Browse to open a window where you can select the project name.

    • In the File name field, type sample_model.
    • Select Derby from the Database list.
    • Select 10.0 from the Version list.
    • Select Create from reverse engineering.
    Your settings should now look like this:
    Screen capture showing the Model File page, as described.
  4. Click Next.
  5. On the Source page, select Database. At this point, if you wanted to reverse engineer from a DDL file, you would select the DDL file check box. If you selected the DDL file option, you would specify the path to a valid DDL script file that is stored in a data design project on the following pages of the wizard.
  6. Click Next.
  7. On the Select Connection page, specify the following settings:
    • Select Use existing connection.
    • Select the Derby sample database from the list of connections.

      The connections in the list are the existing Derby V10.0 connections in the Database Explorer. In this case, the Derby sample database connection was already created for you. If you had selected a different database and version on the Model file page of the wizard, you would not see the Derby SAMPLE database listed. Instead, you would see existing connections for that database and version combination. If you had not yet created a connection to the database you wanted to reverse engineer from, you could select Create a new connection, and you would be prompted to enter connection information on the following pages of the wizard.

    Your settings should now look like this:
    Screen capture showing the settings as described in the last steps.
  8. Click Next.
  9. On the Schema page, click the box next to the SAMP schema. In this case, we only want to reverse engineer from the SAMP schema, but you can select multiple schemas on this page if you want to reverse engineer from more than one schema.
    Screen capture showing the Schema page with the SAMP schema selected, as described.
  10. Click Next.
  11. Keep the default settings on the Database Elements page and the Options page. If you wanted to exclude indexes and triggers, you could clear the check boxes on the Database Elements page.
  12. Click Next.
  13. On the Options page, select Overview.
  14. Click Finish.
The sample_model physical data model is created and displayed in the Data Project Explorer in the Data Models folder. The model opens in the physical data model editor. Because you selected to create an overview diagram, the overview diagram for this schema opens in the diagram editor. The default overview diagram contains all tables that are in the SAMP schema. In the next exercise, you will create a more simple overview diagram and then use the diagram to modify the SAMP schema.
< Previous | Next >