Abstract
Once again consider our usual suppliers relvar S. Since {SNO} is a key, that relvar is certainly subject to the FD {SNO} → {STATUS}. Thus, taking X as {SNO}, Y as {STATUS}, and Z as {SNAME,CITY}, Heath’s Theorem tells us we can decompose that relvar into relvars SNC and ST, where SNC has heading {SNO,SNAME,CITY} and ST has heading {SNO,STATUS}. Sample values for SNC and ST corresponding to the value shown for S in Figure 3-1 are shown in Figure 6-1:
This is a preview of subscription content, log in via an institution to check access.
Access this chapter
Tax calculation will be finalised at checkout
Purchases are for personal use only
Notes
- 1.
To say an FD is “lost” in such circumstances is usual but is also (as explained at the end of the section “Boyce/Codd Normal Form Revisited” in the previous chapter) a trifle inappropriate—to repeat, what’s really happening here is that the FD in question has been replaced by another constraint. However, the present situation differs from that described in the previous chapter in that the new constraint isn’t enforced automatically as a logical consequence of enforcing certain other constraints. That’s the point.
- 2.
There might be performance penalties, too. Now, I shouldn’t really mention this fact; as I said in Chapter 1, I never want performance considerations to be the driving force behind my logical design. But in the case at hand, performance is just an additional point that happens to reinforce my main argument.
- 3.
This is the first time we’ve encountered the term business rule in this book, but we’ll see many more examples of its use in later chapters. Basically, a business rule is a statement, usually expressed (as here) somewhat informally in natural language, that’s supposed to capture some aspect of what the data in the database means and/or how it’s constrained. There’s no consensus on any more precise definition of the term, though most writers would at least agree that relvar predicates are an important special case. See Appendix A for further discussion.
- 4.
- 5.
In the case at hand, of course, we must decompose the relvar as indicated if we want to be able to record the fact that (say) Professor Black teaches physics even though Professor Black has no students at the moment.
- 6.
I’ll have quite a lot more to say on the notion of FDs holding implicitly (“implicit FDs”) in the next chapter, also in Chapter 11.
- 7.
So a more accurate version of the predicate would be: Supplier SNO is in class CLASS (which has status STATUS) and is located in city CITY (which also has status STATUS).
- 8.
Nor is it likely to have been, either, since {SNO} is a key (in fact the only key) for relvar RX2. At most we might expect to see two FDs stated separately, viz., {SNO} → {CLASS} and {SNO} → {CITY}—but even that’s pretty unlikely.
- 9.
Note, moreover, that relvar RX2A′ isn’t really even a correct design, since it prohibits tuples in which the specified class and city have different status values. To put it another way, the predicate for that relvar isn’t just Class CLASS and city CITY have status STATUS—rather, it’s something like this: There exists some supplier s such that s has class CLASS and city CITY, both of which have status STATUS.
- 10.
In support of this contention, I’d like to quote something Codd himself had to say in the paper in which he introduced 2NF and 3NF (see Appendix D): “The basic ideas underlying [2NF and 3NF] are simple, but they have many subtle ramifications. The author has found that numerous examples are needed to explain and motivate the precise definitions of these normal forms.”
- 11.
I give this procedure partly just for historical reasons. You can skip it if you like.
- 12.
I’m being sloppy here. Recall from Chapters 4 and 5 that FD irreducibility is defined only with respect to some relvar—but I haven’t said anything here about the FDs in F as holding in any relvar, and there’s thus no context that would allow us to talk legitimately about FDs being irreducible. What I mean, however, is that no attribute can be discarded from the left side of any FD in C without losing the property that C is a cover for F.
- 13.
Of course SJT is already in 3NF, but we can still apply the procedure to it—and I have my reasons, which I trust will quickly become apparent, for wanting to do so.
- 14.
TABLE_DUM and TABLE_DEE are pet names for, respectively, the unique relation with no attributes and no tuples and the unique relation with no attributes and one tuple (we’ve met these relations before, in the answer to Exercise 2.8 in Chapter 2). For further discussion, see SQL and Relational Theory.
- 15.
For precise definitions of restriction and the associated notion of a restriction condition, see the answer to Exercise 15.3 in Chapter 15.
- 16.
So too does any given relation r, of course.
- 17.
The term contradiction doesn’t mean quite the same in logic as it does in ordinary discourse, but the difference isn’t important for present purposes.
- 18.
Except that there might be a foreign key constraint, or even an equality dependency, between {CITY} in CT and {CITY} in SNC (see Chapter 3).
- 19.
The condition that the intersection of H1 and H2 be nonempty is as in Rissanen’s original statement of the theorem but appears to be unnecessary.
Author information
Authors and Affiliations
Rights and permissions
Copyright information
© 2019 C. J. Date
About this chapter
Cite this chapter
Date, C.J. (2019). Preserving FDs. In: Database Design and Relational Theory. Apress, Berkeley, CA. https://doi.org/10.1007/978-1-4842-5540-7_6
Download citation
DOI: https://doi.org/10.1007/978-1-4842-5540-7_6
Published:
Publisher Name: Apress, Berkeley, CA
Print ISBN: 978-1-4842-5539-1
Online ISBN: 978-1-4842-5540-7
eBook Packages: Professional and Applied ComputingProfessional and Applied Computing (R0)Apress Access Books