Reverse engineering models from the database
This scenario shows how to create a model from your existing database schema. You perform these steps when you want to be able to start using models to document your database changes and to communicate with other members of the team.
- Go to File > New > Data Design Project.
- Name the project "storesModel", and click on Finish. You
should be able to see the project in the Data Project Explorer, as
Figure 7 shows:
Figure 7. Data Project Explorer
- Expand the storeModel project, right-click on Data Models, and select New > Physical Data Model.
- Follow these steps in the New Physical Data Model wizard to create
a physical model from the existing database.
- From the Database drop down, select Informix, as
shown below in Figure 8:
Figure 8. Step 1 of the Physical Model Wizard
- Make sure the Create from Reverse engineering radio button is selected, and then click Next.
- Pick superstores_demo (as shown in Figure 9) for the
connection and click Next:
Figure 9. Step 2 of the Physical Model Wizard
- On the Select Schema page, pick a schema and click
Next. In the example shown in Figure 10, vgopal
Figure 10. Step 3 of the Physical Model Wizard
- In the Database Elements page, de-select all of the
database elements except "Tables" and "Indexes", as shown
in Figure 11. (As you can see, you
can create models for several elements. For this tutorial,
we just want to create a model for all the tables in the
Figure 11. Step 4 of the Physical Model Wizard
- Click Finish.
- Congratulations, you've just created a physical model from
the existing database. You can see the model objects
created by exploring into Data Models in the Data Project
Explorer, as shown in Figure 12:
Figure 12. Physical Model for superstores_demo
- From the Database drop down, select Informix, as shown below in Figure 8:
You can also look at overview diagrams for the elements in the model. You can either pick all the elements or you can explicitly select the elements that you want to see.
Let's look at some overview diagrams for our tables:
- From the Data Project Explorer, navigate to the data model you created in the previous step by selecting storesModel > Data Models > Database Model.dbm > superstores_demo > vgopal > Diagrams. Right-click on Diagrams and select New Overview Diagram.
- Make sure the Infer Implicit relationships check box is checked.
- Select the Customer, Orders, and Items tables.
- Click on Finish. Because you checked the "Infer Implicit relationships" box in the previous step, you can see that the diagram has also picked up the tables Stock and Location because of foreign key relationships from the original tables you chose.
You can look at the table definitions, the primary keys, foreign keys, and user-defined types either by navigating through the explorer on the left or by clicking on the object in the diagram itself. For example, if you click on the Customer object, you can then see its properties in the Properties View at the bottom of the screen. For the orders table, the properties are displayed in Figure 13. Just click on the orders table in the diagram and look at the different tabs in the Properties view.
Enhancements with IDA 7.5.1: With IDA 7.5.1, there have been several enhancements in the underlying support for IDS. You can see additional attributes for elements that you could not see with the 7.5 release. For example, the table properties show whether the table is a "RAW" table, and you can also see and set the Extent sizes and other properties. There are enhancements to trigger, index, and constraint definitions as well. In Figure 13, below, the highlighted portion shows some of the additions for a table:
Figure 13. Properties view for a table