Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

"Group" is a group of pupils. "Topic" is a topic of lessons.

To every group corresponds exactly one topic. (So there may be multiple groups per topic.)

To a pupil corresponds multiple groups, but maximum one group per topic.

How to implement this in terms of UNIQUE indexes and foreign keys in MySQL?

share|improve this question
    
A group can have many students and a student can belong to many groups? –  ypercube Jul 4 '13 at 19:22
    
Yes, a group can have many students and a student can belong to many groups –  porton Jul 4 '13 at 20:04

1 Answer 1

up vote 5 down vote accepted

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 
share|improve this answer
    
1. I don't understand your notation. 2. Topic in PupilParticipatesInGroup seems superfluous because the topic is unambiguously determined by a group –  porton Jul 4 '13 at 19:39
    
Your solution requires me to add redundant Topic field to the PupilParticipatesInGroup table. For having no superfluous fields, isn't it better to use transactions instead of UNIQUE indexes and FKeys? –  porton Jul 4 '13 at 20:03
    
Using transactions is good yes. But that doesn't mean you can remove Primary, Unique and Foreign key constraints. Who is going to take care of the integrity then? The transactions? How? –  ypercube Jul 4 '13 at 20:10
    
Can your solution be modified not to add superfluous PupilParticipatesInGroup.Topic field? –  porton Jul 4 '13 at 20:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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