主页 / dba / 问题 / 80951
Accepted
AlwaysLearningNewStuff
Asked: 2014-10-24 08:13:57 +0800 CST

不知道如何将变量实体转换为关系表

  • 772

简介及相关资料：

以下示例说明了我面临的问题：

动物有一个种族，可以是猫或狗。Cat可以是Siamese或Persian。狗可以是德国牧羊犬或拉布拉多猎犬。

动物是一个强大的实体，而它的种族是一个属性，可以具有两个提供的值（猫或狗）之一。 这两个值都很复杂（我在这里只添加了狗/猫的类型来说明问题，但也可能有猫/狗的名字和其他一些东西）。

问题：

我不知道如何为此示例创建关系表。

我为解决问题所做的努力：

我尝试使用 Chen 的符号绘制 ER 图，这代表了问题，但作为初学者，我不知道我是否做对了。这是我得到的：

在此处输入图像描述

如果我画错了，我深表歉意，如果是这样，请纠正我。我不希望简单地获得“免费解决方案”，还希望学习如何处理这个问题，以便将来我可以自己解决。

我唯一想到的就是创建两个单独的表格，一张给猫，一张给狗。此外，Animal表中的种族属性只会存储cat或dog值。像这样的东西：

Animal< # Animal_ID, race, other attributes >
Cat < # Cat_ID, $ Animal_ID, breed >
Dog < # Dog_ID, $ Animal_ID, breed >

我真的对我的解决方案有一种不好的感觉，我担心它是错误的，因此下面的问题。

问题：

  • 如何将我的示例转换为 ER 图？
  • 如何将该 ER 图转换为关系表？

如果需要更多信息，请发表评论，我会尽快更新我的帖子。也可以随意添加适当的标签，因为我在这里相当新。

谢谢你。

database-design subtypes

4 个回答

  1. Best Answer

    这种情况的正确结构是子类/继承模型，几乎与我在这个答案中提出的概念相同：值的异构有序列表。

    这个问题中提出的模型实际上非常接近，因为Animal实体包含类型（即race）和所有类型共有的属性。但是，需要进行两个小的更改：

    1. 从各自的实体中删除 Cat_ID 和 Dog_ID 字段：

      这里的关键概念是一切都是Animal，而不管race: Cat、Dog、Elephant等等。鉴于该起点，任何特定race的Animal并不真正需要单独的标识符，因为：

      1. 这Animal_ID是独一无二的
      2. 、Cat和将来添加的Dog任何其他race实体本身并不完全代表任何特定的Animal; 它们仅在与父实体中包含的信息结合使用时才有意义，Animal.

      因此，, , etc 实体中的Animal_ID属性既是返回实体的 PK 又是 FK。CatDogAnimal

    2. 区分类型breed：

      仅仅因为两个属性共享相同的名称并不一定意味着这些属性是相同的，即使名称相同意味着这种关系。在这种情况下，您真正​​拥有的实际上CatBreed是DogBreed单独的“类型”

    初始注释

    1. SQL 特定于 Microsoft SQL Server（即 T-SQL）。意思是，要小心数据类型，因为它们在所有 RDBMS 中都不相同。例如，我正在使用，VARCHAR但如果您需要存储标准 ASCII 集之外的任何内容，您应该真正使用NVARCHAR.
    2. “类型”表（、、和）的 ID 字段Race不是CatBreed自动DogBreed递增的（即 T-SQL 中的 IDENTITY），因为它们是应用程序常量（即它们是应用程序的一部分），它们是数据库并enum在 C#（或其他语言）中表示为 s。如果添加值，它们将在受控情况下添加。我保留对通过应用程序传入的用户数据使用自动增量字段。
    3. 我使用的命名约定是命名每个子类表，以主类名开头，后跟子类名。这有助于组织表并清楚地指示（不查看 FK）子类表与主实体表的关系。
    4. 请参阅最后的“最终编辑”部分，了解有关视图的说明。

    “品种”作为“种族”——具体方法

    品种作为种族特定图
    第一组表是查找/类型表：

    CREATE TABLE Race
