Normalisation

[HenryMarv]

Hi! Can anyone give some ideas on how to normalise the provided table please?

As for the normalisation rules I have followed, the table has no repeating groups, therefore it is in 1st normal form.
The table has only one primary key, therefore there is no partial dependencies so the table is in 2NF.
As for the third normal form, I am not sure how to do it.
I know that the Age of the pet and the Price need to contain atomic values.
Any help would be much appreciated! Thank you!

[r937]

@HenryMarv

I know that the Age of the pet and the Price need to contain atomic values.

seems they do, and so they satisfy 1NF

but there is a transitivie dependency between pet_breed and pet_type

to see why, imagine this update –

UPDATE pets
   SET pet_breed = 'Bulldog'
 WHERE pet_id = 'B013'

does it make sense that B013 is now a Bird of the Bulldog breed? no

so that dependency has to be removed

[droopsnoot]

For me, this would need to have a further two tables, the breed and the “type”. The table you show would just contain the breed id, and the breed id would identify the breed name as well as containing a reference to the type id. The “type” table (perhaps “specie”, I can’t remember) would contain the type id and the type name. The table you show would be:

Pet_ID, Pet_breed_id, Pet_age, Quantity, Unit Price

The breed table would be:

Pet_Breed_id, breed_name, pet_type_id

The type table would be

pet_type_id, pet_type_name

You would have a small amount of duplication if there is a breed name which is the same for, say, a cat as a it is for a dog (I’m struggling to think of an example, but I’m sure there is one breed name that applies to more than one type of animal - if you don’t cater for it, your first two users will find it).

I’m not sure storing age is a good idea, date of birth would be better if you don’t want to have to keep updating it. But I don’t know what your table is for, so the age might be more useful.

[r937]

@droopsnoot

if you don’t cater for it, your first two users will find it

[rpkamp]

I’m not sure I agree here. Suppose there is a dog with type X and a cat with type X and then suppose for some reason the dog type is renamed to Y. With type in a separate table you can’t do that without also renaming the cat type to Y.

So theoretically it might be what you should do, but it isn’t what I would do.

[Thallius]

@rpkamp

I’m not sure I agree here. Suppose there is a dog with type X and a cat with type X and then suppose for some reason the dog type is renamed to Y. With type in a separate table you can’t do that without also renaming the cat type to Y.

I cant follow you. With separate table for type, you have a table with columns id and type. A dog will point to its type by having a column type_id which points to the id of the type column. If you change the type of the dog, you will only change the id in the dog table two the new id in the type table. No other pet is changed then…

[rpkamp]

So you wouldn’t reuse the type between breeds? If not, why have two separate tables at all?

[Thallius]

I would do it completly other way around. I would only have a column breed_id in the pet table. Then I would make a breed table with a column type_ip which is pointing to type table. This way I cannot change the type directly but must choose a breed instead, what in my eyes makes much more sense

[rpkamp]

Agreed. That does make more sense

[r937]

@Thallius

I would only have a column breed_id in the pet table. Then I would make a breed table with a column type_ip which is pointing to type table.

so, basically what @droopsnoot described

[r937]

@rpkamp

So you wouldn’t reuse the type between breeds? If not, why have two separate tables at all?

because in the off chance that you really could have the same breed name between dogs and cats, the only way to make it work is with two tables –

pet_types
id  name
12  dog
22  cat

pet_breeds
id  name      type
34  siamese   22
36  bulldog   12  
45  fizzbuzz  22
47  fizzbuzz  12

so the pet table would use breed_id 45 if it’s a fizzbuzz cat, and 47 if it’s a fizzbuzz dog

[droopsnoot]

@rpkamp

Suppose there is a dog with type X and a cat with type X and then suppose for some reason the dog type is renamed to Y. With type in a separate table you can’t do that without also renaming the cat type to Y.

In my post, my intention was that the “dog” or “cat” is the “type” table (which I think is the specie), the X and Y are the breeds. Breeds are the variants of species.