1

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.

  • 2
    There are two common conventions for n-ary cardinality notaitons, look-here & look-across. But you don't seem to be using either of them. You seem ot be giving some cardinalities of binary relationships that are certain projections of this ternary relationship. Please define for us how you expect us to read your n-ary diagram cardinalities & whether you think it is one of the standard ones & exactly what you expect us to know about the n-ary when you give binary cardinalities re it. – philipxy Mar 14 at 0:44
  • 1
    The textbook seems look-here but please find out & edit it into your post. Look-here (when not allowing 0, to simplify) gives how many times an entity can participate in the relationship, while look-across gives how many times an entity can participate in it with the same tuple of other entities. (So "here" is the range of count(*) of an SQL GROUP ON an entity & "across" is cardinality of the binary relationship entity:< tuple of other entities >.) But your bullets are not appropriate for either look. They each involve 2 entities. Suggest: When you give a cardinality give the relationship. – philipxy Mar 14 at 2:04
  • 1
  • 1
    "3.4.3 Constraints on Binary Relationship Types [...] Participation Constraints and Existence Dependencies. [...] This constraint specifies the minimum number of relationship instances that each entity can participate in" "3.9.2 Constraints on Ternary (or Higher-Degree) Relationships [...] The first notation is based on the cardinality ratio notation of binary relationships [...] Here, a 1, M, or N is specified on each participation arc" – philipxy Mar 14 at 3:41
  • 1
    You haven't given both of look-across & look-here, you've given two variants of one of them--minimal & range for number of participations (including the possibility of zero). Unfortunately your textbook section 3.9.2 is poorly written, it never gives a clear description of its generalization of the binary case, without involving example values. Read it all though. PS What is given? Your bullets? Bullet 3 is unclear--do you mean, per time slot? – philipxy Mar 14 at 23:03
2

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.

  • Thanks a lot for your detailed and educational answer. While I completely concur with your reasoning, I feel like you are "re-constructing" what the primary key should be, based on my description. I was looking for a "uniform rule" to map relationships to relation. From your answer, I guess a possible answer could be "Take the foreign key to the relation on the N side and the foreign key of on of the relation on one of the the 1 side to be the primary key". Do you agree? – Clément Mar 14 at 15:22
  • @Clément, I've updated the answer. – Renzo Mar 14 at 16:24
  • That's exactly what I was looking for (I should have made it clear that my example was a "dummy" one), thanks a lot. – Clément Mar 18 at 17:41
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.
  • 1
    Thanks for your detailed and enriching answer. If I may comment on "your relationship is not really 1:1:N": I believe it is, you can have a look at the two posts I added in comment of my question for a bit of context on parity constraints on n-ary relationships. – Clément Mar 14 at 0:02
  • I appreciate the info, as I mentioned I've never done relational theory but I am always up for learning new stuff! I guess its just a different way of looking at it "The combination of Members & Equipment may can occur for multiple time slots" vs "The combination of a member, a piece of equipment and a time slot, may only occur once" – Mr.Brownstone Mar 14 at 0:09
  • 1
    I think it is not a good idea to have a primary key which is a superkey: the system should perform a unuseful check (it is always true since the other two unique constraints already guarantees that the three attributes are unique). – Renzo Mar 14 at 9:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.