World Campus IST210 – Entity Relationship Modeling
Assignment: Case # 11 from Chapter 4 of the textbook
Business Rules for Part A:
Aircraft
1. There are many plane types available in the fleet.
2. There can be more than one plane per type in the fleet.
3. Aircraft can be of only one type. (Rating determines whether the aircraft is jet powered or turbojet powered. This rating is required if the aircraft exceeds 12,500 pounds.).
4. Aircraft can transport passengers, cargo, or both passengers and cargo.
5. Each plane type can hold n passengers, n cargo, or both n passengers and n cargo.
Charter
6. All aircraft are available for charter.
7. Each charter trip has only one plane assigned to it.
8. Aircraft can operate in the United States and Canada.
9. Each aircraft can be assigned to many trips at different dates and times.
10. A trip can be two or more parts including departure point (Round trip miles are based on the actual navigational path flown).
11. Each part of a trip has a departure and arrival time.
12. Waiting periods between destination points are factored into total trip time. (Waiting time = hours waited * aircraft model waiting charge).
13. Distance flown = round trip miles * aircraft model charge per mile.
14. Each trip yields revenue to Charter company, Charges are comprised of aircraft model used, distance flown, waiting time, special customer requirements, and crew expenses.
15. Mileage includes expense of pilots and required crew.
16. A charter trip may only be billed to a single customer.
17. Each charter trip may have special charges based on customer requests.
18. A customer may reserve many trip dates and times.
19. Customers may request additional crew.
Employee
20. A crew can be one or more employees.
21. Not all employees are pilots.
22. An employee can qualify for one or more functions.
23. Additional crew are charged hourly rates based on their qualifications.
24. Each aircraft type has specific crew function requirements (pilot type, load master, flight attendant, flight engineer, etc.).
25. A crew can operate only 1 aircraft per trip.
26. A crew can be assigned to only one trip per date and time
Certification and License
27. Each crew member function has one or more required certifications that must be current.
28. Medical certificates can be type I, II, or III. (Type I and II medical certificates are required for commercial flight operations.).
29. Pilots must have both a current checkride certificate and medical certificate.
30. Flight attendantsand Load masters must have practical certificate and pass a drug test.
31. Crew members can have multiple certifications in different areas.
32. Non-pilot crew are not required to take pilot-specific tests.
33. Pilot crew are not required to take load master and flight attendant tests.
34. Pilots must have one type of license (commercial or ATP).
35. Pilot licenses have many ratings (License ratings determine the type of aircraft that can be operated by license holder e.g. MEL (multi-engine Landplane) or MES (multi-engine Seaplane) and the conditions that can be operated under by license holder e.g. IMC (Instrument Meteorological Conditions), Visual Flight Rules (VFR).)
Expense
36. Each charter trip may have many crew expenses.
37. An employee can have only one expense report per trip.
38. Crew expenses are limited to meals, lodging, and ground transportation.
39. An expense report can contain many expense types.
40. An expense item must have only one expense type.
41. An expense type can have many items.
42. Each expense item must have a cost and a date.
Customer
43. Customer can request many trips on different dates and times.
44. Customer pays charges at flight completion.
45. Customer may have credit account with charter.
46. Customer may pay partial amount and charge the remainder to credit account.
47. Customer may charge entire amount to credit account.
48. Charge amount cannot exceed available credit amount.
49. Customer may pay off previous trips at any time.