Transcript Chapter 24

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 24- 1
Chapter 24
Enhanced Data Models for
Advanced Applications
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Outline





Recap of SQL
Advanced SQL
Active database & Triggers
Assertion
Views
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 24- 3
Recap of SQL Queries


A query in SQL can consist of up to six clauses, but only
the first two, SELECT and FROM, are mandatory. The
clauses are specified in the following order:
SELECT
<attribute list>
FROM
<table list>
[WHERE
<condition>]
[GROUP BY
<grouping attribute(s)>]
[HAVING
<group condition>]
[ORDER BY
<attribute list>]
There are three SQL commands to modify the database:
INSERT, DELETE, and UPDATE
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 8- 4
Aliases

In SQL, we can use the same name for two (or
more) attributes as long as the attributes are in
different relations
A query that refers to two or more attributes with
the same name must qualify the attribute name
with the relation name by prefixing the relation
name to the attribute name
Example:

EMPLOYEE.LNAME, DEPARTMENT.DNAME


Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 8- 5
ALIASES


Some queries need to refer to the same relation twice
 In this case, aliases are given to the relation name
Query 8: For each employee, retrieve the employee's name, and the
name of his or her immediate supervisor.
Q8: SELECT
FROM
WHERE


E.FNAME, E.LNAME, S.FNAME, S.LNAME
EMPLOYEE E S
E.SUPERSSN=S.SSN
In Q8, the alternate relation names E and S are called aliases or
tuple variables for the EMPLOYEE relation
We can think of E and S as two different copies of EMPLOYEE; E
represents employees in role of supervisees and S represents
employees in role of supervisors
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 8- 6
ALIASES (contd.)


Aliasing can also be used in any SQL query for
convenience
Can also use the AS keyword to specify aliases
Q8:
SELECT
FROM
WHERE
E.FNAME, E.LNAME,
S.FNAME, S.LNAME
EMPLOYEE AS E,
EMPLOYEE AS S
E.SUPERSSN=S.SSN
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 8- 7
USE OF DISTINCT



SQL does not treat a relation as a set; duplicate tuples
can appear
To eliminate duplicate tuples in a query result, the
keyword DISTINCT is used
For example, the result of Q11 may have duplicate
SALARY values whereas Q11A does not have any
duplicate values
Q11:
Q11A:
SELECT
FROM
SELECT
FROM
SALARY
EMPLOYEE
DISTINCT SALARY
EMPLOYEE
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 8- 8
SET OPERATIONS




SQL has directly incorporated some set operations
There is a union operation (UNION), and in some
versions of SQL there are set difference (MINUS) and
intersection (INTERSECT) operations
The resulting relations of these set operations are sets of
tuples; duplicate tuples are eliminated from the result
The set operations apply only to union compatible
relations; the two relations must have the same attributes
and the attributes must appear in the same order
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 8- 9
SET OPERATIONS (contd.)

Query 4: Make a list of all project numbers for projects that involve an
employee whose last name is 'Smith' as a worker or as a manager of
the department that controls the project.
Q4:
(SELECT
FROM
WHERE
UNION
(SELECT
FROM
WHERE
PNAME
PROJECT, DEPARTMENT,
EMPLOYEE
DNUM=DNUMBER AND
MGRSSN=SSN AND LNAME='Smith')
PNAME
PROJECT, WORKS_ON, EMPLOYEE
PNUMBER=PNO AND
ESSN=SSN AND NAME='Smith')
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 8- 10
NESTING OF QUERIES

A complete SELECT query, called a nested query, can be
specified within the WHERE-clause of another query,
called the outer query


Many of the previous queries can be specified in an
alternative form using nesting
Query 1: Retrieve the name and address of all employees
who work for the 'Research' department.
Q1:SELECT
FROM
WHERE
FROM
WHERE
FNAME, LNAME, ADDRESS
EMPLOYEE
DNO IN (SELECT DNUMBER
DEPARTMENT
DNAME='Research' )
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 8- 11
NESTING OF QUERIES (contd.)






