Simple scenario needs to go from Unnormalized Data to 3NF – Review Please

database-designnormalization

NOTE: The Question has been updated. View Edit Revisions to view original question.

The scenario. An animal charity that at the moment, track their establishment's costs via a spreadsheet like so:

[broken image link removed]

1NF:

The above table is already in 1NF because it abides to the definition of 1NF.

First Normal Form: A relation in which the intersection of each row and column contains one and only one value.

After reading Catcall's answer I can see that it is in determining my dependencies that I messed up. All I did originally was exactly like Catcall said ! added a primary key column, and said to myself "there, that fixes that!"

So obviously that was wrong. I have now read more carefully into functional dependencies and in this book I have, it defines them as:

Functional Dependency: Describes the relationship between attributes in a relation. For example if A and B are attributes of relation R, and B is functionally dependent on A (denoted by A –> B), if each value of A is associated with exactly one value of B. There exists a functional dependency.

Keeping the above definition in mind, and examining my now expanded (more data) sample table, I can see that PetHouse Ltd is not always going to be associated with Dog food, and this breaks the part of definition about A being associated with EXACTLY one value of B.

So I guess in reality I am stuck here in identifying my functional dependencies. Here I am a little bit in the dark as I've never worked with a composite key that was made up of three different columns, but I'll go ahead anyway and see where I get to.

Catcall suggested using 3 columns [Name, BoughtFrom, TimeBought] as identifiers of data (i.e. a composite key for the table that is made up of 3 columns) and that makes sense as that would uniquely identify each row of data in the table. So the notation for the relation becomes:

IncuredCosts (Name, BoughtFrom, TimeBought, Cost)

The book in my possession also has this interesting part about the functional dependencies we need for normalization. It states that the functional dependencies we are after must have the following characteristics:

There is a one-to-one relationship between the attribute(s) on the left-hand side (determinant) and those on the right-hand side of a functional dependency. (Note that the relationship in the opposite direction can be a one-to-one or one-to-many relationship.)
They hold for all time.
The determinant has the minimal number of attributes necessary to maintain the dependency with the attribute(s) on the right-hand side. In other words, there must be a full functional dependency between the attribute(s) on the left-hand and right-hand sides of the dependency.

I believe all the above characteristics hold true for this functional dependency:

(Name, BoughtFrom, TimeBought) –> Cost

Now then, Transitive Dependencies, which in the book (could be wrong like Catcall said) is defined as:

Transitive Dependency: A condition where A, B and C are attributes of a relation such that if A –> B and B –> C, then C is said to be transitively dependent on A via B (provided that A is not functionally dependent on B or C).

Am I right in thinking that my relationship as defined earlier does not have any transitive dependencies? But then again, if that the case, it would imply that I have reached 3NF! cause apparently the major step to 3NF is the removal of Transitive Dependencies into their own relation.

Have I really reached 3NF?

Thank you by the way. I learnt a lot through this post.

Best Answer

Yes, it's in 1NF.

You can't side-step the often hard work of determining all the candidate keys by hanging a number off the end of the table and saying, "There. I've got a primary key." One natural candidate key for this table is {Name, Bought from, Date bought}. Consider using "Time bought" instead of "Date bought".

Your definition of 2NF is wrong. Instead of

Second Normal Form: A relation that is in First Normal Form and every non-primary-key attribute is fully functionally dependent on the primary key.

you need something more like this.

Second Normal Form: a relation that is in First Normal Form, and every non-prime attribute is fully functionally dependent on every candidate key.

The term non-prime attribute doesn't mean quite what non-primary-key attribute means.

Your definition of 3NF is wrong, and it's wrong for the same reasons as your definition for 2NF was wrong.

Instead of this

Third Normal Form: A relation that is in First and Second Normal Form and in which no non-primary-key attribute is transitively dependent on the primary key.

you need something closer to this.

Third Normal Form: A relation that is in Second Normal Form and in which every non-prime attribute is nontransitively dependent on every candidate key. (There isn't a really good way to express all those negative in one sentence.)

Related Solutions

Why is this relation in 3NF

The mistake is in your understanding of transitive dependency. From wikipedia: Transitive dependency

In mathematics, a transitive dependency is a functional dependency which holds by virtue of transitivity. A transitive dependency can occur only in a relation that has three or more attributes. Let A, B, and C designate three distinct attributes (or distinct collections of attributes) in the relation. Suppose all three of the following conditions hold:

1. A → B
2. It is not the case that B → A
3. B → C

Then the functional dependency A → C (which follows from 1 and 3 by the axiom of transitivity) is a transitive dependency.

In your case though, (2) does not hold:

1. U → T                                  -- correct
2. It is not the case that T → U          -- wrong
3. T → V                                  -- correct

Therefore {V} is NOT transitively dependent on {U}.

Database Normalization – Does 3NF Require 2NF?

The normal forms are specified in such a way that for any normal form, the model is also meets the criteria for the lower numbered normal forms. So, yes if your model is in 3rd normal form, it is also in 2nd normal form and 1st normal form.

When normalizing a model, you normally work at resolving one normal form before the next. This makes normalization simpler and less error prone as you are only applying a few rules at at time. It is possible that an entity at a lower normal form is also at a higher normal form. Most 3rd normal form entities are also in 4th and 5th normal form.

4th, 5th, and 6th normal forms go beyond the functional dependence. Where applicable, they further simplify the data, at the expense of more tables. They reduce redundancy, and increase data integrity.

Best Answer

Related Solutions

Why is this relation in 3NF

Database Normalization – Does 3NF Require 2NF?

Related Question