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.