DATA MODELING - Fundamentals of Database Management Systems - page 28

The Unique Identifier in Many-to-Many Relationships Since, as we have just seen,
a many-to-many relationship can appear to be a kind of an entity, complete with
attributes, it also follows that it should have a unique identifier, like other entities.
(If this seems a little strange or even unnecessary here, it will become essential later
in the topic when we actually design databases based on these E-R diagrams.) In
its most basic form, the unique identifier of the many-to-many relationship or the
associative entity is the combination of the unique identifiers of the two entities
in the many-to-many relationship. So, the unique identifier of the many-to-many
relationship of Figure 2.5 or, as shown in Figure 2.6, of the associative entity, is the
combination of the Salesperson Number and Product Number attributes.
Sometimes, an additional attribute or attributes must be added to this
combination to produce uniqueness. This often involves a time element. As currently
constructed, the E-R diagram in Figure 2.6 indicates the quantity of a particular
product sold by a particular salesperson since the salesperson joined the company .
Thus, there can be only one occurrence of SALES combining a particular salesperson
with a particular product. But if, for example, we wanted to keep track of the sales on
an annual basis, we would have to include a year attribute and the unique identifier
would be Salesperson Number, Product Number, and Year. Clearly, if we want to
know how many units of each product were sold by each salesperson each year,
the combination of Salesperson Number and Product Number would not be unique
because for a particular salesperson and a particular product, the combination of
those two values would be the same each year! Year must be added to produce
uniqueness, not to mention to make it clear in which year a particular value of the
Quantity attribute applies to a particular salesperson-product combination.
The third and last possibility occurs when the nature of the associative entity
is such that it has its own unique identifier. For example, a company might specify
a unique serial number for each sales record. Another example would be the many-
to-many relationship between motorists and police officers who give traffic tickets
for moving violations. (Hopefully it's not too many for each motorist!) The unique
identifier could be the combination of police officer number and motorist driver's
license number plus perhaps date and time. But, typically, each traffic ticket has a
unique serial number and this would serve as the unique identifier.
UNARY RELATIONSHIPS
Unary relationships associate occurrences of an entity type with other occurrences
of the same entity type. Take the entity person, for example. One person may be
married to another person and vice versa. One person may be the parent of other
people; conversely, a person may have another person as one of their parents.
One-to-One Unary Relationship
Figure 2.7a shows the one-to-one unary relationship called Back-Up involving the
salesperson entity. The salespersons are organized in pairs as backup to each other
when one is away from work. Following one of the links, say the one that extends
from the right side of the salesperson entity box, we can say that salesperson
number 137 backs-up salesperson number 186. Then, going in the other direction,
salesperson number 186 backs-up salesperson 137. Notice that in each direction the