Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:
SQL
Animal< # Animal_ID, race, other attributes >
Cat < # Cat_ID, $ Animal_ID, breed >
Dog < # Dog_ID, $ Animal_ID, breed >

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

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


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).
 
Share this answer
 
v2
Comments
AlwaysLearningNewStuff 23-Oct-14 16:23pm    
Thank you for sparing some time to help me, I highly appreciate it.

I have some trouble fully understanding your solution because I am inexperienced. The example I have mentioned in my post is quite simple in my opinion. Can you edit your post with tables that illustrate your solution on that example? It would help me a lot to draw correct conclusions and would greatly reduce the chance for me to misunderstand your post.

Meanwhile, I will use Google to learn more about "variable entities".

Best regards.
"create two separate tables, one for cats and one for dogs"

I wouldn't do it that way; it is unlikely to scale well. You will likely want a self-referential table that defines types/races.

Race
ID ParentID Name
1           Dog
2  1        German shepherd
3  1        Labrador retriver
4           Cat
5  4        Siamese
6  4        Persian


This allows unlimited nesting and sub-classing.

Then the table of individuals would refer to that table (foreign key)

Individual
ID Race Name
1  2    Spot
2  3    Rover
3  5    Mittens
4  6    Killer


Having said that, I'll also mention that although the use of integers for the IDs in the Race table is a good idea, I prefer not to use integer IDs in the Individual table.

Furthermore, you can (if it makes sense in your application) define the Race IDs in a more structured manner:

Race
ID  Name
100 Dog
101 German shepherd
102 Labrador retriver
200 Cat
201 Siamese
202 Persian


This is a much more restrictive design, and each ID carries more information, which can aid debugging.
The real beauty of this technique is that the application code can more easily use a tree-like enumeration: EnumTree[^]
 
Share this answer
 
Comments
AlwaysLearningNewStuff 23-Oct-14 16:28pm    
Thank you for sparing some time to help me, I highly appreciate it.

I have trouble understanding your solution because I am inexperienced. Although you did apply your solution on my above example, I still can't fully grasp what you did.

Can you provide some tutorials or other online resources in your reply to this comment that could help me realize what you did?

Maybe you could elaborate your post with additional info that could help me understand your solution?

If you need specific info on what I am not understanding leave me a comment and I will provide it.

Best regards.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900