Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Thanks for showing the work you've done to date, that's helpful. What do you think you're missing at this point? –  jcolebrand Feb 4 at 18:14
    
im not sure how to progress. I think i am missing 2nd and 3rd normal forms. I have progressed a bit further by removing the one to many link from the Persons to Pets table. From this point i have no idea. –  chris edwards Feb 4 at 18:23
1  
I think that it should be PetName -> PetType,... but otherwise, if the dependencies are only those you mention, then it seems you have reached 3NF. –  ypercube Feb 4 at 19:24

2 Answers 2

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.

share|improve this answer

I think you've got yourself confused because ( like many things in introductory discrete mathematics ) obvious is, well... obvious. You've effectively made the move to 3NF in one fell swoop without really going though the whole process, which is completely understandable since it's Week #1, Assignment #1 for you. The purpose of the exercise is to highlight the thinking behind the structures in order to prepare you for more complicated ( and important ) problems. Understanding an answer is worth a million good guesses ( with the exception of the Lotto, for which I would be perfectly content with one good guess ).

1) Show the unnormalized structures!

Through the normalization forms, you are looking square at a violation of 1NF with the second table. In my broken but generally close enough ZFC,

1NF: ∀ S ∈ ( R ) ↔ ( ∀ TS ↔ | T | = 1 );

In "why the hell would you post that?" terms, it means that for any set ( as an underlying domain ) S in set R, set T is in set S if and only if the carnality of set T is 1. To clear what that means up a little, in your example, we can simply plug it in as:

Pets ∈ Person ↔ ( ∀ T ∈ Pets ↔ | T | = 1 );

Pets is an element ( attribute ) of Person if and only if for any element in Pets, that element has a carnality ( or size, if you will ) of one. In the first record, we can note that our "Steve" human has both "Cat" and "Dog" as pets. We can then say that Cat and Dog are both elements in a set - for our purposes, this set is T:

Cat, Dog ∈ T;

| T | = 2 ≠ 1;

T ∉ Pets;

∴ by contradiction: Pets ∉ Person;

Armed with this information, we can say that the Person table is in violation of 1NF.

In violation of 1NF: ( Person: PersonName, Age, Height, Weight, Pets, Sex );

Since 2NF and above are dependent on functional dependencies in some way or another, that's really all we can do at this point.

2) Identify the primary keys on these unnormalized structures!

While I realize this is absolutely and in every way a question about tables, I will not be referring to the minimal superkey of these relations as a primary key. This is largely because in the realm of relational algebra, there's no such thing as a primary key, but also because in an enterprise level relational database application, you won't be using the minimal superkey as the primary key or you'll be laughed out of the office... I digress. Point is, I'll be calling them candidate keys, which you can consider simply as unique keys if you wish.

Luckily ( ? ) for us, one of the minimal superkeys has been given to us:

[ ... ] pets with the same type have the same details [ ... ]

Which makes the task at hand pretty simple, at least for the first table. The U1 simply denotes that the columns with such a subscript are part of a candidate key, with the possibility of a Un referring to a separate candidate key. The candidate key for the Pet table is, as outlined in the question, PetType. It's a terrible choice, but it's ours.

( Pet: PetTypeU1, PetName, Age, Colour, Sex, Alive );

For the second unnormalized structure, we get to select our own candidate key, which is actually far more painful in this case because we're not blessed with a hand-wavy answer this time. Let's take a look at the table:

( Person: PersonName, Age, Height, Weight, Pets, Sex );

To summarize the discreet jargon, all a candidate key needs to be is unique per row. Based on the data provided in the sample sheet, our possible candidate keys are PersonName, Age, Height and Weight. While Pets qualifies as it is indeed unique per row, since we've pointed out it's not even technically an allowable attribute, we get to dismiss it as a candidate key, along with Sex, which is simply not unique. We also get away with not having to worry about considering ( Age, Height ) type superkeys as candidate keys, as the corresponding ( Age ) and ( Height ) candidate keys are proper subsets, thus eliminating the possibility of the compound versions to be minimal.

That said, while there are intuitive things you can do to further reduce this list in the explicit task of choosing a primary key for a table, as it stands, selecting any one of those first four attributes is technically an equally bad good choice. Since we're likely declined the opportunity to use a surrogate key, which is pretty much The Way Things Are Done™ for a multitude of reasons which enforce just how awesome they are ( thin data types for thin indexes, elimination of compound primary keys thereby simplifying foreign key usage, application of relational algebra to practical RDBMS applications by way of dealing with multiset issues with the theory, virtual automatic promotion to 4NF and often 5NF after 3NF transitive dependencies have been dealt with ), we will arbitrarily pick PersonName and pretend the need to record two different "Steve" people is less likely than having two people of the same age or that Amy grows a little next year and ends up the same height as Lauren.

( Person: PersonNameU1, Age, Height, Weight, Pets, Sex );

3) Specify functional dependencies between attributes!

With our candidate keys out of the way, we can focus on our functional dependencies. On account of the notation we've used so far, we've almost already spelled this out, except for the annoying Pets issue. Since Pets is not actually an attribute, we're left with multiple ( up to three in the data ) PetType attributes.

Pet : { PetType → PetName, Age, Colour, Sex, Alive };

Person : { PersonName → PersonName, Age, Height, Weight, PetType1, ..., PetTypen, Sex };

Frankly, the notation used in Person at this point could probably be contested as not constituting a functional dependency at all. In order to show the functional dependencies in any meaningful form, we must consider that the underlying domain of PetType is functionally dependent on PersonName.

Person : { PersonName → Age, Height, Weight, Sex };

PersonPet : { PersonName → PetType };

Pet : { PetType → PetName, Age, Colour, Sex, Alive };

It is worthwhile noting that this is not the same intermediate structure you arrived at in the original post; that's completely okay. Very possibly, the tables are construed in this way on purpose, for the sake of the assignment, but while your intuition tells you that a candidate key for the intermediate structure is ( PersonName, PetType ), the structure alone in no way enforces that rule. Based on just the data elements, there is no reason why Steve couldn't have two twin Dogs named woofy, outside of it being an absurd reality. This makes PersonName alone a candidate key, eliminating the possibility of ( PersonName, PetType ) from being a minimal superkey on account of the former being a proper subset of the latter.

4) Bring structures up to 3NF showing workings!

Since there are no transitive dependencies, you're good to go for 3NF! While it could be argued that the Person Sex attribute and the Pet Sex attributes are both in the same underlying domain and should thus be brought to a fourth structure ( a notion I do not disagree with, actually ), the actual value of Pet Sex is not determined by the value of Person Sex or vice versa and as such is not in violation of 2NF or 3NF the way it is.

( Person: PersonNameU1, Age, Height, Weight, Sex );

( PersonPet: PersonNameU1, PetType );

( Pet: PetTypeU1, PetName, Age, Colour, Sex, Alive );

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.