Database Design – Mapping a 1:1:N Ternary Relationship to a Relational Schema

database-designerd

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:

enter image description here

Meaning that:

  • 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):

enter image description here

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 MEMBER, EQUIPMENT and TIME_SLOT.

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.

Best Answer

A candidate key of a relation is a set of attributes such that every tuple (record) of the relation must have values differing from those of all the other tuples.

A primary key can be either chosen among the different candidate keys of a relation, or can be a surrogate key, that is an artificial identifier invented to uniquely distinguish all the records of the relation.

Surely, a primary key should not be a strict superkey, that is a key from which we can remove one or more attributes so that the remaining attributes still uniquely identify all the tuples of the relation.

Now to find the primary key we must first ask: which are the candidate keys of this relation? In this case there are only two candidate keys: (EQUIPMENT_ID, TIME_SLOT_ID) and (MEMBER_ID, TIME_SLOT_ID). In fact, each of them must be unique for all the tuples of the relation. And there are no other candidate keys (in fact, (EQUIPMENT_ID, MEMBER_ID) can have duplicate elements, as the single attributes, as you have already noted, while (MEMBER_ID, EQUIPMENT_ID, TIME_SLOT_ID) is a strict superkey).

So the the primary key in your case could be: a) the couple (EQUIPMENT_ID, TIME_SLOT_ID); b) the couple (MEMBER_ID, TIME_SLOT_ID); c) a surrogate key like RESERVES_ID. If there are no particular reason to introduce another attribute as surrogate key, you can use any of the two couples of attributes as primary key, and declare the other pair as unique.

Finally, note that applying the normalization theory, you have two non-trivial functional dependencies MEMBER_ID, TIME_SLOT_ID -> EQUIPMENT_ID and EQUIPMENT_ID, TIME_SLOT_ID -> MEMBER_ID from which can be derived that the relation is in Boyce Codd Normal form and that the two couples are the unique candidate keys.

EDITED

If your question really is: in case of a generic ternary relationship, with the cardinality as in the example, represented as a relation with three foreign keys, which are the candidate keys of such relation?

The answer can be given by considering the functional dependencies inferred by the cardinality. Let’s call the three attributes (foreign keys) A, B and C, and suppose that you can have at most a value of A for a couple of values BC, at most a value of B for a couple of values AC, and any number of values of C for a couple of values AB (this models what you have called 1:1:N relationship in your example, with A being the member, B the equiplement and C the time slot). This can be translated in terms of functional dependencies as:

BC -> A
AC -> B

(technically, this is called a cover of the set of dependencies holding on the relation, meaning that there are no other non-trivial functional dependencies holding in this case).

From this, we can easily derive that the relation has only two candidate keys, AC and BC. So, in terms of a relational database system, you can choose any one of them as primary key, and declare the unique constaint for the other.

Related Question