142 CHAPTER 7: HOW TO USE VIEWS

ON DELETE CASCADE,
start_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_date TIMESTAMP CHECK (start_date <= end_date),
job_type INTEGER DEFAULT 99 NOT NULL
CHECK (job_type = 99));
CREATE TABLE Job_not99_Assignments
(store_nbr INTEGER NOT NULL
REFERENCES Stores (store_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE,
ssn CHAR(9) NOT NULL PRIMARY KEY
REFERENCES Personnel (ssn)
ON UPDATE CASCADE
ON DELETE CASCADE,
start_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_date TIMESTAMP CHECK (start_date <= end_date),
job_type INTEGER DEFAULT 0 NOT NULL
CHECK (job_type BETWEEN 0 AND 98)—no 99 code
);

Then build a UNION-ed VIEW:

CREATE VIEW JobAssignments (store_nbr, ssn, start_date,
end_date, job_type)
AS
(SELECT store_nbr, ssn, start_date, end_date, job_type
FROM Job_not99_Assignments
UNION ALL

SELECT store_nbr, ssn, start_date, end_date, job_type
FROM Job_99_Assignments)

The key and job_type constraints in each table working together will
guarantee only one manager per store. The next step is to add INSTEAD
OF triggers to the VIEW, so that the users can insert, update, and delete
from it easily.
As an exercise for the reader: How would you ensure that no store has
more than two assistant managers?

7.8 Updatable VIEWs 143

7.8 Updatable VIEWs

The SQL-92 standard is actually conservative about which VIEWs are
updatable. They have to be based on the following:
1. A SELECT statement on one and only one table, but the VIEW
can be defined on several layers of VIEWs on top of VIEWs.
2. The VIEW must include all of the columns of a UNIQUE or
PRIMARY KEY constraint in the base table. This guarantees
that all of the rows in the VIEW map back to one and only one
row in the base table from which it is derived.
3. All base table columns not shown in the VIEW must have
default values or be NULL-able. The reason for that is obvious:
You have to delete or insert a complete row into the base table,
so the system must be able to construct such a row.
However, other VIEWs are updatable, and some vendors support
more than the basic version given in the SQL-92 standard. The VIEW
must have an INSERT, UPDATE, and DELETE rule under the covers,
which maps its rows back to a single row in the base table(s).


7.8.1 WITH CHECK OPTION clause

Another feature, which is not used enough, is the WITH CHECK
OPTION clause on a VIEW. It is a bit tricky, when you nest VIEWs inside
each other, but the idea is that an UPDATE or INSERT INTO statement
cannot leave the scope of the set selected by the updatable VIEW. For
example, we have a VIEW like this:

CREATE VIEW NewYorkSalesmen (ssn, name, )
AS
SELECT ssn, name,
FROM Salesmen
WHERE city = 'New York';
And we update it, thus:
UPDATE NewYorkSalesmen
SET city = 'Boston';

144 CHAPTER 7: HOW TO USE VIEWS

The result would be that “NewYorkSalesmen” would be empty when
you come back to it. This is probably not desirable. However, if we had
defined the updatable VIEW as:

CREATE VIEW NewYorkSalesmen (ssn, name, )
AS
SELECT ssn, name,
FROM Salesmen
WHERE city = 'New York'
WITH CHECK OPTION;


the system would test the update for a violation and would reject it.

7.8.2 INSTEAD OF Triggers

Because some VIEWs cannot be updated, you can add INSTEAD OF
triggers to fool the users. This trigger is executed instead of the INSERT,
UPDATE, or DELETE action, thus overriding the actions of the
triggering statements. The syntax will vary from product to product, but
expect something like this:

CREATE TRIGGER <trigger name>
ON <table name >
[BEFORE | AFTER | INSTEAD OF]
[INSERT| DELETE | UPDATE]
AS [<sql stmt> | BEGIN ATOMIC {<sql stmt>;} END]

For obvious reasons, only one INSTEAD OF trigger per INSERT,
UPDATE, or DELETE statement can be defined on a table or VIEW.
However, it is possible to define VIEWs on VIEWs where each VIEW has
its own INSTEAD OF trigger. INSTEAD OF triggers are not allowed on
updatable VIEWs that have a WITH CHECK OPTION.
You can also define INSTEAD OF triggers on base tables, but this is a
bit weird because you have BEFORE and AFTER triggers.

7.9 Have a Reason for Each VIEW

Rationale:

VIEWs should be created only when they achieve a specific, reasonable

goal. Each VIEW should have a specific application or business
requirement that it fulfills before it is created. That requirement should

7.11 Synchronize VIEWs with Base Tables 145

be documented somewhere, preferably in a data dictionary or possibly as
a remark in the VIEW declaration.

Exceptions:

None

7.10 Avoid VIEW Proliferation

Rationale:

The proliferation avoidance rule is based on common sense. Why create
something that is not needed? It just takes up space that could be used
for something that is needed.
Whenever a SQL object is created, additional entries are placed in the
schema information tables. Creating needless schema objects causes
what Craig Mullins calls

catalog clutter

. For example, in DB2, every
unnecessary VIEW that is created in SQL will potentially insert rows into
four VIEW-specific schema information tables (i.e., SYSVTREE,
SYSVLTREE, SYSVIEWS, and SYSVIEWDEP) and three table-specific
schema information tables (i.e., SYSTABLES, SYSTABAUTH, and

SYSCOLUMNS).
It is a good idea to use a utility program to see if you have VIEWs that
are not referenced anywhere. Another good idea is to see if you have
VIEWs that do the same thing, or almost the same thing, so you can
remove one of them.

Exceptions:

None

7.11 Synchronize VIEWs with Base Tables

Rationale:

Whenever a base table changes, all VIEWs that depend on that base table
should be analyzed to determine if the change affects them. All VIEWs
should remain logically pure. The VIEW should remain useful for the
specific reason you created it.
For example, say a VIEW was created to control employee access to a
project and we add the new badge numbers to the Personnel table. This
badge number probably should also be added to the access VIEW. The
badge number column can be added to the Personnel table immediately
and then to the VIEW at the earliest convenience of the development
team.

146 CHAPTER 7: HOW TO USE VIEWS

The synchronization rule requires that strict change impact analysis
procedures be in place. Every change to a base table should trigger the
usage of these utility programs and maintenance procedures.


Exceptions:

None

7.12 Improper Use of VIEWs

Over the years, VIEWs have been used for other purposes that made
sense at the time but have been rendered obsolete with the advent of
new DBMS functionality.

7.12.1 VIEWs for Domain Support

Rationale:

It is a sad fact of life that most RDBMS do not support domains.
Domains were in the original relational model and should have been part
of SQL from the start. A domain basically identifies the valid range of
values that a column can contain. Of course, domains are more complex
than this simple explanation. For example, only columns pooled from
the same domain should be able to be compared within a predicate
(unless explicitly overridden).
Some of the functionality of domains can be implemented using
VIEWs and the WITH CHECK OPTION clause, which ensures the
update integrity of VIEWs. This will guarantee that all data inserted or
updated using the VIEW will adhere to the VIEW specification.

CREATE VIEW Personnel (ssn, name, sex, )
AS
SELECT ssn, name, sex,

FROM ISBN0008 –- a name you did not want anyone to see
WHERE sex IN (0, 1, 2) –- iso codes
WITH CHECK OPTION;

Now, this method of using VIEWs to simulate domains is still viable,
but a better technique to provide the same functionality is available—
namely, CHECK() constraints.

CREATE TABLE Personnel
(ssn CHAR(9) NOT NULL, name, sex, )