Case Study: Malaysia Airlines Reservation System There are four different airlines company in Malaysia: Echo Airline...

Transcribed Image Text:

Case Study: Malaysia Airlines Reservation System There are four different airlines company in Malaysia: Echo Airline (EA1709), Spark Airways (SA1865), Peak Airways (PA2098), Core Airways (CA8760). Their flights involve the following six (6) states: Perak, Negeri Sembilan, Pahang, Sabah, Sarawak, Wilayah Persekutuan Kuala Lumpur. In each of the six (6) states, there is a (single) booking office. You are requested by the airlines company to design a central air-reservation database to be used by all booking offices. Flight has flight number, number of total seats in business class, number of total seats in economy class, date of departure, time of departure (in local time, and time is always in hours and minutes), date of arrival, time of arrival (in local time), origin state and destination state. For each flight travel, the flight must be piloted by two pilots (captain and co-captain) and maximum of 3 flight attendants. A pilot may fly one or more flights. Pilot has staff number, staff first name, staff last name, age, year of experience, position, flying hours, and salary. To be a senior pilot, he/she need to have 20,000 flying hours. Flight attendant has unique id, full name, position, salary, phone number, and address. Address has street, city, province or state, postal code, and country. The customers may come from any state, not just the six (6) above, and from any city in Malaysia. Customer has first and last name, mailing address, zero or more house phone numbers, and zero or more email addresses. Mailing address has street, city, province or state, postal code, and country. A customer may book one or more flights in each booking. The email address is unique for each customer. But the first and last names do not have to be unique. Booking has a unique booking number, booking state (which state the booking was made from), booking date, flight number, number of seats booked for each flight, total charges for each flight and booking total. Business Rules & Normalization Based on the case study, write a list of a complete business rules Provide an example of UNF and perform normalization up to 3NF clearly showing all the steps with explanation Case Study: Malaysia Airlines Reservation System There are four different airlines company in Malaysia: Echo Airline (EA1709), Spark Airways (SA1865), Peak Airways (PA2098), Core Airways (CA8760). Their flights involve the following six (6) states: Perak, Negeri Sembilan, Pahang, Sabah, Sarawak, Wilayah Persekutuan Kuala Lumpur. In each of the six (6) states, there is a (single) booking office. You are requested by the airlines company to design a central air-reservation database to be used by all booking offices. Flight has flight number, number of total seats in business class, number of total seats in economy class, date of departure, time of departure (in local time, and time is always in hours and minutes), date of arrival, time of arrival (in local time), origin state and destination state. For each flight travel, the flight must be piloted by two pilots (captain and co-captain) and maximum of 3 flight attendants. A pilot may fly one or more flights. Pilot has staff number, staff first name, staff last name, age, year of experience, position, flying hours, and salary. To be a senior pilot, he/she need to have 20,000 flying hours. Flight attendant has unique id, full name, position, salary, phone number, and address. Address has street, city, province or state, postal code, and country. The customers may come from any state, not just the six (6) above, and from any city in Malaysia. Customer has first and last name, mailing address, zero or more house phone numbers, and zero or more email addresses. Mailing address has street, city, province or state, postal code, and country. A customer may book one or more flights in each booking. The email address is unique for each customer. But the first and last names do not have to be unique. Booking has a unique booking number, booking state (which state the booking was made from), booking date, flight number, number of seats booked for each flight, total charges for each flight and booking total. Business Rules & Normalization Based on the case study, write a list of a complete business rules Provide an example of UNF and perform normalization up to 3NF clearly showing all the steps with explanation