8.1 Define the terms type constraint and database constraint. When are such constraints checked? What happens if the check fails?
8.2 State The Golden Rule. Is it true that this rule can be violated if and only if some individually declared single relvar constraint is violated?
8.3 What do you understand by the following terms?—assertion; attribute constraint; base table constraint; column constraint; multirelvar constraint; referential constraint; relvar constraint; row constraint; single relvar constraint; state constraint; “the” (total) database constraint; “the” (total) relvar constraint; transition constraint; tuple constraint. Which of these categories if any do (a) key constraints, (b) foreign key constraints, fall into?
8.4 Distinguish between possible and physical representations.
8.5 With the Tutorial D definition of type QTY as given in the body of the chapter, what do the following expressions return?
THE_Q ( QTY ( 345 ) )QTY ( THE_Q ( QTY ) )
8.6 Explain as carefully as you can (a) what a selector is; (b) what a THE_ operator is. Note: This exercise essentially repeats ones in previous chapters, but now you should be able to be more specific in your answers.
8.7 Suppose the only legal CITY values are London, Paris, Rome, Athens, Oslo, Stockholm, Madrid, and Amsterdam. Define a Tutorial D type called CITY that satisfies this constraint.
8.8 Following on from the previous exercise, show how you could impose the corresponding constraint in SQL on the CITY columns in base tables S and P. Give at least two solutions. Compare and contrast those solutions with each other and with your answer to the previous exercise.
8.9 Define supplier numbers as a Tutorial D user defined type. You can assume 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 are numerals denoting a decimal integer in the range 1 to 9999. State any assumptions you make regarding the availability of operators to help with your definition.
8.10 A line segment is a straight line connecting two points in the euclidean plane. Give a corresponding Tutorial D type definition.
8.11 Can you think of a type for which we might want to specify two different possreps? Does it make sense for two or more possreps for the same type each to include a type constraint?
8.12 Can you think of a type for which different possreps might have different numbers of components?
8.13 Which operations might cause constraints CX1-CX9 from the body of the chapter to be violated?
8.14 Does Tutorial D have anything directly analogous to SQL’s base table constraints?
8.15 In SQL, what is it exactly (i.e., formally) that makes base table constraints a little easier to state than their CREATE ASSERTION counterparts? Note: I haven’t covered enough in this book yet to enable you to answer this question. Nevertheless, you might want to think about it now, or possibly use it as a basis for group discussion.
8.16 Following on from the previous question, a base table constraint is automatically regarded as satisfied in SQL if the pertinent base table is empty. Why exactly do you think this is so (I mean, what’s the formal reason)? Does Tutorial D display any analogous behavior?
8.17 In the body of the chapter, I gave a version of constraint CX5 as a base table constraint on table SP. However, I pointed out that it could alternatively have been formulated as such a constraint on base table S, or base table P, or in fact any base table in the database. Give such alternative formulations.
8.18 Constraint CX1 (for example) had the property that it could be checked for a given tuple by examining just that tuple in isolation; constraint CX5 (for example) did not. What is it, formally, that accounts for this difference? What’s the pragmatic significance, if any, of this difference?
8.19 Can you give either a Tutorial D database constraint or an SQL assertion that’s exactly equivalent to the specification KEY{SNO} for relvar S?
8.20 Give an SQL formulation of constraint CX8 from the body of the chapter.
8.21 Using Tutorial D and/or SQL, write constraints for the suppliers-and-parts database to express the following requirements:
All red parts must weigh less than 50 pounds.
Every London supplier must supply part P2.
No two suppliers can be located in the same city.
At most one supplier can be located in Athens at any one time.
There must be at least one London supplier.
At least one red part must weigh less than 50 pounds.
The average supplier status must be at least 10.
No shipment can have a quantity more than double the average of all such quantities.
No supplier with maximum status can be located in the same city as any supplier with minimum status.
Every part must be located in a city in which there is at least one supplier.
Every part must be located in a city in which there is at least one supplier of that part.
Suppliers in London must supply more different kinds of parts than suppliers in Paris.
The total quantity of parts supplied by suppliers in London must be greater than the corresponding total for suppliers in Paris.
No shipment can have a total weight (part weight times shipment quantity) greater than 20,000 pounds.
In each case, state which operations might cause the constraint to be violated.
8.22 Suppose there’s a constraint in effect that says if two suppliers are in the same city, they must have the same status; in other words, suppose relvar S is subject to the functional dependency {CITY} → {STATUS} (I mentioned this possibility in the discussion of constraint CX4 in the body of the chapter). Do either of the following Tutorial D CONSTRAINT statements accurately represent this constraint?
CONSTRAINT CX22a
COUNT ( S { CITY } ) = COUNT ( S { CITY , STATUS } ) ;
CONSTRAINT CX22b
S = JOIN { S { ALL BUT STATUS } , S { CITY , STATUS } } ;8.23 In the body of the chapter, I defined the total database constraint to be a boolean expression of this form:
(RC1) AND (RC2) AND ... AND (RCn) AND TRUE
What’s the significance of that “AND TRUE”?
8.24 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.
8.25 Suppose no cascade delete rule is stated 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 (i.e., without raising the possibility of a referential integrity violation).
8.26 Using the syntax sketched for transition constraints in the section MISCELLANEOUS ISSUES, write transition constraints to express the following requirements:
The total shipment quantity for a given part can never decrease.
Suppliers in Athens can move only to London or Paris, and suppliers in London can move only to Paris.
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?)
8.27 Investigate any SQL product that might be available to you. What semantic optimization does it support, if any?
8.28 Why do you think SQL fails to support type constraints? What are the consequences of this state of affairs?
8.29 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. To what extent do the concepts discussed apply to nonscalar types and/or system defined types?
8.30 Show that any arbitrary UPDATE can be expressed in terms of DELETE and INSERT.