ScientiaEtVeritas ScientiaEtVeritas - 1 year ago 66
SQL Question

Modelling: Relationship of three Tables

I have three entities: students, professors and essay_topics.

Situation: students can write several essays with an essay_topic under supervision of a professor. (bold: entities)

But they can only write an essay under supervision of a specific professor once. (not two times the same professor)
And they can not write an essay with the same essay_topic as they did.

That's the only restriction, but it should be possible that: A Professor can supervise several students with the same essay_topic. Different professors can supervise different student with the same essay_topic.

So we have a relationship essay(StudentID, EssayTopicID, ProfessorID).
But I don't see what are the primary keys now. Or what kind of relationship do we have here? I would guess n:1:1 (er model). But then, only StudentID would be the primary key of that table – what's actually wrong, because the student only could write one essay…

Answer Source

If you write down the functional dependencies for your restrictions, it helps to understand the situation:

(student, essay_topic) -> professor
(student, professor) -> essay_topic

You've got two overlapping candidate keys - (student, essay_topic) and (student, professor). You can choose either as primary key in the physical model, as long as you add a unique constraint for the other.

I suspect this is a situation that can't be exactly modeled in ER (yes, ER isn't a complete data model, unlike the relational model). I would leave out the cardinality indicators and include the FDs as a comment.

PS. Please don't confuse relationships between entities with relationships between tables. Relationships between entities are recorded IN tables as an association between (usually different) entity sets. Relationships between tables are foreign key constraints and enforce a subset restriction on two columns of the same entity set.