DB-Examiner ™. AN INTRODUCTION. Version 2008 - January 2008. An Introduction to DB-Examiner. This document serves two purposes: If a copy of DB-Examiner is installed, it is a script for demonstration of some of the important features of the product.
Version 2008 - January 2008
This document serves two purposes:
If a copy of DB-Examiner is installed, it is a script for demonstration of some of the important features of the product.
If DB-Examiner is not yet installed, this document introduces the reader to the usability, usefulness and unique advantages of DB-Examiner, convincing reasons to install and evaluate the product.
Initially, set DB-Examiner to ONLY process Defined FKs.
Go to Tools > Preferences > General Settings TAB and set the Implied Relationships Procession option to None (which means Do not imply any relationships – use the defined Foreign Keys Only).
Go to File > New and select the DBMS Schema option.
Click the OK button and get the Dialog to select the Database Platform.
Select Oracle (or any database that you have) and click OK.
Click the Connect button and get the list of Owners.
Select the Owner that has the database that you want to analyze and click OK.
The model will be read from the database.
Go to File > New and select the SQL/DDL Script option.
Click OK and then select the type of script (in this case SQL Server).
Select the DDL file that you want to analyze (SQLDemo.txt or DEMO.txt – they are the same).
Click OK and the model will be read for the DDL script.
Go to File > New and select Data Model from Erwin or Data Model from Model Manager.
Click OK and get the Open Model Dialog.
Select the ERwin file you want and click Open. The model will be read directly from the ERwin File.
For the purpose of the demo we shall use a SQL Server DDL script – DEMO.txt.
Select the model as described in example “Reading a model from DDL scripts”. At the following screen, ensure all tables are selected then click OK.
Once the model is opened, you will see:
Number of tables
Name of model
Foreign Keys Only
You can see that the model was processed in DB-Examiner with Foreign Keys Only (NO Implied Relationships option).
You can also see that the model has 12 tables.
If you want you can open the branches of the tables and check the details of each table.
You can drill-down as long as there is a + sign.
Click on the Relationships TAB and you will see the number of relationships (5) that have been defined.
Let us now go to Tools > Preferences and set in the General Settings TAB, the option Implied Relationships Processing to ALL.
Click OK or Apply. DB-Examiner will reprocess the model, now trying to infer
additional relationships that the user may have forgotten to define.
If you click on the Relationships TAB, you will see:
You can see now that we have many more relationships (14) than before (5).
Let us open one of these relationships. Click on the + sign in front of the Table EMPLOYEE.
Before opening the relationships, let us do the following to simplify the viewing:
Right-Click on the Model Name (Demo) on the tree to see the options available.
Then unselect the option Cascade Relationships. By doing this, DB-Examiner will only show the first level of relationship. If you want to see all levels, then leave the option selected.
Open the two relationships, clicking in every + sign. You will see this:
You can also see the PK and the FK or Implied FK of each relationship.
We have a real FK from MANAGER_ID in Table EMPLOYEE to the PK (EMPLOYEE_ID) of table EMPLOYEE. This is a recursive relationship.
DB-Examiner inferred a relationship between EMPLOYEE as a Parent to SALESMAN as a Child because the PK of EMPLOYEE is EMPLOYEE_ID and in table SALESMAN we also find EMPLOYEE_ID as a non-key attribute. This means that possibly there is a One-to-Many relationship from EMPLOYEE to SALESMAN. It is up to the user to confirm (do nothing) or delete this relationship.
This relationship tree shows the relationships from the Parent to the Child. If you want to see the relationships from the Child to the Parent, just right-click on the Model Name on the tree and select View Child to Parent.
Note the other options:
Show Relationship Component – to display the PK and the FK or Implied FK of the Relationship.
Cascade Relationships – With this un-checked, DB-Examiner will only show one level of relationships; otherwise it will show all levels.
The user can customize the Diagnostics by going to Tools > Preferences > Diagnostics Settings > Select Individual Diagnostics.
For each Category, the user can select the diagnostics to be executed and assign a severity level to each diagnostic. Then the user can save the selected set of diagnostics and can use this set to perform the analysis.
Click the Diagnostics TAB; you will see the following screen:
This means that the diagnostics have not been performed. Double-Click on the Model Name to perform all diagnostics. Then collapse all the branches and you will see:
You can see that this model has a total of 50 diagnostic messages, where 2 are in the Columns category, 28 in the Index and Constraints category, 6 in the Normalization category and 14 in the Relationships category.
The diagnostics can be shown by Category or by Severity Level. To change right-click on the top of the diagnostics tree and select the appropriate option: Order by Severity or Order by Category. Later you will see the Reports; they will be sorted by Category or Severity according to the selection made here at the Diagnostics TAB.
If you expand the Columns category, you will see:
Let us open the Inconsistent Definition diagnostic.
Click on the i button in front of the Inconsistent Definition diagnostic; you will see:
This message explains the diagnostic. If you need more details, click the Teach Me button.
Click on the + sign.
You can see that COMPANY_NAME has 2 different definitions.
Let us open COMPANY_NAME to see the different definitions.
If we open both Datatype definitions, we can see where these attributes are located.
Go to View > Graphic to obtain a graphical representation of the data model or schema.
The relationships are represented as a default by Connector Boxes that link any two tables. The diagram can be navigated, by clicking on the link.
Those Connector boxes can be transformed into lines, by right-clicking in them.
To set Connector Boxes as default, go to Tools > Preferences > Display Settings and select Use Connector Boxes; set the value to ZERO.
This means that Connector Boxes will be used for every table in the Diagram. If you set it to 1, it means that Lines will be used for the tables that are next to each other and the rest of the tables will be drawn with Connector Boxes.
In the example before, let us say that we would like to view the definition of table CUSTOMER to check the definition of attribute CUSTOMER_NAME.
Once the model is open in the right side of the screen, we can go to the diagnostics tree and ask for a table to be displayed in the Graphical View.
Right-click the CUSTOMER table in the Incorrect Definition diagnostic and select the option Scroll to Table in Model.
The table CUSTOMER will be displayed at the top left part of the design on the right side of the screen. This capability will help you debug your model.
Collapse the Column diagnostic and expand the Indexes and Constraints diagnostics.
Let us click on the information (i) button of the Incorrectly Defined Foreign Key diagnostic.
You can see a short explanation of the problem.
Let us now open this diagnostic by clicking the + sign.
Here we see that the table that has a problem is REGION and the FK is FK_REGION_COUNTRY.
Let us click the i button on the FK_REGION_COUNTRY.
By clicking on the Teach Me button you get:
DB-Examiner will explain the problem and the impact on the application if this situation is not fixed.
By clicking on the Correction button, you get:
DB-Examiner generates the DDL script to correct the specific situation. DB-Examiner DOES NOT make any changes to the database.
The script can be copied and pasted to a file for further processing later.
If you want to have all the scripts in a file, go to File > SQL Generation > New File. DB-Examiner will generate a file named with the same name as the model and the extension .SQL.
The show me button will show in a graphical representation the files involved in the specific situation. Click on the Show Me button and you will see:
Now you can understand the problem by looking at the tables that are involved in a specific situation.
Collapse the Incorrectly Defined FK and expand the Missing Indexes diagnostic.
Click on the i button.
Click on the + sign.
Now you see that there are 8 relationships that do not have any index on the FK attributes. This means that these joins may be slow, depending on the optimizer.
Some relations are real (the first and the last one) and the others are implied relations.
Let us open the recursive relationship EMPLOYEE to EMPLOYEE.
The FK that is not indexed is FK_EMPLOYEE_EMPLOYEE, based on MANAGER_ID.
In order to fix the problem, we must click the i button in the FK.
Then we must click on the correction button.
Collapse the Index & Constraints Diagnostics and expand the Normalization Diagnostics.
You see that there are 6 messages related to Normalization issues; 1 is called Incorrect Functional Dependency, 2 are related to First Normal Form Deviation, 1 is related to Second Normal Form Deviation and 2 are related to Third Normal Form Deviation.
Redundancies are called Deviations from the Normal Forms (1st, 2nd, 3rd).
It is not bad to have redundancies in your database, but it is very bad if you do not know that you have them or that you do not control them.
Thus, detecting the redundancies is a matter of locating the original attribute and the copy (or copies) so you can update the copies when the original is updated.
After these are located, you can implement a trigger (or other mechanism) to make sure the information is in synch.
First Normal Form Deviation is bad and must be avoid.
Let us click the Second Normal Form Deviation.
Here we can see that the attribute UNIT_PRICE is redundant in tables ITEM_HISTORY and ORDER_ITEM.
Now if you click on the red i button on the attribute, you will see where the attribute UNIT_PRICE is stored in its original form.
Now you can see that when the UNIT_PRICE in table ITEM is updated, you should check whether it is appropriate to update the same attribute in the other tables (ITEM_HISTORY and ORDER_ITEM).
Click the Show Me button and you will see the tables involved in this diagnostic.
B) Second and Third Normal Forms Deviations are very similar, depending only on some technical definitions; you can see the difference in the Teach Me for each one;
C) Incorrect Functional Dependencies is a redundancy that doesn’t fall into these categories.
Collapse the Normalization diagnostics and expand the Relationship Diagnostics.
Expand the Nonenforceable Relationships.
Here we can see all the relationships that DB-Examiner inferred. If the user has not deleted them, then DB-Examiner will generate the scripts to enforce them.
Click on the first one (CUSTOMER/ORDER_TBL).
Here you can see that in table ORDER_TBL there is no defined FK. If you want to enforce the relationship, you must generate the DDL scripts to create a FK to table CUSTOMER.
Click on the red I button in front of the Implied FK.
Now click on the Correction button.
You can see that when this script is applied to the database, the FK will be created.
DB-Examiner allows the user to play with What If scenarios, with the File >Merge option.
After loading a model – let us say from the database – the user may simulate changes to this model by merging a set of DDL Scripts to the model in DB-Examiner.
The model is then re-processed with the combination of data from – in this case – the database and the DDL scripts.
Tables – A Table can be qualified as a Work Table or as a Mirror of.
Work Table - If a table is used for calculation and is not really an essential part of the database, the user may qualify the table as a Work Table. DB-Examiner will exclude the table from the analysis.
Mirror of – Let us look at the table Item_History in the DEMO model. This table contains the history of the prices for each item. DB-Examiner will always list the attribute UNIT_Price as a Second Normal Form Deviation. This is not a real Second Normal Form deviation. To avoid this diagnostic, qualify the table as a Mirror of table Item. This will tell DB-Examiner not to display this diagnostic. There are several ways to qualify a Table. The easiest is to go to the Tables tree, select the table you want to qualify, right-click and select the type of qualification you want.
Columns – a Column (attribute or field) can be qualified as: Log, Code, Homonym or Synonym.
Log – is used to indicate that the attribute (field) is used for logging purposes. Let us say that you may have in all the tables an attribute called Date_Created. You do not want DB-Examiner to analyze this attribute in every table. By qualifying the attribute as LOG, it will be excluded from the analysis.
Code – If you have a First Normal Form deviation that is not really serious, such as Telephone1, Telephone2, Telephone3, you may qualify them as CODE and DB-Examiner will not display the diagnostic.
Homonym – If you have an attribute called Name in several tables such as Employee, Company, etc, you do not want DB-Examiner to analyze this attribute because in reality they probably will refer to different data and have different data definitions. The ideal would be to change these occurrences to EmployeeName and CompanyName but, if you cannot do that, just qualify the column Name as a Homonym. DB-Examiner will not analyze them.
Synonym – If you have attributes such as Client_ID and VIP_Client_ID and they represent the same thing in different tables, you need to tell DB-Examiner that they are Synonyms.
To add these qualifications to the knowledge base, select the attribute you need to qualify in the table tree, for example, and right-click. You will see two options: Qualify Column and Define Synonym.
Relationship – The only qualification for a relationship is to Delete the Implied relationship. If the user does not accept the relationship discovered by DB-Examiner he may qualify it as Deleted. To do that, go to the Relationship TAB, select the offending implied relationship and right-click on it.
DB-Examiner has several reports and listings. Go to Reports and select one of the types of reports (Diagnostics, Listings, Model Statistics and Bitmap Legend).
These reports can be viewed on the screen, printed or exported to HTML. In the TRIAL version, the reports are disabled and the export facility is also inhibited
The Diagnostics reports will be presented according to the Diagnostics tree. If the tree is by Category, the Diagnostics reports will be by category. If the tree is by Severity level, the Diagnostics reports will be by Severity level.
The Reports can be viewed in the screen, printed or exported to PDF. In the TRIAL version, the printing is disabled and the export is blocked.
We prepared this document to help you better understand DB-Examiner and how to use its features. This document is not exhaustive, but it shows the major features.
If you have any questions, comments or suggestions, please send an e-mail to LCS@dbesoftware.com. We will take your suggestions seriously and will incorporate them if they help improve the document.
We really appreciate your feedback.
Luiz C Siqueira
If you are interested in acquiring DB-Examiner, contact: