This book contains examples for each step in the design process for several hy- pothetical business...

  

Transcribed Image Text:

This book contains examples for each step in the design process for several hy- pothetical business applications. One of these is Moon Launch Enterprise, which markets moon junkets to the business or vacationing traveler in the year 2010. Users want a system to support scheduling and managing Moon Launch space trips. We will expand the details of the enterprise as needed to support our design examples. Your first data modeling work session for this case study is with the passen- ger reservations agent of Moon Launch Enterprise. This agent is responsible for placing reservations for business and vacationing passengers. Of course, a given passenger can be a business passenger for some trips and a vacation pas- senger for others. The details follow. The sentences are numbered for easy ref- erence. 1. Passenger Information a. Moon Launch assigns all passengers identification numbers within their respective countries (e.g., in the United States, the identification number may be the Social Security number). The numbers used for each country vary in size and may include special characters (e.g.. So- cial Security number contains two hyphens). b. There may be a future need to record the passenger's age, in case Moon Launch Enterprise decides to offer special fares by age (e.g.. children's fares). For now, fares differ only on the basis of credit- rating discounts. C. Each passenger has a last name, first name, middle name (perhaps), and title (e.g., Mr., Mrs., Msr., Señor). d. Each passenger has a mailing address for tickets and a mailing address for billing. In the case of a business passenger, the mailing address for billing is place of business. The mailing address for tickets can be ei- ther personal residence or place of business. In the case of vacation passengers, both tickets and bills are mailed to personal residence. e. All passengers have credit ratings assigned to them by Moon Launch Enterprise. For a business passenger, the credit rating is that of the place of business. 2. Reservation Information a. The reservation agent records flight, method of payment (e.g., World Express Card), price of ticket, and entry date of the reservation. The agent also records his own Moon Launch employee identification number with the reservations. b. To calculate ticket prices, the agent checks each passenger's credit rat- ing and applies the following discounts to the base price: Credit rating Discount A - 20% - 10% 0% + 10% (Note: Credit Rating D is for undesirable passengers.) C. All seats are nonsmoking and have no windows. There is more information in this narrative than is needed for this step. However, never refuse input from a user! It may be useful in subsequent steps. BCD с O Moon Launch schedules four flights each day: two to the moon and two returning. One hundred seats are available on each flight. On average, 75 percent are booked. Another 10 percent are booked and canceled. Also, eight crew members attend each flight. □ Approximately 50 percent of the passengers on each flight are vacationing and fly Moon Launch an average of 1.05 times per year (round trip, of course). The rest are on business and fly Moon Launch an average of three times per year. Approximately 3000 passengers book both vacation and business trips within the same year. Moon Launch deals regularly with about 1000 businesses. From each, an average of six employees per year fly. Another 2000 companies are on file; their employees fly less regularly. Moon Launch uses four credit ratings: A, B, C, and D. This book contains examples for each step in the design process for several hy- pothetical business applications. One of these is Moon Launch Enterprise, which markets moon junkets to the business or vacationing traveler in the year 2010. Users want a system to support scheduling and managing Moon Launch space trips. We will expand the details of the enterprise as needed to support our design examples. Your first data modeling work session for this case study is with the passen- ger reservations agent of Moon Launch Enterprise. This agent is responsible for placing reservations for business and vacationing passengers. Of course, a given passenger can be a business passenger for some trips and a vacation pas- senger for others. The details follow. The sentences are numbered for easy ref- erence. 1. Passenger Information a. Moon Launch assigns all passengers identification numbers within their respective countries (e.g., in the United States, the identification number may be the Social Security number). The numbers used for each country vary in size and may include special characters (e.g.. So- cial Security number contains two hyphens). b. There may be a future need to record the passenger's age, in case Moon Launch Enterprise decides to offer special fares by age (e.g.. children's fares). For now, fares differ only on the basis of credit- rating discounts. C. Each passenger has a last name, first name, middle name (perhaps), and title (e.g., Mr., Mrs., Msr., Señor). d. Each passenger has a mailing address for tickets and a mailing address for billing. In the case of a business passenger, the mailing address for billing is place of business. The mailing address for tickets can be ei- ther personal residence or place of business. In the case of vacation passengers, both tickets and bills are mailed to personal residence. e. All passengers have credit ratings assigned to them by Moon Launch Enterprise. For a business passenger, the credit rating is that of the place of business. 2. Reservation Information a. The reservation agent records flight, method of payment (e.g., World Express Card), price of ticket, and entry date of the reservation. The agent also records his own Moon Launch employee identification number with the reservations. b. To calculate ticket prices, the agent checks each passenger's credit rat- ing and applies the following discounts to the base price: Credit rating Discount A - 20% - 10% 0% + 10% (Note: Credit Rating D is for undesirable passengers.) C. All seats are nonsmoking and have no windows. There is more information in this narrative than is needed for this step. However, never refuse input from a user! It may be useful in subsequent steps. BCD с O Moon Launch schedules four flights each day: two to the moon and two returning. One hundred seats are available on each flight. On average, 75 percent are booked. Another 10 percent are booked and canceled. Also, eight crew members attend each flight. □ Approximately 50 percent of the passengers on each flight are vacationing and fly Moon Launch an average of 1.05 times per year (round trip, of course). The rest are on business and fly Moon Launch an average of three times per year. Approximately 3000 passengers book both vacation and business trips within the same year. Moon Launch deals regularly with about 1000 businesses. From each, an average of six employees per year fly. Another 2000 companies are on file; their employees fly less regularly. Moon Launch uses four credit ratings: A, B, C, and D.

Answer rating: 100% (QA)

tblpassenger This is the table for the passenger this contains all the data about the passenger tblreservation is the main table for the reservation Here all the data about the reservation is stored a... View the full answer