1 / 15

STA TRAVEL AUSTRALIA

STA TRAVEL AUSTRALIA. Rumana Islam HD Assignment 3, Autumn 2007. STA Travel Database. This Database application was inspired by the ‘STA Travel Australia’ website. The Database contains details about Airline, Airplane, Flight and Ticket.

dennis-wells
Download Presentation

STA TRAVEL AUSTRALIA

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. STA TRAVEL AUSTRALIA Rumana Islam HD Assignment 3, Autumn 2007

  2. STA Travel Database • This Database application was inspired by the ‘STA Travel Australia’ website. • The Database contains details about Airline, Airplane, Flight and Ticket. • It also contains information of the user booking tickets and details of the passengers a user books tickets for. • The database is limited to the reserving and purchasing of tickets and does not include other special services and deals offered by STA Travel

  3. STA Travel Database ERD

  4. 1:mRelationship User– Reservation Foreign key User Reservation

  5. m:m Relationship Flight-Passenger Flight PassFlight m:m relationship Passenger

  6. Single Table Query Selecting a few items from the Flight SELECT FlightNumber, Origin, Destination FROM Flight; flightnumber | origin | destination --------------+-----------+------------- SQ432 | Sydney | Singpore SQ241 | Singapore | Dhaka QF350 | Sydney | New York CX520 | Sydney | London KL366 | Brisbane | Amsterdam KL363 | Melbourne | Munich TG201 | Sydney | Bangkok (7 rows)

  7. NATURAL JOIN Identifying users reserving tickets for passengers and when? SELECT UFirstName, PFirstName, PPassportNumber, ReservationDate FROM Users NATURAL JOIN Reservation NATURAL JOIN Passenger; ufirstname | pfirstname | ppassportnumber | reservationdate ------------+------------+-----------------+----------------- Rumana | Rumana | L898902C | 2007-06-28 Rumana | Rehnuma | L923034Z | 2007-06-28 Rumana | Rafiqul | N123489B | 2007-06-29 Rumana | Milie | B947321F | 2007-06-29 John | James | L629374P | 2007-06-02 Alfred | Alfred | L102571G | 2007-06-07 Jessica | Jessica | L581290X | 2007-06-10 Adam | Adam | K437920F | 2007-06-12 Lara | Lara | L902257D | 2007-06-25 (9 rows)

  8. NATURAL JOIN (Cross Product) Identifying users reserving tickets for passengers and when SELECT UFirstName, PFirstName,PPassportNumber, ReservationDate FROM Users, Reservation, Passenger Where Users.UsersID = Reservation.UsersID AND Reservation.ReservationID=Passenger.ReservationID; ufirstname | pfirstname | ppassportnumber | reservationdate ------------+------------+-----------------+----------------- Rumana | Rumana | L898902C | 2007-06-28 Rumana | Rehnuma | L923034Z | 2007-06-28 Rumana | Rafiqul | N123489B | 2007-06-29 Rumana | Milie | B947321F | 2007-06-29 John | James | L629374P | 2007-06-02 Alfred | Alfred | L102571G | 2007-06-07 Jessica | Jessica | L581290X | 2007-06-10 Adam | Adam | K437920F | 2007-06-12 Lara | Lara | L902257D | 2007-06-25 (9 rows)

  9. GROUP BY List PassengerID and group by number of flights a passenger is associated to SELECT PassengerID, PFirstName, PSurname, count(*) as NumberofFlights FROM PassFlight natural join Passenger GROUP BY PassengerID,PFirstName, PSurname order by PassengerID; passengerid | pfirstname | psurname | numberofflights -------------+------------+----------+----------------- 1 | Rumana | Islam | 2 2 | Rehnuma | Islam | 1 3 | Rafiqul | Islam | 1 4 | Milie | Islam | 1 5 | James | Foster | 1 6 | Alfred | Lai | 1 7 | Jessica | Peterson | 1 8 | Adam | Brody | 1 9 | Lara | Roberts | 1 (9 rows)

  10. Sub Query Identify the cheapest ticket bought by passenger named ‘Rumana Islam’ SELECT Distinct TicketPrice, PaymentDate FROM Ticket NATURAL JOIN Payment WHERE CardHolderName = 'Rumana Islam' And TicketPrice <= all (SELECT TicketPrice FROM Ticket NATURAL JOIN Payment WHERE CardHolderName = 'Rumana Islam'); ticketprice | paymentdate -------------+------------- 1800 | 2007-06-28 (1 row)

  11. Self Join Finding two passengers who paid for their tickets using a MasterCard SELECT c1.cardholdername, c2.cardholdername, c1.cardtype FROM payment c1,payment c2 WHERE c1.cardtype = c2.cardtype AND c1.cardtype='Mastercard' AND c2.cardtype='Mastercard' AND c1.ticketid>c2.ticketid; cardholdername | cardholdername | cardtype ------------------+----------------+------------ Jessica Peterson | John Smith | Mastercard (1 row)

  12. Check Statements Checking for Passenger Service Class type CONSTRAINT PassFlight_PFServiceClass CHECK (PFServiceClass IN ('Economic','Business','First')) Checking for Date of Birth CONSTRAINT Users_UDOB CHECK (UDOB >= '1900-01-01') Checking for Card Expiry Date CONSTRAINT Payment_CardExpiryDate CHECK (CardExpiryDate BETWEEN '2007-01-01' AND '2015-01-01')

  13. Action Statements On Delete Restrict CONSTRAINT Passenger_ReservationIDFK FOREIGN KEY (ReservationID) REFERENCES Reservation ON DELETE RESTRICT ON UPDATE CASCADE On Delete Cascade CONSTRAINT AirPlane_AirLineFK FOREIGN KEY (AirLineID) REFERENCES Airline ON DELETE CASCADE ON UPDATE CASCADE

  14. Views Creating a view for PassengerFlightDetails CREATE VIEW PassengerFlightDetails (PFirstName, PSurname, FlightNumber, Origin, Destination, PFSeatNumber) AS SELECT PFirstName, PSurname, FlightNumber, Origin, Destination, PFSeatNumber FROM Passenger NATURAL JOIN PassFlight NATURAL JOIN Flight; Selecting Details of Passenger flying the KLM Royal Dutch Airlines SELECT PFirstName, PSurname, FlightNumber, Origin, Destination, PFSeatNumber FROM PassengerFlightDetails WHERE FlightNumber LIKE ‘KL%’; pfirstname | psurname | flightnumber | origin | destination | pfseatnumber ------------+----------+--------------+-----------+-------------+-------------- Jessica | Peterson | KL366 | Brisbane | Amsterdam | 23B Adam | Brody | KL363 | Melbourne | Munich | 20F (2 rows)

  15. Thank You! QUESTIONS?

More Related