Chapter 2 DATABASE DESIGN FOR SQL SERVER 2000
Chapter 2 DATABASE DESIGN FOR SQL SERVER 2000 79 As stated previously, a database design s initial goal is to achieve the value and correctness of the third normal form to remove all data redundancy. The third normal form ensures elimination of data redundancy by eliminating interdependencies between non-key attributes. Identifying a 3NF violation involves looking at an attribute and then asking whether this attribute depends on the availability of any other non-key attribute. For example, examine the entity in Figure 2.9. The Trainee entity is violating one of the restrictions of 3NF because it has three interdependent non-key attributes, which are EnrolledCourseID, EnrolledCourse, and DateEnrolled. These are referred to as interdependent attributes because they depend on the attribute EnrolledCourseID. To implement and resolve this 3NF violation, add the enrollment date to the Course table and eliminate the extra unnecessary columns. This eliminates the redundant data in an entity (see Figure 2.10). Traine. TraineeCours. Course Traine. I. Cours. I. Firs. Nam. Cours. I. . F. Traine. I. . F. Name Las. Nam. Location Stree. Addres. Cost Cit. Availabl. Stat. Cours. Dat. Zi. Cod. Gende. Dat. Registere. Dat. o. Birt. SS. Nam. o. Cours. Paymen. Du. Normalizing a design provides for efficient use of storage, but in many situations will affect performance. Planned redundancy or denormalization are often brought into the design to provide for better performance or to clarify data. When to Denormalize Denormalization is the planned placement of redundant data to minimize table joins, reduce network traffic, eliminate repeated calculations, and provide for application-specific procedures. A database that is used primarily for decision support (as opposed to update-intensive Traine. Traine. I. Nam. Addres. Gende. Dat. Registere. Dat. o. Birt. SS. Nam. o. Cours. Paymen. Du. Enrolle. Cours. I. Enrolle. Cours. Dat. Enrolle. FIGURE 2.9 The Trainee entity violating a restriction of 3NF. FIGURE 2.10 The Trainee entity is no longer violating 3NF.
For reliable and cheap web hosting services please check tomcat web hosting website.