Chapter 5 - Normalization Review Questions 1. 2. 3. 3. 4. 5. What is normalization? ? When is a table in 1NF, 2 NF and 3 NF? Explain Boyce Codd normal form (BCNF)? What is a partial dependency? With what normal fommisit associated? Using the table shown below (refer to Cho Construcco database) discuss the three types of anomalies due to data redundancy. DATA ORG INF PROJ_NUM - PROJ_NAME - EMP_NUN 15 Evergreen 103 15 Evergreen 101 15 Evergreen 105 15 Evergreen 106 15 Evergreen 102 18 Amber Wave 114 18 Amber Wave 118 18 Amber Wave 104 18 Amber Wave 112 22 Rolling Tide 105 22 Rolling Tide 104 22 Rolling Tide 113 22 Rolling Tide 111 22 Rolling Tide 106 25 Starflight 107 25 Starflight 115 25 Starflight 101 EMP_NAME JOB_CLASS - CHG_HOL - HOURS, June E. Arbough Elect Engineer $84.50 23.8 John G. News Database Designer $105.00 19.4 Alice K. Johnson Database Designer $105.00 35.7 William Smithfield Programmer $35.75 12.6 David H. Senior Systems Analyst S96.75 23.8 Annelise Jones Applications Designer $48.10 24.6 James J. Frommer General Support $18.36 45.3 Anne K. Ramoras Systems Analyst $96.75 32.4 Darlene M. Smithson DSS Analyst $45.95 440 Alice K. Johnson Database Designer $105.00 64.7 Anne K. Ramoras Systems Analyst $96.75 48.4 Delbert K. Joenbrood Applications Designer $48.10 23.6 Geoff B. Wabash Clerical Support $26.87 220 William Smithfield Programmer $35.75 12.8 Maria D. Alonzo Programmer $35.75 24.6 Travis B. Bawangi Systems Analyst $96.75 45.8 John G. News Database Designer $105.00 56.3 6. Define and discuss the concept of transitive dependency? Problems: 7. Using the INVOICE table structure shown below draw its dependency diagram and identify all dependencies (including all partial and transitive dependencies). You can assume that the table does not contain repeating groups and that any invoice number may reference more than one product. (Hint: This table uses a composite key. Sample Sample Value Value Sample Invoice Records Attribute Sample Name Value Inv Num 211347 Prod Num AA-E34 Sale Dale 20-Jul-11 Prod Label Sander Vend Code 211 Vend Name Hitachi Qty Sold 1 Prod Price $80.99 211347 BD-320 20-Jul-11 Drill bit 211 Hitachi 5 $11.50 Sample Value Sample Value 211317 211318 RV-120 - AA-E31 20-Jul-11 20-Jul-11 Circular Saw Sander 309 211 Builders Inc Hitachi 1 2 $295.95 $80.99 211319 GH-315 21-Jul-11 Power drill 157 Toughco 1 $150.50 8. Using the initial dependency diagram shown in question 7, remove all partial dependencies, draw the new dependency diagrams, and identify the normal foms for each table structure you created (You can assume that any given product is supplied by a single vendor, but a vendor can supply many products. Therefore, it is proper to conclude that the following dependency exists:Prod Num Prod Description, Prod Price, Vend Code, Vend Name (Hint: There will be three dependency diagrams.) 9. Using the table structure you created in the previous question, remove all transitive dependencies, draw the new dependency diagrams, and identify the normal forms for each table structure you created. 10. Using the results of the previous question, draw the Crow's Foot ERD. 11. What is denormalization and what are its advantages and disadvantages
