Cabais, Jefren Paul A.
Yacapin, Neil John
BSCS-2B
Activity 2
I.
Airline ER Diagram
Airline Relationship Model
II. Tables
Airplane Table
Flight Table
Passenger Table
Booking Table
III . View
It displays flight details (flight number, departure location, and destination) along with the total
passenger count for each flight, including those without any bookings.
It displays flight details (flight number, departure location, destination, departure date, and time)
along with the available seat count for each flight. This is determined by subtracting the number of
booked passengers from the airplane's capacity. The data is retrieved by joining the Flight, Airplane,
and Booking tables and grouping by flight details.
The flightdetails view gathers flight information, including the flight number, airplane model
number, departure airport, and destination airport, by joining the Flight and Airplane tables.
IV.
Stored Functions
It displays passenger details (ID, given names, surname, and
email) along with their booking ID, flight number, departure
city, arrival city, departure date, and time for each booking.
The data is retrieved by joining the Passenger, Booking, and
Flight tables based on their relationships.
The CalculateDuration function computes a flight's duration in minutes. It takes a flightID as input,
retrieves the departure and arrival times from the FLIGHT table, and calculates the difference using
TIMESTAMPDIFF. The result is stored in the duration variable and returned, allowing dynamic
determination of flight durations.
The GetFlightCapacity function determines the number of available seats for a flight. It takes a
flightID, retrieves the airplane's total capacity, subtracts the number of booked seats, and returns the
result. If there are no bookings, it returns 0.
V.
Stored Procedures
This stored procedure
verifies whether a
passenger exists using
their email. If not, it adds
the passenger, creates a
booking for the passenger
and flight, and returns a
confirmation message.
This stored procedure verifies whether a booking exists using the
given booking ID. If found, it deletes the booking and returns a
confirmation message; otherwise, it notifies that the booking was
not found or could not be canceled.
VI.
Triggers
The PreventOverbooking trigger checks seat availability before allowing a new booking.
If the RemainingCapacity function for the specified flight returns 0 or less, it raises an error
with the message "No seats available," preventing the booking.
The UpdateCapacityAfterBooking trigger verifies seat availability before confirming a booking. If no
seats are available, it raises an error stating "Capacity exceeded.
VII.
Events
This event automatically transfers completed flights (older
than 1 year) from the Flight table to the ArchivedFlight
table and removes them from the Flight table, usually
running at a scheduled time.
This event updates the status of
flights to "Completed" for those that
have passed their scheduled arrival
time, ensuring that arrived flights are
properly marked as completed.
