I need to introduce one more concept, FD irreducibility (a second kind of irreducibility, observe), before I can get on to the definitions of 2NF, 3NF, and BCNF:
Definition: The FD X → Y is irreducible with respect to relvar R (or just irreducible, if R is understood) if and only if it holds in R and X- → Y doesn’t hold in R for any proper subset X- of X.
For example, the FD {SNO,PNO} → {QTY} is irreducible with respect to relvar SP. Note: This kind of irreducibility is sometimes referred to more explicitly as left irreducibility (since it’s really the left side of the FD that we’re talking about), but I’ve chosen to elide that “left” here for simplicity.
Now—at last, you might be forgiven for thinking—I can define 2NF:
Definition: Relvar R is in second normal form (2NF) if and only if, for every key K of R and every nonkey attribute A of R, the FD K → {A} (which holds in R, necessarily) is irreducible.
Note: The following definition is logically equivalent to the one just given (see Exercise 4.5 at the end of the chapter) but can sometimes be more useful:
Definition: Relvar R is in second normal form (2NF) if and only if, for every nontrivial FD X → Y that holds in R, at least one of the following is true: (a) X is a superkey; (b) Y is a subkey; (c) X is not a subkey.
Points arising:
It would be extremely unusual to regard 2NF as the ultimate goal of the design process. In fact, both 2NF and 3NF are mainly of historical interest; they’re both regarded at best as stepping stones on the way to BCNF, which is of much more pragmatic (as well as theoretical) interest.
Definitions of 2NF in the literature often take the form “R is in 2NF if and only if it’s in 1NF and ... .” However, such definitions are usually based on a mistaken understanding of what 1NF is. As we’ve seen, all relvars are in 1NF, and the words “it’s in 1NF and” therefore add nothing.
Let’s look at an example. Actually, it’s usually more instructive with the normal forms to look at a counterexample rather than an example per se. Consider, therefore, a revised version of relvar SP—let’s call it SCP—that has an additional attribute CITY, representing the city of the applicable supplier. Here are some sample tuples:
This relvar clearly suffers from redundancy: Every tuple for supplier S1 tells us S1 is in London, every tuple for supplier S2 tells us S2 is in Paris, and so on. And the relvar isn’t in 2NF—its sole key is {SNO,PNO}, and the FD {SNO,PNO} → {CITY} therefore certainly holds, but that FD isn’t irreducible: We can drop PNO from the determinant and what remains, {SNO} → {CITY}, is still an FD that holds in the relvar. Equivalently, we can say the FD {SNO} → {CITY} holds and is nontrivial; moreover, (a) {SNO} isn’t a superkey, (b) {CITY} isn’t a subkey, and (c) {SNO} is a subkey, and so—appealing now to the second of the definitions given above—the relvar isn’t in 2NF.