In this assignment you are going to continue using Visio to create an ERD for an airport database design.
In this assignment you are going to continue using Visio to create an ERD for an airport database design.
For Assignment 4 you are going to create an ER diagram using Microsoft Visio for the airport database design (using information below). For this assignment you are required to specify the various attributes of each entity and relationship as well as the key and participation constraints.
Note: For the database design, Visio 2016 does support the N:M relationship (This is discussed in the Appendix D document that is in the lecture Chapter 5 section). Instead, for this assignment, you will create an intersection table and two 1:N relationship. Also do not use the dynamic connector in Visio, you must use the relationship connector for this assignment.
• Every airplane is a specific model (e.g., Boeing 737), and has a unique registration number.
• Each model has a model number (as above – Boeing 737), a capacity and the maximum range information for the plane.
• Technicians are responsible for the maintenance. The database will store the name, SSN, phone number and the salary information for each technician.
• Each technician is capable of maintaining at least one model. Each model can be maintained by more than one technician with the expertise.
• In addition to the technicians (who are part of the employees), the airport also has some traffic controller employees. The database needs to store their name, SSN, phone number. The airport does not track the salary information since they are part of another organization. Traffic controllers must attend an annual training session but we only need to store date of the most recent training.
• Every employee belongs to a union. You must store the union membership number for each employee.
• The airport has a number of tests regularly to ensure that airplanes are still workable. Each test has a test number, a name, and a maximum possible score.
• The airport keeps track of each test event that a given airplane is tested by a given technician using a given test. It needs to record the date, and the received score on the test.
