June 2, 2013 at 6:16 pm
Lets say I have a table with four columns named PersonID1, PersonID2, JobTitle, and PreviousJobTitle. There is a composite primary key on PersonID1 and PersonID2. In all instances PreviousJobTitle can be derived on JobTitle. Let's say PreviousJobTitle is dependent on JobTitle but not the primary key. Please assume PreviousJobTitle is 0% dependent on the primary key and 100% dependent on a non-key attribute. JobTitle is 100% dependent on the composite primary key.
What normal form is such a table in?
June 2, 2013 at 8:28 pm
Golfer22 (6/2/2013)
Lets say I have a table with four columns named PersonID1, PersonID2, JobTitle, and PreviousJobTitle. There is a composite primary key on PersonID1 and PersonID2. In all instances PreviousJobTitle can be derived on JobTitle. Let's say PreviousJobTitle is dependent on JobTitle but not the primary key. Please assume PreviousJobTitle is 0% dependent on the primary key and 100% dependent on a non-key attribute. JobTitle is 100% dependent on the composite primary key.What normal form is such a table in?
I'd say that it doesn't meet any of the requirements for any level of normal form.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 2, 2013 at 9:02 pm
Golfer22 (6/2/2013)
Lets say I have a table with four columns named PersonID1, PersonID2, JobTitle, and PreviousJobTitle. There is a composite primary key on PersonID1 and PersonID2. In all instances PreviousJobTitle can be derived on JobTitle. Let's say PreviousJobTitle is dependent on JobTitle but not the primary key. Please assume PreviousJobTitle is 0% dependent on the primary key and 100% dependent on a non-key attribute. JobTitle is 100% dependent on the composite primary key.What normal form is such a table in?
The description you give is a bit self-contradictory, but if it means what I think it means the table is both in first normal form and in second normal form but not third normal form or in any higher normal form. Although PreviousJobTitle is determined by the primary key and hence is 100% (not 0%, as you suggest) dependent on it, it can also be derived from JobTitle without considering the primary key, so there is a functional dependency on something that doesn't include all of a candidate key. As JobTitle isn't a prime attribute (unless there are functional dependencies you haven't told us about) this doesn't mean it's not in second normal form (we don't have a dependency on a subset of a candidate key), but it certainly means it isn't in third normal form.
See Second Normal Form[/I][/url] and Third Normal Form][/url].
This is a nice example of how bizarre an idea 2NF is when approached described in terms of keys and dependencies instead of in terms of what it achieves in preventing errors and unfortunately the description in terms of what errors it prevents varies from case to case, so the apparently bizarre definition is the useful one.
Tom
June 8, 2013 at 4:29 pm
PreviousJobTitle is 0% dependent on the primary key in my example. Was that not clear?
If the table were changed and PreviousJobTitle were dependent on PersonID2 (instead of JobTitle) would it be in 1NF?
If PreviousJobTitle were dependent on PersonID1 and PersonID2 (and not on JobTitle), would the table be in 3NF?
JobTitle could be a candidate key in a different table. I guess the normal form only looks at the scope of one table.
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.
June 8, 2013 at 5:58 pm
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
June 9, 2013 at 2:15 pm
Golfer22 (6/8/2013)
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.
That's correct as far as it goes but it's important to remember that 2NF is violated if a proper subset of any candidate key happens to be a determinant. Being fully dependent on just one key (a primary key) is not necessarily sufficient if other keys are present. 3NF and later normal forms are concerned equally with all candidate keys of relations and not just any one key per relation.
It is sometimes assumed that a relation automatically satisfies 2NF if it has no composite keys. That isn't invariably so. Violations of 2NF in relations with only simple keys (single attribute keys) occur when the empty set of attributes is a determinant. That's a fairly unusual case and the apparent rarity of such situations perhaps explains why dependencies on the empty set don't often get mentioned in textbooks and get ignored in informal explanations of normal forms.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply