Chapter 9. SQL and Views

They’re concerned by adverse publicity and that I have to move more into public eye. Problem is to define first the exact view we want to project.

H. R. Haldeman: The Haldeman Diaries: Inside the Nixon White House (1994)

Intuitively, there are several different ways of looking at what a view is, all of which are valid and all of which can be helpful in the right circumstances:

  • A view is a virtual relvar; in other words, it’s a relvar that “looks and feels” just like a base relvar but (unlike a base relvar) doesn’t exist independently of other relvars—rather, it’s defined in terms of such other relvars.

  • A view is a derived relvar; in other words, it’s a relvar that’s explicitly derived (and known to be derived, at least by some people) from certain other relvars. Note: If you’re wondering what the difference is between a derived relvar and a virtual one (see the previous bullet item), I should explain that all virtual relvars are derived but some derived ones aren’t virtual. See the section VIEWS AND SNAPSHOTS later in this chapter.

  • A view is a “window into” the relvars from which it’s derived; thus, operations on the view are to be understood as “really” being operations on those underlying relvars.

  • A view is what some writers call a “canned query” (more precisely, it’s a named relational expression).

As usual, in what follows I’ll discuss these ideas in both relational and SQL terms. Regarding SQL specifically, however, let me remind you of something I said in Chapter 1: A view is a table!—or, as I would prefer to say, a relvar. SQL documentation often uses expressions like “tables and views,” thereby suggesting that tables and views are different things—but they’re not; in many ways, in fact, it’s the whole point about a view that it is a table (just as, in mathematics, the whole point about, e.g., the union or intersection of two sets is that the result is a set). So don’t fall into the common trap of thinking the term table means a base table specifically. People who fall into that trap aren’t thinking relationally, and they’re likely to make mistakes as a consequence; in fact, several such mistakes can be found in the design of SQL itself. Indeed, it could be argued that the very names of the operators CREATE TABLE and CREATE VIEW in SQL are at least a psychological mistake, in that they tend to reinforce both (a) the idea that the term table means a base table specifically and (b) the idea that views and tables are different things. Be on the lookout for confusion in this area.

One last preliminary point: On the question of whether the database should “always” be accessed through views, see the section “SQL Column Naming” in Chapter 3 or the section THE RELIANCE ON ATTRIBUTE NAMES in Chapter 6.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.145.174.183