The nested query selects the number of the 'Research'
department
The outer query select an EMPLOYEE tuple if its DNO
value is in the result of either nested query
The comparison operator IN compares a value v with a
set (or multi-set) of values V, and evaluates to TRUE if v
is one of the elements in V
In general, we can have several levels of nested queries
A reference to an unqualified attribute refers to the
relation declared in the innermost nested query
In this example, the nested query is not correlated with
the outer query
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 8- 12
CORRELATED NESTED QUERIES


If a condition in the WHERE-clause of a nested query references an
attribute of a relation declared in the outer query, the two queries are
said to be correlated
 The result of a correlated nested query is different for each tuple
(or combination of tuples) of the relation(s) the outer query
Query 12: Retrieve the name of each employee who has a dependent
with the same first name as the employee.
Q12: SELECT
FROM
WHERE
E.FNAME, E.LNAME
EMPLOYEE AS E
E.SSN IN
(SELECT
ESSN
FROM
DEPENDENT
WHERE
ESSN=E.SSN AND
E.FNAME=DEPENDENT_NAME)
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 8- 13
CORRELATED NESTED QUERIES
(contd.)


In Q12, the nested query has a different result in the outer
query
A query written with nested SELECT... FROM...
WHERE... blocks and using the = or IN comparison
operators can always be expressed as a single block
query. For example, Q12 may be written as in Q12A
Q12A:
SELECT
FROM
WHERE
E.FNAME, E.LNAME
EMPLOYEE E, DEPENDENT D
E.SSN=D.ESSN AND
E.FNAME=D.DEPENDENT_NAME
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 8- 14
CORRELATED NESTED QUERIES
(contd.)

The original SQL as specified for SYSTEM R also had a
CONTAINS comparison operator, which is used in
conjunction with nested correlated queries



This operator was dropped from the language, possibly
because of the difficulty in implementing it efficiently
Most implementations of SQL do not have this operator
The CONTAINS operator compares two sets of values, and
returns TRUE if one set contains all values in the other set

Reminiscent of the division operation of algebra
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 8- 15
CORRELATED NESTED QUERIES
(contd.)

Query 3: Retrieve the name of each employee who works
on all the projects controlled by department number 5.
Q3:
SELECT
FROM
WHERE (
FNAME, LNAME
EMPLOYEE
(SELECT
PNO
FROM
WORKS_ON
WHERE
SSN=ESSN)
CONTAINS
(SELECT
PNUMBER
FROM
PROJECT
WHERE
DNUM=5) )
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 8- 16
CORRELATED NESTED QUERIES
(contd.)


In Q3, the second nested query, which is not
correlated with the outer query, retrieves the
project numbers of all projects controlled by
department 5
The first nested query, which is correlated,
retrieves the project numbers on which the
employee works, which is different for each
employee tuple because of the correlation
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 8- 17
Three Types of Subquery
1.
2.
3.
A scalar subquery returns a single column and
single row
A row subquery returns multiple columns but a
single row
A table subquery returns one or more columns
and multiple rows.
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 24- 18
Illegal Subqueries?
1) WHERE branchNo =
(SELECT branchNo
FROM Branch
WHERE street = ‘163 Main St’);
2) WHERE salary > AVG(salary)
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 24- 19
Subquery
List properties handled by staff at ‘163 Main St’.
SELECT propertyNo, street, city, postcode, type,
rooms, rent
FROM PropertyForRent
WHERE staffNo IN
(SELECT staffNo
FROM Staff
WHERE branchNo =
(SELECT branchNo
FROM Branch
WHERE street = ‘163 Main St’));
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 24- 20
Subquery
List all staff whose salary is greater than the average
salary, and show by how much.
SELECT staffNo, fName, lName, position,
salary – (SELECT AVG(salary) FROM Staff) As
SalDiff
FROM Staff
WHERE salary >
(SELECT AVG(salary)
FROM Staff);
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 24- 21
Subquery
select firstname, lastname, salary
from employee e1
where (salary) in(select max(e2.salary)
from employee e2
where e1.workdept = e2.workdept)
order by salary desc
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 24- 22
Subquery
select firstname, lastname, salary
from employee e1
where (salary, workdept) in
(select max(e2.salary), e2.workdept
from employee e2
group by e2.workdept)
order by salary desc
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 24- 23
ANY and ALL

