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. Chapter 6 Normalization of Database Tables 235Problems1 Using the descriptions of the attributes given in the figure, convert the ERD shownin Figure P6.1 into a dependency diagram that is in at least 3NF.FIGURE P6.1 APPOINTMENT ERD FOR PROBLEM 1APPOINTMENTPKApp NumFK1 Doc EmpIDApp_DateApp TimeApp_PatientIDApp_NameApp PhoneApp StreetApp_CityApp_StateApp_ZipApp_StatusDOCTORleast inPK Doc EmplDDoc FnameDoc LnameDoc CellPhonecy dia-Doc EmpID: Employee ID for the doctor.Doc Fname: Doctor's first name.Doc Lname: Doctor's last name.Doc CellPhone: Doctor's cell phone number.manycy dia-dencyApp_Num: System-generated number.App Date: The date of the appointment.App_Time: The time of the appointment.App PatientID: The ID number of the patient.App_Name: The name of the patient.App_Phone: The contact phone number of the patient.App_Street: The street address for the patient.App_City: The city the patient lives in.App_State: The state the patient lives in.App_Zip: The zip code for the patient's address.App_Status: The status of the appointment (pending, closed, cancelled) 236 Part 2 Design Concepts3. Using the INVOICE table structure shown in Table P6.3, do the following:TABLE P6.-ATTRIBUTESAMPLE VALUESAMPLE VALUETABLE P6.3NAMEATTRIBUTESAMPLE VALUE SAMPLE VALUE SAMPLE VALUESTU NUMNAME211348211349211347STU LNAME211347GH-778345PINV NUM211347AA-E3422QWRU-995748GSTU MAJOQD-300932X15-Jan-201816-Jan-2018PROD_NUMAA-E3422QW15-Jan-2018DEPT_CODSALE_DATE15-Jan-201815-Jan-2018Power drillRotary sanderDEPT_NANBand sawPROD LABELRotary sander0.25-in. drill bit211157DEPT PHC309VEND_CODE211211ToughGo, Inc.NeverFail, Inc.COLLEGEBeGood, Inc.VEND_NAMENeverFail, Inc.NeverFail, Inc.ADVISORQUANT_SOLD8.$39.99$49.95$87.75ADVISORPROD_PRICE$49.95$3.45ADVISOFADVISOFa. Write the relational schema, draw its dependency diagram, and identify aidependencies, including all partial and transitive dependencies. You can assumethat the table does not contain repeating groups and that an invoice number rferences more than one product. (Hint: This table uses a composite primary keSTU GP.STU HCSTU_CLb. Remove all partial dependencies, write the relational schema, and draw the ne.dependency diagrams. Identify the normal forms for each table structure vocreated.youNoteassubetYou 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.)TAATc. Remove all transitive dependencies, write the relational schema, and draw thenew dependency diagrams. Also identify the normal forms for each table struc-ture you created.ITEITd. Draw the Crow's Foot ERD.RO4. 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 derdencies, including all transitive dependencies.b. Write the relational schema and draw the dependency diagram to meet the 3NF2NF is appropriate. If necessary, add or modify attributes to create appropriatedeterminants and to adhere to the naming conventions.requirements to the greatest practical extent possible. If you believe that practicalconsiderations dictate using a 2NF structure, explain why your decision to retainc. Using the results of Problem 4, draw the Crow's Foot ERD.ШИ5.

Question

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. Chapter 6 Normalization of Database Tables 235Problems1 Using the descriptions of the attributes given in the figure, convert the ERD shownin Figure P6.1 into a dependency diagram that is in at least 3NF.FIGURE P6.1 APPOINTMENT ERD FOR PROBLEM 1APPOINTMENTPKApp NumFK1 Doc EmpIDApp_DateApp TimeApp_PatientIDApp_NameApp PhoneApp StreetApp_CityApp_StateApp_ZipApp_StatusDOCTORleast inPK Doc EmplDDoc FnameDoc LnameDoc CellPhonecy dia-Doc EmpID: Employee ID for the doctor.Doc Fname: Doctor's first name.Doc Lname: Doctor's last name.Doc CellPhone: Doctor's cell phone number.manycy dia-dencyApp_Num: System-generated number.App Date: The date of the appointment.App_Time: The time of the appointment.App PatientID: The ID number of the patient.App_Name: The name of the patient.App_Phone: The contact phone number of the patient.App_Street: The street address for the patient.App_City: The city the patient lives in.App_State: The state the patient lives in.App_Zip: The zip code for the patient's address.App_Status: The status of the appointment (pending, closed, cancelled) 236 Part 2 Design Concepts3. Using the INVOICE table structure shown in Table P6.3, do the following:TABLE P6.-ATTRIBUTESAMPLE VALUESAMPLE VALUETABLE P6.3NAMEATTRIBUTESAMPLE VALUE SAMPLE VALUE SAMPLE VALUESTU NUMNAME211348211349211347STU LNAME211347GH-778345PINV NUM211347AA-E3422QWRU-995748GSTU MAJOQD-300932X15-Jan-201816-Jan-2018PROD_NUMAA-E3422QW15-Jan-2018DEPT_CODSALE_DATE15-Jan-201815-Jan-2018Power drillRotary sanderDEPT_NANBand sawPROD LABELRotary sander0.25-in. drill bit211157DEPT PHC309VEND_CODE211211ToughGo, Inc.NeverFail, Inc.COLLEGEBeGood, Inc.VEND_NAMENeverFail, Inc.NeverFail, Inc.ADVISORQUANT_SOLD8.$39.99$49.95$87.75ADVISORPROD_PRICE$49.95$3.45ADVISOFADVISOFa. Write the relational schema, draw its dependency diagram, and identify aidependencies, including all partial and transitive dependencies. You can assumethat the table does not contain repeating groups and that an invoice number rferences more than one product. (Hint: This table uses a composite primary keSTU GP.STU HCSTU_CLb. Remove all partial dependencies, write the relational schema, and draw the ne.dependency diagrams. Identify the normal forms for each table structure vocreated.youNoteassubetYou 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.)TAATc. Remove all transitive dependencies, write the relational schema, and draw thenew dependency diagrams. Also identify the normal forms for each table struc-ture you created.ITEITd. Draw the Crow's Foot ERD.RO4. 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 derdencies, including all transitive dependencies.b. Write the relational schema and draw the dependency diagram to meet the 3NF2NF is appropriate. If necessary, add or modify attributes to create appropriatedeterminants and to adhere to the naming conventions.requirements to the greatest practical extent possible. If you believe that practicalconsiderations dictate using a 2NF structure, explain why your decision to retainc. Using the results of Problem 4, draw the Crow's Foot ERD.ШИ5.

Accepted Answer

Hey, since there are multiple questions posted, we will answer first question. If you want any…