Chapters Datahate Design 953 ‘The only candidate key in CUSTOMER is CustomerNumber. ZIP is not a candidate key for this relation; therefore, this relation is not normalized. Furthermore, another possible funetional dependency involves Phone. Is Phone the phone number of the CUSTOMER, or is it the phone number of the contact? If PhoncNumber is the phone number of the ‘CUSTOMER, then: GustomerNumber — Phone: and no additional normalization problem exists. However, if the PhoneNumber is that of the contact, then: ContactName —+ Phone and because ContactName is not a candidate key, there are modification problems here as well You can determine whose phone number it is by asking the users. Assume that you do that, and the users say that itis incleed the phone numberof the contact. Thus: ContactName Phone Given these facts, you can proceed to normalize the CUSTOMER table. According to the normalization process, you pull the atributes of the functional dependencies out of the ta- bles while leaving a copy of their determinants in the original relation as foreign keys. The results the three relations shown in Figure 5-5: CUSTOMER (CustomerNumber, CustomerName, Street Address, ZIP, ContactName) ZAP (ZAP, City, State} CONTACT (ContactName, Phone) ‘with the referential integrity constraints: ZIP in CUSTOMER must exist ia ZIP in ZIP ContactName in CUSTOMER must exist in ContactName in CONTACT ‘These three relations are now normalized, and you can continue with the design process. However, let us first consider another perspective on normalization. FIGURE 5-5 ‘The Normalized CUSTOMER and Associated Tables {sa foreign key referencing, 20 in 2 ContactName {sa foreign key referencing ContactName in CONTACT354 Part2 Database Design ‘FIGURE 5-6 ‘The Denormalized ‘CUSTOMER and ‘Associated CONTACT “Tables Denormalization tis possible to take normalization too fat. Most practitioners would consider the construc- tion of a separate ZIP table to be going too far. People are aceustomed to writing their city, state, and ZIP as a group, and breaking City and State away from ZIP will make the design difficult to use. It will also mean that the DBMS has to read two separaic tables just to get the customer's address. Therefore, even though it results in normalization problems, a bet- ter overall design would result by lewving ZIP, City, and State in the CUSTOMER relation. ‘This isan example of denormalization. ‘What are the consequences of this decision to denormalize? Consider the three basic ‘operations: insert, update, and delete. If you leave ZIP. City, and State in CUSTOMER, then you will not be able to insert data for a new ZIP code until a customer has that ZIP code, However, you will never want to do that. You only care about ZIP code data when ‘one of the customers bas that ZIP code. Therefore, leaving the ZIP data in CUSTOMER docs not pose problems when inserting. ‘What about modifications? If a city changes its ZEP code, then you might have to change multiple rows in CUSTOMER. How frequently do cities change their ZIP codes, though? Because the answer is almost never, upelates in the denormalized relation are not 3 pproblem. Finally, what about deletes? If only one customer has the ZIP data (80210, Denver, Colorado), then if you delete that customer you will lose the fact that 80210 is in Denver. This does not really matter because when another customer with this ZIP code is inserted, that custemer also will provide the city and state. ‘Therefore, denormalizing CUSTOMER by leaving the atributes ZIP, City, and State in the relation will make the design easier to use and not cause modification problems. The enormalized design is better, and itis shown in Figure 5-6 CUSTOMER (Customer Number. CustomesName, Street Address, City, State, ZIP, ContactName) CONTACT (ContactName, Phome) with the referential integrity constraints: (ContactName in CUSTOMER must exist in ContactName in CONTACT ‘The need for denormalization can also arise for reasons such as security and perfor: ‘mance. Ifthe cost of modification problems is low (as for ZIP codes) and if other factors cease denormalized relations to be preferred, then denormlizing is go ContactName sa foreign key referencing CContaetName in CONTACT