(
  RaceID INT NOT NULL PRIMARY KEY
  RaceName VARCHAR(50) NOT NULL
);

CREATE TABLE CatBreed
(
  CatBreedID INT NOT NULL PRIMARY KEY,
  BreedName VARCHAR(50),
  CatBreedAttribute1 INT,
  CatBreedAttribute2 VARCHAR(10)
  -- other "CatBreed"-specific properties as needed
);

CREATE TABLE DogBreed
(
  DogBreedID INT NOT NULL PRIMARY KEY,
  BreedName VARCHAR(50),
  DogBreedAttribute1 TINYINT
  -- other "DogBreed"-specific properties as needed
);

    第二个清单是主要的“动物”实体：

    CREATE TABLE Animal
(
  AnimalID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  RaceID INT NOT NULL, -- FK to Race
  Name VARCHAR(50)
  -- other "Animal" properties that are shared across "Race" types
);

ALTER TABLE Animal
  ADD CONSTRAINT [FK_Animal_Race]
  FOREIGN KEY (RaceID)
  REFERENCES Race (RaceID);

    这第三组表是补充的子类实体，它们完成了每个表的Race定义Animal：

    CREATE TABLE AnimalCat
(
  AnimalID INT NOT NULL PRIMARY KEY, -- FK to Animal
  CatBreedID INT NOT NULL, -- FK to CatBreed
  HairColor VARCHAR(50) NOT NULL
  -- other "Cat"-specific properties as needed
);

ALTER TABLE AnimalCat
  ADD CONSTRAINT [FK_AnimalCat_CatBreed]
  FOREIGN KEY (CatBreedID)
  REFERENCES CatBreed (CatBreedID);

ALTER TABLE AnimalCat
  ADD CONSTRAINT [FK_AnimalCat_Animal]
  FOREIGN KEY (AnimalID)
  REFERENCES Animal (AnimalID);


CREATE TABLE AnimalDog
(
  AnimalID INT NOT NULL PRIMARY KEY, -- FK to Animal
  DogBreedID INT NOT NULL, -- FK to DogBreed
  HairColor VARCHAR(50) NOT NULL
  -- other "Dog"-specific properties as needed
);

ALTER TABLE AnimalDog
  ADD CONSTRAINT [FK_AnimalDog_DogBreed]
  FOREIGN KEY (DogBreedID)
  REFERENCES DogBreed (DogBreedID);

