Modeling and Designing Databases - Learning MySQL - page 126

What it doesn't do
Our database design is rather simple, but this is because the requirements are simple.
For a real university, many more aspects would need to be captured by the database.
For example, the requirements don't mention anything about campus, study mode,
course prerequisites, lecturers, timetabling details, address history, financials, or as-
sessment details. The database also doesn't allow a student to be in more than one
degree program, nor does it allow a course to appear as part of different programs.
The Flight Database
The flight database stores details about an airline's fleet, flights, and seat bookings.
Again, it's a hugely simplified version of what a real airline would use, but the principles
are the same.
Consider the following requirements list:
• The airline has one or more airplanes.
• An airplane has a model number, a unique registration number, and the capacity
to take one or more passengers.
• An airplane flight has a unique flight number, a departure airport, a destination
airport, a departure date and time, and an arrival date and time.
• Each flight is carried out by a single airplane.
• A passenger has given names, a surname, and a unique email address.
• A passenger can book a seat on a flight.
The ER diagram derived from our requirements is shown in Figure 4-13:
• An Airplane is uniquely identified by its RegistrationNumber , so we use this as the
primary key.
• A Flight is uniquely identified by its FlightNumber , so we use the flight number as
the primary key. The departure and destination airports are captured in the From
and To attributes, and we have separate attributes for the departure and arrival date
and time.
• Because no two passengers will share an email address, we can use the EmailAd
dress as the primary key for the Passenger entity.
• An airplane can be involved in any number of flights, while each flight uses exactly
one airplane, so the Flies relationship between the Airplane and Flight relation-
ships has cardinality 1:N; because a flight cannot exist without an airplane, the
Flight entity participates totally in this relationship.
• A passenger can book any number of flights, while a flight can be booked by any
number of passengers. As discussed earlier in “Intermediate Entities,” we could
specify an M:N Books relationship between the Passenger and Flight relationship,