The primary key of a relationship set in an E-R data model

[Eus]

Hi Ho!

I have posted this same question about three weeks ago on MySQL forum (http://forums.mysql.com/read.php?125,197805,197805) and on the mailing list of the book itself about two weeks ago (db-book-list@cs.yale.edu). But, still there is no response at all. Therefore, I try my luck by posting the question here.

On a book, which is published by McGraw Hill, titled Database System Concepts, 4th Edition, by Silberschatz, Korth, and Sudarshan, I found a contradiction that I would like to clarify with you.

The problem is within chapter 2, which starts on page 27 and ends on page 77, that discusses Entity-Relationship Model.
On page 36 in a section that discusses the key to distinguish among the various relationships of a relationship set, there is the following statement:

Let R be a relationship set involving entity sets $E_{1},\ E_{2},\ ...,\ E_{n}$.
Let $primary-key(E_{i})$ denote the set of attributes that forms the primary key for entity set $E_{i}$.
Assume for now that the attribute names of all primary keys are unique, and each entity set participates only once in the relationship.
The composition of the primary key for a relationship set depends on the set of attributes associated with the relationship set $R$.

In my opinion, the last sentence is wrong. The right one should be as follows:

"The composition of the primary key for a relationship set does not depend on the set of attributes associated with the relationship set $R$."

The following evidences support my argument:
1. On page 32 in a section that discusses Relationship Sets there is the following statement:

"A relationship instance in a given relationship set must be uniquely identifiable from its participating entities, without using the descriptive attributes."

Previously, the descriptive attributes are defined as attributes that are associated with a relationship set.
With other words, the composition of the primary key for a relationship set does not depend on the set of attributes associated with the relationship set R.

2. On page 36 just below the concerned statement, it is written that:

If the relationship set R has no attributes associated with it, then the set of attributes
$$primary-key(E_{1})\ \bigcup\ primary-key(E_{2})\ \bigcup\ ...\ \bigcup\ primary-key(E_{n})$$
describes an individual relationship in set $R$.

If the relationship set $R$ has attributes $a_{1},\ a_{2},\ ...,\ a_{m}$ associated with it, then the set of attributes
$$primary-key(E_{1})\ \bigcup\ primary-key(E_{2})\ \bigcup\ ...\ \bigcup\ primary-key(E_{n})\ \bigcup\ \left \{a_{1},\ a_{2},\ ...,\ a_{2}\right \}$$
describes an individual relationship in set $R$.

In both of the above cases, the set of attributes
$$primary-key(E_{1})\ \bigcup\ primary-key(E_{2})\ \bigcup\ ...\ \bigcup\ primary-key(E_{n})$$
forms a superkey for the relationship set.

Because in both of the above cases the superkey (i.e., a set of one or more attributes that, taken collectively, allow us to identify uniquely a relation in a relationship set) is just
$$primary-key(E_{1})\ \bigcup\ primary-key(E_{2})\ \bigcup\ ...\ \bigcup\ primary-key(E_{n})$$,
the composition of the primary key for a relationship set does not depend on ${a_{1},\ a_{2},\ ...,\ a_{m}}$ (i.e., the set of attributes associated with the relationship set $R$).
With other words, in both of the above cases, the set of attributes
$$primary-key(E_{1})\ \bigcup\ primary-key(E_{2})\ \bigcup\ ...\ \bigcup\ primary-key(E_{n})$$
is sufficient to describes an individual relationship in set $R$.
Therefore, the composition of the primary key for a relationship set does not depend on the set of attributes associated with the relationship set $R$.

To conclude, is it true that the composition of the primary key for a relationship set does not depend on the set of attributes associated with the relationship set $R$?

In practice there are some examples that require the use of descriptive attributes to form the primary key of a relationship set. One such examples is as follows:

Consider two entity sets: Student and Course along with one relationship set: Took that describes a many-to-many relationship between Student and Course, as well as one descriptive attribute associated with the relationship set: Quarter.

If one student is only permitted to take a course once, then the primary key of the relationship set will be
$$primary-key(Student)\ \bigcup\ primary-key(Course)$$

But, if one student is permitted to take a course more than once, then the primary key of the relationship set will be
$$primary-key(Student)\ \bigcup\ primary-key(Course)\ \bigcup\ {Quarter}$$

If it is true that:
"The composition of the primary key for a relationship set does not depend on the set of attributes associated with the relationship set $R$.",
the relationship set of the aforementioned example must be transformed to an entity set named: Academic Quarter in spite of the fact that we are still working on the E-R data model (usually we start creating bridging-tables and other necessary transformations when we translate the E-R data model into the relational data model).

Is such transformation a common practice when modeling data with E-R data model because the composition of the primary key for a relationship set does not depend on the set of attributes associated with the relationship set $R$?

Thank you very much.


Eus

 

[Aaron Bex]

Hi Eus,

It is true that the composition of the primary key for a relationship set does not depend on the set of attributes associated with the relationship set R. This is because the primary key of the relationship set must be able to uniquely identify a relationship instance from its participating entities without using the descriptive attributes.

The example you have given is a good illustration of this concept. In this case, if one student is permitted to take a course more than once then the primary key must include the descriptive attribute Quarter in order to uniquely identify each instance of the relationship between the student and the course.

Transforming the relationship set into an entity set is not a common practice when modeling data with the E-R data model. It is more common to use a bridging table or other necessary transformations when translating the E-R data model into the relational data model.

I hope this helps!

 

[quicknote]

Hello Eus,

Thank you for bringing this contradiction to my attention. After reviewing the statements in the book, I agree with your argument that the composition of the primary key for a relationship set does not depend on the set of attributes associated with the relationship set R. This is consistent with the definition of a relationship set, which is simply a set of relationships between entities and does not include any descriptive attributes.

In practice, it is common to use descriptive attributes to form the primary key of a relationship set, as you have mentioned in your example. This is done in order to uniquely identify a specific instance of the relationship set. However, this is not a requirement and as you pointed out, the relationship set can be transformed into an entity set if necessary.

In conclusion, I believe your understanding of the concept is correct and I would recommend reaching out to the authors or editors of the book to clarify this contradiction. Thank you for bringing this to my attention and I hope this helps clarify your understanding of the primary key in an E-R data model.

Best,