Create the database SQL file for the following conditions. You will be creating a database for Regal Airways.

The airline company has several types of aircrafts, each with with different capacity, mileage, routing range, different travel classes (like economy class, premium economy class business class, first class, family class), different types of services (like WiFi, battery charger and include some more services of your choice) and price. Being an international company, it has several branches all over the world, offering domestic as well as international flights. It also offers several discount schemes for its member customers, for children and people with disabilities. Furthermore, different aircraft will have different number of crew members (like air hostess, pilots, security personnel especially for international flights). Due to the quality of services offered by the airlines has received number of prestigious awards in the past.

Following are some business rules that needs to be considered in the design:

Customers:

Customers are welcomed to the flight based upon flight availability.

All customers are required to submit valid contact details.

All customers must reserve a flight to travel. No on the gate sales are available.

Customers can buy tickets only at sales counter.

Full payments are necessary in-order to confirm a booking.

There can be several discounts schemes which can be claimed by customers & is given upon proper alignment with discount descriptions.

Customers must be penalized for cancellation.

How much charges they have to pay, depends when they are canceling the flights.

If its before 10 hour of flight they need to pay 10% of sales and if its within 10 hour of flight 33.33% will be deducted from actual sales amount

customers can demand the cancellation & 100% refund of flight is cancelled due to technical reason, bad weather.

All customers are allowed up to 5 KG cargo free with each ticket. o Exceeding 5 KG causes the additional fees. o Each customer is eligible for $20,000 life insurance. o Wheel chairs & oxygen is available for special passengers.

Employees:

All employees must be dressed according to company dress code with an ID card, easily visible to guests.

Employees are expected to be presence on their seat within duty hours. o All employees are assigned to serve client based on first come first services.

Employees are hereby responsible for serving customers first, second their own jobs.

Employees are not allowed to take flight reservations within 10 hour of flight.

There are different types of transactions takes place in Real Airways on day to day basis. These transactions include flight reservation, flight cancellation, passenger information, membership, schedule publishing and flight payments.

Customer should be able to make following transactions

Make new reservation

One-way

Round way

Multi way

Cancel an existing reservation

View the itinerary

Enroll for membership

Make payment via cash/debit/credit o Employee should be able to perform:

Reporting:

Get all customers who have seats reserved on a given flight.

Get all the details about the crew members on that flight.

Get all flights for a given airport.

View flight rooster, schedule.

Get all flights whose arrival and departure times are on time/delayed.

Calculate total sales for a given flight.

Administrative :

Add/Delete a flight

Add a new airport

Update fare for flights

Update crew members for the flight

Update departure/arrival time for the flight

Any missing or relevant data can be assumed by the while designing. However, all such assumptions have to be clearly specified