AN INTRODUCTION

1. DB-Examiner™ AN INTRODUCTION Version 2008 - January 2008

2. 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 DB-Examiner, convincing reasons to install and evaluate the product.

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

4. 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).

5. 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).

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

7. Reading a Model directly from the Database (2/3) Select Oracle (or any database that you have) and click OK.

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

9. Reading a model from DDL scripts (1/3) Go to File > New and select the SQL/DDL Script option.

10. Reading a model from DDL scripts (2/3) Click OK and then select the type of script (in this case SQL Server).

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

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

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

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

15. 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 DB-Examiner with Foreign Keys Only (NO Implied Relationships option). You can also see that the model has 12 tables.

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

17. Relationships Click on the Relationships TAB and you will see the number of relationships (5) that have been defined. Defined Relationships

18. 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. DB-Examiner will reprocess the model, now trying to infer additional relationships that the user may have forgotten to define.

19. 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).

20. 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: • one Defined (the Red R indicates the On Delete Restrict rule for the FK) with table EMPLOYEE itself, and; • one Implied (the green I indicates Implied relationship) with table SALESMAN as a child.

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

22. Details of the Relationships (2/4) Open the two relationships, clicking in every + sign. You will see this:

23. 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 One-to-Many relationship from EMPLOYEE to SALESMAN. It is up to the user to confirm (do nothing) or delete this relationship.

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

25. 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:

26. 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:

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

28. Columns Diagnostics If you expand the Columns category, you will see:

29. 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:

30. 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: • Char (30) in one table, and; • Varchar(25) also in one table.

31. 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: • defining the two occurrences with the same definition, or; • eliminating the attribute from table ORDER_TBL, or; • renaming one of the attributes.

32. Viewing the Model in a Graphical Representation (1/2) Go to View > Graphic to obtain a graphical representation of the data model or schema.

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

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

35. Indexes and Constraints Diagnostics Collapse the Column diagnostic and expand the Indexes and Constraints diagnostics.

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

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

38. 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): • Teach Me – to get additional explanation about the situation or problem; • Correction – to generate the DDL script to fix a situation; • Show Me – to isolate the tables involved in the problem.

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

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

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

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

43. Missing Indexes (1/5) Collapse the Incorrectly Defined FK and expand the Missing Indexes diagnostic. Click on the i button.

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

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

46. Missing Indexes (4/5) In order to fix the problem, we must click the i button in the FK.

47. Missing Indexes (5/5) Then we must click on the correction button.

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

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

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