[ Pobierz całość w formacie PDF ]
.TipBy adding other entity types, you can add support for more than one address toallow the customer to ship to different addresses.Each relationship has a degree of cardinality.In other words, there can be one andonly one Manufacturer of a certain product, but that Manufacturer can have manyproducts.Determining the relationships is an art that takes practice.Thinkingthrough the business process thoroughly will help to work out the true relation-ships for your data model.Figure 7-1 shows an example of relationships in an Entity-Relationship Diagram.Theexample illustrates the relationship between manufacturer and products.&' Each manufacturer can have one or more products, represented by thecrow s-foot line near the Product box.&' Each product can and must have one and only one manufacturer, as repre-sented by the single line near the manufacturer box.094932-4 ch07.F 5/29/02 3:39 PM Page 113Chapter 7 &' Database Concepts and Design113TipThe O shape near the Product entity type indicates that the existence of a productis not required for a Manufacturer to exist in the database.This O is missing fromthe Manufacturer side of the relationship because a manufacturer is required ifthere is a product in the database.ManufacturerProductManufacturer Name Product NameManufacturer Address Product PriceManufacturer City Quantity On HandManufacturer StateManufacturer ZipFigure 7-1: Relationships among some example entity typesEntity-relationship diagramsWith a list of entity and attribute types for a data model in place  and a basicunderstanding of how to relate them to each other  you create a diagram that for-mally shows the relationships of each entity type.The diagram simply groupstogether like information.This will help to normalize the data and understand thebusiness processes, and possibly find additional entities, attributes, and relation-ships that must be included in the data model.Square or rectangular boxes on the diagram represent entities.Relationships can berepresented by number of line formats.In looking again at the list of attribute types in the example, it is evident that certaincustomer-related information is stored in the database.My initial draft of an Entity-Relationship Diagram (ERD) for the example database is shown in Figure 7-2.Analysis of diagramsAs I touched on earlier, the manufacturer and product relationships are (mostly)fine.One manufacturer can have many products, but a given product can have onlyone manufacturer.Examining customer and credit-card relationships in the draft ofthe ERD in Figure 7-2, you can see that each customer can have more than onecredit card stored in the database.However, each credit card can only be associ-ated with one customer.This is not what I would like  I want the entire family tocreate accounts and use Dad s credit card to buy.To alleviate this problem, I mustmake both sides of the relationship many-to-many.The second draft of the ERD, inFigure 7-3, shows the new many-to-many relationship.094932-4 ch07.F 5/29/02 3:39 PM Page 114Part I &' Getting Started114CustomerCredit CardCustomer First Name Credit Card TypeCustomer Last Name Credit Card NumberCustomer Address Credit Card ExpirationCustomer Supplemental Address Credit Card Name on CardCustomer CityCustomer StateCustomer ZipCustomer Area CodeCustomer Telephone NumberCustomer Email AddressManufacturerProductManufacturer Name Product NameManufacturer Address Product PriceManufacturer City Product Quantity On HandManufacturer State Product CategoryManufacturer ZipManufacturer Area CodeManufacturer Telephone NumberManufacturer ContactFigure 7-2: First Draft of an ERD for the online store094932-4 ch07.F 5/29/02 3:39 PM Page 115Chapter 7 &' Database Concepts and Design115CustomerCredit CardCustomer First Name Credit Card TypeCustomer Last Name Credit Card NumberCustomer Address Credit Card ExpirationCustomer Supplemental Address Credit Card Name on CardCustomer CityCustomer StateCustomer ZipCustomer Area CodeCustomer Telephone NumberCustomer Email AddressManufacturerProductManufacturer Name Product NameManufacturer Address Product PriceManufacturer City Product Quantity On HandManufacturer State Product CategoryManufacturer ZipManufacturer Area CodeManufacturer Telephone NumberManufacturer ContactFigure 7-3: Customers can use more than one card, and more than one customer canuse the card.However, a many-to-many relationship should be broken down into two separateone-to-many relationships.Along with breaking the many-to-many relationshipapart, I must search for extra attributes that may be disguising themselves asrelationships.The result of this analysis is shown in Figure 7-4.094932-4 ch07.F 5/29/02 3:39 PM Page 116Part I &' Getting Started116Customer Customer Card Info Credit Card TypeCustomer First Name Customer CC Number Credit Card TypeCustomer Last Name Customer CC ExpirationCustomer Address Customer Name on CardCustomer Supplemental AddressCustomer CityCustomer StateCustomer ZipCustomer Area CodeCustomer Telephone NumberCustomer Email AddressManufacturer ProductManufacturer Name Product NameManufacturer Address Product PriceManufacturer City Product Quantity On HandManufacturer State Product CategoryManufacturer ZipManufacturer Area CodeManufacturer Telephone NumberManufacturer ContactFigure 7-4: Results from breakdown of many-to-many relationship.One major area for improvement in this data model is in the area of the City, Stateand Zip storage.Since Zip codes define and are always tied to one city and state,there is no reason to store the City and State in both the Customer entity type andthe Manufacturer entity type.It would be much more efficient to make another entitytype to house the City and State and relate that entity type to both the Customer andManufacturer entity types.This draft of the ERD is shown in Figure 7-5.As you can see from the new draft of the data model shown in Figure 7-5, the Zipattribute type is duplicated in both the Customer/Manufacturer entity types and inthe Locale entity type as well.This is so I can join or match the informationtogether.For example, I have a customer with a Zip code of 54481.I can then lookup the city and state for the zip code of 54481 in the Locale table.Without the zipcode being stored in the Customer table, I would have no way to know where thecustomer lives or where to ship their goodies!The Zip Code attribute within the Customer entity is known as a foreign key.A for-eign key enables you to relate an attribute in one entity to the attribute of anotherentity.In this case, the Zip Code attribute in the Customer entity type is related tothe Zip Code attribute of the Locale entity type.Based on information about foreign keys, the astute observer may have alreadynoticed the flaw in the Customer to Credit Card relationship.There is no way to tiecustomers to their credit card information.Either I charge random cards or don tcharge at all.A recipe for disaster no matter how you look at it.I must choose a wayto relate a customer with their credit card info.Customer name is out because Ihope to have more than one John Smith shop at my store.I must find an attributetype that will always be unique within the Customer entity type.094932-4 ch07 [ Pobierz całość w formacie PDF ]