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