I am new to database design/normalisation and functional dependencies. I have started a new database class in school this week and we have been given a worksheet that requires the following:
This is a real simple design, and pets with the same type have the same details just to keep things nice and simple.
- show the un-normalised structures
- identify the primary keys on these un-normalised structures
- specify function dependencies between attributes
- bring structures up to 3NF showing workings
These are the tables:
My un-normalised structures are as follows:
person(PersonName, Age, Height, Weight, Pets, Sex)
Pet(petType, petName, Age, Colour, Sex, Alive)
Bold shows the primary keys.
Functional dependencies:
personName -> {Age, Height, Weight, Pets, Sex)
petType -> {petName, Age, Colour, Sex, Alive}
I cant think of any more (if any).
Now bring to 3NF.
I can get it to 1NF (i think)
So now I have:
Person(PersonName, Age, Height, Weight,Sex)
Pet(PetType, PetName, Age, Colour, Sex, Alive)
PersonsPet(PersonName*, petType*)
Then I have no idea where to go from here? That is assuming I have been correct until this point, which I most probably have not.
Can somebody help me and point me in the right direction?
Best Answer
You need a one-to-many table to list the types of pets that a person has, like
Assuming name is the primary key of People (which it shouldn't be, else you can't have two people with the same name).
This allows a person to have many pets types.
Also, "Age" and "Alive" should probably be derived from date_of_birth and date_of_death (nullable). Otherwise, you need to update the table to re-calculate the age at least every year. You can use a VIEW to calculate the age, and alive values for you.