Don't know how to transform variable entity into relational table

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:

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
Cat 
Dog 

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 since I am fairly new here.

Thank you.

The proper structure for this scenario is a SubClass / Inheritance model, and is nearly identical to the concept I proposed in this answer: Heterogeneous ordered list of value.

The model proposed in this question is actually quite close in that the Animal entity contains the type (i.e. race) and the properties that are common across all types. However, there are two minor changes that are needed:

-
Remove the Cat_ID and Dog_ID fields from their respective entities:

The key concept here is that everything is an Animal, regardless of race: Cat, Dog, Elephant, and so on. Given that starting point, any particular race of Animal doesn't truly need a separate identifier since:

• the Animal_ID is unique

• the Cat, Dog, and any additional race entities added in the future do not, by themselves, fully represent any particular Animal; they only have meaning when used in combination with the information contained in the parent entity, Animal.

Hence, the Animal_ID property in the Cat, Dog, etc entities is both the PK and the FK back to the Animal entity.

-
Differentiate between types of breed:

Just because two properties share the same name does not necessarily mean that those properties are the same, even if the name being the same implies such a relationship. In this case, what you really have is actually CatBreed and DogBreed as seperate "types"

Initial Notes

• The SQL is specific to Microsoft SQL Server (i.e. is T-SQL). Meaning, be careful about datatypes as they are not the same across all RDBMS's. For example, I am using VARCHAR but if you need to store anything outside of the standard ASCII set, you should really use NVARCHAR.

• The ID fields of the "type" tables (Race, CatBreed, and DogBreed) are not auto-incrementing (i.e. IDENTITY in terms of T-SQL) because they are application constants (i.e. they are part of the application) that are static lookup values in the database and are represented as enums in C# (or other languages). If values are added, they are added in controlled situations. I reserve the use of auto-increment fields for user data that comes in via the application.

• The naming convention I use is to name each subclass table starting with the main class name followed by the subclass name. This helps organize the tables as well as indicates clearly (without looking at the FKs) the relationship of the subclass table to the main entity table.

• Please see "Final Edit" section at the end for a note regarding Views.

"Breed" as "Race"-Specific Approach

This first set of tables are the lookup / types tables:

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


This second listing is the main "Animal" entity:

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


This third set of tables are the complimentary sub-class entities that complete the definition of each Race of 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);


The model using a shared breed type is shown after the "Additional Notes" section.

Additional Notes

• The concept of breed seems to be a focal point for confusion. It was suggested by jcolebrand (in a comment on the question) that breed is a property shared across the different races, and the other two answers have it integrated as such in their models. This is a mistake, however, because the values for breed are not shared across the different values of race. Yes, I am aware that the tw

StackExchange Database Administrators Q#80951, answer score: 12