Unanswered: Identifying the relationship details (Cardinality & Participation Constraints)
Im creating a database for a project. I have to identify the relationships but im not sure if they are correct. Can anyone help me? The entities im using are Bookings, Passengers, Flights and Airports. Im creating a database which records BOOKINGS made by PASSENGERS for FLIGHTS to various destination AIRPORTS
The central business objects are bookings which are aggregated into flights. Hence the bookings are children (i.e. many-to-one records) of flights. Each flight can call at several airports on its journey. At the very least, there will be a departure airport and an arrival airport. Hence, the airports are children of flights. Each booking will point to one airport as its departure point and another as its arrival point. These points may be intermediate ones on the flight. The passengers are only parents of bookings.
Thus the flight is defined by its ID (airline, initial departure point and date/time) and its attributes will be a list of airports visited, each of which has an arrival and departure time.
The booking (made by a passenger) points to a selected flight, but in addition must also point to the airports defining the journey, the basis of the selection.
A good way to sort out these things is to imagine an operator making a booking. First of all, the passenger will be identified (entered if new, looked up from the database if a previous client). From the passengers' declared departure and arrival points, the flights using these airports and their arrival/departure times noted. A selection will be made and the booking populated with the data. Think combo boxes !