Mapping a 1:1:N ternary relationship to a relational schema

Question

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:

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

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.

Answer 1

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.

Answer 2

If you are trying to implement this with just constraints & indexes then to me it sounds like you are going to need multiple constraints over the same items of data to enforce this behaviour. I'll explain below:

A member can reserve a particular equipment at multiple time slots (the N)

You can meet the requirements of this rule by placing the primary key across all 3 fields. This will enforce that the combination of all 3 fields may only occur once.

An equipment can be reserved at a particular time slot by only one member (the 1 on the left)

You will need a unique constraint over TIME_SLOT.id and EQUIPMENT.id in order to achieve this. This will enforce that the equipment can only be booked once per time slot.

A member can reserve only one equipment by time slot (the 1 on the right).

You will need a unique constraint over TIME_SLOT.id and MEMBER.id in order to achieve this. This will ensure that a member cannot book the same time slot more than once.

This will then give you a table like this:

CREATE TABLE RESERVES
(
   MEMBER_ID INT NOT NULL REFERENCES MEMBER(ID),
   EQUIPMENT_ID INT NOT NULL REFERENCES EQUIPMENT(ID),
   TIME_SLOT_ID INT NOT NULL REFERENCES TIME_SLOT(ID),

   PRIMARY KEY(MEMBER_ID, EQUIPMENT_ID, TIME_SLOT_ID),
   UNIQUE KEY (MEMBER_ID, TIME_SLOT_ID),
   UNIQUE KEY (EQUIPMENT_ID, TIME_SLOT_ID)
);

I've never done a course on relational theory, so excuse me if the below seems naive but your relationship is not really 1:1:N. As all entities can occur multiple times and only the combination of the relations produce the constraint.

So assuming the order of MEMBER, EQUIPMENT, TIME_SLOT:

• Your primary key is actually 1:1:1
  • One member can book one piece of equipment for one time slot.
• You have a secondary key 1:0:1
  • One member can book a time slot once.
• And a tertiary key 0:1:1
  • One piece of equipment can be booked once per time slot.