Exercises

Exercise 4-1

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.

Exercise 4-2

Why are SQL's "positioned update" operations a bad idea?

Exercise 4-3

Give definitions for SQL analogs of the TAX_BRACKET, ROSTER, and MARRIAGE relvars from the section "More on Candidate Keys."

Exercise 4-4

Why doesn't it make much sense to say a relation has a key?

Exercise 4-5

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

Exercise 4-6

"Key values are not scalars but tuples." Explain this remark.

Exercise 4-7

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

Exercise 4-8

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. Does this example lead inevitably to a requirement for null support? (Answer: No, but it does serve to show how seductive the nulls idea can be.) What can be done in this example if nulls are prohibited?

Exercise 4-9

SQL has nothing analogous to Tutorial D's renaming option in its foreign key specifications. Why not?

Exercise 4-10

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

Exercise 4-11

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 that the product doesn't support but might be useful?

Exercise 4-12

The relational model has nothing to say about triggered procedures (often known simply as triggers). Is this omission a problem? If so, why? If not, why not? Do you think triggered procedures are necessary? Or desirable?

Exercise 4-13

Let view LSSP be defined as follows (SQL):

     CREATE VIEW LSSP       AS ( SELECT S.SNO, S.SNAME, S.STATUS, SP.PNO, SP.QTY            FROM   S, SP            WHERE  S.SNO = SP.SNO            AND    S.CITY = 'London' ) ;

Here's a query on this view:

     SELECT DISTINCT LSSP.STATUS, LSSP.QTY     FROM   LSSP     WHERE  LSSP.PNO IN          ( SELECT P.PNO            FROM   P            WHERE  P.CITY <> 'London' )

What might the query that's actually executed on the underlying base relvars look like?

Exercise 4-14

What key(s) does view LSSP from the previous exercise have?

Exercise 4-15

Investigate any SQL product available to you. Are there any apparently legitimate queries on views that fail in that product? If so, state as precisely as you can which ones they are. What justification does the vendor offer for failing to provide full support? (Note that this exercise asks about queries only, not updates.)

Exercise 4-16

Investigate any SQL product available to you. What view updates does that product support? Be as precise as you can in your answer.

Exercise 4-17

Using either the suppliers-and-parts database or any database you happen to be familiar with, give some further examples to illustrate the point that which relvars are base and which virtual is largely arbitrary.

Exercise 4-18

Investigate any SQL product available to you. In what ways there will be some! does that product violate The Principle of Interchangeability?

Exercise 4-19

Distinguish between views and snapshots. Does SQL support snapshots? Does any product that you're aware of?

Exercise 4-20

What's a "materialized view"? Why is the term deprecated?

Exercise 4-21

Define the terms proposition and predicate. Give examples.

Exercise 4-22

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

Exercise 4-23

What do you understand by the terms intension and extension?

Exercise 4-24

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 the fundamental 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.

Exercise 4-25

Consider views LS and NLS from the section "More on Views." What are the corresponding relvar predicates? Would it make any difference if they weren't views but base relvars instead?

Exercise 4-26

What's the predicate for view LSSP from Exercise 4-13?

Exercise 4-27

Explain the Closed World Assumption.

Exercise 4-28

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 set of attributes is empty. Can you think of any uses for such a key?

Exercise 4-29

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

Exercise 4-30

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