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

LS
┌─────┬───────┬────────┬────────┐
│ SNO │ SNAME │ STATUS │ CITY │
├═════┼───────┼────────┼────────┤
│ S1 │ Smith │ 20 │ London │
│ S4 │ Clark │ 20 │ London │
└─────┴───────┴────────┴────────┘
NLS
┌─────┬───────┬────────┬────────┐
│ SNO │ SNAME │ STATUS │ CITY │
├═════┼───────┼────────┼────────┤
│ S2 │ Jones │ 10 │ Paris │
│ S3 │ Blake │ 30 │ Paris │
│ S5 │ Adams │ 30 │ Athens │
└─────┴───────┴────────┴────────┘
Fig. 1.2: Views LS and NLS—sample values
Views LS and NLS are the ones I want to use in this initial chapter as the basis for my
motivating example. In essence, what I want to do with that example is try to give you some
preliminary idea as to why I believe that—contrary to popular opinion and most conventional
wisdom in this area— all views are updatable. (Note, however, that I must immediately qualify
this very strong claim by making it clear that I'm necessarily speaking rather loosely at this
stage. Later chapters will elaborate.)
THE PRINCIPLE OF INTERCHANGEABILITY
So far, then, table S is a base table and tables LS and NLS are views. Observe now, however,
that it could have been the other way around —that is, I could have made LS and NLS base
tables and S a view, like this:
CREATE TABLE LS
( SNO VARCHAR(5) NOT NULL ,
SNAME VARCHAR(25) NOT NULL ,
STATUS INTEGER NOT NULL ,
CITY VARCHAR(20) NOT NULL ,
UNIQUE ( SNO ) ) ;
CREATE TABLE NLS
( SNO VARCHAR(5) NOT NULL ,
SNAME VARCHAR(25) NOT NULL ,
STATUS INTEGER NOT NULL ,
CITY VARCHAR(20) NOT NULL ,
UNIQUE ( SNO ) ) ;
CREATE VIEW S AS
( SELECT SNO , SNAME , STATUS , CITY
FROM LS
UNION
SELECT SNO , SNAME , STATUS , CITY
FROM NLS ) ;