Re: What is 'Database Mining'

->From: proberts@informix.com (Paul Roberts) ->Subject: What is 'Database Mining' ->Date: 9 Apr 93 20:11:22 GMT ->Reply-To: proberts@informix.com (Paul Roberts) ->Organization: Informix Software, Inc. -> ->I feel sure I have heard the phrase "database mining" here and there. ->Can someone tell me what it is? -> ->I find myself doing something that feels a lot like database mining ->(dirty, dangerous, exhausting, environmentally questionable) in that ->I am searching through large, non-normalised tables, trying to uncover ->structure in the underlying data. -> ->I find myself wanting to "factor" a large table into two smaller tables ->- basically "unjoin" it. -> ->If there is a theory or a method behind this, I'd love to get a pointer ->to it. -> ->Paul -> ->PS As you can see, I don't exactly know what I am talking about, but I am -> hoping it strikes a chord with someone out there. Paul, I have not previously heard the phrase "database mining", but it is an interesting concept, and I love your "environmentally questionable" description. It seems to me that a standard text on normalization theory would be of help to you. C. J. Date wrote several. I am sure there are other authors who would also be of use. While the books can give you a more formal procedure, you may find the following informal process useful: 1. Evaluate the data in your large table to try to determine which columns seem to depend on other columns. Also try to determine which column or group of columns forms a unique key into the table. 2. Any columns that seem to depend on the unique key found above can not be factored into other tables. 3. Any columns that appear to depend on (or be correlated with) columns other than the unique key are candidates for "factoring". 4. Given that column5 seems to depend only on column3, you can do something like the following: CREATE look_up_table_1 ( column3 ..., column5 ... ); INSERT INTO look_up_table_1 SELECT UNIQUE column3, column5 FROM large_table; 5.a. If the values of column3 in your newly formed look_up_table_1 are not unique, then you have made an error in this factoring. Perhaps column5 depends on both column3 AND column4. Or, if column 5 is unique, perhaps column3 depends on column5, not the other way around. If neither column is unique, go back to step 3 and try again, after DROPping your look-up table. 5.b. If column3 (or the group column3, column4) IS unique in your look-up table, then you can remove the redundant column5 by using: ALTER TABLE large_table DROP ( column5 ); 6. Repeat steps 3-5 until all of the columns remaining in large_table are either part of the unique key or depend only and entirely on the unique key. My normalization theory is a little rusty, so I don't remember whether this gives you a database in "third normal form" or "Boyce-Codd normal form". What I *do* remember is that a database in this form WORKS. Keep in mind that you may not *WANT* to completely normalize your table. For example, an address list table could actually have the city and state normalized out to a look-up table, leaving only the ZIP code. But that would make the table much harder to work with, especially making data entry rather unnatural. Hope this helps, Alan +------------------------------+---------------------------------------+ | R. Alan Popiel | Internet: alan@den.mmc.com | | Martin Marietta, LSC | ( Please note: My opinions do not ) | | P.O. Box 179, M/S 5422 | ( represent official Martin policy. ) | | Denver, Colorado 80201-0179 | Voice: 303-977-9998 | +------------------------------+---------------------------------------+