EXERCISES

5.1 It’s sometimes suggested that a relvar is really just a traditional computer file, with tuples instead of records and attributes instead of fields. Discuss.

5.2 Explain in your own words why remarks like (for example) “This UPDATE operation updates the status for suppliers in London” aren’t very precise. Give a replacement for that remark that’s as precise as you can make it.

5.3 Why are SQL’s “positioned update” operations a bad idea?

5.4 In Tutorial D, INSERT and D_INSERT are defined in terms of UNION and D_UNION, respectively, and DELETE and I_DELETE are defined in terms of MINUS and I_MINUS, respectively. In SQL, by contrast, INSERT is defined in terms of UNION ALL, and there’s nothing analogous to D_INSERT. There’s also nothing in SQL analogous to I_DELETE; but what about the regular SQL DELETE operator? How do you think that’s defined?

5.5 Let the SQL base table SS have the same columns as table S. Consider the following SQL INSERT statements:

     INSERT INTO SS ( SNO , SNAME , STATUS , CITY )
          ( SELECT SNO , SNAME , STATUS , CITY
            FROM   S
            WHERE  SNO = 'S6' ) ;

     INSERT INTO SS ( SNO , SNAME , STATUS , CITY ) VALUES
          ( SELECT SNO , SNAME , STATUS , CITY
            FROM   S
            WHERE  SNO = 'S6' ) ;

Are these statements logically equivalent? If not, what’s the difference between them? Note: Thinking about Tutorial D analogs of the two statements might help you answer this question.

5.6 (This is essentially a repeat of Exercise 2.22 from Chapter 2, but you should be able to give a more comprehensive answer now.) State The Assignment Principle. Can you think of any situations in which SQL violates that principle? Can you identify any negative consequences of such violations?

5.7 Give definitions for SQL base tables corresponding to the TAX_BRACKET, ROSTER, and MARRIAGE relvars in the section MORE ON CANDIDATE KEYS.

5.8 Why doesn’t it make sense to say a relation has a key?

5.9 In the body of the chapter, I gave one reason why key irreducibility is a good idea. Can you think of any others?

5.10 “Key values are not scalars but tuples.” Explain this remark.

5.11 Let relvar R be of degree n. What’s the maximum number of keys R can have?

5.12 What’s the difference between a key and a superkey? And given that the superkey concept makes sense, do you think it would make sense to define any kind of subkey concept?

5.13 Relvar EMP from the section MORE ON FOREIGN KEYS is an example of what’s sometimes called a self-referencing relvar. Invent some sample data for that relvar. Do such relvars lead inevitably to a requirement for null support? (Answer: No, they don’t, but they do serve to show how seductive the nulls idea can be.) What can be done in the example if nulls are prohibited?

5.14 Why doesn’t SQL have anything analogous to Tutorial D’s renaming option in its foreign key specifications?

5.15 Can you think of a situation in which two relvars R1 and R2 might each have a foreign key referencing the other? What are the implications of such a situation?

5.16 The well known bill of materials application involves a relvar—PP, say—showing which parts (“major” parts) contain which parts (“minor” parts) as immediate components, and showing also the corresponding quantities (e.g., “part P1 contains part P2 in quantity 4”). Of course, immediate components are themselves parts, and they can have further immediate components of their own. Give appropriate base relvar (Tutorial D) and base table (SQL) definitions. What referential actions do you think might make sense in this example?

5.17 Investigate any SQL product available to you. What referential actions does that product support? Which ones do you think are useful? Can you think of any others the product doesn’t support but might be useful?

5.18 Define the terms proposition and predicate. Give examples.

5.19 State the predicates for relvars P and SP from the suppliers-and-parts database.

5.20 What do you understand by the terms intension and extension?

5.21 Let DB be any database you happen to be familiar with and let R be any relvar in DB. What’s the predicate for R? Note: The point of this exercise is to get you to apply some of the ideas discussed in the body of this chapter to your own data, in an attempt to get you thinking about data in general in such terms. Obviously the exercise has no unique right answer.

5.22 Explain The Closed World Assumption in your own terms. Could there be such a thing as The Open World Assumption?

5.23 A key is a set of attributes and the empty set is a legitimate set; thus, we could define an empty key to be a key where the pertinent set of attributes is empty. What are the implications? Can you think of any uses for such a key?

5.24 A predicate has a set of parameters and the empty set is a legitimate set; thus, a predicate could have an empty set of parameters. What are the implications?

5.25 What’s the predicate for a relvar of degree zero? (Does this question even make sense? Justify your answer.)

5.26 Every relvar has some relation as its value. Is the converse true?—that is, is every relation a value of some relvar?

5.27 In Chapter 1 I said I’d be indicating primary key attributes, in tabular pictures of relations, by double underlining. At that point, however, I hadn’t discussed the logical difference between relations and relvars; and in this chapter we’ve seen that keys in general apply to relvars, not relations. Yet I’ve shown numerous tabular pictures in previous chapters that represent relations as such (I mean, relations that aren’t just a sample value for some relvar), and I’ve certainly been using the double underlining convention in those pictures. So what can we say about that convention now?

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.137.198.183