When to use ternary relationship instead of aggregation in RDBMS?

Question

I was wondering when one would represent a relationship between an entity set and a relationship with a ternary relationship? I understand the benefit of aggregation, but why use it if there is no attribute in the relationship between the entity set and the relationship set? For instance, a grad student (with a student # and name) works on a project (with pid, start date, and end date) and each project that a student works on has a supervising professor. Every project must have only one professor as supervisor. The supervisor relation has no attribute that is unique yet I have been told that aggregation should be used, but why?

Answer 1

Aggregation is not just in case there is an attribute in the relationship, ternary is more restrictive in some cases, ill explain with an example:

Let's say you have a small database used for scheduling classes in various dates and assign teachers to those classes at those dates, using ternary relationship it would look something like that:

ERD: https://i.sstatic.net/8FQ87.png

CREATE TABLE teacher
{
  teacher_id int PRIMARY KEY
}

CREATE TABLE class
{
  class_id int PRIMARY KEY
}

CREATE TABLE date
{
  date_id int PRIMARY KEY
}

CREATE TABLE teaching_class_in_date
{
  date_id int,
  class_id int,
  teacher_id,
  constraint teaching_class_in_date_PK PRIMARY KEY (date_id,class_id,teacher_id)
  constraint teacher_FK FOREIGN KEY (teacher_id) references teacher (teacher_id)
  constraint class_FK FOREIGN KEY (class_id) references class (class_id)
  constraint date_FK FOREIGN KEY (date_id) references date (date_id)
}

This means that when you want to schedule a new class with a teacher you'll need all three records in teacher, class and date beforehand. but what if you want to assign first a class at a certain date but not sure about the teacher yet ?

you have 2 options, one which I usually see in a messy enterprises databases is to create a dummy record in teacher table to allocate to each new class at a certain date if you're not sure about the teacher yet, and it will show in the GUI as "default", or "empty". Option two, make the teacher_id not part of the primary key and allow null values to the teacher_id foreign key.

Both are bad decisions, the second is worse as it breaks the database third normal form.

To fix this you can use aggregation like this:

ERD: https://i.sstatic.net/wAEXF.png

So now you'll have an extra table:

CREATE TABLE class_in_date
{
  date_id int,
  class_id int,
  constraint class_in_date_PK PRIMARY KEY (date_id,class_id)
  constraint class_FK FOREIGN KEY (class_id) references class (class_id)
  constraint date_FK FOREIGN KEY (date_id) references date (date_id)
}

Now you can assign the two without worrying about the teacher. it all comes down to requirements, If I didn't create the requirement to assign a class at a certain date without a teacher before hand, then a ternary relationship would have been sufficient.

Hope this helped !