At Air Botswana, passengers often inquire about flight's availability based on their departure (source) and their final destinations, book and cancel tickets as well as finding out about the status of their booked tickets.

The details of flights that are often captured include the flight number, name, source, destination, and days on which it is available. The Air Botswana administrator also maintains a record of flight status comprises of dates for which tickets can be booked, total number of seats available, and number of seats already booked.

Passengers  are  allowed  to  reserve  tickets  for  flights  in  which  seats  are  available  by providing their ID's, the flight number and the date for which the ticket is being booked.

 

Assumptions

• There can only be 6 flight at Air Botswana.
• There are only two categories of tickets that can be booked namely, Special and general.
• Only ten tickets can be booked in each category.
• There can be only 4 tickets with the status "waiting."

 

Consider the following tables for the Air Botswana database.

• Flight table: stores all the details of the flights thus, the flight number, flight name, source, destination, Price for the Special ticket, Price for the General ticket and the date on which the flight is available.

 

• Flight_Status: stores information about the statuses of flights. This include the flight number , date of flight availability, total Seats/capacity, total general seats, total special seats , number of special  seats booked, and number of general seats booked.
   
• Passenger: consists of passenger details. The information stored in this table includes Passenger ID, name, date of birth, sex and address (city and street) of the passenger, status of reservation (either confirmed or waiting), and category for which ticket is booked.

 

Tasks

 

1. Please help me create ER diagram for the above scenario. Use Crow foot notation.

 

2. Please help me convert the ER diagram into a relational database schema. Be certain to indicate primary keys and referential integrity constraints.
 

3. Please help me normalize the abstracted tables to 3NF.

 

Use SQL to solve the following question;

 

4. Please help me create database named "Air_Botswana."     

 

5. Please help me create relevant tables (tables obtained after the 3NF) for the database. Apply relevant constraints and enforce referential integrity where necessary.
 

6. Please help me create constraint that prohibits users from entering a ticket price that is less than 200.
 

7. Please help me insert a minimum of 10 records in each table.

 

8. Please help me display only the first two letters of destination name with the column heading "Part

destination name" from the flight table.

 

9. Please help me display the date of availability for a specific flight.

 

10.Please help me display the average number of special seats booked.

 

11. Please help me display all the passenger details whose reservation status is "waiting." 

 

12. Please help me display the ticketID, Passenger name, source and destination of all passengers who purchased the general type of ticket.
 

13. Please help me display the flight name that has a lesser capacity than any other flight that travels to a specific destination.
 

14.Please help me create view named "PassengerView" showing the name, age and sex of passengers who purchased a special ticket and their status of reservation are "Confirmed".
 

15.Please help me write batch to display the flight number, flight name and price of the ticket. If the price is more than 2300, display "Expensive ticket" else "Cheap ticket".

 

16. Please help me list all the male passengers travelling from the same source/departure. Use a stored procedure