ANY and ALL may be used with subqueries that
produce a single column of numbers.

With ALL, condition will only be true if it is
satisfied by all values produced by subquery.

With ANY, condition will be true if it is satisfied
by any values produced by subquery.

If subquery is empty, ALL returns true, ANY
returns false.

SOME may be used in place of ANY.
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
24
Example 5.22 Use of ANY/SOME
Find staff whose salary is larger than salary
of at least one member of staff at branch
B003.
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary > SOME
(SELECT salary
FROM Staff
WHERE branchNo = ‘B003’);
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
25
Example 5.23 Use of ALL
Find staff whose salary is larger than salary of
every member of staff at branch B003.
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary > ALL
(SELECT salary
FROM Staff
WHERE branchNo = ‘B003’);
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
26
THE EXISTS FUNCTION

EXISTS is used to check whether the result of a
correlated nested query is empty (contains no
tuples) or not

We can formulate Query 12 in an alternative form
that uses EXISTS as Q12B
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 8- 27
THE EXISTS FUNCTION (contd.)

Query 12: Retrieve the name of each employee
who has a dependent with the same first name as
the employee.
Q12B:
SELECT
FROM
WHERE
FNAME, LNAME
EMPLOYEE
EXISTS (SELECT
FROM
WHERE
AND
*
DEPENDENT
SSN=ESSN
FNAME=DEPENDENT_NAME)
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 8- 28
THE EXISTS FUNCTION (contd.)

Query 6: Retrieve the names of employees who have no
dependents.
Q6:

SELECT
FROM
WHERE
FNAME, LNAME
EMPLOYEE
NOT EXISTS (SELECT
*
FROM
DEPENDENT
WHERE
SSN=ESSN)
In Q6, the correlated nested query retrieves all
DEPENDENT tuples related to an EMPLOYEE tuple. If
none exist, the EMPLOYEE tuple is selected

EXISTS is necessary for the expressive power of SQL
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 8- 29
EXPLICIT SETS


It is also possible to use an explicit
(enumerated) set of values in the WHEREclause rather than a nested query
Query 13: Retrieve the social security numbers of
all employees who work on project number 1, 2,
or 3.
Q13:
SELECT
FROM
WHERE
DISTINCT ESSN
WORKS_ON
PNO IN (1, 2, 3)
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 8- 30
NULLS IN SQL QUERIES



SQL allows queries that check if a value is NULL (missing
or undefined or not applicable)
SQL uses IS or IS NOT to compare NULLs because it
considers each NULL value distinct from other NULL
values, so equality comparison is not appropriate.
Query 14: Retrieve the names of all employees who do
not have supervisors.
Q14:

SELECT
FNAME, LNAME
FROM
EMPLOYEE
WHERE
SUPERSSN IS NULL
Note: If a join condition is specified, tuples with NULL values
for the join attributes are not included in the result
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 8- 31
Joined Relations Feature
in SQL2

Can specify a "joined relation" in the FROMclause


Looks like any other relation but is the result of a
join
Allows the user to specify different types of joins
(regular "theta" JOIN, NATURAL JOIN, LEFT
OUTER JOIN, RIGHT OUTER JOIN, CROSS
JOIN, etc)
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 8- 32
Joined Relations Feature
in SQL2 (contd.)

Examples:
Q8:SELECT
FROM
WHERE

