

SSC Journeyman
Group: General Forum Members
Last Login: Saturday, December 28, 2013 5:45 PM
Points: 94,
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?




SSCAddicted
Group: General Forum Members
Last Login: 2 days ago @ 11:36 AM
Points: 455,
Visits: 3,603


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 nonkey 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 nonprime 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.




SSCrazy Eights
Group: General Forum Members
Last Login: Yesterday @ 7:32 PM
Points: 8,322,
Visits: 10,034


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 nonprime 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 nonprime 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




SSC Journeyman
Group: General Forum Members
Last Login: Saturday, December 28, 2013 5:45 PM
Points: 94,
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 nonkey 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 nonkey 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.




SSCAddicted
Group: General Forum Members
Last Login: 2 days ago @ 11:36 AM
Points: 455,
Visits: 3,603


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 nonkey 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 nonkey attribute A is fully dependent on keys doesn't exclude the possibility that it may have other nonkey detereminants as well. 2NF doesn't exclude the possibility of those nonkey determinants. 3NF does.




SSCrazy Eights
Group: General Forum Members
Last Login: Yesterday @ 7:32 PM
Points: 8,322,
Visits: 10,034


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 nonkey 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 nonprime 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




SSC Journeyman
Group: General Forum Members
Last Login: Saturday, December 28, 2013 5:45 PM
Points: 94,
Visits: 96


1. If a table was in 1NF and had nonkey attributes with trivial transitive dependencies on candidate keys, could it be in 3NF? I think it could if the nonkey attributes were not dependent on a subset of any candidate key.
2. Assuming the nonkey 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?




SSC Journeyman
Group: General Forum Members
Last Login: Saturday, December 28, 2013 5:45 PM
Points: 94,
Visits: 96


Can a transitive dependency be a nonkey determinant having a nonkey attribute?




SSCrazy Eights
Group: General Forum Members
Last Login: Yesterday @ 7:32 PM
Points: 8,322,
Visits: 10,034


Golfer22 (8/17/2013) 1. If a table was in 1NF and had nonkey attributes with trivial transitive dependencies on candidate keys, could it be in 3NF? I think it could if the nonkey attributes were not dependent on a subset of any candidate key.
2. Assuming the nonkey 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 nonprime 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 nonprime 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




SSCrazy Eights
Group: General Forum Members
Last Login: Yesterday @ 7:32 PM
Points: 8,322,
Visits: 10,034


Golfer22 (8/17/2013) Can a transitive dependency be a nonkey determinant having a nonkey 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



