CODEWITHSUNDEEP
databaseentity-relationship
aromaticcurry
aromaticcurry

Reputation: 19

Ternary and binary ER relationships

I'm having trouble understanding the difference between a ternary relationship like this :

enter image description here

and multiple binary relationships like this (if that's right) : enter image description here

From what I've been reading I think it has to do something with constraints? If someone can explain the difference to me clearly I would greatly appreciate it.

Upvotes: 0

Views: 1658

Answers (1)

reaanb
reaanb

Reputation: 10064

A ternary relationship is a single relationship between 3 elements, e.g.:

enrollment (student PK, subject PK, teacher PK)

In this case, students who enroll for a particular subject must register with a teacher who teaches that subject. Teachers could teach multiple subjects and subjects could be taught by multiple teachers, so we can't derive either from the other.

A different way of modeling this situation might be to define classes. Each class belongs to a particular subject and teacher and is used to enroll students, e.g.:

class_teacher (class PK, teacher)
class_subject (class PK, subject)
class_student (class PK, student PK)

Thus, the ternary relation has been decomposed into 3 binary relations. This isn't equivalent to the enrollment relation above - previously we couldn't relate subjects to teachers without including students.

In some cases it may be useful to decompose a relation like that, in other cases not. It's up to you as data modeler to decide how best to describe a situation.

Upvotes: 2

Related Questions