E.FNAME, E.LNAME, S.FNAME, S.LNAME
EMPLOYEE E S
E.SUPERSSN=S.SSN
can be written as:
Q8:SELECT
FROM
E.FNAME, E.LNAME, S.FNAME, S.LNAME
(EMPLOYEE E LEFT OUTER JOIN
EMPLOYEES ON E.SUPERSSN=S.SSN)
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 8- 33
Joined Relations Feature
in SQL2 (contd.)

Examples:
Q1:SELECT
FNAME, LNAME, ADDRESS
FROM EMPLOYEE, DEPARTMENT
WHERE
DNAME='Research' AND DNUMBER=DNO

could be written as:
Q1:SELECT
FROM
WHERE

FNAME, LNAME, ADDRESS
(EMPLOYEE JOIN DEPARTMENT
ON DNUMBER=DNO)
DNAME='Research’
or as:
Q1:SELECT
FNAME, LNAME, ADDRESS
FROM
(EMPLOYEE NATURAL JOIN
DEPARTMENT
AS DEPT(DNAME, DNO, MSSN, MSDATE)
WHERE
DNAME='Research’
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 8- 34
Joined Relations Feature
in SQL2 (contd.)

Another Example: Q2 could be written as follows;
this illustrates multiple joins in the joined tables
Q2:
SELECT
FROM
WHERE
PNUMBER, DNUM, LNAME,
BDATE, ADDRESS
(PROJECT JOIN
DEPARTMENT ON
DNUM=DNUMBER) JOIN
EMPLOYEE ON
MGRSSN=SSN) )
PLOCATION='Stafford’
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 8- 35
Joining

SQL provides alternative ways to specify joins:
FROM Client c JOIN Viewing v ON c.clientNo = v.clientNo
FROM Client JOIN Viewing USING clientNo
FROM Client NATURAL JOIN Viewing

In each case, FROM replaces original FROM and
WHERE. However, first produces table with two
identical clientNo columns.
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 24- 36
Join Three Tables

Alternative formulation for FROM and WHERE:
SELECT b.branchNo, b.city, s.staffNo, fName, lName,
propertyNo
FROM Branch b, Staff s, PropertyForRent p
WHERE b.branchNo = s.branchNo AND
s.staffNo = p.staffNo
ORDER BY b.branchNo, s.staffNo, propertyNo;
FROM (Branch b JOIN Staff s USING branchNo) AS
bs JOIN PropertyForRent p USING staffNo
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 24- 37
Outer Joins



If one row of a joined table is unmatched, row is
omitted from result table.
Outer join operations retain rows that do not satisfy
the join condition.
Consider following tables:
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
38
Outer Joins

The (inner) join of these two tables:
SELECT b.*, p.*
FROM Branch1 b, PropertyForRent1 p
WHERE b.bCity = p.pCity;
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
39
Outer Joins



Result table has two rows where cities are
same.
There are no rows corresponding to branches
in Bristol and Aberdeen.
To include unmatched rows in result table,
use an Outer join.
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
40
Example 5.28 Left Outer Join
List branches and properties that are in same
city along with any unmatched branches.
SELECT b.*, p.*
FROM Branch1 b LEFT JOIN
PropertyForRent1 p ON b.bCity = p.pCity;
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
41
Example 5.28 Left Outer Join


Includes those rows of first (left) table
unmatched with rows from second (right) table.
Columns from second table are filled with
NULLs.
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
42
Example 5.29 Right Outer Join
List branches and properties in same city and
any unmatched properties.
SELECT b.*, p.*
FROM Branch1 b RIGHT JOIN
PropertyForRent1 p ON b.bCity = p.pCity;
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
43
Example 5.29 Right Outer Join


Right Outer join includes those rows of second
(right) table that are unmatched with rows from
first (left) table.
Columns from first table are filled with NULLs.
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
44
Example 5.30 Full Outer Join
List branches and properties in same city and
any unmatched branches or properties.
SELECT b.*, p.*
FROM Branch1 b FULL JOIN
PropertyForRent1 p ON b.bCity = p.pCity;
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
45
Example 5.30 Full Outer Join


