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:
![enter image description here][1]
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 >
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. [1]: https://i.sstatic.net/KnBRK.png
