You were just hired by an airline to create a database to record the various types of aircraft in their fleet; all types of aircraft each pilot is certified to fly, and the date a pilot’s certification for each type of aircraft expires.Discussions with the airline representatives focused on two initial entities, Pilots and Aircraft; the following key points were agreed:1. Each pilot is given a unique PilotID2. Each aircraft type has a unique AirID designation3. Each pilot may be certified to fly zero, one, or many types of aircraft, and each type of aircraft may be certified to be flown by zero, one, or many pilots.4. The cDate field is the date the pilot’s certification on that type of aircraft expires5. DOB is date of birth6. nOwned is the number of that type of aircraft in service by the airline7. No fields beyond those in the spreadsheet are needed.The objective of this exercise is to demonstrate an understanding of some basic concepts covered in the course. The exercise is a straight forward application of those concepts – there are no “hidden” complexities – should you identify something in the key points or data that adds complexity, contact the instructor before submission – you may be over thinking the exercise.The sample data may not represent all possible values of each field – consider each field’s domain during the design.Your objective is to replace the following report with a relational database. Your submission will consist of a word compatible document to record the design process, and an Access DB.Here is the un-normalized relational schema (table notation) for the above report / table:PilotAircraft (PilotID, LastName, FirstName, DOB, (AirID, nOwned, cDate))The specific tasks are listed below, the percentage in parenthesis corresponds to the grade weight for each task.Name your document Last Name_CaseStudy (i.e. Smith_CaseStudy). When you are asked to provide an explanation or description, you must include enough content to demonstrate that you understand the definition, term, concept, etc. and how it applies to this exercise.SUGGESTION: Review the Terms and Concepts Forum, especially the Many-to-Many and One-To-Many Normalization Process: Parts and Suppliers examples. There is also a normalization MP4 file that can be downloaded from the Resources section.You must use the following outline in your submission – only include the section numbers – not the questions or other content from the instructions. 1) Review the existing report, with the sample data (as well as field domains and common knowledge) and document any assumptions you feel are appropriate (beyond those in the key points). (10%) 2 a) Submit a functional dependency analysis (all fields must be included at least once), use the functional dependency notation shown below and on pages 410-413 Examples 14.2, 14.3, 14.4 and 14.5 of the Connolly text to document the functional dependencies. Only one set of functional dependencies is needed. There may be one or more rows of functional dependencies.(10%)Functional dependency notation example:Field A -> Field B, Field C, means that Field A is a determinant for Field B and Field C.Similarly if a field is dependent on the combination of values of two fields then:Field X, Field Y -> Field Z2 b) Define functional dependency, and explain one row of the submitted functional dependencies in plain English (use field names and values in this exercise). (10%) (you do not need to explain partial or transitive dependency) 3) Explain the relationship between the initial Entities in plain English – (either one-to-many, or many-to-many). (10%) 4) Design: Hint: all fields should be included in each normal form level.a) Submit a complete set of 1NF table(s). You must use relational scheme, table notation (see the above UNF example). Also refer to the relational schema under Figure 4.2.6 on page 111 of the Connolly text) include the first normal form definition, and a plain English (original) explanation of why the table(s) is (are) in 1NF. Copy the definition from the Connolly text and include fields from this exercise in the original explanation. (10%) b) Submit a complete set of 2NF table(s). You must use relational scheme, table notation (see the above UNF example). Also refer to the relational schema under Figure 4.2.6 on page 111 of the Connolly text) include the second normal form definition, and a plain English (original) explanation of why the table(s) is (are) in 2NF. Copy the definition from the Connolly text and include fields from this exercise in the original explanation. (10%) c) Submit a complete set of 3NF table(s). You must use relational scheme, table notation (see the above UNF example). Also refer to the relational schema under Figure 4.2.6 on page 111 of the Connolly text) include the third normal form definition, and a plain English (original) explanation of why the table(s) is (are) in 3NF. Copy the definition from the Connolly text and include fields from this exercise in the original explanation. (10%) 5) Use the 3NF tables in your design, to create an new MS Access database, including the data ,Name your database Last Name_CaseStudy (i.e. Smith_CaseStudy). (5%).6) Create a Query (In MS Access), that provides the results for the following request: List each PilotID, FirstName, LastName, and all their AirID certifications, sorted by pilot. (5%)7) Create one Form: New Pilot Input - this form should include all relevant fields (5%)8) Create One Report: Pilot List – list all pilots - this report should include all relevant fields (5%)Upload the database to the assignment area as one of the deliverables9) Submission content organization, clarity, spelling and grammar (10%)

Assignment: Provide a reflection of at least 500 words (or 2 pages double spaced) of how the knowledge, skills, or theories of this course have been applied, or could be applied, in a practical manner to your current work environment. If you are not currently working, share times when you have or could observe these theories and knowledge could be applied to an employment opportunity in your field of study. Course: InfoTech in a Global EconomyRequirements:Provide a 500 word (or 2 pages double spaced) minimum reflection.Use of proper APA formatting and citations. If supporting evidence from outside resources is used those must be properly cited.Share a personal connection that identifies specific knowledge and theories from this course.Demonstrate a connection to your current work environment. If you are not employed, demonstrate a connection to your desired work environment.You should NOT, provide an overview of the assignments assigned in the course. The assignment asks that you reflect how the knowledge and skills obtained through meeting course objectives were applied or could be applied in the workplace.No Plagarism