SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Second normal form or third normal form?


Second normal form or third normal form?

Author
Message
Golfer22
Golfer22
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 96
About.com says this:

"In order to be in Second Normal Form, a relation must first fulfill the requirements to be in First Normal Form. Additionally, each nonkey attribute in the relation must be functionally dependent upon the primary key. "

I don't think that is correct. I think you could have a nonkey attribute that is transitively dependent on the primary key and that table would be in second normal form. A nonkey attribute could be dependent on another nonkey attribute. This wouldn't mean the table is in first normal form. It would be in second normal form because no nonkey attribute is functionally dependent on a subset of the primary key. Am I right?

If I am wrong, how is third normal form different from the above description from About.com?
sqlvogel
sqlvogel
Right there with Babe
Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)

Group: General Forum Members
Points: 782 Visits: 3706
Golfer22 (8/10/2013)
About.com says this:

"In order to be in Second Normal Form, a relation must first fulfill the requirements to be in First Normal Form. Additionally, each nonkey attribute in the relation must be functionally dependent upon the primary key. "

About.com is not correct (no surprise there) but not quite for the reason you mentioned. Their definition is accurate to the extent that it does not exclude the possibility of a transitive dependency involving non-key attributes. That's because if A->B->C then it is necessarily the case that A->C. So C is dependent on B (could potentially be a violation of 3NF) but is also dependent on A and therefore satisfies the definition you quoted from About.com.

Their definition is not sufficient in another respect however. Here is Codd's actual definition of 2NF:

A relation R is in second normal form if it is in first normal form and every non-prime attribute of R is fully dependent on each candidate key of R.

E.F.Codd, Further Normalization of the Data Base Relational Model

Full dependence on all candidate keys is the defining feature of 2NF. In common with other normal forms, 2NF is equally concerned with all the candidate keys of a relation and not just any one "primary" key.

Don't waste your time reading About.com. Pick up a decent book by a reputable author.
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14208 Visits: 12197
You can't exclude something from 3NF just because some attribute is transitively dependent on a key. Suppose A and B are primarycandidate keys and x is a non-prime attribute; then it is certainly true that A -> B, because A is a candidate key; it is equally certain that B -> x because B is a candidate key; so A ->B and B-> x, which is the definition of "x is transitively dependent on A". Disallowing all transitive dependencies (as do several authors who write about normalisation but clearly don't understand it) in 3NF is not sensible, it would imply that no 3NF relation which has any non-prime attributes has two or more candidate keys, which is just plain nonsense, you can only disallow nontrivial transitive dependencies.

Read something reliable. I will shamelessly plug my 3NF article at SQL Server central. And my 2NF article too, although some people seem to have a problem understanding the first sentence (which I should rewrite sometime, as a hard to understand first sentence is a serious fault), as that was what your question was about.

Tom

Golfer22
Golfer22
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 96
Here is the definition of 3NF:

"A relation R is in 3NF if and only if (i) it is in 2NF and (ii) whenever X is a set of attributes of R and A is a non-key attribute of R that is not in X, the value of A can be determined from the values of X only if X includes a candidate key."

If a relation is in 2NF by the definition in the first response to my original post, how could it not be in 3NF?

"...full dependency on all candidate keys..." seems to be the same as a non-key attribute being determined from the values of X only if X includes a candidate key. Two ways to get a relation to not be in 2NF would be for the relation to have a transitive dependency or a dependency on a subset of the candidate key. Either of those would put it in 1NF. But if a relation had neither and was in 2NF, I think it would be in 3NF based on the definition above. Obviously I don't understand. Please help me see the distinction.
sqlvogel
sqlvogel
Right there with Babe
Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)

Group: General Forum Members
Points: 782 Visits: 3706
Golfer22 (8/11/2013)
Here is the definition of 3NF:

"A relation R is in 3NF if and only if (i) it is in 2NF and (ii) whenever X is a set of attributes of R and A is a non-key attribute of R that is not in X, the value of A can be determined from the values of X only if X includes a candidate key."

