Question

Design a database through an E/R diagram to store information about a flight mission planning system. The aircrafts in the system have a unique id, model, width, height, maximum velocity and fuel capacity information. The aircrafts may be in two types: cargo plane or fighter. In addition to aircrafts information, cargo planes have extra information named load capacity and fighter planes have bomb capacity. The pilots in the system have name information, which consists of forename and surname. In addition, the pilots have salary, rank and minimum one phone number. A pilot works in one of the squadrons defined in the system, and pilot ids are unique within a squadron. The squadrons have name and city name. A pilot can perform a flight mission which has a certain route and uses an aircraft. Each flight mission has unique number, task date and estimated fuel requirement information. Each route consists of a unique id, departure airport name, landing airport name and it is associated with one or more route points. Each route point can exist with its owner route, and it has latitude, longitude and altitude information. There is previous or next relation between route point pairs in order to provide point ordering. In addition, during a flight mission there may be zero or more forbidden circular zones. Id of a zone is unique within its flight mission, and each zone has center coordinates (latitude - longitude) and radius information. The reports in the system are used to analyze mission results. Each report has a unique id, mission success rate, maintenance status, fuel consumed and comment information.

Answer 1