db-04-flights/README.md

FlightModel Management

We return to the flight database we used in one of the first assignments. However, this time it got extended by a passenger list instead of a simple count.

@startuml
hide circle
hide empty methods
skinparam nodesep 80
skinparam linetype ortho

entity AirportModel {
    * icao: string <<PK>> 
    * name: string
    * country: string
    * runwayLength: int
}
entity PlaneModel {
    * tailNo: string <<PK>>
    * model: string
    * manufacturer: string
    * capacity: int
}
entity FlightModel {
    * flightNo: string <<PK>>
    * departure: datetime
    arrival: datetime
}
entity PassengerModel {
    * ssn: string <<PK>>
    * name: string
    * dateOfBirth: datetime
    isFemale: bool
}
entity ReservationModel {
    * ticketNo: int <<PK>>
    * seat: string
}

AirportModel ||-l-o{ FlightModel: departs
AirportModel ||-l-o{ FlightModel: arrives
FlightModel }o-l-|| PlaneModel: operates
FlightModel ||--o{ ReservationModel: has
PassengerModel ||-r-|{ ReservationModel: has
@enduml

Task 1

• Create the tables
  • The original three tables are mostly unchanged and can be taken from the previous assignment
• Make sure to use proper data types & foreign keys again
• Mind the following hints for the new tables
  • PassengerModel: isFemale is nullable on purpose, so we can put null in case a person is neither male nor female
  • ReservationModel: we don't use a composite primary key here (despite it normally being the way to go here), because the ticketNo has to use the 'autoincrement' feature
    • You already know this feature from DBI (a sequence in Oracle)
    • Read up on how to use it in SQLite - you won't need any keywords, read the page 😉
    • During inserts the ticketNo has not to be provided for this to work
    • You'll have to figure out how to get the last insert (row)id on your own - a short internet research will turn up the answer

Task 2

• To get things going populate the database with several planes and airports
• No endpoint is needed here, just run the script when the server boots
  • You have to check if the data already exists before inserting to prevent unique constraint violations
  • Hint: prepared statements can be reset and then used again
• You may use the following data

Tail Number	Model	Manufacturer	Capacity
OE-LAA	A320 Neo	Airbus	164
OE-LAB	B737-800	Boeing	188
OE-LAC	A319	Airbus	152
OE-LAD	B787-900	Boeing	260
OE-LAE	B737-800	Boeing	188
OH-LPE	B777-200	Boeing	330

ICAO	Name	Country	RunwayLength
LOWL	Blue Danube AirportModel Linz	Austria	9842
LOWW	Vienna International	Austria	11811
LIRF	Leonardo da Vinci–Fiumicino	Italy	12795
EGLL	Heathrow	UK	12802
EDDF	Frankfurt	Germany	13123
RJAA	Narita International	Japan	13123
KATL	Hartsfield–Jackson Atlanta International	USA	12390

Task 3

• Allow passenger registration
• Create an endpoint for adding new and updating existing passengers to/in the database
• Data is provided as JSON in the body of the request
• Use a PUT operation (with proper resource URI using the ssn)
  • This allows us to use this method for updates (by replacement) as well
  • Be careful when reading entities back from the database: types are lost at runtime, so what you expect to be a Date may not be one - the same is true for a boolean, some casting/mapping may be required
• Provide another endpoint for retrieving:
  1. a specific passenger by ssn
  2. a list of all passengers
• Apply the best practices you know by now

Task 4

• Provide information about airports
• Two-fold process:
  1. One endpoint provides a list of all ICAO codes
  2. A second endpoint allows retrieving detail information of an airport by its ICAO code

Task 5

• Provide information about planes
• A list of available planes
  • A new flight can only be planned for a plane which does not already operate a different flight at the same time
    • The request has to contain departure time information
  • We will use a simplified method to determine that:
    • We assume that any flight takes at most 12 hours (no matter which airports are connected)
    • Flights can start at any airport, independent of the last destination of the plane - it can reach any departure airport within 12 hours as well
    • Thus, we block a plane for 24 hours after its last departure time
  • All airports in our database have runways long enough for each plane in our database (it might be close for the 777 in Linz, but we'll ignore that 🙂)
• Again, a two-step process:
  1. Query for a list of airplane ids which are available in the specified time frame
  2. Query for details of a plane by id

Task 6

• To create a flight the following information has to be supplied:
  • Departure & destination airport
    • Both have to exist
  • Departure time
    • Has to be in the future
  • PlaneModel which will operate the flight
    • Has to be one of the available planes
• Implement an endpoint for creating such flights
• Also allow for updates - for which all conditions still apply
  • Hint: if the plane does not change, that one will be occupied during an update, handle that case accordingly

Task 7

• Finally, we will book passengers on flights
• The endpoint will receive
  • The SSN
  • The seat
  • the flight number
• It has to be verified that
  • Person exists
    • As a simplification you do not have to check if the Person is already booked on another flight at the same time
  • FlightModel exists
  • FlightModel still has open seats (= capacity not exceeded)
  • Seat not taken by another passenger
• The ticket number is created automatically by the database and returned to the user
• To make it easier you don't have to worry about updates or delete this time

Task 8

• Provide an endpoint for cancelling (= deleting a flight)
• This includes cancelling all reservations as well
  • Return the ticket numbers which have been removed so passengers can be notified