Golfer22 (6/8/2013)
PreviousJobTitle is 0% dependent on the primary key in my example. Was that not clear?
No, it was clear that you were misusing the term "dependent" to exclude "transitively dependent", and that is not the usage of "dependent" in normalisation. You explicitly said that PreviousJobTitle was dependent on JobTitle and that JobTitle is dependent on the primary key, so in the ordinary terminology of normalisation PreviousJobTitle is dependent on the primary key; because that is a transitive dependency and JobTitle (the thing in the middle) is not a prime attribute the table is not in 3NF. But everything is dependent (directly or indirectly) on the primary key so the table is in 1NF and nothing is dependent on just a (proper) subset of any candidate key so the table is in 2NF.
If the table were changed and PreviousJobTitle were dependent on PersonID2 (instead of JobTitle) would it be in 1NF?
Yes, because once you have the primary key you can derive everything from the dependency relationships. But it now would not be in 2NF, because something would be dependent on a proper subset of the primary key (and hence not in any normal form higher than 2NF).
If PreviousJobTitle were dependent on PersonID1 and PersonID2 (and not on JobTitle), would the table be in 3NF?
Yes, because everything (except the components of the primary key) is now dependent on the whole primary key and nothing is dependent on anything other than the primary key. is now directly dependent
JobTitle could be a candidate key in a different table. I guess the normal form only looks at the scope of one table.
Yes, that's right. But having each table in normal form isn't actually the whole of normal form: having the domain constraints (check constraints, not null constraints), primary key constraints, uniqueness constraints, and foreign key constraints explicitly stated in the schema definition is part of normalisation too.
Please confirm the following:
If a non-key attribute is dependent on one of 1,000 columns (when the primary key is composed of 1,000 columns), the table is in 1NF or lower. But if the non-key attributes are all dependent on either the primary key (all 1,000 columns) except that one non-key attribute is dependent another non-key attribute, the table is in 2NF. If the non-key attribute that is dependent on a different non-key attribute is removed from the table, then the table is in 3NF.
Yes, that's correct (given that in the 2NF case your "another key attribute" is dependent on all 1000 primary key columns).
Tom