Exercises

Exercise 6-1.

Define the terms type constraint and database constraint. When are such constraints checked? What happens if the check fails?

Exercise 6-2

State The Golden Rule.

Exercise 6-3

What do you understand by the terms attribute constraint; tuple constraint; relvar constraint; "the" database constraint; "the" relvar constraint; single-relvar constraint; multi-relvar constraint?

Exercise 6-4

Distinguish between possible and physical representations.

Exercise 6-5

Explain as carefully as you can (a) what a selector is and (b) what a THE_ operator is.

Exercise 6-6

Suppose the only legal CITY values are London, Paris, Rome, Athens, Oslo, Stockholm, Madrid, and Amsterdam. Define a type called CITY that satisfies this constraint. Is there a way to impose the same constraint without an explicit CITY type? If so, compare and contrast the different approaches.

Exercise 6-7

Throughout this book I assume that SNO is a user-defined type. Give a definition for this type. Assume that the only legal supplier numbers are ones that can be represented by a character string of at least two characters, of which the first is an "S" and the remainder denote a decimal integer in the range 1 to 9999.

Exercise 6-8

A line segment is a straight line connecting two points in the euclidean plane. Give a corresponding type definition.

Exercise 6-9

Can you think of a type for which we might want to specify two different possreps? If there are two or more possreps for the same type, does it make sense for each to include a type constraint?

Exercise 6-10

Can you think of an example of a type for which different possreps have different numbers of components?

Exercise 6-11

Which operations might cause constraints C1-C8 from the body of the chapter to be violated?

Exercise 6-12

Constraint C1 (for example) had the property that it could be checked for a given tuple by examining just that tuple in isolation; constraint C4 (for example) did not. What is it, formally, that accounts for this difference? What's the pragmatic significance, if any, of this difference?

Exercise 6-13

Can you give a Tutorial D database constraint that's exactly equivalent to the specification KEY{SNO} for relvar S?

Exercise 6-14

Give an SQL formulation of constraint C7 from the body of the chapter.

Exercise 6-15

Give an SQL formulation of constraint C8 from the body of the chapter.

Exercise 6-16

Using Tutorial D and/or SQL, write constraints for the suppliers-and-parts database to express the following requirements:

1. All red parts must weigh less than 50 pounds.

2. Every London supplier must supply part P2.

3. No two suppliers can be located in the same city.

4. At most one supplier can be located in Athens at any one time.

5. There must exist at least one London supplier.

6. At least one red part must weigh less than 50 pounds.

7. The average supplier status must be at least 10.

8. No shipment can have a quantity more than double the average of all such quantities.

9. No supplier with maximum status can be located in the same city as any supplier with minimum status.

10. Every part must be located in a city in which there is at least one supplier.

11. Every part must be located in a city in which there is at least one supplier of that part.

12. Suppliers in London must supply more different kinds of parts than suppliers in Paris.

13. Suppliers in London must supply more parts in total than suppliers in Paris.

In each case, state which operations might cause the constraint to be violated.

Exercise 6-17

In a footnote in the section "Constraints and Predicates," I said that if the values S1 and London appeared together in some tuple, then it might mean (among many other possible interpretations) that supplier S1 doesn't have an office in London. Actually, this particular interpretation is extremely unlikely. Why? (Hint: Remember the Closed World Assumption.)

Exercise 6-18

Suppose no "cascade delete" rule is stated declaratively for suppliers and shipments. Write a Tutorial D statement that will delete some specified supplier and all shipments for that supplier in a single operation.

Exercise 6-19

Using the syntax sketched for transition constraints in the section "Miscellaneous Issues," write transition constraints to express the following requirements:

1. Suppliers in Athens can move only to London or Paris, and suppliers in London can move only to Paris.

2. The total shipment quantity for a given part can never decrease.

3. The total shipment quantity for a given supplier cannot be reduced in a single update to less than half its current value. (What do you think the qualification "in a single update" means here? Why is it important? Is it important?)

Exercise 6-20

Distinguish between correctness and consistency.

Exercise 6-21

Do you think the following is or should be a valid Tutorial D TYPE statement?

   TYPE TTT POSSREP { ... CONSTRAINT FALSE } ; 

Exercise 6-22

Do you think the following is or should be a valid Tutorial D CONSTRAINT statement?

   CONSTRAINT FFF FALSE ; 

Exercise 6-23

Investigate any SQL product that might be available to you. What semantic optimization does it support, if any?

Exercise 6-24

Why do you think SQL fails to support type constraints? What are the consequences of this state of affairs?

Exercise 6-25

The discussion in this chapter of types in general, and type constraints in particular, tacitly assumed that types were all (a) scalar and (b) user-defined. Do the concepts discussed apply equally well to nonscalar types and/or system-defined types?