EXERCISES

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.

1.2 Who was E. F. Codd?

1.3 What’s a domain?

1.4 What do you understand by the term referential integrity?

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.

1.6 Distinguish between the two meanings of the term data model.

1.7 Explain in your own words (a) physical data independence, (b) the difference between model and implementation.

1.8 In the body of the chapter, I said that tables like those in Figure 1-1 and Figure 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?

1.9 (Try this exercise without looking back at the body of the chapter.) What relvars does the suppliers-and-parts database contain? What attributes do they involve? What keys and foreign keys do they have? (The point of this exercise is that it’s worth making yourself as familiar as possible with the structure, at least in general terms, of the running example. It’s not so important to remember the actual data values in detail—though it wouldn’t hurt if you did.)

1.10 “There’s only one relational model.” Explain this remark.

1.11 The following is an excerpt from a certain 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.

1.12 The following is an excerpt from another 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?

1.13 Write CREATE TABLE statements for an SQL version of the suppliers-and-parts database.

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

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

Show an equivalent relational assignment operation. Note: 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.

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

     UPDATE S SET STATUS = 25 WHERE 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 the discussion of “what if” queries in Chapter 7 for a detailed explanation.)

1.16 In the body of the chapter, I said that SQL doesn’t directly support relational assignment. Does it support it indirectly? If so, how? A related question: Can all relational assignments be expressed in terms of INSERT and/or DELETE and/or UPDATE? If not, why not? What are the implications?

1.17 From a practical standpoint, 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 be taking a closer look at such matters later in the book.)

Get SQL and Relational Theory, 2nd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.