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

Question

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.

Answer 1

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.)

Answer 2

The key to determining whether your table is in 3NF is the transitive dependencies. To figure out if you have any of these, the best question to ask is:

If I change the value in column x, does that imply I also have to change the value in column y?

for all non-prime columns. If the answer is yes, you're not in 3NF.

In this case, you have to answer yes for the name and bought_from columns. If line three was dog food instead of gas, that also implies you have to change bought_from to someone other than British Gas.

To get this into 3NF, you need a separate products table, which lists the name of the product and the vendor. You would then have a foreign key from incurred_costs to the primary key of this new table.

Answer 3

First, it looks to me like you are building an accounting database. Let me just advise you to reconsider or look at existing open source dbs out there (I maintain LedgerSMB for example).

Secondly regarding normalization, you need extra tables. You should break off what you bought and who you bought it from into other tables, and then have a table which records, more or less, the information you have here but with keys to the other tables, as a minimalist solution. So something like (sorry for the PostgreSQL-isms here):

CREATE TABLE vendor (
   id serial not null unique, 
   company_name text primary key,
   vat_number text unique,
   ...
);
CREATE TABLE things_to_purchase (
   id serial not null unique,
   label text primary key,
   ....
);
CREATE TABLE invoice (
   vendor_id int not null references vendor(id),
   what_purchased int not null references things_to_purchase(id),
   when_purchased date not null,
   expense numeric not null
);