Question

For this project, following assumptions have been made:  

1. The booking is only open for the next seven days from the current date.

2. Only two categories of the plane can be booked i.e. Economy and Business.

3. The total number of tickets that can be booked in each category is 10 only.

4. By ‘user’, we mean the person who is booking the ticket for the passenger(s). For example, an employee of Saudi Airline.

Your project should include information about the following entities and their respective attributes:

Entities	Attributes
User	User_id User_password First_name Last_name Age Gender Email_address Contact_number City
Passenger	Passenger_ID Name Gender Age Phone_num BookingRef_num Seat_no Status
Flight	Flight_number Flight_name Departure_airport Arrival_airport Departure_time Arrival_time Availabilty_of_seats
Airport	Number Name Flight_number Arrival_time Departure_time
Ticket	Ticket_id Flight_number Booked_user Status Number_of_passengers

Required SQL Queries:

1. Write a query that displays the User_id and names of all those passengers who booked ticket on any particular flight.

2. Write a query to display details (ex. Passenger_ID, name, etc.) of all passengers travelling under a particular ticket.

3. Write a query to display the time at which any Flight_number reaches any Airport_name. For example, EK233, Dammam.

4. Write a query to display all flights in ascending order of their departure time.

5. Write a query to display the flight_number that stops for the longest time at any airport (e.g., Dammam).

questions:

Design & ER Model

Normalize the Tables (in 3NF at least)

Create the Normalized Tables and Populate them with at least 5 Rows

Write the sample requested Queries & Execute them

Answer 1

SOLUTION:

Given details:

Table Design and Details:

Relation between tables and key:

SQL Queries:

1. Write a query that displays the User_id and names of all those passengers who booked ticket on any particular flight.

Syntax:

SELECT *

FROM Table_name
WHERE condition;

Answer: Assuming to obtain details of Flight_number = ‘EK233’

SELECT u.User_id, p.Name
FROM Passenger p, Ticket t, User u
WHERE Flight_number = ‘EK233’ AND t.BookingRef_num=p.BookingRef_num AND t.Booked_user=u.User_id;

2. Write a query to display details (ex. Passenger_ID, name, etc.) of all passengers travelling under a particular ticket.

Answer: Assuming to obtain passenger details of BookingRef_num = ‘XYZ123’

SELECT *
FROM Passenger
WHERE BookingRef_num = ‘XYZ123’;

3. Write a query to display the time at which any Flight_number reaches any Airport_name. For example, EK233, Dammam.

Answer:

SELECT Arrival_time
FROM Airport

WHERE Flight_number= EK233 AND Name= Dammam;

4. Write a query to display all flights in ascending order of their departure time.

ORDER_BY Syntax: Use ‘ASC’ for ascending, ‘DESC’ for descending

SELECT *
FROM table_name
ORDER BY column_name ASC | DESC;

SELECT *
FROM Flight
ORDER BY Departure_time ASC;

5. Write a query to display the flight_number that stops for the longest time at any airport (e.g., Dammam).

Answer:

SELECT Flight_number

FROM Airport

WHERE Max(Departure_time - Arrival_time) = Departure_time - Arrival_time AND Name= Dammam;

***************************************************************************************

Please comment for any further help on this question