If a relation is in 2NF by the definition in the first response to my original post, how could it not be in 3NF?

Because the fact that non-key attribute A is fully dependent on keys doesn't exclude the possibility that it may have other non-key detereminants as well. 2NF doesn't exclude the possibility of those non-key determinants. 3NF does.
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14208 Visits: 12197
Golfer22 (8/11/2013)
Here is the definition of 3NF:

"A relation R is in 3NF if and only if (i) it is in 2NF and (ii) whenever X is a set of attributes of R and A is a non-key attribute of R that is not in X, the value of A can be determined from the values of X only if X includes a candidate key."

If a relation is in 2NF by the definition in the first response to my original post, how could it not be in 3NF?

I think you are probably having trouble with Codd's phrase "fully dependent on"; what it means is "determined by the whole of but not by any proper subset of".
The easiest way to see the difference between 2nd and 3rd normal form is to take your 3NF definition above and insert the word "prime" before "attributes", which transforms it into a definition of 2NF.
2NF says: a non-prime attribute can't be determined from a proper subset of a candidate key. 3NF says in addition that it can't be determined from anything but a candidate key.

Tom

Golfer22
Golfer22
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 96
1. If a table was in 1NF and had non-key attributes with trivial transitive dependencies on candidate keys, could it be in 3NF? I think it could if the non-key attributes were not dependent on a subset of any candidate key.

2. Assuming the non-key attributes were not dependent on a candidate key, nor any subset of the candidate key, what is the highest normal form the table could be in?
Golfer22
Golfer22
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 96
Can a transitive dependency be a non-key determinant having a non-key attribute?
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14208 Visits: 12197
Golfer22 (8/17/2013)
1. If a table was in 1NF and had non-key attributes with trivial transitive dependencies on candidate keys, could it be in 3NF? I think it could if the non-key attributes were not dependent on a subset of any candidate key.

2. Assuming the non-key attributes were not dependent on a candidate key, nor any subset of the candidate key, what is the highest normal form the table could be in?


1. Yes. Any 3NF table with at least one non-prime attribute and at least two candidate keys has at least one trivial transitive dependency on the primary keys ("trivial" just means that either the target is included in some element of the dependency chain before the last element of the chain or every element of the chain except the last includes the whole of at least one candidate key).
Here is an example table
create table Example (
Pkey varchar(32) primary key,
Nickname varchar(8) not null unique,
ExpiryDate date not null
);


This is in third normal form because the only non-prime attribute (ExpiryDate) is dependent on every candidate key, and there is a trivial transient dependency Pkey -> Nickname -> ExpiryDate.

2. The definition of a candidate key is that it is a minimal key, the definition of a key is that it is a set of attributes such that every attribute (whether prime or not) is dependent on it. So the idea of a relation which has attribute which isn't dependent on a candidate key contradicts the definition of candidate key.

Tom

TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14208 Visits: 12197
Golfer22 (8/17/2013)
Can a transitive dependency be a non-key determinant having a non-key attribute?

Not sure exactly what you mean here, but maybe an example would be a relation with 6 attributes A, B, C, D, E, F with dependencies
A -> {B,C,D,E,F} (so {A} is a candidate key)
{B,C} -> D
{C,D} -> E
F -> {B,D}
so that there is a transitive dependency {B,C} -> {C,D} -> E which involves no prime attributes. Such a relation is in 1NF but not in 2NF or any higher normal form, and looking at possible update, delete, and insert anomalies in such a relation usually provides a pretty good indication of why not being in 2NF is a terrible idea.

Obviously there are simpler examples of that sort, for example a relation with four attributes {A,B,C,D} and dependencies
A -> B ({A} is the primary key)
B -> C
C -> D
with transient dependency B -> C -> D involving no prime attribute, but these are a bit boring and maybe too simple to be realistic.

Tom

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