## Second normal form or third normal form?

 Author Message Golfer22 Ten Centuries Group: General Forum Members Points: 1160 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 SSCarpal Tunnel Group: General Forum Members Points: 4964 Visits: 3713 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 ModelFull 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. Tom Thomson SSC Guru Group: General Forum Members Points: 50766 Visits: 13159 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 Ten Centuries Group: General Forum Members Points: 1160 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 SSCarpal Tunnel Group: General Forum Members Points: 4964 Visits: 3713 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. Tom Thomson SSC Guru Group: General Forum Members Points: 50766 Visits: 13159 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 Ten Centuries Group: General Forum Members Points: 1160 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 Ten Centuries Group: General Forum Members Points: 1160 Visits: 96 Can a transitive dependency be a non-key determinant having a non-key attribute? Tom Thomson SSC Guru Group: General Forum Members Points: 50766 Visits: 13159 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 Tom Thomson SSC Guru Group: General Forum Members Points: 50766 Visits: 13159 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 dependenciesA -> {B,C,D,E,F} (so {A} is a candidate key){B,C} -> D{C,D} -> EF -> {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 dependenciesA -> B ({A} is the primary key)B -> CC -> Dwith transient dependency B -> C -> D involving no prime attribute, but these are a bit boring and maybe too simple to be realistic. Tom