1. Engineering
  2. Computer Science
  3. draw an er diagram for the airline database...

Question: draw an er diagram for the airline database...

Question details

Draw an ER diagram for the airline database

1. Each airplane that the company operates has a unique registration number (e.g., A9876), and is of a specific model (e.g., B781). In addition, the following information is maintained for each airplane: the year it was purchased, the year it was manufactured, and its passenger capacity 2. Each airport has a unique code (e.g., PUW), a name (e.g., Pullman-Moscow Airport), a city, and a state. For each flight, the flight number (e.g., WA254), the scheduled departure time, and scheduled arrival time is recorded. Each flight is identified with its flight number and departure time (i.e., there can be only one flight with a specific flight number per day). 3. 4. All flights are non-stop, i.e., each flight departs/arrives from/to a single airport. Every flight is associated with the codes of its departure/arrival airports. Each flight is assigned to a single airplane. Each employee who work for the airline company has a unique SSN, an employee ID, a phone number, an address, birthdate, and a job description. Employees include pilots, flight attendants, technicians, and service personnel. An employee cannot conduct two types of tasks, i.e., a pilot cant also serve as a technician. For each pilot, the company keeps track of the date he/she got certified to operate airplanes. A pilot can operate multiple flights. Each flight should have at least one pilot assigned. For each flight attendant, the company keeps track of the number of years of experience A flight attendant can be assigned to multiple flights and a flight has one or more flight attendants. 5. 6. 7. 8. 9. 10. Technicians check airplanes before and after each flight. A technician can be assigned to multiple airplanes. Each airplane has exactly one technician assigned to it. 11. The service staffs work in airports. Each service staff is associates with an airport and he/she cant work in more than one airport. Of course, an airport might have several service staffs. 12. The company owns a number of VIP lounges at various airports. Each lounge has an ID number (e.g., V41) and a floor number (e.g., 2). Each lounge is identified with its ID and the airport code (i.e., the lounge ID is unique in a particular airport.) 13. Customers need to register their current address, gender, information about one or more credit cards, and an email address. The company assigns a unique ID for each customer 14. Each credit card consists of a number and an expiration date. (You may assume that each credit card number is unique.) 15. Customers make reservations for flights. For each reservation the unique reservation ID the number of tickets purchased, the total purchase price, the date of the purchase is recorded. A customer can make many reservations for multiple flights and a flight can have many reservations by multiple customers. Also a reservation can be made for multiple flights and multiple customers. 16. Customers can become members of the lounges. For each membership, the membership start date and the type of the membership is recorded. All lounges should have at least one member otherwise it will be closed.

Solution by an expert tutor
Blurred Solution
This question has been solved
Subscribe to see this solution