This is a common problem. The "trick" is to:
- add in
Groups
- besides the Primary Key - a redundant Unique constraint on (Topic, Group)
.
- this allows you to add the
Topic
attribute in the Participates
relation and
- forces you to modify the Foreign Key constraint from
Participates
to Groups
so it includes Topic
, not only Group
.
then you can add a Unique constraint on Participates (Pupil, Topic)
(meaning a pupil can participate only once in a topic, not twice or more, which is the wanted result.)
Topics
Topic PK
Lessons
Lesson PK
Topic FK -> Topics
Groups
Group PK UQ1
Topic UQ1 FK -> Topics
Pupils
Pupil PK
PupilParticipatesInGroup
Pupil PK UQ1 FK1 -> Pupils
Group PK FK2 -> Groups
Topic UQ1 FK2
Appendix
Abbreviation Constraint
------------ -----------
PK Primary Key
UQ Unique
FK Foreign Key
Note that the design is in 3NF
and that the redundant Unique constraint on Topics
is only needed for the implementation in most DBMS (so the Foreign Key is enforced.)
The only other issue is the Group -> Topic
dependency in the Participates
relation which makes the schema to violate BCNF
.
In the linked page, in paragraph Achievability of BCNF, you'll see that it has been proven that not all tables can be decomposed into tables that satisfy BCNF
and preserve the dependencies that held in the original table. The provided example is striking similar to the Participates
table here:
In some cases, a non-BCNF table cannot be decomposed into tables that satisfy BCNF and preserve the dependencies that held in the original table. Beeri and Bernstein showed in 1979 that, for example, a set of functional dependencies {AB → C, C → B}
cannot be represented by a BCNF schema.[6] Thus, unlike the first three normal forms, BCNF is not always achievable.
Lets make one more change nonetheless, in order to remedy that (if we can!):
Lets examine what happens if we remove (as if it never existed) the Primary Key (Group)
constraint from Groups
(and put the Unique in its place.) Lets also change the Primary key in Participates
- notice - to be exactly the Unique constraint that is our target from the beginning. The design becomes (first two tables omitted as they remain intact):
Groups
Group PK
Topic PK FK -> Topics
Pupils
Pupil PK
PupilParticipatesInGroup
Pupil PK FK1 -> Pupils
Group FK2 -> Groups
Topic PK FK2
No redundant columns at all. Magic! The Participates
is a common many-to-many relation between Groups
and Pupils
(as our model wants) and the two Foreign Keys are referencing the Primary Keys of the corresponding tables.
So, what happened? Magic? (not really, no) And isn't the Group
still a candidate key in Groups
? (removing it was only a thought experiment anyway.) Don't we lose something by omitting this constraint from the actual tables?
The issue - and why the above is solving it - is that the Unique constraint you are trying to model needs the Topic
attribute in the Participates
relation. So, you have to use a candidate key in Groups
which is compound and includes Topic
. It doesn't necessarily have to be (Topic, Group)
. It could be some other combination. What other attributes does Groups
have?
If, for example, there is an attribute OrderNo
in Groups
and (Topic, OrderNo
) is unique, then we could have this design (at last!) where there is no redundancy, no superfluous attributes, all constraints are there and the schema is in BCNF
:
Groups
Group PK
Topic UQ1 FK -> Topics
OrderNo UQ1
Pupils
Pupil PK
PupilParticipatesInGroup
Pupil PK FK1 -> Pupils
Topic PK FK2 -> Groups
OrderNo FK2
ER Diagrams were originally used only to represent the ER model. The ER model does not use foreign keys to represent relationships. It uses lines between boxes. The lines have some kind of indicator for cardinality at either end or both ends. Sometimes, a relationship will be indicated separately by a diamond.
Today, more than half of the ER diagrams floating around are really diagrams of a relational model, and not of an ER model. A relational model has the foreign keys included in the tables, and these serve to implement the relationships which the ER model identifies. And a relational model will have an extra table, often called a "junction table" between two entity tables that are linked by a many-to-many relationship. This junction table contains two or more foreign keys.
There are many ways to represent a relational model. Perhaps the simplest is the "Relationship Diagram" that MS Access can produce from a completed database. This will be fairly complete, if the database builder has identified the foreign keys.
There are many tools that are more sophisticated than MS Access for making diagrams on a larger scale. Some of these are used before building the database. Some are used after.
Best Answer
I think I found the answer, it is more or less not possible and anyway not intended in an ERM. There is a small workaround left in using double underlining.
There is simply no FK to mark in an official ERM. See http://faculty.juniata.edu/rhodes/dbms/ermodel.htm:
...
And in https://en.m.wikipedia.org/wiki/Weak_entity:
...
Thus, the weak key attribute is not equal to a foreign key, it is simply a primary key of another entity needed to reach an entity’s uniqueness (a weak entity is effectively the interim entity that you need to dissolve an M to N relation, using the PKs of “both sides” as attributes; this is out of my course script). Thus the dotted line is not the format for FKs.
Result:
Then, only double underlining remains as an unofficial convention which is up to now used at the university. And mind that showing the FKs is not needed anyway, a named relation implies it and should not explain it, an ERM is not about FKs; do not think in tables.