It goes like this: Relvar R is in BCNF if and only if every nontrivial FD satisfied by R is implied by the keys of R. Relvar R is in 4NF if and only if every nontrivial MVD satisfied by R is implied by the keys of R. Relvar R is in 5NF if and only if every nontrivial JD satisfied by R is implied by the keys of R. For an explanation of exactly what it means for an FD or MVD or JD to be nontrivial or to be implied by keys, see the O Reilly book mentioned a couple of times already (Database in Depth: Relational Theory for Practitioners).

Using Barcode generator for Font Control to generate, create Universal Product Code version A image in Font applications.

www.OnBarcode.com

As already mentioned, the third normalization principle is: The decomposition process should preserve dependencies. Here s a simple example to illustrate the point. Consider the original suppliers relvar S once again; for simplicity, let s ignore attribute SNAME, but let s also assume that suppliers satisfy the following additional FD: { CITY } { STATUS } The obvious decomposition here is as follows: SC { S#, CITY } KEY { S# } CS { CITY, STATUS } KEY { CITY } Now consider by contrast the following alternative decomposition: SC { S#, CITY } KEY { S# } ST { S#, STATUS } KEY { S# } (Projection SC is the same in both decompositions; the difference is in the other projection.) Now, the second decomposition here is certainly nonloss (S is certainly equal to the join of SC and ST); what s more, projections SC and ST are both in 5NF. However, the FD from CITY to STATUS has been lost in this decomposition. What do I mean by lost here I mean the pertinent constraint to the effect that CITY determines STATUS is no longer an FD as such; rather, it has become a constraint that spans two distinct relvars. Here s a precise formulation of that constraint in terms of the SC/ST decomposition:

Using Barcode creation for iPad Control to generate, create QR Code ISO/IEC18004 image in iPad applications.

www.OnBarcode.com

CONSTRAINT CITY_DETERMINES_STATUS COUNT ( ( SC JOIN ST ) { CITY } ) = COUNT ( ( SC JOIN ST ) { CITY, STATUS } ) ; (Awful, isn t it ) In other words, the original FD, which was a constraint on a single relvar, has become a multi-relvar constraint instead. What s more, not only is the constraint now harder to state, it s probably harder for the system to enforce as well by which I mean there s likely to be a performance penalty to pay in enforcing it, compared with what s involved in enforcing the original FD. Now, I didn t discuss the foregoing example in detail in 12, because I assumed that readers were probably familiar with the general idea of dependency preservation already. (It might be a little late to be saying this!) What I did do, however, was show that the objective of preserving dependencies could actually be in conflict with the objective of reducing redundancy. I used the following example. We re given a relvar SJT with attributes S, J, and T and (simplified) predicate: Student S is taught subject J by teacher T. In addition, the following constraints apply: For each subject, each student of that subject is taught by just one teacher (so the FD {S,J} {T} holds). Each teacher teaches just one subject (so the FD {T} {J} holds). And I showed that if we decompose the relvar (in order to reduce redundancy) into 5NF projections as follows ST { S, T } KEY { S, T } TJ { T, J } KEY { T } then the FD {S,J} {T} is lost, in the sense that it s replaced by the following multi-relvar constraint: CONSTRAINT S_AND_J_DETERMINES_T COUNT ( ST JOIN TJ ) = COUNT ( ( ST JOIN TJ ) { S, J } ) ; ADR commented on this example: But after the decomposition into ST and TJ we have the opportunity to represent teachers (in relvar TJ) who teach a subject that nobody studies. What s more, that opportunity is one of the justifications given for doing such decompositions. If we grasp that opportunity, then how can we say that the FD is preserved (In any case, I find it difficult to think of preserving the FD when we are discarding the very relvar in which that FD holds!) I suppose we have preservation in the sense that the FD in question does hold in ST JOIN TJ, but (ST JOIN TJ){T,J} is no longer equal to TJ if TJ represents teachers who teach subjects that nobody studies. I think this point needs to be added.