Databases Reference
In-Depth Information
Passenger
M
N
Flight
Books
Figure 4-7. A passenger participates in an M:N relationship with flight
Intermediate Entities
It is often possible to conceptually simplify many-to-many relationships by replacing
the many-to-many relationship with a new
intermediate entity
(sometimes called an
associate entity
) and connecting the original entities through a many-to-one and a one-
to-many relationship.
Consider the statement: “A passenger can book a seat on a flight.” This is a many-to-
many relationship between the entities “passenger” and “flight.” The related ER dia-
gram fragment is shown in Figure 4-7.
However, let's look at this from both sides of the relationship:
• Any given flight can have many passengers with a booking.
• Any given passenger can have bookings on many flights.
Hence, we can consider the many-to-many relationship to be in fact two one-to-many
relationships, one each way. This points us to the existence of a hidden intermediate
entity, the booking, between the flight and the passenger entities. The requirement
could be better worded as: “A passenger can make a booking for a seat on a flight.”
The related ER diagram fragment is shown in Figure 4-8.
Each passenger can be involved in multiple bookings, but each booking belongs to a
single passenger, so the cardinality of this relationship is 1:N. Similarly, there can be
many bookings for a given flight, but each booking is for a single flight, so this rela-
tionship also has cardinality 1:N. Since each booking must be associated with a par-
ticular passenger and flight, the booking entity participates totally in the relationships
with these entities. This total participation could not be captured effectively in the
representation in Figure 4-7. (We described partial and total participation earlier in
“Partial and Total Participation.”)
Weak and Strong Entities
Context is very important in our daily interactions; if we know the context, we can
work with a much smaller amount of information. For example, we generally call family
members by only their first name or nickname. Where ambiguity exists, we add further
information such as the surname to clarify our intent. In database design, we can omit
