9.1 Define a view consisting of supplier-number / part-number pairs for suppliers and parts that aren’t colocated. Give both Tutorial D and SQL definitions.
9.2 Let view LSSP be defined as follows (SQL):
CREATE VIEW LSSP AS ( SELECT SNO , SNAME , STATUS , PNO , QTY
FROM S NATURAL JOIN SP
WHERE CITY = 'London' ) ;Here’s a query on this view:
SELECT DISTINCT STATUS , QTY FROM LSSP
WHERE PNO IN ( SELECT PNO FROM P WHERE CITY <> 'London' )What might the query that’s actually executed on the underlying base tables look like?
9.3 What key(s) does view LSSP from Exercise 9.2 have? What’s the predicate for that view?
9.4 Given the following Tutorial D view definition—
VAR HP VIRTUAL ( P WHERE WEIGHT > 14.0 ) KEY { PNO } ;—show the converted form after the substitution procedure has been applied for each of the following expressions and statements:
9.5 Give SQL solutions to Exercise 9.4.
9.6 Give as many reasons as you can think of for wanting to be able to declare keys for a view.
9.7 Using either the suppliers-and-parts database or any other database you happen to be familiar with, give some further examples (over and above the London vs. non London suppliers example, that is) to illustrate the point that which relvars are base and which virtual is largely arbitrary.
9.8 In the body of the chapter, in the discussion of logical data independence, I discussed the possibility of restructuring—i.e., changing the logical structure of—the suppliers-and-parts database by replacing base relvar S by two of its restrictions (LS and NLS). However, I also observed that such a replacement wasn’t a completely trivial matter. Why not?
9.9 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?
What updates on what views does that product support? Be as precise as you can in your answer. Are the view updating rules in that product identical to those in the SQL standard?
More generally, in what ways—there will be some!—does that product violate The Principle of Interchangeability?
9.10 Distinguish between views and snapshots. Does SQL support snapshots? Does any product that you’re aware of?
9.11 What’s a “materialized view”? Why is the term deprecated?
9.12 Consider the suppliers-and-parts database, but ignore the parts relvar for simplicity. Here in outline are two possible designs for suppliers and shipments:
Design a. is as usual. In Design b., by contrast, relvar SSP contains a tuple for every shipment, giving the applicable part number and quantity and full supplier details, and relvar XSS contains supplier details for suppliers who supply no parts at all. (Are these designs information equivalent?) Write view definitions to express Design b. as views over Design a. and vice versa. Also, show the applicable constraints for each design. Does either design have any obvious advantages over the other? If so, what are they?
9.13 Following on from the previous exercise: In the body of the chapter, I said two database designs were information equivalent if they represented the same information (meaning that for every query on one, there’s a logically equivalent query on the other). But can you pin down this notion more precisely?
9.14 Views are supposed to provide logical data independence. But didn’t I say in Chapter 6 that a hypothetical mechanism called “public tables” was supposed to perform that task? How do you account for the discrepancy?