Using the following narrative, design and document a conceptual data model using Erwin. You should also make and document any reasonable assumptions where you feel the case is vague or ambiguous or missing any data necessary to meet the requirements. However, you cannot ignore or overrule the requirements that are explicitly stated. Show all cardinality, primary keys and foreign keys. Use verbs to clarify your relationships. Show only the attributes that are contained explicitly in the case. Use role names for Foreign keys to clarify attributes.

The narrative

The Fly By Night Operation (FNBO) is a newly formed airline aimed at the growing market of clandestine travelers (fugitives, spies, con artists, scoundrels, deadbeats, etc). The FNBO needs a database to track flights, customers, fares, aircraft, and personnel assignment. Since the FNBO is touted as a fast way out of town, individual seats are not assigned, and connecting flights with other carriers are not tracked. More specific notes about different requirements are listed below:

Information about a flight includes its unique flight number, its city and country of origin, its city and country of destination and (roughly) estimated departure and arrival dates and times. To reduce costs, the FNBO only has nonstop flights with a single origin and destination city. Information about past as well as upcoming flights is retained.

For each city involved as either an origin or a destination, FBNO has a contact person to help smooth custom and immigration procedures for which services that person receives a negotiated fee as well as the fee, the name and phone # of this smoother needs to be retained.

All flights are scheduled as a special flight on a one-off basis. Aircraft and flight personnel are assigned to each scheduled flight. Employees assigned to a flight are identified by their employee number and their flight role (e.g. captain, navigator, flight attendant, security).

Each flight may have a different complement of personnel and to save costs an employee may perform different roles on different flights. Each aircraft used on a flight has its own unique serial number as well as a model type, capacity, and a date for its next scheduled maintenance.

A history of maintenance activity is kept for each aircraft. Recorded is the maintenance activity date, a general description of maintenance activity performed and the employees that worked on that maintenance, one of whom would have been designated as the maintenance chief. The personnel working maintenance is not fixed but may vary with different employees and chiefs assigned to any particular maintenance activity.

Information to be kept on employees will be their unique employee number, name, phone, normal job title and immediate supervisor if any.

Information to be kept on customers will be their unique customer number, phone and name (even though it will typically be an alias). Some customers are regulars and have flown many times with FBNO.

Information is maintained for individual reservations on scheduled flights including a unique reservation number, the unique flight number, the customer number, reservation date, fare amount and the method of payment (cash preferred but a barter deal is acceptable e.g. gold, jewelry, bearer bonds etc.).