تصميم قاعدة البيانات

Problem 4

Major airlines companies that provide passenger services keep database with lots of information on all airlines.

1. Each airline has an identification number, name and address, name of the contact person and telephone numbers.

2. Each employee works in Airline Company has an employee identification number, name, address, birthday recorded as (day, month, year), gender, position with the company, and qualifications.

3. Each airline owns different aircrafts. For each aircraft an aircraft identification number, capacity, and model is recorded.

4. The aircrafts are assigned to different routes. An aircraft can work on more than one route and a route has many aircrafts going on it. Some information as number of passengers, price per passenger, departure date time, arrival date time and the time that aircraft spent in travelling the route are recorded.

Each route has a route identification number, origin, destination, distance,classification (e.g. domestic or international route).

5. Each aircraft has its own crew (major pilot, assistant pilot and two hostesses), the aircraft crew not stored as employee. Each crew is assigned to only one aircraft.

6. Each airline keeps information about their buy/sell transactions (for example selling an airplane ticket is a sell transaction, paying for maintenance is a buy transaction). Each transaction has a transaction identification number, date, description, and amount of money paid/received.

Draw an E-R diagram for the database presented above.