Team 1 FastFlight Flight Management System

1. Team 1FastFlight Flight Management System Alex Hoffpauir Daniel Porter Jairo Cepero

2. FastFlight FastFlight is a small airline in the process of updating its computer systems. FastFlight needs a database which keeps track of all of its flights, passengers, and reservations. The FAA requires that all late flights be reported.

3. Business Rules Reservations do not need an agentIDor seatNumber. Passengers must have a passengerId.

4. ER Diagram

5. MySQL datetime The mySQL database has built in types to handle date and time. mySQL also provides a comprehensive library of functions to help manipulate these objects. Our database required the storage of four different datetime objects for each flight record. mySQL’s functions made it easy to find late flights. ADDDATE(), ADDTIME(), SUBDATE(), SUBTIME(), DIFFTIME(), NOW(), DAYNAME()

6. Triggers # create trigger to find late flights DELIMITER $$ CREATE TRIGGER update_delay BEFORE UPDATE ON Flights FOR EACH ROW         IF NOT (NEW.actualArrival <=> OLD.actualArrival)         THEN SET NEW.delay = TIMEDIFF(NEW.actualArrival, NEW.scheduledArrival);      END IF $$ DELIMITER ;

7. Example 1 If all flights leaving JFK this week will be delayed by 15 minutes, we could execute this statement: UPDATE Flights SET actualArrival = ADDTIME(actualArrival, '00:15:00') WHERE origin = 'JFK’; And the results can be seen using: select * from Flights WHERE delay >0; To veiw how many total flights that have been delayed: Select Count(delay > 0) From Flights;

8. Example 2 These statements will show all the reservations with late flights: SELECT r.reservationID as 'ID', p.fname as 'First', p.lname as 'Last', r.flightNumber as 'Flight', f.delay as 'Delay' FROM Reservations r INNER JOIN Flights f ON r.flightNumber = f.flightNumber and r.scheduledDeparture = f.scheduledDeparture and f.delay != 0 INNER JOIN Passengers p ON r.passengerID = p.passengerID;

9. Questions