Datasheet

17
Chapter 1: Relational Database Fundamentals
Say, for example, that you’re working with a database that has a CUSTOMER
table and an INVOICE table. The CUSTOMER table has the columns
CustomerID, FirstName, LastName, Street, City, State, Zipcode, and
Phone. The INVOICE table has the columns InvoiceNumber, CustomerID,
Date, TotalSale, TotalRemitted, and FormOfPayment.
A national sales manager wants to look at a screen that contains only the
customer’s first name, last name, and telephone number. Creating from the
CUSTOMER table a view that contains only the FirstName, LastName, and
Phone columns enables the manager to view what he or she needs without
having to see all the unwanted data in the other columns. Figure 1-4 shows
the derivation of the national sales manager’s view.
Figure 1-4:
The sales
manager’s
view derives
from the
CUSTOMER
table.
CUSTOMER Table
Customer ID
FirstName
LastName
Street
City
State
Zipcode
Phone
SALES_MGR View
FirstName
LastName
Phone
INVOICE Table
InvoiceNumber
CustomerID
Date
TotalSale
TotalRemitted
FormOfPayment
A branch manager may want to look at the names and phone numbers of
all customers whose zip codes fall between 90000 and 93999 (southern and
central California). A view that places a restriction on the rows it retrieves, as
well as the columns it displays, does the job. Figure 1-5 shows the sources for
the columns in the branch manager’s view.
The accounts-payable manager may want to look at customer names
from the CUSTOMER table and Date, TotalSale, TotalRemitted, and
FormOfPayment from the INVOICE table, where TotalRemitted is less
than TotalSale. The latter would be the case if full payment hasn’t yet
been made. This need requires a view that draws from both tables. Figure 1-6