0

Cross post warning:

I recently found dba.stack exchange so i thought it would be better to post there:

Can somebody help me normalise this database/check my workings

I have just started a database class in school and i have no idea how to normalise. We were given a worksheet which has 2 tables and we are asked to first show them as un-normalised structures. Then we must show functional dependencies and finally take it to 3NF showing workings.

I think i have managed to show them an un-normalised structures, and show the function dependencies but then i'm not sure how to normalise from there. I know the theory behind it and i know why you should, but i cant see how to simplify.

Can anybody walk me through the example or point out where i've gone wrong? This in not assessed it is just for your revision purposes.

This Database is very simple and assumes every pet of the same type has the same name.

The two tables:

Pets:

enter image description here

Persons

enter image description here

Currently i have:

un-normalised structures as:

Person(PersonName, Age, Height, Weight,Pets, sex) Pet(petType, petName, age, colour, sex, alive)

then:

Person(PersonName, Age, Height, Weight,Pets, sex) Pet(petType, petName, age, colour, sex, alive)

Bold indicates foreign key here.

The functional Dependencies i have identified are:

petType ->{petName, age, colour, sex, alive) personName ->{age, height, weight, pets, sex)

I am not sure if that is correct. I understand that a functional dependency is no two B's can belong to the same A so e.g. personName cannot be "ken" and "dan".

I am thinking this is my next move:

Person(PersonName, Age, Height, Weight,Pets*, sex) Pet(petType, petName, age, colour, sex, alive)

PersonsPet(PersonName*, petType*)

But i have no idea how to get to 3NF, can anybody help.

Cheers, Chris.

migrated from stackoverflow.com Mar 3 '14 at 14:01

This question came from our site for professional and enthusiast programmers.

  • 1
    You're not far off, but adding the PersonPet table means you don't need the Pets column on Person (it's a repeating group and you've extracted it to a table). I would check your assumption that "every pet of the same type has the same name"; it means that there is only one of each animal in the world and even Noah was better off... – simon at rcl Feb 4 '14 at 18:02
  • thanks, it is just a very, very simple example they set to get people like me trying stuff out. SO once i remove that, it is 3NF? – chris edwards Feb 4 '14 at 18:06
  • 1
    Can't remember the actual definitions off the top of my head, but it looks pretty close. You could argue about age: it depends on more than just the key (PersonName or PetType), but on the current date and the birthday. I'd store the birthday instead but I don't know what your teachers are looking for. – simon at rcl Feb 4 '14 at 18:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.