Includes rows that are unmatched in both
tables.
Unmatched columns are filled with NULLs.
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
46
AGGREGATE FUNCTIONS


Include COUNT, SUM, MAX, MIN, and AVG
Query 15: Find the maximum salary, the
minimum salary, and the average salary among
all employees.
Q15:
SELECT
FROM

MAX(SALARY),
MIN(SALARY), AVG(SALARY)
EMPLOYEE
Some SQL implementations may not allow more
than one function in the SELECT-clause
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 8- 47
AGGREGATE FUNCTIONS (contd.)

Query 16: Find the maximum salary, the
minimum salary, and the average salary among
employees who work for the 'Research'
department.
Q16:
SELECT
FROM
WHERE
MAX(SALARY),
MIN(SALARY), AVG(SALARY)
EMPLOYEE, DEPARTMENT
DNO=DNUMBER AND
DNAME='Research'
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 8- 48
AGGREGATE FUNCTIONS (contd.)

Queries 17 and 18: Retrieve the total number of
employees in the company (Q17), and the number of
employees in the 'Research' department (Q18).
Q17:
SELECT
FROM
COUNT (*)
EMPLOYEE
Q18:
SELECT
FROM
WHERE
COUNT (*)
EMPLOYEE, DEPARTMENT
DNO=DNUMBER AND
DNAME='Research’
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 8- 49
GROUPING




In many cases, we want to apply the aggregate
functions to subgroups of tuples in a relation
Each subgroup of tuples consists of the set of
tuples that have the same value for the grouping
attribute(s)
The function is applied to each subgroup
independently
SQL has a GROUP BY-clause for specifying the
grouping attributes, which must also appear in
the SELECT-clause
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 8- 50
GROUPING (contd.)

Query 20: For each department, retrieve the department
number, the number of employees in the department, and
their average salary.
Q20:



DNO, COUNT (*), AVG (SALARY)
EMPLOYEE
DNO
In Q20, the EMPLOYEE tuples are divided into groups

SELECT
FROM
GROUP BY
Each group having the same value for the grouping attribute
DNO
The COUNT and AVG functions are applied to each such
group of tuples separately
The SELECT-clause includes only the grouping attribute
and the functions to be applied on each group of tuples
A join condition can be used in conjunction with grouping
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 8- 51
GROUPING (contd.)

Query 21: For each project, retrieve the project number,
project name, and the number of employees who work on
that project.
Q21:

SELECT
FROM
WHERE
GROUP BY
PNUMBER, PNAME, COUNT (*)
PROJECT, WORKS_ON
PNUMBER=PNO
PNUMBER, PNAME
In this case, the grouping and functions are applied after
the joining of the two relations
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 8- 52
THE HAVING-CLAUSE


Sometimes we want to retrieve the values of
these functions for only those groups that satisfy
certain conditions
The HAVING-clause is used for specifying a
selection condition on groups (rather than on
individual tuples)
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 8- 53
THE HAVING-CLAUSE (contd.)

Query 22: For each project on which more than
two employees work, retrieve the project number,
project name, and the number of employees who
work on that project.
Q22:
SELECT
PNUMBER, PNAME,
COUNT(*)
FROM
PROJECT, WORKS_ON
WHERE
PNUMBER=PNO
GROUP BY PNUMBER, PNAME
HAVING
COUNT (*) > 2
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 8- 54
CASE expression


