Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What normal form would this be considered? 2NF or 3NF?


What normal form would this be considered? 2NF or 3NF?

Author
Message
Golfer22
Golfer22
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 96
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?
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44768 Visits: 39845
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10631 Visits: 11975
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 and Third Normal Form].

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

Golfer22
Golfer22
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 96
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.
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10631 Visits: 11975
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

sqlvogel
sqlvogel
SSC-Addicted
SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)

Group: General Forum Members
Points: 472 Visits: 3706
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search