将关系属性从ER图转换为SQL [英] Translating relationship attributes from ER diagram into SQL

问题描述

当前是第一次尝试使用SQL，因此我正在解决一些问题.这是一个示例数据库规范:

Currently trying to get to grips with SQL for the first time, so I am working through a few problems. Here is a sample database spec:

学生(姓名，性别，课程)做项目(标题).每个项目都有 两名主管(姓名，性别，部门).所有学生都做一个项目 但并非所有项目都被采纳.一个以上的学生可以做同样的事情 项目.学生定期会见一位导师，而这些导师 会议被记录下来(日期，时间，学生，主管，笔记).

Students (name, gender, course) do projects(title). Each project has two supervisors (name, gender, department). All students do a project but not all projects get taken. More than one student can do the same project. Students meet one of their supervisors regular and these meetings are recorded (date, time, student, supervisor, notes).

到目前为止，我已经绘制了一张我认为是正确的ER图:

So far I've got an ER diagram drawn up which I think is correct:

我可以获得基本知识(例如，创建Student表等)，但是我很难理解如何表示这种关系，特别是会议关系，以及如何在SQL中表示它及其属性.我应该创建一个会议"实体吗?

I can get the basics (e.g. creating a Student table etc) but I'm having trouble getting my head around how to represent the relationships, specifically the meetings relationship, and how to represent it and its attributes in SQL. Should I instead create a 'meetings' entity?

推荐答案

是的，您应该创建一个Meeting实体来表示Student和Supervisor之间的多对多关系.在其中，您可以使用与那些表相对应的外键来关联这些表.在SQL中，可能看起来像这样:

Yes, you should create a Meeting entity to represent the many to many relationship between Student and Supervisor. In it you can relate to those tables using foreign keys that correspond to the those respective tables. In SQL it may look something like this:

Create table Meeting {
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
student_id INT NOT NULL,
supervisor_id INT NOT NULL,
//rest of the fields...   
FOREIGN KEY (student_id) REFERENCES Student(id)
FOREIGN KEY (supervisor_id) REFERENCES Supervisor(id)
}

对于Project和Supervisor之间的Supervise，您也将执行相同的操作.您也可以在会议表上使用称为复合键的东西，我想这取决于个人喜好，当代表多对多关系时，我通常会采用这种方式.我并不是说这将是您要使用的语法，它取决于您的数据库，这只是一个向您指明正确方向的示例.希望能帮助到你.

You would also do the same thing for the Supervise between Project and Supervisor. Also you could use something called a composite key on your Meeting table, I guess it comes down to personal preference, I usually do it this way when representing many to many relationships. I'm not saying this is the syntax you will use, that depends on your database, this was just an example to point you in the right direction. Hope it helps.

对于您的图表(我只是猜想这是针对一类的)，您可能想研究诸如visio或visual paradigm之类的软件来创建ER图表.虽然大多数人都可以理解您当前的图表，但这并不是正确的建模.

Also for your diagram (I'm just guessing this is for a class) you might want to look into software such as visio or visual paradigm to create your ER diagram. While most people will be able to understand your current diagram, that's not correct modeling.

为了好玩，我根据您的表格制作了一个图表:

For fun I made a diagram based on your tables:

如果Supervisor和Project之间存在多对多关系，则需要一个实体.这称为associative entity.我标记了我的SupervisorProject只是为了使它们更加清晰.

You would want an entity between Supervisor and Project if they are a many to many relationship. This is called an associative entity. I labeled mine SupervisorProject just so they are a little more clear.

修改 忽略了Student和project是一对多的事实，对此进行了修复，对不起.

Edit Overlooked the fact that Student and project was a many to one, fixed that, sorry.

这篇关于将关系属性从ER图转换为SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！