Can somebody help me normalise this database/check the workings

database-designnormalization

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.

  1. show the un-normalised structures
  2. identify the primary keys on these un-normalised structures
  3. specify function dependencies between attributes
  4. bring structures up to 3NF showing workings

These are the tables:

Pets table

Person table

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

create table people_pet_types (
  person_name varchar(...) references people(name),
  pet_type varchar(...),

  primary key (person_name, pet_type)
);

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.

Related Question