Simple CASE expression:
CASE input_expression
WHEN expression THEN result_expression
...
ELSE result_expression
END
Searched CASE expression
CASE
WHEN Boolean_expression THEN result_expression
...
ELSE result_expression
END
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 24- 55
CASE in SELECT Clause
SELECT product, 'Status' = CASE
WHEN quantity > 0 THEN 'in stock‘
ELSE 'out of stock' END
FROM dbo.inventory;
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 24- 56
CASE in WHERE Clause
select firstnme, lastname,
from employee
where 50000 < case job
when 'PRES' then salary
when 'MANAGER' then salary*1.03
when 'DESIGNER' then salary*1.05
when 'CLERK' then salary*1.07
else salary*1.10
end
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 24- 57
CASE in ORDER BY Clause
SELECT BusinessEntityID, SalariedFlag
FROM HumanResources.Employee
ORDER BY
CASE SalariedFlag
WHEN 1 THEN BusinessEntityID
END DESC ,
CASE
WHEN SalariedFlag = 0 THEN BusinessEntityID
END;
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 24- 58
CASE in HAVING Clause
SELECT JobTitle, MAX(ph1.Rate) AS MaximumRate
FROM Employee AS e JOIN EmployeePayHistory AS ph1
ON e.BusinessEntityID = ph1.BusinessEntityID
GROUP BY JobTitle
HAVING (MAX(CASE
WHEN Gender = 'M' THEN ph1.Rate
ELSE NULL END) > 40.00
OR MAX(CASE
WHEN Gender = 'F' THEN ph1.Rate
ELSE NULL END) > 42.00)
ORDER BY MaximumRate DESC;
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 24- 59
Use CASE in UPDATE statement
UPDATE Employee
SET VacationHours =
( CASE WHEN ((VacationHours - 10.00) < 0)
THEN VacationHours + 40
ELSE (VacationHours + 20.00)
END )
WHERE SalariedFlag = 0;
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 24- 60
Be Careful!!!
UPDATE Employee SET VacationHours =
(CASE WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40
ELSE (VacationHours + 20.00) END )
WHERE SalariedFlag = 0;
Is it the same with a following set of update statements?
UPDATE Employee SET VacationHours = VacationHours + 40
WHERE SalariedFlag = 0 and (VacationHours - 10.00) < 0 ;
UPDATE Employee SET VacationHours = VacationHours + 20.00
WHERE SalariedFlag = 0 and (VacationHours - 10.00) >= 0 ;
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 24- 61
Views
View
Dynamic result of one or more relational
operations operating on base relations to
produce another relation.

Virtual relation that does not necessarily
actually exist in the database but is produced
upon request, at time of request.
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
62
Views



Contents of a view are defined as a query on
one or more base relations.
With view resolution, any operations on view
are automatically translated into operations
on relations from which it is derived.
With view materialization, the view is stored
as a temporary table, which is maintained as
the underlying base tables are updated.
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
63
SQL - CREATE VIEW
CREATE VIEW ViewName [ (newColumnName [,...])
]
AS subselect
[WITH [CASCADED | LOCAL] CHECK OPTION]



Can assign a name to each column in view.
If list of column names is specified, it must
have same number of items as number of
columns produced by subselect.
If omitted, each column takes name of
corresponding column in subselect.
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
64
SQL - CREATE VIEW




List must be specified if there is any
ambiguity in a column name.
The subselect is known as the defining query.
WITH CHECK OPTION ensures that if a row
fails to satisfy WHERE clause of defining
query, it is not added to underlying base
table.
Need SELECT privilege on all tables
referenced in subselect and USAGE privilege
on any domains used in referenced columns.
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
65
Example 6.3 - Create Horizontal View
Create view so that manager at branch B003
can only see details for staff who work in his
or her office.
CREATE VIEW Manager3Staff
AS
SELECT *
FROM Staff
WHERE branchNo = ‘B003’;
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
66
Example 6.4 - Create Vertical View
Create view of staff details at branch B003
excluding salaries.
CREATE VIEW Staff3
AS SELECT staffNo, fName, lName, position, sex
FROM Staff
WHERE branchNo = ‘B003’;
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
67
Example 6.5 - Grouped and Joined Views
Create view of staff who manage properties
for rent, including branch number they work
at, staff number, and number of properties
they manage.
CREATE VIEW StaffPropCnt (branchNo, staffNo,
cnt)
AS SELECT s.branchNo, s.staffNo, COUNT(*)
FROM Staff s, PropertyForRent p
WHERE s.staffNo = p.staffNo
GROUP BY s.branchNo, s.staffNo;
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
68
Example 6.3 - Grouped and Joined Views
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
69
SQL - DROP VIEW
DROP VIEW ViewName [RESTRICT | CASCADE]


