问题描述
以下示例说明了我面临的问题：
Animal 有种族，可以是 cat 或狗的。 Cat 可以是 Siamese 或 Persian 。 Dog 可以是德国牧羊犬或 Labrador retriver 。
动物是一个强大的实体，而它的种族是一个属性，可以拥有两个提供的价值之一（猫或狗）。这两个值都很复杂（我在这里只添加了狗/猫的类型来说明问题，但也可能有猫/狗的名字和一堆其他东西）。
我不知道如何创建关系这个例子的表格。
我试图用陈的符号绘制ER图，代表问题，但作为一个初学者，我不知道我做得对。 [是我迄今为止所得到的。
如果我弄错了，请向我道歉，如果是这样，请纠正我。我不想简单地获得免费解决方案，而且还想学习如何处理这个问题，以便我将来可以自己解决。
我想到的唯一一件事是创建两个单独的表，一个用于猫，一个用于狗。此外，Animal表中的race属性仅存储cat或dog值。这样的事情：
The following example illustrates the problem I face:
Animal has a race, which can be a cat or a dog. Cat can be either Siamese or Persian. Dog can be a German shepherd or Labrador retriver.
Animal is a strong entity, while its race is an attribute that can have one of the two offered values ( cat or a dog ). Both these values are complex ( I have added here only the type of dog/cat to illustrate the problem, but there can also be the cat's / dog's name and bunch of other stuff ).
I don't know how to create relational tables for this example.
I have tried to draw ER diagram, using Chen's notation, that represents the problem but being a beginner I don't know if I did it right. Here[^] is what I have got so far.
I apologize if I drew something wrong, please correct me if that is the case. I don't wish to simply get "free solution" but also to learn how to deal with this problem so I can solve it on my own in the future.
The only thing that comes to my mind is to create two separate tables, one for cats and one for dogs. Also, the race attribute in the Animal table would only store cat or a dog value. Something like this:
Animal< # Animal_ID, race, other attributes >
Cat < # Cat_ID, $ Animal_ID, breed >
Dog < # Dog_ID, $ Animal_ID, breed >
我对我的解决方案感觉不好，我担心这是错误的，因此下面的问题。
- 如何转换我的例子进入ER图？
- 如何将ER图转换为关系表？
如果需要进一步的信息，请发表评论我会尽快更新我的帖子。如果需要，也可以随意添加适当的标签。所有我能找到的都是SQL标签。
谢谢。
I really have a bad feeling about my solution and I fear it is wrong, hence the below question.
- How can I transform my example into ER diagram ?
- How to transform that ER diagram into relational tables?
If further info is required leave a comment and I will update my post as soon as possible. Also feel free to add appropriate tags if required. All I was able to find was SQL tag.
Thank you.
推荐答案
我对我的解决方案感觉不好，我担心这是错误的，因此下面的问题。
- 如何将我的示例转换为ER图？
- 如何将ER图转换为关系表？
如果需要进一步的信息，请留下评论，我会尽快更新我的帖子。如果需要，也可以随意添加适当的标签。所有我能找到的都是SQL标签。
谢谢。
I really have a bad feeling about my solution and I fear it is wrong, hence the below question.
- How can I transform my example into ER diagram ?
- How to transform that ER diagram into relational tables?
If further info is required leave a comment and I will update my post as soon as possible. Also feel free to add appropriate tags if required. All I was able to find was SQL tag.
Thank you.
USE [DB_Animals]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Animals](
[AnimalID] [int] IDENTITY(1,1) NOT NULL,
[AnimalName] [varchar](32) NOT NULL,
[SpeciesID] [int] NOT NULL,
CONSTRAINT [PK_Animals] PRIMARY KEY CLUSTERED
(
[AnimalID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Dogs](
[AnimalID] [int] NOT NULL,
[DogBreedID] [int] NOT NULL,
[Barks] [bit] NOT NULL,
CONSTRAINT [PK_Dogs] PRIMARY KEY CLUSTERED
(
[AnimalID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cats](
[AnimalID] [int] NOT NULL,
[CatBreedID] [int] NOT NULL,
[Purrs] [bit] NOT NULL,
CONSTRAINT [PK_Cats] PRIMARY KEY CLUSTERED
(
[Purrs] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trg_SpeciesConstraint]
ON [dbo].[Cats]
AFTER INSERT
AS
BEGIN
DECLARE @OtherSpecies int
SELECT @OtherSpecies = COUNT(*) FROM Dogs INNER JOIN inserted ON inserted.AnimalID = Dogs.AnimalID
If @OtherSpecies > 0 Raiserror ('This animal is already another species and cannot be a cat',16,1)
END
GO
ALTER TABLE [dbo].[Cats] ADD CONSTRAINT [DF_Cats_Purrs] DEFAULT ((1)) FOR [Purrs]
GO
ALTER TABLE [dbo].[Dogs] ADD CONSTRAINT [DF_Dogs_Barks] DEFAULT ((1)) FOR [Barks]
GO
ALTER TABLE [dbo].[Cats] WITH CHECK ADD CONSTRAINT [FK_Cats_Animals] FOREIGN KEY([AnimalID])
REFERENCES [dbo].[Animals] ([AnimalID])
GO
ALTER TABLE [dbo].[Cats] CHECK CONSTRAINT [FK_Cats_Animals]
GO
ALTER TABLE [dbo].[Dogs] WITH CHECK ADD CONSTRAINT [FK_Dogs_Animals] FOREIGN KEY([AnimalID])
REFERENCES [dbo].[Animals] ([AnimalID])
GO
ALTER TABLE [dbo].[Dogs] CHECK CONSTRAINT [FK_Dogs_Animals]
GO
注意：
假设有些猫没有发出咕噜声，有些狗不会吠叫给予变种属性（也是品种）。
不包括查找表。
我在Cats上使用触发器强制执行约束（您可以自己编写Dogs触发器并在有更多物种时进行修改）。 />
您不需要变量表中的ID（它们没有用处）。
Notes:
Assumes some cats do not purr and some dogs do not bark to give variant attributes (also breed).
Lookup tables not included.
I used a trigger on Cats to enforce a constraint (you could code a Dogs trigger yourself and modify if there are more species).
You do not need IDs in the variant tables (they serve no purpose).
这篇关于不知道如何将复杂属性转换为关系表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！