Data and Information ManagementNormalization AssignmentTo document dependencies you can use the following notation:StudentID –> StLname, StFname, StDOBClassID –> ClassRoom, ClassCapacity, CourseID, InstructorIDInstructorID –> InstructorLname, InstructorFnameCourseID –> CourseName, CourseHrs, CourseDescriptionClassID, StudentID –> GradeInstructions:Create the original and 3NF dependency diagrams as well as an ERD in Visio thatcapturesthe information requirements described below.Combine your diagrams and ERD into one file and save in PDF format, and submityour fileScenario:A friend of yours works for a company that runs charter flights and has asked you tohelp her evaluatea database that contains the table structure shown below in Table 1. She created thistable so she cankeep track of the charter flights flown by the company’s planes. AC_NUMBER is theaircraft number.CHAR_PAX indicates the number of passengers carried. The CHAR_MILES entry isbased on roundtripmiles, including pickup points. (Hint: Look at the data values to determine the natureof therelationships. For example, note that employee Melton has flown two charter trips aspilot and one tripas copilot.)Assignment1. You recognize that the existing table structure is very poor and want to help yourfriend.Given what you see in the table above, draw the dependency diagram for the existingtablestructure. Label all transitive and/or partial dependencies. (20 points)2. You notice several dependencies in the original diagram and now need to break upthedependency diagram you drew to produce dependency diagrams that are in 3NF. Asyouwork through the diagram you might have to create a few new attributes based on thescenario description. You also should make sure that the new dependency diagramscontainattributes that meet proper design criteria; that is, make sure that there are nomultivaluedattributes, that the naming conventions are met, and so on.) (25 points)3. Now that you have a better understanding of the data that your friend is trying totrack, listthe business rules that you have identified (for example, every charter trip must have aatleast one crew member, i.e. pilot, but could have many). (10 points)4. You know that normalization is a very useful tool to help eliminate dataredundancies,however, it is incapable of serving as the sole source of good database design. Everygooddatabase design must start with a data model. Thus, to help your friend you decide tocreatean ER diagram to properly document and design the database that she needs. Basedon thetables that you identified through normalization, create a Crow’s Foot ERD. Label allentities, attributes, keys (primary and foreign) and relationships. Make sure allattributesare atomic and properly named. Resolve all many-to-many relationships by creatingassociative entities and identify their primary and foreign keys. (20 points)