Re: 2nd and 3rd Normals

batonnet@zeta.org.au (Bryan Tonnet) writes: > So, on with the question. Forgetting the book definition, and talking > in real terms, I have always believed that the 2nd normal form of a DB > is where every column in a record is dependant on the primary key. For > example, in an order file, where the primary key is Order Number, a > column called Customer number is in the second normal form if there is > a "business dependancy" between the order number and the customer > number. The DB would not be in 2nd normal if the customer number had > nothing to do with order number (hard to imagine) or if the customer > number/order number pair was already held in another table making the > instance in the order file redundant. No. As the definition says, a table is in 2NF when all non-key attributes are fully functionally dependent on the primary key. (You can generalize this definition to several candidate keys, but let's ignore that for now.) In this case, the "full" in "full functional dependency" matters only when the primary key is composite, that is, when the primary key consists of several columns. Therefore the table of orders in your example will always be in 2NF. A full functional dependency is a functional dependency where you can't remove any of the attributes in the determinant and still have a functional dependency. Note that all the non-key attributes will always be functionally dependent (although they need not be fully functionally dependent) on the primary key. Otherwise it couldn't be the primary key. The redundancy you mention wouldn't matter. > For 3rd normal, each non key column in the table (not primary or > foreign) has to be dependant on the primary key only. For example, if > our order file contained stock number, and stock description, then it > could be said that stock description is functionaly linked to the > order number but also to the stock number, and therefore the table in > not 3rd normal. As far as I know, foreign keys has nothing to do with this. Except for that, you are right. (This definition can also be generalized, but let's ignore that too.) I do hope that this is right. Otherwise I have mis-taught several hundred students by now. Please advise me if I'm wrong! -- Thomas Padron-McCarthy, CS Dept, Linkoping U, Sweden -- tpm@ida.liu.se