This chapter is from the book
This chapter is from the book
This chapter is from the book
Advantages of Views
To clarify the advantages of using views, consider several possible users of the bookbiz database. Let's say that the promotion manager needs to know which authors are connected to which books and who has first, second, and third billing on the cover. Prices, sales, advances, royalties, and personal addresses are not of interest, but the promotion manager does need some information from each of the three tables: titles, authors, and titleauthors. Without a view, a query something like the following might be used:
SQL
select titles.title_id, au_ord, au_lname, au_fname from authors, titles, titleauthors where authors.au_id = titleauthors.au_id and titles.title_id = titleauthors.title_id
This query involves a lot of typing, and there are any number of places where an error might slip in. Quite a bit of knowledge of the database is required, too. Creating a view called books that is based on this SELECT statement would facilitate the use of this particular set of data. Here's the statement that creates the view (unlike the oaklanders view, this one inherits column names from the SELECT clause):
SQL
create view books as select titles.title_id, au_ord, au_lname, au_fname from authors, titles, titleauthors where authors.au_id = titleauthors.au_id and titles.title_id = titleauthors.title_id
Now the promotion manager can use the view to get the same results without thinking about joins or select lists or search conditions:
SQL
select * from books title_id au_ord au_lname au_fname ======== ====== ============================= ========== PC8888 1 Dull Ann PC8888 2 Hunter Sheryl BU1032 1 Bennet Abraham BU1032 2 Green Marjorie PS7777 1 Locksley Chastity PS3333 1 White Johnson BU1111 1 MacFeather Stearns BU1111 2 O'Leary Michael MC2222 1 del Castillo Innes TC7777 1 Yokomoto Akiko TC7777 2 O'Leary Michael TC7777 3 Gringlesby Burt TC4203 1 Blotchet-Halls Reginald PC1035 1 Carson Cheryl BU2075 1 Green Marjorie PS2091 1 Ringer Albert PS2091 2 Ringer Anne PS2106 1 Ringer Albert MC3021 1 DeFrance Michel MC3021 2 Ringer Anne TC3218 1 Panteley Sylvia BU7832 1 Straight Dick PS1372 1 Karsen Livia PS1372 2 MacFeather Stearns PC9999 1 Locksley Chastity [25 rows]
An accountant might want to create a different view. Author order on the title page doesn't matter, just the bottom line: To whom should checks be written and for how much. The query involves computing how many books were sold at what price, with what percentage rate for each author:
SQL
select au_lname, au_fname, sum(price*ytd_sales*royalty*royaltyshare) as Total_Income from authors, titles, titleauthors, roysched where authors.au_id = titleauthors.au_id and titles.title_id = titleauthors.title_id and titles.title_id = roysched.title_id and ytd_sales between lorange and hirange group by au_lname, au_fname
If the accountant uses this SELECT statement to create a view named royaltychecks, the equivalent query is this:
SQL
select * from royaltychecks
The results (who gets a check and for how much) are as follows:
Results
au_lname au_fname Total_Income ================== ===================== =============== Bennet Abraham 7368.54 Blotchet-Halls Reginald 46390.01 Carson Cheryl 60336.16 DeFrance Michel 43346.33 del Castillo Innes 7312.76 Dull Ann 8190.00 Green Marjorie 48688.14 Gringlesby Burt 3684.27 Hunter Sheryl 8190.00 Karsen Livia 1169.72 Locksley Chastity 6001.46 MacFeather Stearns 5494.60 O'Leary Michael 7087.40 Panteley Sylvia 1535.63 Ringer Albert 2881.97 Ringer Anne 17142.04 Straight Dick 12280.91 White Johnson 12211.93 Yokomoto Akiko 4912.36 (19 rows affected)
Finally, consider an executive at the parent publishing company who needs to find out how the different categories of books are doing at each subsidiary. The executive can use a query something like this:
SQL
select pub_id, type, sum(price*ytd_sales), avg(price), avg(ytd_sales) from titles group by pub_id, type
However, the executive may not want to bother with anything so complex. A much simpler query is
SQL
select * from currentinfo PUB# TYPE INCOME AVG_PRICE AVG_SALES ==== ============ ========== ========== ========== 1389 popular_comp 40901.00 41.48 6437.50 1389 business 330696.30 27.31 4022.00 0736 psychology 244504.92 25.70 1987.80 0877 mod_cook 349915.22 21.49 12139.00 0877 trad_cook 469522.50 30.96 6522.00 0736 business 243198.78 12.99 18722.00 0877 (NULL) (NULL) (NULL) (NULL) [7 rows]
Using this view, the busy executive can quickly see which publishing lines are making money, and he or she can compare the relationship among income, average price, and average sales.
SQL Variants
You might see some formatting differences in results, depending on what datatypes your systems uses and how your system displays numbers. Microsoft SQL Server, for example, shows AVG_SALES values as whole numbers:
SQL Server
PUB# TYPE INCOME AVG_PRICE AVG_SALES ---- ------------ --------------- ------------ --------- 0877 NULL NULL NULL NULL 0736 business 243198.7800 12.9900 18722 1389 business 330696.3000 27.3100 4022 0877 mod_cook 349915.2200 21.4900 12139 1389 popular_comp 540901.0000 41.4750 6437 0736 psychology 244504.9200 25.7040 1987 0877 trad_cook 469522.5000 30.9633 6522 (7 row(s) affected)
As the previous examples demonstrate, you can use views to focus, simplify, and customize each user's perception of the database. In addition, views provide a security mechanism. Finally, they can protect users from the effects of changes in the database structure, providing independence.
Focus, Simplification, and Customization
Views allow the promotion manager, the accountant, and the executive in the previous examples to focus in on the particular data and tasks. No extraneous or distracting information gets in the way.
Working with the data is simpler, too. When favorite joins, projections, and/or selections are already defined as views, it's relatively simple to add other clauses. Constructing the entire underlying query, on the other hand, could be a daunting prospect.
Views are a good way of customizing a database or tailoring it to suit a variety of users with dissimilar interests and skill levels. Our three users see the data in different ways, even when they're looking at the same three tables at the same time.
Security
Views provide security by hiding sensitive or irrelevant parts of the database. If permissions are set up properly, the accountant can find out how big an author's check should be, but can't look at the underlying figures or compare his or her own paycheck to a coworker's. You can restrict the accountant's access in the database to just those views that are relevant to accounting. Using views as a security mechanism is discussed in more detail in Chapter 10.
Independence
Finally, there's the issue of independence. From time to time, you may have to modify the structure of the database, but there's no reason that users should suffer from these changes. For example, say you split the titles table into two new tables and drop titles. The new tables are shown in Figure 9.1.
)
Figure 9.1 Splitting a Table into Two Tables
Notice that the old titles table can be regenerated by joining the title_id columns of the two new tables. To shield the changed structure of the database from users, you can create a view that is the join of the two new tables (Figure 9.2). You can even name it titles (although here it's called titlesview). In most systems, doing so means you won't have to change any views that were based wholly or in part on the old titles.
)
Figure 9.2 Uniting Two Base Tables in a Single View
Restrictions on updating data through views (explained later in this chapter) limit the independence of this kind of view. Certain data modification statements on the new titles may not be allowed.