Analyze Your Database and Improve Data Quality › Analyze the Model

Analyze the Model

CA ERwin Data Model Validator provides diagnostic messages and suggestions to optimize your database operating efficiency.

The following categories can be diagnosed:

• Columns
• Indexes and Constraints
• Normalization
• Relationships

A corresponding severity level is assigned:

• Severe Errors
• Errors
• Performance
• Cautions
• Warnings

You can begin to analyze a model after you open it. In the example that follows, we use the model DEMO.erwin.

1. Create a new validation project and click on the Diagnostics tab. The following screen appears:

   

   The numbers represent the amount of messages that were found for each category.

2. To obtain more information about the Columns diagnostics, expand the Columns folder. The following information displays:

   

   The numbers represent the amount of messages that were found for each category.

3. Expand Inconsistent Definition.

   

   You can see that COMPANY_NAME has 2 inconsistent definitions. In table ORDER_TBL it is defined as Varchar(25). In table CUSTOMER it is defined as Char(30).

   CA ERwin DMV shows that the attribute COMPANY_NAME exists in two different tables with different data types and that this must be avoided.

4. Expand Index and Constraint. You will see the following:

   

5. Expand Incorrectly Defined Foreign Key and click the information icon.

   You can see that the Foreign Key FK_REGION_COUNTRY is not defined correctly. CA ERwin DMV suggests dropping this FK and adding another FK in table REGION referencing table STATE.

   

6. Expand Normalization. The following appears:

   

7. Expand Second Normal Form. Click the information icon for table ORDER_ITEM.

   

   The Column UNIT_PRICE occurs as a second normal form deviation in table ORDER_ITEM. CA ERwin DMV warns that redundancy must be controlled.

8. Expand Relationship:

   

9. Expand Nonenforceable Relationships and click the information icon for the Implied FK in the CUSTOMER/ORDER_TBL relationship.

   

   You are warned that there are eight instances of implied relationships that will not be enforced.

10. If you click the Correction button, the scripts creating the Foreign Key are generated. This will enforce the referential integrity between the referenced tables. The scripts are not applied to the database. A text file with the name of the model and .sql extension is generated (in this case DEMO.sql).