什么时候在RDBMS中使用三元关系而不是聚合？ [英] When to use ternary relationship instead of aggregation in RDBMS?

问题描述

我想知道什么时候代表一个实体集和一个三元关系之间的关系？我了解聚合的好处，但是如果实体集和关系集之间的关系中没有属性，为什么要使用聚合呢？例如，一个研究生（带有学生编号和姓名）在一个项目上工作（带有pid，开始日期和结束日期），并且该学生从事的每个项目都有一位指导教授。每个项目都只能由一位教授担任主管。主管关系没有唯一的属性，但有人告诉我应该使用聚合，但是为什么呢？

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?

推荐答案

聚合是不仅是在关系中有一个属性的情况下，在某些情况下三元的约束更严格，下面以一个例子来解释一下：

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.stack.imgur.com/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 ?

您有2个选择，我通常会看到一个混乱的选择企业数据库将在教师表中创建一个虚拟记录，以便在不确定日期（如果您不确定教师的时间）分配给每个新班级时，它将在GUI中显示为默认或空。
第二个选项，使Teacher_id不成为主键的一部分，并允许对Teacher_id外键使用空值。

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.stack.imgur.com/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.

希望这对您有所帮助！

这篇关于什么时候在RDBMS中使用三元关系而不是聚合？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！