Causes definition of view to be deleted from
database.
For example:
DROP VIEW Manager3Staff;
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
70
SQL - DROP VIEW


With CASCADE, all related dependent objects
are deleted; i.e. any views defined on view
being dropped.
With RESTRICT (default), if any other objects
depend for their existence on continued
existence of view being dropped, command is
rejected.
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
71
View Resolution
Count number of properties managed by each
member at branch B003.
SELECT staffNo, cnt
FROM StaffPropCnt
WHERE branchNo = ‘B003’
ORDER BY staffNo;
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
72
View Resolution
(a) View column names in SELECT list are
translated into their corresponding column
names in the defining query:
SELECT s.staffNo As staffNo, COUNT(*) As
cnt
(b) View names in FROM are replaced with
corresponding FROM lists of defining
query:
FROM Staff s, PropertyForRent p
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
73
View Resolution
(c) WHERE from user query is combined with
WHERE of defining query using AND:
WHERE s.staffNo = p.staffNo AND branchNo =
‘B003’
(d) GROUP BY and HAVING clauses copied from
defining query:
GROUP BY s.branchNo, s.staffNo
(e)
ORDER BY copied from query with view
column name translated into defining query
column name
ORDER BY s.staffNo
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
74
View Resolution
(f) Final merged query is now executed to
produce the result:
SELECT s.staffNo, COUNT(*)
FROM staff s, PropertyForRent p
WHERE s.staffNo = p.staffNo AND
branchNo = ‘B003’
GROUP BY s.branchNo, s.staffNo
ORDER BY s.staffNo;
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
75
Restrictions on Views
SQL imposes several
creation and use of views.
restrictions
on
(a) If column in view is based on an aggregate
function:


Column may appear only in SELECT and
ORDER BY clauses of queries that access
view.
Column may not be used in WHERE nor be
an argument to an aggregate function in any
query based on view.
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
76
Restrictions on Views

For example, following query would fail:
SELECT COUNT(cnt)
FROM StaffPropCnt;

Similarly, following query would also fail:
SELECT *
FROM StaffPropCnt
WHERE cnt > 2;
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
77
Restrictions on Views
(b) Grouped view may never be joined with a
base table or a view.

For example, StaffPropCnt view is a grouped
view, so any attempt to join this view with
another table or view fails.
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
78
View Updatability


All updates to base table reflected in all views
that encompass base table.
Similarly, may expect that if view is updated
then base table(s) will reflect change.
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
79
View Updatability


However, consider again view StaffPropCnt.
If we tried to insert record showing that at
branch B003, SG5 manages 2 properties:
INSERT INTO StaffPropCnt
VALUES (‘B003’, ‘SG5’, 2);

Have
to
insert
2
records
into
PropertyForRent showing which properties
SG5 manages. However, do not know which
properties they are; i.e. do not know primary
keys!
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
80
View Updatability

If change definition of view and replace count
with actual property numbers:
CREATE VIEW StaffPropList (branchNo,
staffNo, propertyNo)
AS SELECT s.branchNo, s.staffNo, p.propertyNo
FROM Staff s, PropertyForRent p
WHERE s.staffNo = p.staffNo;
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
81
View Updatability

Now try to insert the record:
INSERT INTO StaffPropList
VALUES (‘B003’, ‘SG5’, ‘PG19’);


Still problem, because in PropertyForRent all
columns except postcode/staffNo are not
allowed nulls.
However, have no way of giving remaining
non-null columns values.
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
82
View Updatability


ISO specifies the views that must be
updatable in system that conforms to
standard.
A view is updatable if and only if:
- DISTINCT is not specified.
- Every element in SELECT list of defining query is a
column name and no column appears more than
once.
- FROM clause specifies only one table, excluding
any views based on a join, union, intersection or
difference.
- No nested SELECT referencing outer table.
- No GROUP BY or HAVING clause.
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
83
Updatable View
For view to be updatable, DBMS must be able
to trace any row or column back to its row or
column in the source table.
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
84
WITH CHECK OPTION





