0

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…

ScientiaEtVeritas
  • 5,158
  • 4
  • 41
  • 59
  • I would have the Student table, Essay Table, and Professor table with a denormalized table joining all three that has a joint unique key constraint on the student id and professor id – Marshall Tigerus Jun 14 '16 at 19:28

2 Answers2

0

If you want your database engine to prevent adding multiple equal topics and professors per student, you will need a composite key / composite constraint over student_id + topic_id + professor_id. Have a look at this question: How do I specify unique constraint for multiple columns in MySQL?

Community
  • 1
  • 1
Marat
  • 617
  • 5
  • 12
  • Thank you for the reference. But what I wonder is what would it look like in the entity relationship model? – ScientiaEtVeritas Jun 14 '16 at 19:57
  • Do you mean you want him to generate an ER diagram for you?! – Drew Jun 15 '16 at 02:48
  • As I understand, you will have at least 4 tables: students, topic, professor and essays. In my case, essays will have student_id, topic_id and professor_id as foreign keys. – Marat Jun 15 '16 at 06:52
0

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.

reaanb
  • 9,806
  • 2
  • 23
  • 37