Problems 1 and 3 on pages 234 - 236 of the course textbook.
You must use Draw.IO or MS Visio to complete this case study.
Create an ER diagram
Explain all normalization issues that exist in the ER Diagram.
I want the ER Diagram before normalization and after normalization.
Explain all normalization problems that you corrected between the first and second ER diagram.
Normalize the data in the ER Diagram to 3NF See the video for a demonstration on how to do this:
https://www.youtube.com/watch?v=Nq91b4TQ29Q
Create a dependency diagram using the information from your ER diagram. See the video for a demonstration on how to do this:
https://www.youtube.com/watch?v=wFOJnRxqrp8
You must label all determinants and dependencies.
Note: You must convert your DrawIO files to .jpg, gif or bmp images before you upload the assignment to Canvas. Copy and paste all diagrams into an MS Word document. Please make sure that you enlarge all diagrams so they can be easily read in Canvas preview. This will account for 20% of the lab grade.
Extracted text: 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.