Exercises

As noted in the preface, you certainly don't have to do any of the exercises, but I think it's a good idea to try at least some of them. Answers, often giving more information about the subject at hand, can be found online at http://oreilly.com/catalog/databaseid.

Exercise 1-1

(Repeated from the body of the chapter, but slightly reworded here.) If you haven't done so already, go through the chapter again and identify all of the places where I used the term relation when I should by rights have used the term relvar instead (or as well).

Exercise 1-2

Who was E. F. Codd?

Exercise 1-3

What's a domain?

Exercise 1-4

What do you understand by the term referential integrity?

Exercise 1-5

The terms heading, body, attribute, tuple, cardinality, and degree, defined in the body of the chapter for relation values, can all be interpreted in the obvious way to apply to relvars as well. Make sure you understand this remark.

Exercise 1-6

Distinguish between the two meanings of the term data model.

Exercise 1-7

Explain the difference between model and implementation in your own words.

Exercise 1-8

In the body of the chapter, I said that tables like those in Figures Figures 1-1 and 1-3 weren't relations as such but, rather, pictures of relations. What are some of the specific points of difference between such pictures and the corresponding relations?

Exercise 1-9

Explain data independence in your own words.

Exercise 1-10

(Try this exercise without looking back at the body of the chapter.) What relvars are contained in the suppliers-and-parts database? What attributes do they involve? What candidate and foreign keys exist? (The point of this exercise is that it's worth making yourself as familiar as possible with the structure, at least, of the running example. Of course, it's not so important to remember the actual data values in detail though it wouldn't hurt if you did.)

Exercise 1-11

"There's only one relational model." Explain this remark.

Exercise 1-12

The following is an excerpt from a recent database textbook: "[It] is important to make a distinction between stored relations, which are tables, and virtual relations, which are views . . . [We] shall use relation only where a table or a view could be used. When we want to emphasize that a relation is stored, rather than a view, we shall sometimes use the term base relation or base table." This text betrays several confusions or misconceptions regarding the relational model. Identify as many as you can.

Exercise 1-13

The following is an excerpt from another recent database textbook: "[The relational] model . . . defines simple tables for each relation and many-to-many relationships. Cross-reference keys link the tables together, representing the relationships between entities. Primary and secondary indexes provide rapid access to data based upon qualifications." This text is intended as a definition of the relational model . . . what's wrong with it?

Exercise 1-14

Write SQL CREATE TABLE statements to define an SQL version of the suppliers-and-parts database.

Exercise 1-15

The following is a typical SQL INSERT statement against the suppliers-and-parts database:

     INSERT INTO SP ( SNO, PNO, QTY )            VALUES ( SNO('S5'), PNO('P6'), QTY(250) ) ;

Show an equivalent relational assignment operation. Note: I'm assuming here that attributes SNO, PNO, and QTY are of types SNO, PNO, and QTY, respectively, and the expressions SNO('S5'), PNO('P6'), and QTY(250) are literals of those types, with the obvious interpretation. I'll have more to say on such matters in the next two chapters. Also, I realize I haven't yet explained the syntax of relational assignment in detail, so don't worry too much about giving a syntactically correct answer just do the best you can.

Exercise 1-16

(Harder.) The following is a typical SQL UPDATE statement against the suppliers-and-parts database:

     UPDATE S     SET    STATUS = 25     WHERE  S.CITY = 'Paris' ;

Show an equivalent relational assignment operation. (The purpose of this exercise is to get you thinking about what's involved. I haven't told you enough in this chapter to allow you to answer it fully. See Chapter 5 for further discussion.)

Exercise 1-17

In the body of the chapter, I said that SQL doesn't directly support relational assignment. Does it support it indirectly? If so, how?

Exercise 1-18

From a practical point of view, why do you think duplicate tuples, top-to-bottom tuple ordering, and left-to-right attribute ordering are all very bad ideas? (These questions deliberately weren't answered in the body of the chapter, and this exercise might best serve as a basis for group discussion. We'll take a closer look at such matters later in the book.)