ALTER TABLE AnimalDog
  ADD CONSTRAINT [FK_AnimalDog_Animal]
  FOREIGN KEY (AnimalID)
  REFERENCES Animal (AnimalID);

    使用共享breed类型的模型显示在“附加说明”部分之后。

    补充说明

    1. 的概念breed似乎是混淆的焦点。jcolebrand 建议（在对问题的评论中）这breed是一个在不同 s 之间共享的属性race，而其他两个答案已将其集成到他们的模型中。然而，这是一个错误，因为 的值breed不会在 的不同值之间共享race。是的，我知道另外两个提议的模型试图通过race将breed. 虽然这在技术上解决了关系问题，但它无助于解决如何处理非公共属性的整体建模问题，也无助于解决race没有breed. 但是，如果这样的属性保证存在于所有Animals，我也会为此提供一个选项（如下）。
    2. vijayp 和 DavidN 提出的模型（似乎是相同的）不起作用，因为：
      1. 他们要么
        1. 不允许存储非通用属性（至少不允许存储 any 的单个实例Animal），或
        2. 要求所有races 的所有属性都存储在Animal实体中，这是表示此数据的一种非常扁平（且几乎非关系）的方式。是的，人们一直都在这样做，但这意味着每行有许多 NULL 字段用于不用于该特定的属性，并且race知道每行的哪些字段与race该记录的特定相关联。
      2. 它们不允许在将来添加不具有作为属性的raceof 。并且即使 ALL有一个，由于前面已经提到过的内容，这不会改变结构：这取决于（即for与for不同）。AnimalbreedAnimalbreedbreedbreedracebreedCatbreedDog

    “品种”作为公共/共享财产方法

    在此处输入图像描述
    请注意：

    1. 下面的 SQL 可以在与上述模型相同的数据库中运行：

      1. 表Race是一样的
      2. Breed桌子是新的
      3. 这三个Animal表都附加了一个2
    2. 即使Breed是现在的共同财产，在主/父实体中没有注明似乎也不正确Race（即使它在技术上是正确的）。所以，RaceID和BreedID都表示在 中Animal2。为了防止RaceIDinAnimal2和 aBreedID之间的不匹配RaceID，我在两者上都添加了一个 FK，RaceID, BreedID它引用了Breed表中这些字段的 UNIQUE CONSTRAINT。我通常鄙视将 FK 指向 UNIQUE CONSTRAINT，但这是为数不多的这样做的正当理由之一。UNIQUE CONSTRAINT 在逻辑上是一个“备用键”，这使得它对此用途有效。另请注意，该Breed表仍然有一个 PK BreedID。
      1. 不只在组合字段上使用 PK 而没有 UNIQUE CONSTRAINT 的原因是它允许BreedID在RaceID.
      2. 不切换 PK 和 UNIQUE CONSTRAINT 的原因是，这可能不是 的唯一用法BreedID，因此仍然可以在Breed没有RaceID可用的情况下引用 的特定值。
    3. 虽然以下模型确实有效，但它在共享概念方面存在两个潜在缺陷Breed（这也是我更喜欢Race-specificBreed表的原因）。
      1. 有一个隐含的假设，即 的所有值Breed都具有相同的属性。在这个模型中没有简单的方法在Dog“品种”和Elephant“品种”之间具有不同的属性。但是，仍然有一种方法可以做到这一点，这在“最终编辑”部分中有说明。
      2. 没有办法在一个Breed以上的种族中分享一个。我不确定这是否是可取的（或者可能不是在动物的概念中，但可能在其他使用这种类型模型的情况下），但在这里是不可能的。
    CREATE TABLE Race
(
  RaceID INT NOT NULL PRIMARY KEY,
  RaceName VARCHAR(50) NOT NULL
);

CREATE TABLE Breed
(
  BreedID INT NOT NULL PRIMARY KEY,
  RaceID INT NOT NULL, -- FK to Race
  BreedName VARCHAR(50)
);

ALTER TABLE Breed
  ADD CONSTRAINT [UQ_Breed]
  UNIQUE (RaceID, BreedID);

ALTER TABLE Breed
  ADD CONSTRAINT [FK_Breed_Race]
  FOREIGN KEY (RaceID)
  REFERENCES Race (RaceID);

CREATE TABLE Animal2
(
  AnimalID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  RaceID INT NOT NULL, -- FK to Race, FK to Breed
  BreedID INT NOT NULL, -- FK to Breed
  Name VARCHAR(50)
  -- other properties common to all "Animal" types
);

ALTER TABLE Animal2
  ADD CONSTRAINT [FK_Animal2_Race]
  FOREIGN KEY (RaceID)
  REFERENCES Race (RaceID);

-- This FK points to the UNIQUE CONSTRAINT on Breed, _not_ to the PK!
ALTER TABLE Animal2
  ADD CONSTRAINT [FK_Animal2_Breed]
  FOREIGN KEY (RaceID, BreedID)
  REFERENCES Breed (RaceID, BreedID);


CREATE TABLE AnimalCat2
(
  AnimalID INT NOT NULL PRIMARY KEY, -- FK to Animal
  HairColor VARCHAR(50) NOT NULL
);

ALTER TABLE AnimalCat2
  ADD CONSTRAINT [FK_AnimalCat2_Animal2]
  FOREIGN KEY (AnimalID)
  REFERENCES Animal2 (AnimalID);

CREATE TABLE AnimalDog2
(
  AnimalID INT NOT NULL PRIMARY KEY,
  HairColor VARCHAR(50) NOT NULL
);

