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 from the main menu.
To use a wizard to reverse engineer an existing database
schema:
- Select the EMPLOYEE project in the Data Project Explorer.
- From the main menu, click . The New Physical Data Model wizard opens.
- On the Model File page, specify the following settings:
Your settings should now look like this:
- Click Next.
- 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.
- Click Next.
- 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:
- Click Next.
- 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.
- Click Next.
- 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.
- Click Next.
- On the Options page, select Overview.
- 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.