The Keys & Indexes diagnostics
After releasing the first prod version of Data Model Designer, I hired a sales person and began to visit several shops to show my software (US$ 3,300 per workstation). In one of them their DBA handed me a document produced by Petrobras and IBM on how to enhance a DB2 database. That document discussed several implementations that were harmful to the performance of DB2. As soon as I read it I thought to myself: "I already have in memory all these elements! I could easily diagnosed them". So the first diagnostic was born, as a report which showed all the inconsistencies in the database. Initially every message was issued with an explanation of the possible causes of the problem, and a "What you should do to correct this" explanation.. Soon afterwards, the first users of the product complained: "I already know this. Can't you just tell me what is wrong without further explanations?". I then implemented some diagnostic options: "Fully documented" (each message was followed by a report explaining why DMD issued the warning, and possible measures to correct the problem), "Short explanation" (a brief explanation) or "No explanations" (just the problems where pinpointed). This was the base of the Teach Me feature you see nowadays in ERWin Examiner.
The denormalization report
"To normalize or not to normalize?" That was the question back in the '90s. During the '80s I was teaching a very popular 3-days course (more than a thousand attendants) on "Fundamentals of Relational Database Design". One of its sections was "Why, when and how to denormalize". Once I implemented the first diagnostic, I though to myself: "What else can I diagnose?" Obviously, to pinpoint denormalizations would add a flavor to DMD, so I concentrate on how to detect redundant columns. It took me a while to figure how to say "Column "X" is unnormalized in tables T2 and T3 but not in T1". Back then there were no public algorithms to recognize that. Initially I found out that a huge percentage of unnormalized columns were fields like "data_added", "telephone_number" and so on. How to avoid them? Enter the Qualify Column feature, which basically would allow you to tell the algorithm "ignore this column". But there was another problem: the algorithm would not recognize "customer_name" and "name_of_customer" as the same thing (supposing they were). So the synonyms option of DMD was enhanced to let the user add as many synonyms as necessary to guarantee a correct unnormalization report. Initially I implemented 1NF, 2NF and 3NF deviations. I did not have time to implement BCNF, 4NF and 5NF deviations, and thought to myself: "That's harder, and probably doesn't happen too often. I'll do it later, whenever I have some free time". I should have known I'll never had time, so the product still lacks those features.
The data types inconsistency report
Back in the '90s inconsistencies like Customer_name CHAR (5) and Customer_name CHAR (7) were rather common. So the data types inconsistencies report was added to pinpoint those anomalies. Initially the message was:
"Attribute Customer_name is declared as CHAR(5) in table X and as CHAR(7) in table Y. This can lead to inconsistencies ........"
In huge models it was a mess, and the report produced dozens of pages. So an upgrade was made to print:
Attribute Customer_name is declared as CHAR(5) in table9s) X1, X2, X3
as CHAR(7) in table(s) Y1, Y2, Y3
and so on.
The multiple paths report
Although ERWIN Examiner claims this is a diagnostic, it is not. There is nothing wrong, per se, in the output of this report. Just consider:
T1 = { K1 } + ....
T2 = { K1, K2 } + .....
T3 = { K1, K3 } + .....
T4 = { K1, K2 , K3 } + .....
There are multiple paths between T1 and T4, and nothing is wrong (from the relationships point of view).
This report is based on the output produced by another component of the Data Workstation project, called the Data Navigator. As it was ready, I packed it with the Diagnostics since I was going to enhance it and turn it into a diagnostic. Just to show the multiple paths is useless; something else, much more complex, has to be done to diagnose those paths and detect potential problems. But I never had time to develop the "something else", so it remained like that, until the day it blew up and I discoverd a loop, caused by what is called in ERWin Examiner an Infinite Loop. So the "Infinite Loop" detection algorithm was born and added to the set of benefits of the software.
The Model Listing report
The Model Listing report was a feature of a system I developed for ALCAN (Canadian Aluminum Company) called the SIA (which stands for Application Information System). The format of the report was suggested by Edson D'Amico, ALCAN's Tech Support Manager at the time. The format was so good that I didn't change anything, just converted the source code from IDEAL (Datacom's 4GL) to C.