A Motivating Example - View Updating and Relational Theory - page 2

 Table S represents suppliers under contract . Each supplier has one supplier number
(SNO), unique to that supplier; one name (SNAME), not necessarily unique (though the
sample values shown in Fig. 1.1 do happen to be unique); one status value (STATUS); and
one location (CITY). Note: In the rest of this topic I'll abbreviate “suppliers under
contract,” most of the time, to just suppliers .

 Table P represents kinds of parts . Each kind of part has one part number (PNO), which is
unique; one name (PNAME); one color (COLOR); one weight (WEIGHT); and one
location where parts of that kind are stored (CITY). Note: In the rest of this topic I'll
abbreviate “kinds of parts,” most of the time, to just parts .

 Table SP represents shipments —it shows which parts are shipped, or supplied, by which
suppliers. Each shipment has one supplier number (SNO); one part number (PNO); and
one quantity (QTY). Also, there's at most one shipment at any given time for a given
supplier and given part, and so the combination of supplier number and part number is
unique to any given shipment. Note: In the rest of this topic I'll assume QTY values are
always greater than zero.

Now I want to focus on table S specifically; for the rest of this chapter, in fact, I'll mostly
ignore tables P and SP, except for an occasional remark here and there. Here's an SQL
definition for that table S:

CREATE TABLE S
( SNO VARCHAR(5) NOT NULL ,
SNAME VARCHAR(25) NOT NULL ,
STATUS INTEGER NOT NULL ,
CITY VARCHAR(20) NOT NULL ,
UNIQUE ( SNO ) ) ;

As I've said, table S is a base table, but of course we can define any number of views “on
top of” that base table. Here are a couple of examples—LS (“London suppliers”) and NLS (“non
London suppliers”):
CREATE VIEW LS /* London suppliers */ AS
( SELECT SNO , SNAME , STATUS , CITY
FROM S
WHERE CITY = 'London' ) ;

CREATE VIEW NLS /* non London suppliers */ AS
( SELECT SNO , SNAME , STATUS , CITY
FROM S
WHERE CITY <> 'London' ) ;
Sample values for these views corresponding to the value of table S in Fig. 1.1 are shown
in Fig. 1.2.