Databases Reference
In-Depth Information
recomposition via union instead of join). Indeed, that's exactly what the final subsection in the body of the chapter
was all about. More specifics are given in Part IV of this topic.
CHAPTER 14
14.1
See the body of the chapter.
14.2
No answer provided.
14.3
No, it isn't. See the further remarks on examples of this kind in Chapter 15.
14.4 The design doesn't violate orthogonality, but there are several other things wrong with it. For example, how
would you express the query “Get the city for supplier S1”? (There are two cases to consider: one where you do at
least know what supplier cities exist, and one where you don't. In the latter case, you might want to think about this
query too: “Is supplier S1 represented in the database?”) Also, what's happened to the FD {CITY} → {STATUS}?
And what about the {SNO} foreign key in relvar SP? (Again there are two cases to consider─the same two cases as
before, in fact.)
Next, if we did keep the CITY attribute in relvars LS, PS, etc., then the FD {} → {CITY} would hold in each
of those relvars. Since this FD isn't “an arrow out of a key,” the relvars wouldn't be in BCNF. (See the answer to
Exercise 4.6, where an essentially similar example is discussed.)
What's more, given that the FD {CITY} → {STATUS} holds in the original suppliers relvar S, it certainly
still holds in relvars LS, PS, etc.─assuming, that is, that we keep the CITY attribute in those relvars─and so again
those relvars aren't in BCNF.
Finally, regardless of whether we keep the CITY attribute or not, the FD {} → {STATUS} also holds in each
of those relvars, and so yet again the relvars wouldn't be in BCNF. Note, therefore, that the FD {CITY} →
{STATUS}, if it holds at all (which it does only if the CITY attribute is retained), is in fact reducible, under the
suggested horizontal decomposition.
CHAPTER 15
15.1 If you have a good answer to this exercise, please communicate it to me at PO Box 1000, Healdsburg, CA
95448, USA (regular mail only, please).
