Reverse Engineering an Existing Database

3 4

Visio Professional makes it easy to extract information from a DBMS and add it to the drawing page. The Reverse Engineering Wizard walks you through the steps required to connect to a data source, specify a DSN, connect to a target database, and select the objects to extract from the database. Visio can create a diagram automatically based on the objects you extracted, or you can create the diagram yourself. The wizard analyzes and validates the schema before adding it to the database model diagram in Visio.

The information that the wizard can extract depends on your target DBMS. If the DBMS doesn't make all of the schema definition available, the wizard displays options for the available items and dims other options. After you finish using the wizard, its status is displayed in the Output window and the extracted schema is displayed in the Tables And Views window, Code window, and the drawing page if you choose.

Setting Up a Data Source

You can use the Reverse Engineer Wizard to connect to a target DBMS, but it's quicker to configure a default driver first. To connect to and extract information from a database, Visio uses built-in database drivers that are enhanced to work specifically with the Database Model Diagram template. Visio includes built-in drivers for the following DBMSs:

• IBM DB2 Universal Database
• INFORMIX OnLine/SE Server
• Microsoft Access
• Microsoft SQL Server
• Oracle Server
• Sybase Adaptive Service Enterprise

In addition, a generic OLE DB Provider and ODBC driver are included. If you are developing a database for a DBMS other than the ones listed here, you can use the generic ODBC driver with a vendor-supplied 32-bit ODBC driver to connect to your DBMS.

Using the Drivers command, you can configure a driver in advance to specify default data type properties and a default data type to use when creating columns, as Figure 19-7 shows.

Figure 19-7.  You can set up and select a database driver before using the Reverse Engineer Wizard.

Follow these steps to specify a default database driver:

1. Choose Database, Options, Drivers.
2. In the Default Driver For Visio box, select the driver you want to use.
3. To associate a vendor-specific driver with the selected Visio driver, click Setup, specify the settings you want, and then click OK.
4. Click the Default Mapping tab.

   

5. Under Default Category Type For Column Creation, choose the default category type, and then for each category type, set the default data Category, Type, Size, Length, and Precision or Scale.
6. Click OK.

Step 1: Connecting to a Data Source

When you start the Reverse Engineer Wizard, you can create a data source for a particular database. A data source includes the data you want to access and crucial information for accessing that data, such as the name of the database, the server on which it resides, and network information. Visio refers to a data source by its data source name (DSN) and lists default DSNs on the first screen of the wizard, as Figure 19-8 shows. You can create, edit, and manage DSNs within Visio.

Follow these steps to start the wizard and connect to a data source:

1. Choose Database, Reverse Engineer.
2. In the Installed Visio Drivers list, the driver you set up in the previous section is selected.

   

   Figure 19-8.  You can connect to a database by specifying a Visio driver and then an associated data source.

3. If you want to set up a new driver, click Setup, associate the selected Visio driver with a vendor-supplied driver, and then click OK.
4. In the Data Sources list, select the data source that points to your database. Or click New to create a new File DSN, User DSN, or System DSN. When you are finished, click OK to add the new DSN to the Data Sources list.
5. In the wizard, click Next to proceed. The Connect Data Source dialog box appears. Enter a valid user name in the User box and password in the Password box. Then click OK.

   

6. If a Select Database, Select Workbook, or any other dialog box opens for you to select a file, locate the file you want to reverse engineer and click OK.

Step 2: Extracting Database Objects

On the next screen of the Reverse Engineer Wizard, you can choose the schema information to extract, as Figure 19-9 shows. Select the options you want, and clear the check boxes for the objects you don't want to include in your database model. To proceed, click Next.

Figure 19-9.  You can select from the options that your DBMS makes available to Visio. An Access database does not support triggers and stored procedures, so those options appear dimmed.

Step 3: Selecting Tables and Views

Visio lists the tables and views that your target database contains on the next screen of the Reverse Engineer Wizard, as Figure 19-10 shows. For convenience, you can click the Select All button, and then clear the check boxes for any tables you don't want to include. The options you select here will be included in the model that Visio preserves, but don't necessarily have to appear in the diagram—which is an option on the next screen of the wizard. Click Next to continue.

Figure 19-10.  You can choose the tables and views you want to include in the model that Visio creates.

Step 4: Creating a Diagram

If you want Visio to generate a diagram based on the objects you've extracted from the database, you can select the Yes, Add The Shapes To The Current Page option on the next screen of the wizard, as Figure 19-11 shows. If you select the No, I Will Add The Shapes Later option, Visio still extracts the selected information from the database and adds it to the model that's stored with the diagram. However, no shapes for the tables, views, and relationships are added to the drawing page. Instead, the objects are listed in the Tables And Views window. To continue, click Next.

Figure 19-11.  You can choose whether Visio diagrams the tables and views that the wizard extracts or adds them to the model without adding them to the drawing page.

Step 5: Finishing the Wizard

On the last screen of the wizard, you can review the options you've selected. Click Back to change your selections on any of the screens, or click Finish to extract the selected information and add it to the model in the Visio drawing file. Visio creates a database diagram if you selected that option. Otherwise, choose Database, View, Tables And Views to see the tables and views extracted from the target database. Review the contents of the Output window for information about the reverse engineering process.

Tip - Display Related Tables

---

To quickly display related tables on the drawing page, drag a table from the Tables And Views window onto the page. Right-click the shape, and then choose Show Related Tables. Visio adds all the tables in your model that have a relationship to the first table and connects them.

Updating a Database Model Diagram

To keep your model in sync as changes are made to the physical database, you can use the Refresh Model command on the Database menu. This command starts a wizard that looks and works much like the Reverse Engineer Wizard. With the Refresh Model Wizard, you connect to your target DBMS, and then the wizard compares your database model diagram to the current physical schema. If the wizard detects any differences, you can choose to update the diagram to match the database, or ignore the difference on the screen shown in Figure 19-12.

Figure 19-12.  If your diagram no longer matches the target database, the wizard lists each difference.

To run the wizard, choose Database, Refresh Model. To resolve a conflict, select an item from the list displayed by the Refresh Model Wizard, and then choose the No Change option to ignore the conflict or the Refresh Model option to update your diagram.

Note

---

In Visio Professional, the Update Database option in the Refresh Model Wizard always appears dimmed. This option is provided for compatibility with Visual Studio.net Enterprise Edition.