Re: ERwin and Informix

In <gburnoreD8G6nx.4G4@netcom.com> gburnore@netcom.com (Gary L. Burnore) writes: > >Bill MacLean (bmaclean@ix.netcom.com) wrote: >{ If you are looking for design tools, you might want to check out one >{ called Infomodeler, published by Asymetrix. Infomodeler is based on >{ Object Role Modeling (ORM). I have an MSWORD version of a paper >{ written by Dr. Terry Halpin, who is one of the fathers of ORM. I have >{ also just started a thread called "Object Role Modeling / Infomodeler" >{ in the comp.databases.theory newsgroup. > >{ The 30 second overview of ORM (and hence Infomodeler) is that you model >{ business facts and rules, instead of tables. Once you have a complete >{ conceptual model of facts (expressed in natural language), an >{ algorithim is applied that automatically creates a fully normalized >{ logical schema. From the logical schema, Infomodeler will write DDL >{ for most major databases, including Informix. > > >Erwin does the same thing the same way. Curious, does Infomodler do >reverse engineering the same as Erwin? Does it work with DB2, Oracle, >Ingres, NetwareSql, SQL Server, SQLBase Sybase RDB Watcom AS/400 and >Progress the same as Erwin? How about local databases like Clipper >FoxPro, dBaseIII, dBaseIV Access and Paradox? Does it work directly with >PowerBuilder? > >Does it sound like I like Erwin? > >-- gburnore@garys.arasmith.com Sorry about the long quote, but it has been awhile since I have checked this group, so I thought the quote was worth it to help those (like me) who have readers that don't thread. Actually ERWin does not work the same way. ERWin is based on IDEF1X, which is a logical modeling language. Infomodeler is based on Object Role Modeling, which is a conceptual modeling language. I will give a few examples of the differences in a second but here is asummary of some of the most important: 1) ORM is conceptual, IDEF1X is logical. ORM models are not specifically tied to the relational structure, though they can be mapped very effectively to relational databases. 2) ORM is much more closely tied to natural language. ORM facts are quite close to natural language. This is a big benefit in communicating with users during the design process. 3) ORM models can be populated with sample instances of fact types, IDEF1X models cannot (that i know of). 4) ORM has a more robust constraint notation than IDEF1X (e.g. ring constraints, join constraints, set operators.) An example is set operators between logical attributes. How do you graphically represent the rule "A person can be given a company car, OR given a car allowance, but not both". If they are given at most one car, or at most one allowance amount, then car and allowance are both attributes of the Person entity. In ORM, a simple set exclusion constraint takes care of the rule. In ERWin, how would you represent such a constraint? 5) ORM allows n-ary facts, and does not force binarization. Check fact 2 below for an example. 6) Normalization completely automatic with ORM, as it is accomplished by the algorithim that maps the conceptual model to a logical schema. With ORM, you simply identify data objects and the roles they play with each other. This is different than trying to identify entities and attributes. All objects are treated as equal at the conceptual level. What you're really modeling with ORM (and Infomodeler) is business facts and rules. The input to Infomodeler is business facts and rules, the output is a logical model, and ultimately DDL for your chosen database. You type the facts in, and then you add sample populations and constraints based on those samples. Infomodeler then automatically draws a graphic representation of the fact. Here is an example of what some of these facts would look like in English (taken straight from a small model) Facts: ------------ 1. City has PopulationCount Each City has at most one PopulationCount Examples: City '1' has PopulationCount '3000000' City '100' has PopulationCount '3000000' City '21' has PopulationCount '40000' 2. SalesRep earns bonus of MoneyAmount for Year Each (SalesRep, MoneyAmount, Year) combination is unique Examples: SalesRep '1' earns bonus of MoneyAmount '10000' for Year '1995' SalesRep '1' earns bonus of MoneyAmount '10000' for Year '1994' SalesRep '2' earns bonus of MoneyAmount '10000' for Year '1995' SalesRep '2' earns bonus of MoneyAmount '5000' for Year '1995' 3. SalesRep earns salary of MoneyAmount Each SalesRep earns salary of at most one MoneyAmount Examples: SalesRep '1' earns salary of MoneyAmount '100000' SalesRep '2' earns salary of MoneyAmount '100000' SalesRep '15' earns salary of MoneyAmount '35000' 4. SalesRep is hired on Date Each SalesRep is hired on at most one Date Examples: SalesRep '1' is hired on Date '1/1/95' SalesRep '21' is hired on Date '1/1/95' SalesRep '3' is hired on Date '3/4/98' 5. SalesRep is terminated on Date Each SalesRep is terminated on at most one Date Examples: SalesRep '1' is terminated on Date '1/1/95' SalesRep '2' is terminated on Date '1/1/95' SalesRep '3' is terminated on Date '2/4/95' These facts come from a sample model that I made for client during a demo. After getting the user to help me create the facts, I go through facts and constraints one by one. The populations really help, because many people feel more comfortable working with concrete data than abstract fact types. Look at fact 2. The last two rows look kind of fishy, because you should probably just say SalesRep '2' earned '15000' for the year, in which the constraint should be different (Uniqueness over SalesRep and Year only). Or, it may be that the fact doesn't tell the entire story, because you really want to track quarterly, or perhaps semi-annual bonuses. Either way, the population helped point out an error early on, well before any code was written. Another example: looking at facts 2 and 3 along with their sample populations makes it clear that the current model will support historical tracking of SalesRep's bonus amounts, but not of their salaries. This may be a deficiency in the model (then again, the historical tracking of bonuses may be overkill), but it can be caught easily when the facts are clearly stated with their supporting sample data. The key to me is that the user can be involved in all this, and help to ferret out these problems. One of the thing I like to do is put a sample row in that I "know" is wrong. Not uncommonly, I will be surprised by a user who will say that my "intentionally wrong" row is acceptable. When this happens, I know I need to change the constraints. As I change the facts and constraints, I know the table structure that is ultimately generated will change as well, but I don't have to get either myself or the customer wrapped up prematurely in those considerations. A key point to my way of thinking is that the facts drive the schema. I don't first create the schema, and then try to extract facts from it to review with customers. IDEF1X (and consequently ERWin) does not s