试图了解实体关系图中的基数? [英] Trying to understand cardinality in an entity relationship diagram?

问题描述

我对关系数据库很陌生，最近在尝试理解我得到的实体关系图时遇到了很多麻烦.

I'm quite new to relational databases and have been having a lot of trouble recently trying to understand an entity relationship diagram I've been given.

在这里是:律师ERD (ERD适用于虚构的律师公司)

Here it is: Solicitor ERD (ERD is for a made up solicitor company)

基本上，我的任务是获取此ERD并编写一个SQL脚本来创建数据库，显然要用我可以组成的数据填充表. SQL语法不是我很难解决的问题，它只是理解图中的基数.

Basically my task is to take this ERD and write an SQL script to create the database, obviously filling in the tables with data I can make up. The SQL syntax isn't the thing I'm having a hard time with, it's simply understanding the cardinality in the diagram.

对我来说，术语"1对1"，"1对许多"，许多对许多"只是不单击，我不知道它们的含义以及它们如何影响主键和外键的位置.

To me the terms '1 to 1', '1 to many', 'many to many' just don't click, I have no idea what they mean and how they affect where primary and foreign keys go.

我可以使用这些表并轻松创建相关列，例如，我知道客户端"表将包含"client_name"之类的内容.但是，说到将客户"表与案例"表链接起来，我怎么知道外键在哪里呢?

I can take these tables and easily create relevant columns, for example I know the 'client' table will include something like 'client_name'. However, when it comes to, say, linking the 'client' table with the 'case' table, how do I know where foreign keys go?

客户"表中是否包含案例"表中的"caseID"以及案例"表中是否包含客户"表中的"clientID"?还是只有一个表具有外键?这样的事情我只是不明白.

Would the 'client' table contain 'caseID' from the 'case' table as well as the 'case' table containing 'clientID' from the 'client' table? or would only one table have a foreign key? It's things like this I just don't understand.

很抱歉，如果您能以简单的英语解释我如何着手开发此ERD，将不胜感激！我已经困惑了两天了:(

Sorry for the long post, if anyone could explain in plain english how I go about developing this ERD it would be most appreciated! I've been puzzled for two days now :(

推荐答案

这种图表样式

有一个实体类型/类/集合&每个盒子的表格和一个关系类型/类/集合&每条标记行的表格.

There is an entity type/class/set & table for every box and a relationship type/class/set & table for every labeled line.

线的末端指向参与关系类型的实体类型.您需要了解在应用程序域方面的实体和关系.表的一行代表实体/关系实例.每个端点都有一个从关系类型表到实体类型表的外键.他们将引用实体类型的候选键.

The ends of a line point to entity types participating in the relationship type. You need to know what the entities and relationships are in terms of the application domain. A row of a table represents an entity/relationship instance. There will be a foreign key from relationship type table to the entity type table for every endpoint. They will reference the candidate keys of the entity types.

行尾的基数告诉您对于给定的实体实例，它可以出现在多少个关系实例/行中.如果给定的实例/值不必在实例/行中出现，则为0. (一个人不必拥有一只宠物.)如果它只能出现在一个实例/行中，则为1.(一个人必须拥有一个头部.)如果它可以出现多次，则为多次. (一个人可以拥有许多宠物.因此，persons-owns-pet将是0或-MANY.)通常，我们将可能性的数量放在一行的两端，并说" possibility -to - possibility "或" possibility : possibility "，即我们阅读标签的方向.

The cardinality at the end of a line tells you for a given entity instance how many relationship instances/rows it can appear in. If a given instance/value doesn't have to appear in an instance/row then that's 0. (A person doesn't have to own a pet.) If it can only appear in one instance/row then that's 1. (A person has to possess a head.) If it can appear more than one time then that's MANY. (A person can own many pets. So person-owns-pet would be 0-or-MANY.) In general we put the number of possibilities on either end of a line and say "possibility-to-possibility" or "possibility:possibility" in the direction that we read the label.

存在具有"，是"或与...关联"的行，标签没有明确的关系类型/表，而实际关系显示为实体类型(已确定/关联的实体类型/类/集).这些行实际上只是从表到参与者实体表的外键. (一个人可以与另一个人结婚；该对象的关系实例也是婚姻的关联实体实例.)大概就是这种风格显示n元关系的方式. (令人困惑.)(这种标签的实际关系由关联实体类型表的投影表示.)

There are "has" or "is" or "is associated with" lines with no explicit relationship type/table for the label while the real relationship is displayed as an entity type (a reified/associative entity type/class/set). The lines are really just foreign keys from the table to participant entity tables. (A person can be married to another person; a relationship instance of that is also an associative entity instance of a marriage.) Presumably it's how this style shows n-ary relationships. (Confusing.) (The actual relationship for such a label is represented by a projection of the associative entity type table.)

其他约定

某些方法将可能性限制为特定的选择.有时"1"表示"0-或-1".一些方法通过不存在或存在关系行与通过强制性但可为空的外键来区分0或1参与关系.一些方法允许与两个以上参与者的关系. (好主意.)然后，您只需在标签到实体之间画另一条线即可.它是 X : Y : Z :....某些方法从具有基数的实体. (除非将n元关系编码为关联实体，否则所有关系"都是二进制的，否则将不进行处理.)某些方法具有用于标签的符号.有些方法具有未标记的行，而这些行只是外键.

Some methods restrict possibilities to particular choices. Sometimes "1" means "0-or-1". Some methods distinguish 0-or-1 participation in a relationship via a relationship row being absent or present vs via an obligatory but nullable foreign key. Some methods allow relationships with more than two participants. (Good idea.) Then you just draw another line from the label to an entity. It's X:Y:Z:.... Some methods label the far end of a line from an entity with its cardinality. (Doesn't handle n-ary relationships unless you encode them as associative entitites so all "relationships" are binary.) Some methods have symbols for labels. Some methods have unlabelled lines which are just foreign keys.

Wiki 中恰好有一篇文章.

外键

我们不需要外键即可知道关系的含义或更新或查询数据库.关系表中将出现实体表的候选/主键列集，因为这些实体参与了该关系.当DBMS构建相应的表表达式并计算其值时，我们通过将关系和条件组合为其他关系来进行查询.

We do not need foreign keys to know what the relationships mean or to update or query a database. There will be candidate/primary key column sets of entity tables appearing in relationship tables because those entities participate in that relationship. We query by combining relationships and conditions into other relationships while the DBMS builds a corresponding table expression and calculates its value.

外键仅表示列列表的子行值必须是表中唯一的另一个列列表的子行值.因此，在这种情况下，请声明一个外键. (这有助于DBMS拒绝错误的更新和优化，并可以帮助人们理解关系或注意错误.)通常是因为实体表键列列表出现在关系表中，因为该实体参与了该关系. (不幸的是，某些方法和工具调用的不是外键关系；它们只是真实的语句.请查看您自己的图中如何存在/具有/关联标签，这些标签不是，但是像它们一样绘制.)

A foreign key just says that a subrow value for a column list must be a subrow value for another column list that is unique in its table. So when that's so, say so by declaring a foreign key. (That helps the DBMS reject erroneous updates and optimize, and can help people understand the relationships or notice errors.) Which is generally when an entity table key column list appears in a relationship table because that entity participates in that relationship. (Some methods & tools unfortunately call foreign keys relationships, which they are not; they are just true statements. See how in your own diagram there are is/has/associated labels that aren't relationships but are drawn like them.)

这篇关于试图了解实体关系图中的基数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！