STA TRAVEL AUSTRALIA Rumana Islam HD Assignment 3, Autumn 2007

Transcript STA TRAVEL AUSTRALIA Rumana Islam HD Assignment 3, Autumn 2007

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.  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

STA Travel Database ERD

1:m Relationship

User – Reservation Users

UsersID UTitle UFirstName USurname UDOB UNumber UMobileNumber UEmailAddress Reserves

Reservation

ReservationID UserID ReservationDate Foreign key

User Reservation

1 2 3 UsersID UTitle UFirstName … Ms Mr Mr … Rumana John Alfred … Other columns … … … … UserID 1 2 3 4 5 … ReservationID ReservationDate 1 1 1 1 2 2007-06-28 2007-06-28 2007-06-29 2007-06-29 2007-06-02 …

m:m Relationship

Flight-Passenger Flight

FlightID AirplaneID FlightNumber Origin Destination DepartureDate ReturnDate DepartureTime ReturnTime Has

PassFlight

PassengerID FlightID PFSeatNumber PFServiceClass PFType Part of

Flight

2 3 Flight 1 ..

FlightNumber SQ432 SQ241 QF350 … Origin Sydney Singapore Sydney … Destination Singapore Dhaka New York … Other Columns … … … …

Passenger

PassengerID 1 2 … PTitle Ms Ms ..

PFirstName Rumana Rehnuma … OtherColumns ..

… 2 3 PassengerID 1 1

Passenger

PassengerID ReservationID PTitle PFirstName PSurname PPassportNumber PDOB PHomeNumber PMobileNumber PStreet PSuburb PCity PState PPostCode PCountry

PassFlight m:m relationship

1 1 FlightID 1 2 PFSeatNumber 53J 44M 53K 53L Other Columns … … … …

Single Table Query

Selecting a few items from the Flight

NATURAL JOIN

Identifying users reserving tickets for passengers and when?

NATURAL JOIN (Cross Product)

Identifying users reserving tickets for passengers and when

GROUP BY

List PassengerID and group by number of flights a passenger is associated to

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)

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)

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')

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

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