Database Reference
In-Depth Information
involved than the full database, its use can represent a great simplification. Views also provide a measure of
security because omitting sensitive tables or fields from a view will render them unavailable to anyone who is
accessing the database via that view.
To illustrate the idea of a view, suppose Juan is interested in the part number, part description, units on
hand, and unit price for Premiere Products parts that are in class HW. He is not interested in any of the
other fields in the Part table, nor is he interested in any of the rows that correspond to parts in other item
classes. Viewing this data would be simpler for Juan if the other rows and fields were not even present.
Although you cannot change the structure of the Part table and omit some of its rows just for Juan, you
can do the next best thing. You can provide him with a view that consists of precisely the rows and fields he
needs to access. Using SQL, the following CREATE VIEW command creates the view that Juan can use to see
the data he needs.
120
CREATE VIEW Housewares AS
SELECT PartNum, Description, OnHand, Price
FROM Part
WHERE Class=
'
HW
'
;
The SELECT command that creates the view, which is called the defining query, indicates what to
include in the view. Conceptually, given the current data in the Premiere Products database, this view will
contain the data shown in Figure 4-1. The data does not really exist in this form, however, nor will it ever
exist in this form. It is tempting to think that when this view is used, the query is executed and will produce
some sort of temporary table named Housewares, which Juan then could access, but that is not what
happens.
Housewares
PartNum
Description
OnHand
Price
AT94
Iron
50
$24.95
DL71
Cordless Drill
21
$129.95
FD21
Stand Mixer
22
$159.95
FIGURE 4-1
Housewares view
Instead, the query acts as a sort of window into the database, as shown in Figure 4-2. As far as Juan is
concerned, the entire database is just the darker-shaded portion of the Part table. Juan can see any change
that affects the darker portion of the Part table, but he is totally unaware of any other changes that are made
in the database.
