Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!wuarchive!uunet!pmafire!mica.inel.gov!gem-hy!cdm From: cdm@gem-hy.Berkeley.EDU (Dale Cook) Newsgroups: comp.databases Subject: Re: ER versus dependency normalization methods. Message-ID: <1990Dec11.003251.4494@inel.gov> Date: 11 Dec 90 00:32:51 GMT References: <33445@netnews.upenn.edu> <47315@sequent.UUCP> <358@6sigma.UUCP> <34324@netnews.upenn.edu> Sender: news@inel.gov Reply-To: cdm@gem-hy.Berkeley.EDU (Dale Cook) Organization: Idaho National Engineering Laboratory, Idaho Falls, Idaho Lines: 76 In article <34324@netnews.upenn.edu>, aaron@grad2.cis.upenn.edu (Aaron Watters) writes: |> In article <358@6sigma.UUCP> zuker@6sigma.UUCP (Hunter Zuker) writes: |> >From what I've seen of ER diagrams there is not enough information to go |> >to third normal form. You easily get 1st normal form and might scramble |> >to 2nd. But an automized normalization procedure doesn't get enough |> >information from ER diagrams to go to third normal form. |> |> Okay: example? A factory has the following ER model: (1) Employees assemble many parts. (2) A part is assembled by one employee. Thus, we have the entities EMPLOYEE and PART, and a single relationship, call it ASSEMBLING. Q: Is this normalized? A: That depends. On what, you ask. Well, it obviously meets 1NF (nonredundancy). How about 2NF (full functional dependancy)? That depends on the attributes of each entity and relationship. If we have identified the attributes EMPLOYEE.NAME, PART.DESCRIPTION, and EMPLOYEE-PART-ASSEMBLY-TIME.INSPECTOR, then the answer is yes. But suppose we have identified an attribute called EMPLOYEE-PART-ASSEMBLY-TIME.AVG-ASSEMBLY-RATE. Is it normalized now? NO! The average rate at which an employee assembles his PARTs is NOT fully functionally dependent on the relationship ASSEMBLING. It is most properly attributed to EMPLOYEE. How about 3NF (nontransitivity)? The 2NF example above is also in 3NF. Bawaddif we add a foreign key to PART, namely, PART.EMPLOYEE-ID? Away goes 3NF, because this is a redundant relationship. However, this is not necessarily a BAD THING. It is a matter of the policies of the business. Which brings me to the point of my ramblings: In order to normalize, you must (1) fully attribute the logical data model (one does not typically attribute an ER diagram - it is a conceptual tool) and (2) fully understand and take into consideration all business rules and policies. |> Also, how do you get the information (dependancies) |> from the user so you can say whether you are at 3rd normal form |> or not. I find it hard to imagine a user casually mentioning |> `by the way FLIGHTSPEED conditionally-multidetermines WINGSTRENGHT |> given WEIGHT and WINGSPAN.' -aaron. You get them from the user by translating his english statements about the current system, by looking at existing reports, screens, etc, from which you derive the attributes. You would no more ask the user to describe his data model in your terminology than you would ask a user to describe his business process in terms of: for (cust=0;custMAX_CREDIT_LIMIT) printf("...)};. The keys to look for in his english statements are nouns, adjectives, and verbs. Nouns translate roughly into entities, verbs to relationships, and adjectives to attributes. Normally, however, the user will NOT be able to give you all of the attributes in english (there are far too many). This is why you look at reports, screens, forms, etc. to discover the attributes. ---------------------------------------------------------------------- --- Dale Cook cdm@inel.gov "The only stupid question is the unasked one." The following disclaimer is my employer's. No flames, please. ---------------------------------------------------------------------- ========== long legal disclaimer follows, press n to skip =========== ^L Neither the United States Government or the Idaho National Engineering Laboratory or any of their employees, makes any warranty, whatsoever, implied, or assumes any legal liability or responsibility regarding any information, disclosed, or represents that its use would not infringe privately owned rights. No specific reference constitutes or implies endorsement, recommendation, or favoring by the United States Government or the Idaho National Engineering Laboratory. The views and opinions expressed herein do not necessarily reflect those of the United States Government or the Idaho National Engineering Laboratory, and shall not be used for advertising or product endorsement purposes.