不知道如何将复杂属性转换为关系表 [英] Don't know how to transform complex attribute into relational table

问题描述

介绍及相关信息：



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



Animal 有种族，可以是 cat 或狗的。 Cat 可以是 Siamese 或 Persian 。 Dog 可以是德国牧羊犬或 Labrador retriver 。



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



问题：



我不知道如何创建关系这个例子的表格。



我努力解决问题：



我试图用陈的符号绘制ER图，代表问题，但作为一个初学者，我不知道我做得对。 此处 [ ^ 是我迄今为止所得到的。



如果我弄错了，请向我道歉，如果是这样，请纠正我。我不想简单地获得免费解决方案，而且还想学习如何处理这个问题，以便我将来可以自己解决。



我想到的唯一一件事是创建两个单独的表，一个用于猫，一个用于狗。此外，Animal表中的race属性仅存储cat或dog值。这样的事情：

INTRODUCTION AND RELEVANT INFORMATION:

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 ).

PROBLEM:

I don't know how to create relational tables for this example.

MY EFFORTS TO SOLVE THE PROBLEM:

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.

QUESTIONS:

- 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.

推荐答案

Animal_ID，品种>
狗< ＃Dog_ID，

Animal_ID, breed > Dog < # Dog_ID,

Animal_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.

QUESTIONS:

- 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.

我们在这里知道的是什么作为变异实体。纯粹设计要求你有3张桌子：动物，猫和狗。不属于猫或狗的属性将在动物表中，而特定于猫表中的猫和狗/狗的属性。所有3个表的PK都是相同的，关系基数为零或一（可选一对一）。此模型允许扩展，允许您向模型添加更多动物类型。变体表中的字段充当动物的PK和外键。



应添加约束以防止任何具有相关记录的记录的动物记录更多一个表。



一个可能的替代方案是只有一个表允许变量属性使用空值。纯粹主义者（像我一样）会对此感到不满，并使可扩展性变脏（你需要多少个可空的字段？）并违反第三范式。



很多取决于你为每个变种（物种）存储的属性。



当然，实现也可能有查找表（当然是物种）。 />


这是SQL Server的脚本：

What we have here is known as a "Variant Entity". Purist design would require you to have 3 tables: Animals, Cats, and Dogs. Attributes that are not specific to cats or dogs would be in the Animals table, while attributes specific to cats in the Cats table and dogs/Dogs. The PK for all 3 tables would be the same with a relationship cardinality of "zero or one" (optional one-to-one). This model allows extensibility, allowing you to add more animal types to the model. The field in the variant tables acts as both PK and foreign key to Animals.

A constraint should be added to prevent any animal record having a record with related records in more than one table.

One possible alternative would be to have just one table with nulls allowed for the variant attributes. This would be frowned on by purists (like me!) and make extensibility dirty (how many nullable fields would you need?) and violates 3rd normal form.

A lot depends on what attributes you are storing for each variant (species).

Of course, implementation is likely to have lookup tables too (certainly "Species").

Here is script for SQL Server:

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).

这篇关于不知道如何将复杂属性转换为关系表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！