1

In trying to learn about database normalization, I am confused by the following table:

Name      Date          Organisms
aging     1984-07-13    human
aging     1984-07-13    worm
wrinkle   1987-08-15    human
wrinkle   1987-08-15    mouse
hairy     1990-09-30    mouse

The book I am using presents this as an example of a table in 1NF. However, after reading the Wikipedia articles on 1NF, 2NF, and 3NF, I'm a bit confused.

As far as I can tell, this table has two candidate keys: {Name,Organisms} and {Date,Organisms}. According to Wikipedia (link text):

A 1NF table is in 2NF if and only if all its non-prime attributes are functionally dependent on the whole of every candidate key. (A non-prime attribute is one that does not belong to any candidate key.)

By this definition for "non-prime attribute", it seems to me that this table has no non-prime attributes because every field belongs to at least one of the two candidate keys. If there are no non-prime attributes, then (since this table is in 1NF) it seems this table is in 2NF by the Wikipedia definition.

However, I recognize that this table is poorly designed and prone to update errors, so I think I must be making an error in logic somewhere. If someone could explain which normal form this table is in and/or where my reasoning is faulty, I would greatly appreciate it.

ADR
  • 73
  • 3
  • dont think {Date, Organisms} is a candidate key, cos that would mean that you could not have different names for the same date, organism. but {name, organism} looks like it could... question is if date depends on name or organism – Diogo Nov 22 '10 at 10:55
  • 1
    There is no contradiction here. Being in 2NF implies that it is also in 1NF. In fact any relation is in 1NF by definition. So it seems that the book is correct. – nvogel Nov 22 '10 at 12:55
  • 1
    Wiki is a cesspool, with ever-changing "definitions" written by amateurs. Do not waste your time, it will confuse you and hinder your progress. – PerformanceDBA Nov 28 '10 at 08:02
  • That definition is wrong. By "functionally dependent on the whole of every candidate key" (which is alway true, by definition of CK) it is trying to say "not functionally dependent on any proper/smaller subset of a candidate key". – philipxy Mar 01 '19 at 03:30
  • A base table is in a NF when all the valid values it will hold are in that NF. So an example's highest NF only tells you an upper limit on the hightest NF of a base that can hold it. Also ["1NF" has no single meaning](https://stackoverflow.com/a/40640962/3404097). PS Without giving FDs & JDs one can't tell whether a value/base suffers updating anomalites or what its highest NF it is in. We can know some NFs & above that it isn't in. – philipxy Mar 14 '19 at 07:27

1 Answers1

1

If you are correct that there are no nonprime attributes then the relation in question would be in at least 2NF. However, guessing at what keys and dependencies may apply based only on a set of attribute names is always going to be a highly subjective exercise. Examples like this are really only useful if you define what dependencies are supposed to be satisfied.

I recommend you don't rely on anything you read in Wikipedia. It's not a good educational tool. Get a decent book on the subject, such as those by Chris Date or David Maier.

nvogel
  • 24,981
  • 1
  • 44
  • 82