blm@6sceng.UUCP (Brian Matthews) (04/12/90)
In article <3560074@wdl1.UUCP> jo@wdl1.UUCP (Jo Mitchell) writes: | (1) determine the major entities in your system (the nouns) | (2) determine the relationships between your entities (verbs) | (3) determine the attributes |Lets walk through your problem using the above three steps: | (1) The major nouns here CUSTOMER, ORDER, LINE-ITEM, PART | and SHIPMENT. Actually, in the example as written, customer isn't an entity, because it's just an attribute of order. In the broader context of a company, you would also have things like the customer's address, that you would want to maintain independent of any outstanding orders. In that case, customer would be an entity. |CUSTOMER(SS#,ADDRESS,PHONE) |ORDER(Order_no,order_date,SS#) |SHIPMENT(Shipment_no, Order_no, shipment_date, total_qty_shipt) |LINE-ITEM(Order_no,line-item,qty,cost) |LINE-TO-PART(Order_no,line_item,part_no) |PART(Part_no,Indiv_Cost,MTBF,MTTR...) There are still a couple of problems here. For one, the shipment record is full of transitive dependencies. For instance, shipment_date really depends on the order_no as well, as does the quantity_shipped. Also, you probably want a quantity_shipped on the line-item so you know which line-items aren't complete. Also, the cost will probably be dependent on the customer and order numbers (customer and quantity discounts, etc.) I don't think line-item should depend on order_no as well as being in a concatenated relation with it either. |Now you have an ER diagram - by following these steps |you also automatically also almost have a relational schema |in 3rd normal form (all nonkey attributes of each entity |are not functionaly dependent on other nonkey attributes & |every nonkey attributes is fully functionally dependent |on PK & every attribute is atomic). Also, 3rd normal form schemas can't have any transitive dependencies, as menitioned above. Because it's somewhat relevant here, I thought I'd mention a product my company sells, called the Canonizer, that allows you to design and normalize databases. It uses something similar to ER diagrams, except (in my opinion), simpler. You just deal with items and relations. For example, you'd just enter the fact that customer_number is in a one-to-many relationship with order_number. You don't need to figure out what's an entity and what's an attribute, it's done for you. (You still have to tell it what the items and relations are - we're not magicians :-)) You can modify the model when things change (e.g. when you realize you need to charge sales tax on an order, so you need to add a sales tax item) by just making the changes and regenerating the normalized schema instead of starting over from scratch. If you'd like information on the Canonizer, send e-mail to canon@6sigma.UUCP (or me - blm@6sceng.UUCP) or call 1-800-827-4462 or 1-206-643-6911. -- Brian L. Matthews blm@6sceng.UUCP Six Sigma CASE, Inc. +1 206 643 6911