Trying to determine a specific database normalization issue

Question

A coworker sketched out the values of a new table as:

"Foo", "some value 1"
"Foo", "some value 2"
"Foo", "some value 3"
"Bar", "some value 3"

These are the only columns in the table. The column names are Col1, Col2.

One person said that this table is not normalized, another said it is.

The specific argument that it violated normalization is that removing the three records with "Foo" in Col1 "Foo" would no longer be present in the system. That person said there should be a lookup table containing an ID, and Name column. The table above would reference the Id of that table as its FK.

The argument that it wasn't normalized is that there wasn't a third column in the table dependent on the first (3rd normalized form).

The confusion I think comes from it being 1NF in that it satisfies this example:

Customer    Tr. ID  Date          Amount
Jones   12890  14-Oct-2003  -87
Jones   12904  15-Oct-2003  -50
Wilkins     12898  14-Oct-2003  -21
Stevens     12907  15-Oct-2003  -18
Stevens     14920  20-Nov-2003  -70
Stevens     15003  27-Nov-2003  -60

from http://en.wikipedia.org/wiki/Database_normalization.

But it sounds like it violates this rule, "The same information can be expressed on multiple rows; therefore updates to the table may result in logical inconsistencies." This applies to normalization beyond 1NF.

So it looks like the original table would violate 2NF, and thereby 3NF, but would satisfy 1NF. Is this correct?

Answer 1

http://en.wikipedia.org/wiki/Database_normalization

Answer 2

There are different normalization levels. But without the actual field names, you can't really know if you need to normalize.

Answer 3

There are a few different levels of normalization.

If "Foo", "some value 1" "Foo", "some value 2" "Foo", "some value 3" "Bar", "some value 3" means that the table would look like:

Col1| Col2
------------------
Foo | some value 1
Foo | some value 2
Foo | some value 3
Bar | some value 3

And there is a primary key on Col1/Col2 then yes, it is 'Normalized'.
If there is no key at all, then no, it is not normalized, as you could insert another instance of "Bar", "some value 3".

As to the new question you added:
If there is a PK spanning Col1 & Col2, then it is still in 2NF and 3NF. You'd have to add a column that is not a part of the key to violate either, and then it would have to be derivable from only Col1 or only Col2.

Answer 4

I believe the list of values in the table represent four rows:

col1 col2
Foo  some value 1
Foo  some value 2
Foo  some value 3 
Bar  some value 3

Based on my understanding, this table would be considered normalized. I would expect the primary key here to be a composite key of {col1, col2}.

I would normally expect to see this type of many-to-many mapping of values in a table when col1 and col2 are each foreign keys into other tables that contain additional attributes of the entities being mapped.

I would also recommend considering numeric keys rather than these nvarchar values. I suspect that these textual values may not be good candidate keys for the entities they represent, but I don't have enough information to fully make that judgment.

Answer 5

If those two columns are really all there are, then I would say this database table is in third normal form. Here's my reasoning:

1. It's CLEARLY in 1NF since none of the attributes are "multi-valued"
2. Since neither col1 nor col2 are a valid key candidate (duplicate values!), the only possible and valid primary key on this table is (col1,col2)
3. 2NF stipulates that no non-prime attribute shall be functionally dependent on a part of a candidate key. Since there are only col1 and col2 which are both part of the only possible candidate key, this point is moot - the table IS in 2NF
4. 3NF according to E.F.Codd basically says that any non-key attribute must be dependent "on the key, the whole key, and nothing but the key". Since we ONLY have two columns which make up the key, there are no other non-key attributes, so none of the non-key attributes violates this rule --> the table IS is 3NF

I don't know if your work buddy wants to really get into 4NF, 5NF or Boyce-Codd NF - I highly doubt it......

Marc