Transcript 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.

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.
If DB-Examiner is not yet installed, this document introduces the
reader to the usability, usefulness and unique advantages of DBExaminer, convincing reasons to install and evaluate the product.
What is DB-Examiner
DB-Examiner is a tool to “debug” the database schema, in its
several stages of development:
- as a data model;
- a set of SQL/DDL scripts, or;
- the database schema itself.
It is well known that a flawed schema is the main reason why
applications:
- take so long to develop;
- cost so much;
- do not run at peak, and;
- produce poor information.
By debugging the schema, these issues will be minimized.
Preparation
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).
Sources of INPUT to DB-Examiner
DB-Examiner can read a model from 3 different sources:
• From an ERwin V7 model (.erwin file);
• From a set of DDL scripts (eventually created by another
modeling tool);
• From the Database itself (dictionary, catalog, system tables).
Reading a Model directly from the Database (1/3)
Go to File > New and select the DBMS Schema option.
Click the OK button and get the Dialog to select the Database
Platform.
Reading a Model directly from the Database (2/3)
Select Oracle (or any database that you have) and click OK.
Reading a Model directly from the Database (3/3)
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.
Reading a model from DDL scripts (1/3)
Go to File > New and select the SQL/DDL Script option.
Reading a model from DDL scripts (2/3)
Click OK and then select the type of script (in this case SQL
Server).
Reading a model from DDL scripts (3/3)
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.
Reading from an ERwin Model (.erwin file) (1/2)
Go to File > New and select Data Model from Erwin or Data Model
from Model Manager.
Reading from an ERwin Model (.erwin file) (1/2)
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.
Analyzing the Model (1/2)
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.
Analyzing the Model (2/2)
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 DBExaminer with Foreign Keys Only (NO Implied
Relationships option).
You can also see that the model has 12 tables.
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.
Relationships
Click on the Relationships TAB and you will see the number of
relationships (5) that have been defined.
Defined
Relationships
Changing the Relationships Option to ALL (1/2)
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. DBExaminer will reprocess the
model, now trying to infer
additional relationships that
the user may have
forgotten to define.
Changing the Relationships Option to ALL (2/2)
If you click on the Relationships TAB, you will see:
You can see now that we have many more relationships (14)
than before (5).
Implied Relationships
Let us open one of these relationships. Click on the + sign in front
of the Table EMPLOYEE.
You can see that Table EMPLOYEE (as a Parent) has 2
relationships:
a) one Defined (the Red R indicates the On Delete Restrict rule
for the FK) with table EMPLOYEE itself, and;
b) one Implied (the green I indicates Implied relationship) with
table SALESMAN as a child.
Details of the Relationships (1/4)
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.
Details of the Relationships (2/4)
Open the two relationships, clicking in every + sign. You will see
this:
Details of the Relationships (3/4)
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 Oneto-Many relationship from EMPLOYEE to SALESMAN. It is up to the
user to confirm (do nothing) or delete this relationship.
Details of the Relationships (4/4)
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.
Diagnostics (1/3)
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:
Diagnostics (2/3)
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:
Diagnostics (3/3)
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.
Columns Diagnostics
If you expand the Columns category, you will see:
Inconsistent Definition (1/3)
Let us open the Inconsistent Definition diagnostic.
Click on the i button in front of the Inconsistent Definition
diagnostic; you will see:
Inconsistent Definition (2/3)
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.
We can see that COMPANY_NAME is defined with two different
Datatypes:
a) Char (30) in one table, and;
b) Varchar(25) also in one table.
Inconsistent Definition (3/3)
If we open both Datatype definitions, we can see where these
attributes are located.
Now you can see that the attribute COMPANY_NAME in table
CUSTOMER is defined as Char(30) and in table ORDER_TBL it is
defined as Varchar(25).
This is a bad situation and must be fixed by either:
a) defining the two occurrences with the same definition, or;
b) eliminating the attribute from table ORDER_TBL, or;
c) renaming one of the attributes.
Viewing the Model in a Graphical Representation (1/2)
Go to View > Graphic to obtain a graphical representation of the
data model or schema.
Viewing the Model in a Graphical Representation (2/2)
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 rightclicking 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.
Looking at a Table in the Graphical Representation
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.
Indexes and Constraints Diagnostics
Collapse the Column diagnostic and expand the Indexes and
Constraints diagnostics.
Incorrectly Defined FK (1/3)
Let us click on the information (i) button of the Incorrectly
Defined Foreign Key diagnostic.
You can see a short explanation of the problem.
Incorrectly Defined FK (2/3)
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.
Incorrectly Defined FK (3/3)
Let us click the i button on the FK_REGION_COUNTRY.
Now you can see that we can click three buttons (in addition to
the OK button):
A) Teach Me – to get additional explanation about the situation
or problem;
B) Correction – to generate the DDL script to fix a situation;
C) Show Me – to isolate the tables involved in the problem.
Teach Me
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.
Correction
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.
Script Generation to a File
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.
Show Me
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.
Missing Indexes (1/5)
Collapse the Incorrectly Defined FK and expand the Missing
Indexes diagnostic.
Click on the i button.
Missing Indexes (2/5)
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.
Missing Indexes (3/5)
Let us open the recursive relationship EMPLOYEE to EMPLOYEE.
The FK that is not indexed is FK_EMPLOYEE_EMPLOYEE, based
on MANAGER_ID.
Missing Indexes (4/5)
In order to fix the problem, we must click the i button in the FK.
Missing Indexes (5/5)
Then we must click on the correction button.
Normalization Diagnostics
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.
Second Normal Form Deviation (1/4)
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.
Second Normal Form Deviation (2/4)
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.
Second Normal Form Deviation (3/4)
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).
Second Normal Form Deviation (4/4)
Click the Show Me button and you will see the tables involved in
this diagnostic.
Here you can see that DBExaminer displays a 2 in a red
circle before the attribute that
is in a Second Normal Form
Deviation.
Note that:
A) First Normal Form is a
repeating group;
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.
Relationship Diagnostics
Collapse the Normalization diagnostics and expand the
Relationship Diagnostics.
Non Enforceable Relationships (1/4)
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.
Non Enforceable Relationships (2/4)
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.
Non Enforceable Relationships (3/4)
Click on the red I button in front of the Implied FK.
Non Enforceable Relationships (4/4)
Now click on the Correction button.
You can see that when this script is applied to the database, the
FK will be created.
What If Scenarios
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.
Customizing the Model or Schema
The user can add information about his model so DB-Examiner
will better understand his/her model specifics. This is done using
the User Definitions option.
The user can provide knowledge to DB-Examiner about his/her
model or database schema, in particular about:
a) Tables
b) Columns
c) Relationship
Let us examine each option the user has:
Customizing the Model or Schema - Tables
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.
Customizing the Model or Schema - Columns
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.
Customizing the Model or Schema - Columns
Homonym – If you have an attribute called Name in several
tables such as Employee, Company, etc, you do not want DBExaminer 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.
Customizing the Model or Schema - Relationship
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.
Reports and Listings
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.
Comments, Suggestions and Doubts
We prepared this document to help you better understand DBExaminer 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 [email protected]. We will take your
suggestions seriously and will incorporate them if they help
improve the document.
We really appreciate your feedback.
Luiz C Siqueira
DBE Software
+1-703-847-9500
[email protected]
Acquiring DB-Examiner
If you are interested in acquiring DB-Examiner, contact:
Europe
Tarragon Solutions Ltd
Phone: +44-1978-369-146
[email protected]
http://www.tarragon.co.uk/dbexaminer
Latin America
Princeton Systems Computação Ltda.
Phone: +55-13-3474-4650
[email protected]
http://www.princeton.com.br
Other Countries
DBE Software
Phone: +1-703-847-9500
[email protected]
http://dbesoftware.com