I have a question about mapping (conceptual) n-ary relationships to (logical) relations.
Assume we have three strong entity types: Member, Equipment, and Time Slot, and a relationship named Reserves between them.
The attributes do not matter (just assume we have an Id key attribute), but the cardinality ratios are as follows:
- A member can reserve a particular equipment at multiple time slots (the N),
- An equipment can be reserved at a particular time slot by only one member (the 1 on the left),
- A member can reserve only one equipment per time slot (the 1 on the right).
And there is no total participation constraint.
In look across notation (I believe):
I'm trying to map this relationship to a relation in a relational design. We would have:
RESERVES (MEMBER.Id, EQUIPMENT.Id, TIME_SLOT.Id)
Where the three attributes are foreign keys to, respectively, the relations that correspond to
But what should be the primary key?
Elmasri, Ramez, and Shamkant B. Navathe. 2015. Fundamentals of Database Systems (7th Edition). Pearson. reads, p. 296:
The primary key of S [the relation resulting from the mapping of the n-ary relationship R to the relational model] is usually a combination of all the foreign keys that reference the relations representing the participating entity types. However, if the cardinality constraints on any of the entity types E participating in R is 1, then the primary key of S should not include the foreign key attribute that references the relation E' corresponding to E (…).
Applying this recipe blindly make that only
TIME_SLOT.Id is the Primary Key, which does not make sense at all (two different equipments can be reserved at the same time!).
Having the three attributes being the primary key does not reflect the fact that one equipment cannot be reserved multiple times at the same time slot.