236 Part 2 Design Concepts 3. Using the INVOICE table structure shown in Table P6.3, do the following: TABLE P6.- ATTRIBUTE SAMPLE VALUE SAMPLE VALUE TABLE P6.3 NAME ATTRIBUTE SAMPLE VALUE SAMPLE VALUE SAMPLE VALUE STU NUM NAME 211348 211349 211347 STU LNAME 211347 GH-778345P INV NUM 211347 AA-E3422QW RU-995748G STU MAJO QD-300932X 15-Jan-2018 16-Jan-2018 PROD_NUM AA-E3422QW 15-Jan-2018 DEPT_COD SALE_DATE 15-Jan-2018 15-Jan-2018 Power drill Rotary sander DEPT_NAN Band saw PROD LABEL Rotary sander 0.25-in. drill bit 211 157 DEPT PHC 309 VEND_CODE 211 211 ToughGo, Inc. NeverFail, Inc. COLLEGE BeGood, Inc. VEND_NAME NeverFail, Inc. NeverFail, Inc. ADVISOR QUANT_SOLD 8. $39.99 $49.95 $87.75 ADVISOR PROD_PRICE $49.95 $3.45 ADVISOF ADVISOF a. Write the relational schema, draw its dependency diagram, and identify ai dependencies, including all partial and transitive dependencies. You can assume that the table does not contain repeating groups and that an invoice number rf erences more than one product. (Hint: This table uses a composite primary ke STU GP. STU HC STU_CL b. Remove all partial dependencies, write the relational schema, and draw the ne. dependency diagrams. Identify the normal forms for each table structure vo created. you Note assu bet You can assume that any given product is supplied by a single vendor, but a ven- dor can supply many products. Therefore, it is proper to conclude that the follow- ing dependency exists: PROD_NUM → PROD_LABEL, PROD_PRICE, VEND_CODE, VEND_NAME (Hint: Your actions should produce three dependency diagrams.) TA AT c. Remove all transitive dependencies, write the relational schema, and draw the new dependency diagrams. Also identify the normal forms for each table struc- ture you created. ITE IT d. Draw the Crow's Foot ERD. RO 4. Using the STUDENT table structure shown in Table P6.4, do the following: a. Write the relational schema and draw its dependency diagram. Identify an der dencies, including all transitive dependencies. b. Write the relational schema and draw the dependency diagram to meet the 3NF 2NF is appropriate. If necessary, add or modify attributes to create appropriate determinants and to adhere to the naming conventions. requirements to the greatest practical extent possible. If you believe that practical considerations dictate using a 2NF structure, explain why your decision to retain c. Using the results of Problem 4, draw the Crow's Foot ERD. ШИ 5.