关系架构到ER图/基数差异 [英] Relational Schema to ER Diagram /Cardinalities difference
问题描述
如果我们有一个 ER图，并且想要转换为关系模式，我们将遵循特定的过程(例如Elmashri& Navathe书).我不清楚的是，当基数为 1:1对1:N 时是否存在差异.这种差异如何体现在关系模式中?
If we have an ER diagram and we want to convert to a relational schema we follow a specific procedure (eg Elmashri& Navathe book). What is not clear to me is if there is a difference when there is a cardinality of 1:1 vs 1:N. How is this difference represented in the relational schema?
例如，在下图的Elmashri中，如果我们关注基数为1:N的Department-Project之间的关系，我们将采用以下关系模式.如果基数为1:1，会有区别吗?
For example in the following figure from Elmashri if we focus on the relation between Department-Project that has a cardinality of 1:N we take the following relational schema. If the cardinality was 1:1 would there be a difference?
直接问一下:在下图中，如果有人只给我带有关系模式的左半部分，我怎么说两个关系(黑圈和红圈)是1:1还是1:N?
And to ask more directly: In the following figure, if somebody was giving me only the left part of it with the relational schema, how would I say if the 2 relations (in black and red circle) are 1:1 or 1:N?
推荐答案
从ER&伪ER到关系
那是Chen风格的ER图.钻石表示关系(船)/协会与有相应的表/关系.线表示参加者&有相应的FK.红衣主教是关于钻石的，即关系/关系/表/关系.它们告诉您有关实体的哪些组合可以参与关系/关系的某些信息.
That is a Chen style of ER diagram. Diamonds denote relation(ship)s/associations & have corresponding tables/relations. Lines denote participations & have corresponding FKs. Cardinalites are about diamonds ie relation(ship)s/associations ie tables/relations. They tell you certain things about what combinations of entities can participate in a relation(ship)/association.
您的教科书是Elmashri& amp;的第7版数据库系统基础知识.纳瓦特遵循上述用法&解释了如何映射X:Y:etc&解释了它如何特别映射您的示例.DDL可以通过将ERD映射到明显的交叉引用"/关系"表，然后将它们组合而产生.但是您的教科书只是提供了多种映射X:Y的方法，而没有清楚地说明快捷方式是如何通过明显的方式加组合而产生的.
Your textbook is Fundamentals of Database Systems 7th edition by Elmashri & Navathe. It follows the above usage & explains how to map X:Y:etc & explains how it mapped your example in particular. That DDL can arise from mapping the ERD to obvious "cross reference"/"relationship" tables then combining them. But your textbook just gives multiple ways to map X:Ys without clearly explaining how the shortcut ways arise from the obvious ways plus combining.
例如:作为查询 select Dnum，PROJECT中的Pnum 的值的表表示对于某些Pname& Plocation，部门Dnum控制项目Pnum"的二进制关系.正好是部门Dnum控制项目Pnum"的时间-即部门&一个专案.ER图表示Dnum:Pnum&中对应的二进制关系为1:N.Pnum:Dnum中的N:1.由于 CONTROLS在基数上具有特定的1，因此可以将其明显的表 与PROJECT实体表合并为表PROJECT.如果是M:N，则合并将存在某些问题，因此您的文本将给出不同的映射关系-但在先前的假设下，它仍然可能是 .如果您这样做了，或者只是使用了明显的映射，则1:N& NM:N.1:1允许其他组合.
Eg: The table that is the value of query select Dnum, Pnum from PROJECT represents the binary relationship "for some Pname & Plocation, department Dnum controls project Pnum"--which is presumably true exactly when "department Dnum controls project Pnum"--which is relationship CONTROLS on a DEPARTMENT & a PROJECT. The ER diagram says that the corresponding binary relation is 1:N in Dnum:Pnum & N:1 in Pnum:Dnum. Because CONTROLS has that particular 1 in its cardinality, its obvious table can be combined with the PROJECT entity table into table PROJECT. If it were M:N then combining would have certain problems so your text gives a different mapping--but under the previous presumption it still could be. And if you did that or just used the obvious mapping then your relational design would be the same for both 1:N & M:N. 1:1 allows yet other combinations.
不同的设计和图表绘制方法对基数和名称有不同的约定映射到关系.许多所谓的ER方法并不是真正的ER，因为它们只是ER的微不足道-一切都是(可能是)关联的实体.所有行的一端或两端都包含1或1或0，因为它们与某些隐式二进制关系有关.与FK关联的表格-就像当上述明显的Chen表格被合并时一样.也有在这里&视线基数其他约定，例如显式空FK.一切都取决于您使用的是什么教科书/参考书/方法.
Different design & diagramming methods have different conventions for cardinalities & mapping to relations. Many so-called ER methods are not really ER in that they are trivially ER--everything is a (possibly associative) entity. All lines involve a 1 or 1-or-0 at one or both ends--because they are about certain implicit binary relationships & tables associated with FKs--like when obvious Chen tables are combined as above. Also there are look-here & look-away cardinalities & other conventions like explicit null FKs. All depends on what textbook/reference/method are you using.
您的DDL图关系"是一个FK，但被伪ER方法错误地但普遍地称为关系".您没有在DDL图中有意义地使用"X:Y".您正在或多或少地使用它，伪ER图将如何标记FK.但是基数是关于由FK参考表的某个投影表示的关系(船)/关联.
Your DDL diagram "relation" is a FK--wrongly but ubiquitously called a "relationship" by pseudo-ER methods. You are not using "X:Y" meaningfully in the DDL diagram. You are using it more or less how a pseudo-ER diagram would label a FK. But the cardinality is about the relation(ship)/association represented by a certain projection of the FK's referencing table.
从关系到ER&的重新映射伪ER
如果您是从Chen ER设计与开发部门开始的仅使用明显的映射，则表1:1与实体&关系和实体表将是没有FK的表.但是您的教科书有多种映射选择，涉及结合ER图实体和将表以多种方式关联到其他表中，因此数据库中的表不会告诉您图实体&他们产生的关系.
If you started from a Chen ER design & used only the obvious mappings then you would have tables 1:1 with entities & relationships & the entity tables would be the ones with no FKs. But your textbook has multiple mapping choices that involve combining ER diagram entity & relationship tables into other tables, in multiple ways, so the tables in the database don't tell you the diagram entities & relationships that they arose from.
关系设计比Chen ER设计更笼统-表表示0或多个值上的关系.(每个基表的每个超键和查询结果都标识一个实体.)因此，并非所有合理的关系设计都对应于Chen ER设计.伪ER方法的一个好处是，它们实际上是在记录DDL，而不是区分实体和实体.关系.但是，如果它们确实来自实体&设计中未记录的关系.因此，您无法从这种关系/伪ER设计映射回这些实体&实体.关系.
Relational designs are more general than Chen ER designs--tables represent relations on 0 or more values. (Every superkey of every base table & query result identifies some entity.) So not all reasonable relational designs correspond to Chen ER designs. Whereas one benefit of the pseudo-ER methods is that they are really recording DDL and not distinguishing between entities & relationships. But if they do arise from entities & relationships that's not recorded in the design. So you can't map from such a relational/pseudo-ER design back to those entities & relationships.
除非您将DDL约束放入基数中，否则您将不知道基数，最好以声明方式将其放入-否则会触发.FKs&CK(通过PK& UNIQUE NOT NULL)足以表示二进制关系(船)/关联的Chen基数约束，但不是n元.伪ER方法可能会也可能不会解决超出PK& amp;的限制.CKs.而且Chen ER的设计可能存在问题约束，必须通过一般的关系设计原则来解决-因此，它们实际上只是临时的.(而且-不必要.)
You wouldn't know the cardinality from DDL constraints unless you put them in --which you should, preferably declaratively but otherwise triggers. FKs & CKs (via PKs & UNIQUE NOT NULL) are enough to express Chen cardinality constraints for binary relation(ship)s/associations, but not n-ary. Pseudo-ER methods may or may not address constraints beyond PKs & CKs. And Chen ER designs can have problem constraints that must be addressed through general relational design principles--so really they are only provisional. (And--unnecessary.)
这篇关于关系架构到ER图/基数差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
