In this exercise, you will evaluate a logical design and then redesign it based on your knowledge of keys and normalization to produce a fully normalized data design. | Activity Normalizing Data In this activity you will normalize a set of entities from the Ferguson and Bardell Inc. case study. Objectives After completing this activity you will be able to Normalize a logical data design to third normal form. Before You Begin This activity is completed individually with a class discussion to follow. Time to complete this activity 30 minutes 26 Activity Normalizing Data Exercise 1 Creating a Normalized Data Design In this exercise you will evaluate a logical design and then redesign it based on your knowledge of keys and normalization to produce a fully normalized data design. Normalize the design to third normal form 1. Review the ER diagram on the next page. 2. Identify the areas of the ER diagram in which normalization rules are violated as follows a. Data is duplicated within multiple entities. b. Attributes of an entity are not directly associated with that entity. c. Each entity does not have attributes that uniquely define each instance. 3. On the page opposite the ER diagram sketch out your own ER diagram with normalization applied to the third normal form. Tip Keep in mind that the data design shown on the next page is not optimized in any way. The keys provided may need to be redesigned as part of the normalization process. Next you will discuss your answers with the class. Activity Normalizing Data 27 Contracts With Employee EmployeeID PK Name Address SSN E-Mail 1 Type Completes Salary BillableRate Vehicle VehicleID PK Is Issued EmployeeID FK Make Model VIN Year BeginMileage EndMileage MaintenanceCost1 MaintenanceDesc1 MaintenanceDate1 MaintenanceMiles1 MaintenanceCost2 MaintenanceDesc2 MaintenanceDate2 MaintenanceMiles2 MaintenanceCost3 MaintenanceDesc3 MaintenanceDate3 MaintenanceMiles3 Timesheet Invoice Contract ContractID PK ClientID FK EmployeeID FK 1 œ StartDate EndDate Description Purchases Client ClientID PK 1 Name Address City State Zip
