逻辑数据库设计（基本1：m规则VS.可选1：m规则） [英] Logical Database Design(Basic 1:m rule VS. Optional 1:m rule)

问题描述

我必须将er图转换为关系模式。但是，我看不出Basic 1：M规则和Optional 1：M（最小null）规则之间的区别。

I have to transform a er diagram into a relation schema. However, I can't tell the difference between Basic 1:M rule and Optional 1:M (Minimal null) rule.

推荐答案

显然，您正在关注类似 http://www.javaguicodexample.com/erdrelationalmodelnotes.html ：

Apparently you're following something like http://www.javaguicodexample.com/erdrelationalmodelnotes.html:

1-M关系规则：：表中的每个1-M关系将成为与子类型（鱼尾纹符号附近的实体类型）相对应的FK。如果关系的父方的最小基数为1，则FK无法接受空值（必须使用NOT NULL）。

1-M Relationship Rule: Each 1-M relationship becomes a FK in the table corresponding to the child type (the entity type near Crow’s Foot symbol). If the minimum cardinality on the parent side of the relationship is one, the FK cannot accept null values (NOT NULL must be used).

您的ER [sic]图样式在设计的每个表中都有一个框，其中一个表代表应用程序实体类型的实例。 （可能是关联实体类型，其键是一组其他实体ID。）在原始ER建模中，关系是实体之间的关联。但是在您的伪ER图样式中，关系以一种混乱的方式使用，有时是指一个关联，有时是指一个FK（外键）。

Your style of ER [sic] diagram has a box for every table of a design, where a table represents the instances of an application entity type. (Possibly an associative entity type, whose key is a set of other entity ids.) In original ER modeling a "relationship" is an association among entities; but in your style of pseudo-ER diagram "relationship" is used in a confused way sometimes referring to an association and sometimes to a FK (foreign key).

父实体类型与子实体类型具有一对多（业务/应用程序）关联/关系，然后我们可以根据基本1：M规则对其进行建模。我们在子表中添加一个NOT NULL FK列，以提供子代的父ID。但是，如果关联/关系是一对多0或1-，即是可选的一对多（即以前的孩子不必具有父级，即父级是可选的），则可以进行建模根据可选的1：M规则。我们在子表中添加了一个可为空的FK列，该列可给出子项的父ID或如果没有父项则为NULL。

If a "parent" entity type has a 1-to-many (business/application) association/relationship with a "child" entity type then we can model that per a "Basic 1:M rule". We add a NOT NULL FK column to the child table giving the child's parent id. But if the association/relationship is 0-or-1-to-many, ie is optional 1-to-many (ie an erstwhile child doesn't have to have a parent, ie a parent is optional), then we can model that per an "Optional 1:M rule". We add a NULLable FK column to the child table that either gives the child's parent id or NULL if it has no parent.

初始设计：

-- T identifies a thing with property ...
thing(t, ...)
-- W identifies a whatsit with property ...
whatsit(w, ...)

现在我们要能够记录一对多关系/关联my_1_to_many（t1，w）和0或一对一关系/关联my_0_or_1_to_many（t0，w）的参与者：

Now we want to be able to record participants in 1-to-many relationship/association my_1_to_many(t1, w) and 0-or-1-to-many relationship/association my_0_or_1_to_many(t0, w):

-- T identifies a thing with property ...
thing(T, ...)
--     W identifies a whatsit with property ...
-- AND my_1_to_many(T1, W)
-- AND (my_0_or_1_to_many(T0, W) OR T0 IS NULL AND NOT my_0_or_1_to_many(T0, W))
whatsit_plus_associated_things(w, ..., t1, t0)
NOT NULL t1
NULL t0
FK (t1) references thing (t)
FK (t0) references thing (t)

（您可以看到为什么NULL使设计复杂化的原因与仅拥有无空表 whatsit（w，...）， my_0_or_1_to_many（t，w）和; my_0_or_1_to_many（t，w）。）

(You can see why NULL complicates designs compared to just having NULL-free tables whatsit(w, ...), my_0_or_1_to_many(t, w) & my_0_or_1_to_many(t, w).)

这篇关于逻辑数据库设计（基本1：m规则VS.可选1：m规则）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！