7.2 VIEWs Provide Row- and Column-Level Security 137

FROM Projects
WHERE authorized_user = CURRENT_USER;

Or, if you need to limit access to a team, you can create a table of
teams to which only team managers have access.

CREATE VIEW MyProjects ( )
AS
SELECT
FROM Projects AS P
WHERE CURRENT_USER
IN (SELECT team_user_id
FROM ProjectTeams AS PT
WHERE P.team_nbr = PT.team_nbr);

Another trick is to use the CURRENT_TIMESTAMP or
CURRENT_DATE in VIEWs to get an automatic update to schedules and
other time-related events.

CREATE TABLE AssignmentSchedule
(ssn CHAR(9) NOT NULL
REFERENCES Personnel(ssn)
ON UPDATE CASCADE
ON DELETE CASCADE,
task_code CHAR(5) NOT NULL,
start_date TIMESTAMP NOT NULL,
end_date TIMESTAMP NOT NULL,
CHECK (start_date < end_date),

PRIMARY KEY (upc, start_date));
CREATE VIEW Assignments (now, ssn, task_code)
AS
SELECT CURRENT_TIMESTAMP, ssn, task_code
FROM AssignmentSchedule
WHERE CURRENT_TIMESTAMP BETWEEN start_date AND end_date;

Each time the VIEW is invoked, it will check the clock and see if
anything has changed for you.

138 CHAPTER 7: HOW TO USE VIEWS

7.3 VIEWs Ensure Efficient Access Paths

By coding the appropriate join criteria into the VIEW definition SQL,
you can ensure that the correct join predicate will always be used. Of
course, this technique becomes more useful as the SQL becomes more
complex.

7.4 VIEWs Mask Complexity from the User

Somewhat akin to coding appropriate access into VIEWs, complex SQL
can be coded into VIEWs to mask the complexity from the user. This can
be extremely useful when your shop employs novice SQL users (whether
those users are programmers, analysts, managers, or typical end users).
As an example, consider the code for a relational division. Relational
division is one of the eight basic operations in Codd’s (1979) relational
algebra. The idea is that a divisor table is used to partition a dividend
table and produce a quotient or results table. The quotient table consists
of those values of one column for which a second column had all of the

values in the divisor.
This is easier to explain with an example. We have a table of pilots
and the planes they can fly (dividend); we have a table of planes in the
hangar (divisor); we want the names of the pilots who can fly every plane
(quotient) in the hangar. To get this result, we divide the PilotSkills table
by the planes in the hangar.

CREATE TABLE PilotSkills
(pilot CHAR(15) NOT NULL,
plane CHAR(15) NOT NULL,
PRIMARY KEY (pilot, plane));
CREATE TABLE Hangar
(plane CHAR(15) NOT NULL PRIMARY KEY);

Here is one way to write the query:

CREATE VIEW QualifiedPilots (pilot)
AS
SELECT DISTINCT pilot
FROM PilotSkills AS PS1
WHERE NOT EXISTS
(SELECT *
FROM Hangar

7.5 VIEWs Ensure Proper Data Derivation 139

WHERE NOT EXISTS
(SELECT *
FROM PilotSkills AS PS2
WHERE (PS1.pilot = PS2.pilot)

AND (PS2.plane = Hangar.plane)));

This not the sort of thing that newbie SQL programmers can pull out
of their hats, but they can write “SELECT pilot FROM QualifiedPilots;”
without much trouble. Furthermore, the VIEW definition can be
changed, and the user will never know it. Here is another version of
relational division:

CREATE VIEW QualifiedPilots (pilot)
AS
SELECT PS1.pilot
FROM PilotSkills AS PS1, Hangar AS H1
WHERE PS1.plane = H1.plane
GROUP BY PS1.pilot
HAVING COUNT(PS1.plane) = (SELECT COUNT(plane) FROM Hangar);

7.5 VIEWs Ensure Proper Data Derivation

Another valid usage of VIEWs is to ensure consistent derived data by
creating new columns for VIEWs that are based on arithmetic formulae
(e.g., creating a VIEW that contains a column named “tot_comp,” which
is defined by [salary + commission + bonus]). Because this column name
is at the table level, it can be used in the SELECT of the invoking
SELECT statement. That is, this is illegal:

SELECT emp_id, (salary + commission + bonus) AS tot_comp
FROM Payroll
WHERE tot_comp > 12000.00;

and this is legal:


CREATE VIEW PayrollSummary (emp_id, tot_comp)
AS
SELECT emp_id, (salary + commission + bonus)
FROM PayrollSummary;

140 CHAPTER 7: HOW TO USE VIEWS

followed by:

SELECT emp_id, tot_comp
FROM PayrollSummary
WHERE tot_comp > 12000.00;

Although this is an easy formula, it is a good idea to have a
complicated one in only one place in the schema. It might not be right,
but at least it will be consistent.

7.6 VIEWs Rename Tables and/or Columns

You can rename columns in VIEWs. This is particularly useful if a table
contains arcane or complicated column names. There are some prime
examples of such tables in the schema information tables of most SQL
products. Additionally, if other tables exist with clumsy table and/or
column names, VIEWs can provide a quick solution until you can
rename them. In many SQL products, doing this can require dropping
and recreating the tables.

7.7 VIEWs Enforce Complicated Integrity Constraints


Consider a schema for a chain of stores that has three tables, thus:

CREATE TABLE Stores
(store_nbr INTEGER NOT NULL PRIMARY KEY,
store_name CHAR(35) NOT NULL,
);
CREATE TABLE Personnel
(ssn CHAR(9) NOT NULL PRIMARY KEY,
last_name CHAR(15 NOT NULL,
first_name CHAR(15 NOT NULL,
);

The first two tables explain themselves. The third table shows the
relationship between stores and personnel—namely, who is assigned to
which job at which store and when this happened. Thus:

CREATE TABLE JobAssignments
(store_nbr INTEGER NOT NULL

7.7 VIEWs Enforce Complicated Integrity Constraints 141

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 99),
PRIMARY KEY (store_nbr, ssn, start_date));

Let job_type 0 = “unassigned”, 1 = “stockboy”, and so on, until we get
to 99 = “Store Manager”; we have a rule that each store has one and only
one manager. In full SQL-92 you could write a constraint like this:

CHECK (NOT EXISTS
(SELECT store_nbr
FROM JobAssignments
WHERE job_type = 99))
GROUP BY store_nbr
HAVING COUNT(*) > 1))

But many SQL products do not allow CHECK () constraints that
apply to the table as a whole, and they do not support the scheme-level
CREATE ASSERTION statement. So, how to do this? You might use a
trigger, which will involve—ugh!—procedural code. Despite the SQL/
PSM and other standards, most vendors implement different trigger
models and use their proprietary 4GL language, but, being a fanatic, I
want a pure SQL solution.
Let’s create two tables like this:

CREATE TABLE Job_99_Assignments
(store_nbr INTEGER NOT NULL PRIMARY KEY
REFERENCES Stores (store_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE,
ssn CHAR(9) NOT NULL

REFERENCES Personnel (ssn)
ON UPDATE CASCADE