Projection-Join Normalization
196 Interesting Special Cases of FDs
In the following discussions, to save repetition, all FDs mentioned are assumed to hold in relvar r
unless otherwise stated, and the symbol C refers to the set of all the FDs that hold in r. Now, in general
C contains FDs that are redundant in the sense that they are implied under the given inference rules by other FDs in C. For example, C might contain {a} → {b}, in which case it must also contain {a} →
{a, b}. We say that if S1 and S2 are sets of FDs and every FD in S2 is implied by those in S1, then S1 is a cover for S2. We are interested in finding proper subsets S of C where S is a cover for C and there is no proper subset of S that is also a cover for C. In that case, to enforce all the FDs in C it is sufficient for the DBMS just to enforce those in S—the others will all be enforced “automatically”, as a logical consequence. Consideration of the special classes of FD that now follow helps us to find such sets. Most of the terms defined for these special classes are used conventionally in the literature, but I add some of my own.
Empty dependants
Because the determinant and dependant of an FD are both sets, we have to consider the possibility of either or both being the empty set, { }. Theorem 5, Reflexivity, tells us that for every subset A of the heading of r,A→ { } holds in r. In particular { } → { } holds in r. If we eliminate from C all the FDs
having empty dependants, then the resulting subset is clearly a cover for C. Empty determinants
Much more interesting is the case, { } →B, where B is nonempty. This FD is satisfied only when each attribute a of B is such that a has the same value in each tuple of r—in other words, a is constant in r. If you need to be convinced of this fact, check it using the formal definition for FD given earlier. When A
is the empty set, t1{A} and t2{A} are both the 0-tuple. It follows that every tuple of r has the same value (the 0-tuple) for the determinant of the FD and must therefore have the same value for the dependant. Note that if some FD in C has an empty determinant and we eliminate from C all the FDs having empty determinants, then the resulting subset is not in general a cover for C.
Left-irreducible FDs
A→B is a left-irreducible FD if and only if there is no proper subset A’ of A such that A’→B also holds.
Conversely, if there is such a proper subset, then it is left-reducible. If we eliminate from C all those that are left-reducible, then from the resulting subset S we can recover C by application of Theorem 2, Left Augmentation. Thus, S is a cover for C.
Right-irreducible FDs
A→B is a right-irreducible FD if and only if B contains at most one attribute. If B contains more than
one attribute, then the FD is right-reducible. If we eliminate from C all those that are right-reducible, then from the resulting subset S we can recover C by application of Theorem 6, Union. Thus, S is a cover for C.
Download free eBooks at bookboon.com
197
Right-extendible FDs
A→B is a right-extendible FD if and only if there is some proper superset B’ of B such that A → B’
also holds. If we eliminate from C all those that are right-extendible, then from the resulting subset S
we can recover C by application of Theorem 3, Decomposition. Thus, S is a cover for C. Overlapping FDs
The FD A→B is overlapping if AB (where “” denotes set intersection) is nonempty; otherwise it
is non-overlapping. If we eliminate from C all those that are overlapping, then from the resulting subset
S we can recover C by application of Theorem 6, Union. Thus, S is a cover for C. Trivial FDs
The FD A→ B is trivial if B is a subset of A; otherwise it is nontrivial. Note that if B is empty, then A → B is trivial but non-overlapping. If we eliminate from C all those that are trivial, then from the resulting subset S we can recover C by application of Theorem 5, Reflexivity. (Note that this elimination subsumes the elimination of FDs with empty dependants.) Thus, S is a cover for C.
Interesting Cases of Sets of FDs
We are interested in sets S of FDs, where no proper subset of S is a cover for the closure S+ of S. In general
there can be many such subsets of S,of which two kinds are particularly useful.
Irreducible covers
A set S of FDs is irreducible if and only if it satisfies the following three conditions: 1. Every FD is S is right-irreducible.
2. Every FD is S is left-irreducible.
3. No proper subset of S is a cover for the closure S+ of S.
If S does satisfy those three conditions, then it is an irreducible cover for S+. Note that condition 3
implies that S contains no overlapping FDs, no trivial ones, and none that are implied by others under Theorem 4, Transitivity. Some texts—[3], for example—use the term nonredundant for sets that satisfy condition 3 but do not necessarily satisfy conditions 1 and 2.
Minimal covers
Let S1 be an irreducible cover for S+. Let the set S2 be derived from S1 as follows. Wherever two or
more FDs in S1 have the same determinant A, replace those FDs by the single FD A → B, where B is the union of the dependants of those FDs. Then S2 is a minimal cover for S+. (Alert: some texts use this
Download free eBooks at bookboon.com
Click on the ad to read more
198
For example, the set { { StudentId } → { Name } } is both an irreducible cover and a minimal
cover for all the FDs that hold in ENROLMENT. Note in particular that the FD { StudentId, CourseId } → { Name } follows, under Theorem 8, Unification, from { StudentId } → { Name }
(given) and { CourseId } → { CourseId } (self-determination).
7.6 Keys
You might wish to read again the material under the heading Keys in Chapter 6, Section 6.4, which gives the following definitions:
Definitions for superkey and key
Let K be a subset of the heading of relvar r. Then K is superkey for r if and only if, at all times, if tuples t1
and t2 both appear in the body of r, and the projection t1{K} is equal to the projection t2{K}, then t1 = t2
(i.e., they are the same tuple).
K is a key for r if and only if (a) K is a superkey for r and (b) no proper subset of K is a superkey for r.
With us you can
shape the future.
Every single day.
For more information go to:www.eon-career.com
Download free eBooks at bookboon.com
199
Appealing to FDs we can now give rather more concise definitions: Let K be a subset of the heading H of relvar r. Then:
• K is a superkey of r if and only if FD K → H holds in r
• K is a key of r if and only if FD K → H is left-irreducible and holds in r
When a key is declared to the DBMS, only its uniqueness property is subsequently checked and maintained. Its irreducibility has to be the database designer’s responsibility, for at any time r might be assigned a value that satisfies a putative key constraint implied by some proper subset of the declared key. For example, it is probably not a design error if at some point in time no two tuples in IS_ENROLLED_ON have the same CourseId value, even though the real world situation reflected by that state is perhaps a little surprising (no more than one student on any of the courses). On the other hand, it definitely would be a design error to include Mark along with StudentId and CourseId in the declared key for EXAM_MARK, allowing the same student to score several different marks in the same exam.
It must be emphasized that a key is a set of attributes, not an attribute per se. EXAM_MARK has just one key, consisting of two attributes, StudentId and CourseId. It is a common error to misunderstand the definition and refer to the attributes constituting the key as if each one were itself a key. In general a relvar can have several keys. For example, employees are identified in the company by their employee number but the database might record their national insurance number too, in some relvar that therefore has at least two keys, perhaps {Employee#} and {NatIns#}. Note the braces. Even though those two keys are both singleton sets, to omit the braces would (a) be sloppy and (b) perhaps foster the common misunderstanding I mentioned. On the other hand, the term “key attribute” is acceptable and useful. Chapter 6 draws attention to two special cases of keys: the entire heading as a key, and the empty set as a key. Now you can see how the significance of the empty key arises from that of FDs having empty determinants.
7.7
The Role of FDs and Keys in Optimization
This section is a digression, inserted here to show you that database design is not the only area in which the theory of functional dependence is applicable. I take the opportunity to mention one other important application of the theory. It concerns optimization that a relational DBMS is expected to undertake in the interests of efficient evaluation of relational expressions, a process commonly called query optimization.
Download free eBooks at bookboon.com
Click on the ad to read more
200
Just as the value assigned to a variable, by definition, varies from time to time, so also, as a consequence, does the relation denoted by a relational expression that references one or more relvars. Therefore our definition of “key” can be generalized to apply to relational expressions as well as to relvars. For example, given that { StudentId, CourseId } is a key of EXAM_MARK, we can conclude (for example) that
{ StudentId } is a key of (for example) EXAM_MARK WHERE CourseId = CID('C1').
Constraining CourseId values to be constant in the result of that expression means that the FD { } →
{ CourseId } holds in it, because application of Theorem 8, Unification, to { } → { CourseId } and
{ StudentId, CourseId } → { Mark } yields { StudentId } → { Mark, CourseId }. A subsequent
projection over just StudentId and Mark (likely to be given because the constant CourseId value is known) can be performed efficiently just by dropping the CourseId attribute with no need to incur the overhead of detecting redundant duplicate tuples—there cannot be any—and eliminating them. If the DBMS can detect projections that preserve at least one key of the operand, then it can evaluate those projections more efficiently.TMP PRODUCTION NY026057B
PSTANKIE gl/rv/rv/baf ACCCTR0005 Bookboon Ad Creative 4 6 x 4 12/13/2013
Bring your talent and passion to a global organization at the forefront of business, technology and innovation. Discover how great you can be. Visit accenture.com/bookboon © 20 13 Accentur e. All rights r eserved.