Databases Reference
In-Depth Information
key { A } considered in isolation. No. 4 would be implied by keys─actually by an individual key─if and only if { C }
were a key, but it isn't; what's more, that JD can't possibly hold in R , because if it did, then { C } would have to be a
key after all (think about it!). As for No. 5, it clearly isn't implied by the keys; it might or might not hold in R , but if
it does, then R can't be in 5NF.
So what exactly is going on in these examples? Let me try to explain the intuition behind what I've been
saying (you might like to try interpreting what follows in terms of the suppliers relvar S and the JD
{{SNO,SNAME},{SNO,STATUS},{SNAME,CITY}}, under the assumption once again that {SNO} and
{SNAME} are both keys for that relvar):
Let X1 , ..., Xn be subsets of the heading H of relvar R , such that the union of X1 , ..., Xn is equal to H .
Let J be the JD { X1 ,..., Xn }, and let J be implied by the keys of R .
Let r be the relation that's the current value of R .
Choose, arbitrarily, two distinct elements (components) of the set { X1 ,..., Xn }, say X1 and X2 .
Let r1 and r2 be the projections of r on X1 and X2 , respectively.
Now, if X1 and X2 both include the same key K of R , then the join r12 of r1 and r2 ─whose heading X12 will
be the union of X1 and X2 ─will be a strict one to one join, and so r1 and r2 can be replaced by r12 without loss of
information. (At the same time, X1 and X2 can be replaced in J by X12 .) Since the original version of J was implied
by the keys of R , performing such replacements repeatedly will, by definition, eventually yield a relation (a) that's
equal to the original relation r , and in particular (b) will therefore have a heading equal to the entire heading H .
Let me now point out that everything I've said so far becomes much simpler in the common special case
where the pertinent relvar R has just one key K . In that case, the JD { X1 ,..., Xn } is implied by keys if and only if
both of the following are true:
Every attribute of R is included in at least one of X1 , ..., Xn . (This requirement always applies, of course, in
the general case as well as in this special case.)
The sole key K of R is included in each of X1 , ..., Xn ─in other words, each of X1 , ..., Xn is a superkey.
So if R has just one key K , then R is in 5NF if and only if every component of every JD that holds in R includes that
key K . 4 (However, please note that─ important! ─I'm assuming here that the only JDs under consideration are ones
that are irreducible with respect to R . See Chapter 11.) By way of example, consider the parts relvar P. The only
irreducible JDs { X1 ,..., Xn } that hold in that relvar are such that each Xi ( i = 1, ..., n ) includes the sole key {PNO}.
Those JDs are clearly all implied by that sole key, therefore, and relvar P is in 5NF. Here's one of the JDs in
{ { PNO , PNAME , COLOR } , { PNO , WEIGHT , CITY } }
4 Note that this isn't the case with relvar S─that relvar is subject to at least one JD for which at least one component fails to include the sole key
{SNO} (viz., {{SNO,SNAME,CITY},{CITY,STATUS}}) and so isn't in 5NF.
Search WWH ::

Custom Search