Rows exist in a view because they satisfy
WHERE condition of defining query.
If a row changes and no longer satisfies
condition, it disappears from the view.
New rows appear within view when
insert/update on view cause them to satisfy
WHERE condition.
Rows that enter or leave a view are called
migrating rows.
WITH CHECK OPTION prohibits a row
migrating out of the view.
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
85
WITH CHECK OPTION



LOCAL/CASCADED apply to view hierarchies.
With LOCAL, any row insert/update on view
and any view directly or indirectly defined on
this view must not cause row to disappear
from view unless row also disappears from
derived view/table.
With CASCADED (default), any row insert/
update on this view and on any view directly
or indirectly defined on this view must not
cause row to disappear from the view.
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
86
Example 6.6 - WITH CHECK OPTION
CREATE VIEW Manager3Staff
AS
SELECT *
FROM Staff
WHERE branchNo = ‘B003’
WITH CHECK OPTION;


Cannot update branch number of row B003 to
B002 as this would cause row to migrate from
view.
Also cannot insert a row into view with a
branch number that does not equal B003.
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
87
Example 6.6 - WITH CHECK OPTION

If Manager3Staff is defined not on Staff
directly but on another view of Staff:
CREATE VIEW LowSalary
AS SELECT * FROM Staff WHERE salary > 9000;
CREATE VIEW HighSalary
AS SELECT * FROM LowSalary
WHERE salary > 10000
WITH LOCAL CHECK OPTION;
CREATE VIEW Manager3Staff
AS SELECT * FROM HighSalary
WHERE branchNo = ‘B003’;
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
88
Example 6.6 - WITH CHECK OPTION
UPDATE Manager3Staff
SET salary = 9500
WHERE staffNo = ‘SG37’;


Update would fail: although update would
cause row to disappear from HighSalary, row
would not disappear from LowSalary.
However, if update tried to set salary to 8000,
update would succeed as row would no
longer be part of LowSalary.
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
89
Example 6.6 - WITH CHECK OPTION


If HighSalary had specified WITH CASCADED
CHECK OPTION, setting salary to 9500 or
8000 would be rejected because row would
disappear from HighSalary.
To prevent anomalies like this, each view
should be created using WITH CASCADED
CHECK OPTION.
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
90
Advantages of Views







Data independence
Currency
Improved security
Reduced complexity
Convenience
Customization
Data integrity
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
91
Disadvantages of Views



Update restriction
Structure restriction
Performance
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
92
View Materialization




View resolution mechanism may be slow,
particularly if view is accessed frequently.
View
materialization
stores
view
as
temporary table when view is first queried.
Thereafter, queries based on materialized
view can be faster than recomputing view
each time.
Difficulty is maintaining the currency of view
while base tables(s) are being updated.
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
93
View Maintenance


View maintenance aims to apply only those
changes necessary to keep view current.
Consider following view:
CREATE VIEW StaffPropRent(staffNo)
AS SELECT DISTINCT staffNo
FROM PropertyForRent
WHERE branchNo = ‘B003’ AND
rent > 400;
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
94
View Materialization





If insert row into PropertyForRent with rent 400 then
view would be unchanged.
If insert row for property PG24 at branch B003 with
staffNo = SG19 and rent = 550, then row would
appear in materialized view.
If insert row for property PG54 at branch B003 with
staffNo = SG37 and rent = 450, then no new row
would need to be added to materialized view.
If delete property PG24, row should be deleted from
materialized view.
If delete property PG54, then row for PG37 should
not be deleted (because of existing property PG21).
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
95
References


http://www.tizag.com/sqlTutorial/sqlcase.php
http://msdn.microsoft.com/enus/library/ms181765.aspx
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Slide 24- 96