ALTER TABLE AnimalDog2
  ADD CONSTRAINT [FK_AnimalDog2_Animal2]
  FOREIGN KEY (AnimalID)
  REFERENCES Animal2 (AnimalID);


    最终编辑（希望 ;-)

    1. 关于处理类型之间不同属性的可能性（然后是困难），可以Breed使用相同的子类/继承概念，但作为主要实体。在此设置中，表格将具有所有类型的通用属性（就像表格一样），并表示表格的类型（与表格中的相同）。然后，您将拥有子类表，例如、等。对于较小的项目，这可能被认为是“过度设计”，但它被认为是可以从中受益的情况的一种选择。BreedBreedBreedAnimalRaceIDBreedAnimalBreedCatBreedDog

    2. 对于这两种方法，有时将视图创建为完整实体的快捷方式会有所帮助。例如，考虑：

      CREATE VIEW Cats AS
   SELECT  an.AnimalID,
           an.RaceID,
           an.Name,
           -- other "Animal" properties that are shared across "Race" types
           cat.CatBreedID,
           cat.HairColor
           -- other "Cat"-specific properties as needed
   FROM    Animal an
   INNER JOIN  AnimalCat cat
           ON  cat.AnimalID = an.AnimalID
   -- maybe add in JOIN(s) and field(s) for "Race" and/or "Breed"
    3. 虽然不是逻辑实体的一部分，但在表中包含审计字段以至少了解何时插入和更新记录是相当普遍的。所以实际上：
      1. CreatedDate将向表中添加一个字段Animal。在任何子类表（例如）中都不需要此字段，AnimalCat因为为两个表插入的行应该在事务中同时完成。
      2. 一个LastModifiedDate字段将被添加到Animal表和所有子类表中。仅当更新该特定表时，此字段才会更新：如果更新发生在AnimalCat但不是Animal针对特定的AnimalID，则只会设置LastModifiedDate字段 in AnimalCat。
    • 11

  2. First off, you are doing well to distinguish between ER modeling and relational modeling. Many newbies don't.

    Here are some buzzwords you can use to look up helpful articles on the web.

    Your case is a classic case of class/subclass or, if you like, type/subtype.

    The phrase that's used in ER modeling is "generalization/specialization". And many of the articles show this under something called EER (Enhanced Entity-Relationship) modeling. This wasn't in Peter Chen's original presentation of ER modeling. It was added later. For a pretty good summary of gen/spec in pdf form, click here

    Next, when converting a class/subclass case to relational modeling you design tables. There is more than one approach. The two main approaches are called single table inheritance and class table inheritance. Each has advantages and drawbacks. The best presentation of these two designs comes from Martin Fowler. You can see his outline here and here.

    The big advantage of single table inheritance is simplicity. It's all stored in one table. The big drawback is a lot of NULLS. This can waste space and time and result in confusing logic.

    类表继承需要连接，但它们既简单又快速。特别是如果您使用一种称为共享主键的技术，其中子类表中的 PK 是超类表中 PK 的副本。您可以为每个连接超类数据和子类数据的子类创建视图。

    最后，这个区域有一个标签，可以将像你这样的问题收集在一起。
    这是：

    • 4

  3. 我认为可能的设计为

    桌子Race

    RaceId- PK- Int
RaceName - Varchar(50)

    桌子Breed

    BreedId - PK- Int
RaceId - FK - Int
BreedName - varchar(50)

    桌子Animal

    AnimalId - PK- Int
BreedId - FK - Int
Other Columns....

    上面的这些 PK 将是自动递增的列。表中的其他列Animal可以相应命名。

    在此处输入图像描述

    • 3

  4. 你现在的方法还不错。但是，如果您稍后要添加更多种族（鸟类、鱼类等），那么为每个种族创建单独的表格可能会很麻烦。我会推荐如下内容：

    Animal < # Animal_ID, Breed_ID, other attributes >
Breed < # Breed_ID, Race_ID >
Race < # Race_ID >

    据我了解，一个品种应该只有一个种族。因此，如果您将品种存储在动物表中，您将能够通过加入品种表来确定种族。显然，根据需要将任何其他属性（名称、描述等）添加到 Breed 和 Race 表。

    • 0

相关问题