mysql - 如何在SQL中实现聚合？ (这与 GroupBy 无关)

Question

在大学项目范围内，我应该实现数据库的聚合。
我得到了一个实体关系模型，它看起来与此类似: 现在我应该实现一个 SQL 脚本来创建这样的数据库，但我在谷歌或其他任何地方找不到有关此主题的任何内容。在我教授的幻灯片中，上面写着

• For example, to represent aggregation manages between relationship works_on and entity set manager, create a schema
  manages(employee_id, branch_name, title,manager_name)
• Schema works_on is redundant provided we are willing to store null values for attribute manager_name in relation on schema manages

因此，我尝试将两张表放入我的 SQL 脚本中，一张名为 works-on，一张名为 manages。在 works-on 中，我放置了 job、branch 和 employee 的所有主键，并将它们定义为外键。在manages中，我放置了所有这些主键，另外我还放置了manager。现在的问题是，当我使用 MySQL-workbench 的逆向工程创建数据库的 EER 模型时，我没有从中得到任何与此聚合有关的信息。那么我在这里做错了什么？
按照@Barmar的要求，我刚刚编写了我将用于此目的的CREATE TABLE语句:

CREATE TABLE job
(jobid INT,
PRIMARY KEY(jobid));

CREATE TABLE employee
(employeeid INT,
PRIMARY KEY(employeeid));

CREATE TABLE branch
(branchid INT,
PRIMARY KEY(branchid));

CREATE TABLE manager
(managerid INT,
PRIMARY KEY(managerid));

CREATE TABLE works_on
(jobid INT, KEY(jobid),
branchid INT, KEY(branchid),
employeeid INT, KEY(employeeid));

CREATE TABLE manages
(jobid INT, KEY(jobid),
branchid INT, KEY(branchid),
employeeid INT, KEY(employeeid),
managerid INT, KEY(managerid));

ALTER TABLE works_on
ADD CONSTRAINT FK_workson_employee FOREIGN KEY(employeeid) REFERENCES employee(employeeid);
ALTER TABLE works_on
ADD CONSTRAINT FK_workson_branch FOREIGN KEY(branchid) REFERENCES branch(branchid);
ALTER TABLE works_on
ADD CONSTRAINT FK_workson_job FOREIGN KEY(jobid) REFERENCES job(jobid);

ALTER TABLE manages
ADD CONSTRAINT FK_manages_employee FOREIGN KEY(employeeid) REFERENCES employee(employeeid);
ALTER TABLE manages
ADD CONSTRAINT FK_manages_branch FOREIGN KEY(branchid) REFERENCES branch(branchid);
ALTER TABLE manages
ADD CONSTRAINT FK_manages_job FOREIGN KEY(jobid) REFERENCES job(jobid);
ALTER TABLE manages
ADD CONSTRAINT FK_manages_manager FOREIGN KEY(managerid) REFERENCES job(managerid);

Answer 1

您的 ER 图表缺少一个重要信息:经理和由其他 4 个元素构建的新实体之间的基数 job、employee、manager、branch 和 works-on(这个新实体由它们周围的方 block 标记)。

从幻灯片上的引用我们可以推断出这是一个0..1关系，这意味着 job、branch 和 employee 的每个组合(或 works-on 中的每个条目)最多有一名经理，但不需要一名(与例如组合相反)。

但是您必须在实际任务中验证该基数。

您通常可以通过多种方式实现 ER 图，但幻灯片暗示了以下实现:

CREATE TABLE manages
( jobid INT not null,
  branchid INT not null,
  employeeid INT not null,
  managerid INT null,
  PRIMARY KEY (jobid, branchid, empoyeeid)
);

我省略了表job、employee、manager和branch的简单外键。

通过此实现，您不再有用于 works-on 关系的显式表，就像幻灯片中的第二个语句所示。它包含在 manages 表中。这仅适用于 0..1 关系，这就是基数是可推导的原因。

如果您想保留一个用于works-on的表，您可以使用

CREATE TABLE works_on
( jobid INT not null,
  branchid INT not null,
  employeeid INT not null,
  PRIMARY KEY (jobid, branchid, empoyeeid)
);

CREATE TABLE manages
( jobid INT not null,
  branchid INT not null,
  employeeid INT not null,
  managerid INT not null,
  PRIMARY KEY (jobid, branchid, empoyeeid),
  FOREIGN KEY (jobid, branchid, employeeid) 
    REFERENCES works_on (jobid, branchid, employeeid)
);

我再次省略了琐碎的外键。

为了简化外键(也许是为了强调该组合被视为一个新实体)，您可以按照@Barmar的建议，向works_on添加一个额外的(通常是自动增量)键>-table 并在 manages-table 中使用该值，尽管幻灯片在这里没有这样做。

如果您需要实现0..n-关系(多个经理可以管理特定的works-on-组合)，您无法吸收 manages 关系中的 >works-on-relation 不再存在(因此您需要两个表)，并且为了尊重 n，您必须包含 managerid 在主键中，PRIMARY KEY (jobid,branchid,empoyeeid,managerid)(但仍需要保留FOREIGN KEY (jobid,branchid,employeeid))。