8.1 It’s sometimes claimed that one advantage of binary relvars over the general n-ary relvars supported by the relational model is that binary relvars are always in BCNF (implying among other things that we don’t need to worry about normalization and denormalization, perhaps). Either prove the correctness of this claim or show that it’s incorrect by producing a counterexample.
8.2 The following is an excerpt from a published interview with a database consultant.[86] It begins with a statement from the consultant:
Consultant: The problems ... largely result from normalizing data across multiple [relvars] ... Many queries, however, are much easier to understand if the data is denormalized ...
Interviewer: Doesn’t denormalization potentially lower data integrity and reduce flexibility in supporting unanticipated queries?
Consultant: Normalization, and its emphasis on elimination of redundant storage, is purely a transaction processing issue. When users view data, they see it in a redundant form. In order to transform data into a form that is useful to users, it must be denormalized by means of a join, which is essentially a way of dynamically denormalizing data for greater ease of use.
The problem is that users can’t tolerate the time and cost of joins. To address the problem, companies replicate data in an ever increasing number of decision support databases, which represent denormalized views of the data.
What in your opinion is wrong (if anything) with the opinions expressed?
8.3 The possibility of using surrogate identifiers or keys was mentioned in the body of the chapter. Indeed, many designers recommend the use of such artifical or surrogate keys in place of what are sometimes called “natural” keys. For example, we might add an attribute SPNO, say, to our usual shipments relvar (making sure it has the uniqueness property, of course) and then make {SPNO} a surrogate key for that relvar. (Note, however, that {SNO,PNO} would still be a key; it just wouldn’t be the only one any longer.) Thus, surrogate keys are keys in the usual relational sense, but (a) they always involve exactly one attribute and (b) their values serve solely as surrogates for the entities they stand for (i.e., they serve merely to represent the fact that those entities exist—they carry absolutely no additional meaning or baggage of any kind). Ideally, those surrogate values would be system generated, but whether they’re system or user generated has nothing to do with the basic idea of surrogate keys as such. Two questions: Are surrogate keys the same thing as tuple IDs? And do you think they’re a good idea?
8.4 If two designs are information equivalent, it must be possible to use operations of the relational algebra to convert them into one another. Consider, therefore, the following competing designs for an employees relvar from the body of the chapter:
EMP { ENO , JAN_PAY , FEB_PAY , ..., DEC_PAY }
KEY { ENO }
EMP { ENO , MONTH , PAY }
KEY { ENO , MONTH }Using Tutorial D or SQL (or your own preferred database language), show how each of these designs can be converted into the other.