Transcript Ensuring transaction integrity

Advanced SQL (part 2) and SQL
in practice
• CS263 Lecture 8
Ensuring transaction integrity
• User-defined transactions can improve system performance
because transaction will be processed as sets rather than
individually
• Some SQL systems have concurrency controls that handle
the updating of a shared database by concurrent users
• These can journalise database changes, so that a database
can be recovered after abnormal termination in the middle
of a transaction
• Such controls are transparent to the user, no special
programming is needed to ensure proper control of
concurrent access to data
Data dictionary facilities
• Data dictionary = system tables that store metadata
• Users usually can view some of these tables, using
SELECT statements that can generate reports about
system usage, user privileges etc,
• Users are restricted from updating them, sine the DBMS
maintains them and depends on them for its processing
• In Oracle there are over 100 data dictionary views
• Tables that anyone can access begin with USER or ALL
• Tables that only the Database Administrator can use begin
with DBA
Data dictionary facilities
–
–
–
–
Examples:
DBA_TABLES – descriptions of all tables in database
DBA_CONSTRAINTS – description of constraints
DBA_USERS – information about the users of the
system
– DBA_TAB_PRIVS – descriptions of grants on objects
in the database
SQL-99 enhancements/extensions
• Enhancements to SQL have been built into many
implementations (including Oracle)
• User-defined data types (UDT) subclasses of standard
types or an object type
• Analytical functions (for OLAP/Data visualisation) - many
mathematical/statistical and related functions
• Persistent Stored Modules (SQL/PSM) - capability to
create and drop code modules. Persistent means that a
module of code will be stored until dropped, making it
available for execution across user sessions.
SQL-99 enhancements/extensions
– New statements:
• CASE, IF, LOOP, FOR, WHILE, REPEAT etc
• New statements and PSM introduce procedurality into
SQL (statements are processed sequentially) whereas base
SQL is a non-procedural language and no statement
execution sequence is implied
• SQL-99 Standard not widely adopted yet
• Oracle has propriety version called PL/SQL
Routines and triggers
• These are stored in the database and controlled by the
DBMS
• This promotes stronger data integrity and consistency of
use within the database
• Since they are stored once, code maintenance is simplified
• Both consist of blocks of procedural code
• Trigger code is stored in the database and runs
automatically whenever the triggering event (such as an
UPDATE) occurs
• Routines do not run automatically, they have to be called
in to operate
Triggers
• Since triggers are stored and executed in the database, they execute
against all applications that access the database
• Triggers can also cascade, causing other triggers to fire
• They can be used to ensure referential integrity, enforce business rules,
create audit trails etc.
• Constraints can be thought of as a special case of triggers, as they are
applied automatically as a result of data modification commands
(though they are not as flexible as triggers)
• Triggers have 3 parts, the event, the condition and the action
• The following trigger will automatically insert the order number
whenever a new order is added
Triggers
• BIR stands for Before Insert Row, also requires that a
sequence ID_SEQUENCE has been previously defined
• CREATE TRIGGER ORDER_ID_BIR
• BEFORE INSERT ON ORDER_T
• FOR EACH ROW
• BEGIN
• SELECT ID_SEQUENCE.NEXTVAL
• INTO: NEW.ORDER_ID
• FROM DUAL;
• END ORDER_ID_BIR;
Triggers
• Triggers may occur either before or after the statement that
aroused the trigger is executed
• They may occur on INSERT, UPDATE or DELETE
commands
• They may fire once for each time a row is affected, or they
may fire once per statement
• Care should be taken when using them, since they fire
automatically the user will be unaware of them
• One trigger can cause another to fire, can easily end up
with an endless loop of triggers
Routines
• Routines are Program modules that execute on demand
• Functions – routines that return values and take input
parameters
• Procedures – routines that do not return values and can
take input or output parameters
Routines - example procedure
• CREATE OR REPLACE PROCEDURE
PRODUCT_LINE_SALE
• AS BEGIN
• UPDATE PRODUCT_T
• SET SALE_PRICE = 0.90*STANDARD_PRICE
• WHERE STANDARD_PRICE >= 400;
• UPDATE PRODUCT_T
• SET SALE_PRICE = 0.85*STANDARD_PRICE
• WHERE STANDARD_PRICE < 400;
• END (To run this procedure we would use: EXEC
PRODUCT_LINE_SALE)
Triggers contrasted with routines
Procedures are called explicitly
Triggers are event-driven
Oracle PL/SQL trigger syntax
SQL-99 Create routine syntax
Embedded and dynamic SQL
• Embedded SQL - including hard-coded SQL statements in
a program written in another language such as C or Java =
more efficient processing than interactive SQL
• Dynamic SQL - ability for an application program to
generate SQL code on the fly, as the application is running
- central to many internet applications (discussed in a later
lecture)
SQL in practice
• Following material illustrates moving from ER diagram to
SQL code
• Following Fig. Shows a simple ER diagram
• Other slides show how to populate and query tables using
SQL code
Identify entities and attributes
EmpNo
Emp
Job
Name
Hire Date
Salary
Dept
Location
Name
DeptNo
Comm
Identify relationships
Managed by
Manage
Emp
Works in
Assigned
Dept
Integrity Rules – derived from ER Diagram:
Each employee may be managed by one other employee
Each employee may manage one or more other employees
Each employee must work in a single department
Each department may be assigned one or more employees
Create a relational schema
Dept (DeptNo, Name, Location)
Emp (EmpNo, Name, Job, Sal, Comm, HireDate, Mgr, DeptNo)
Dept
DeptNo
Name
Location
Number(2)
Varchar2(14)
Varchar2(13)
Emp
EmpNo
Name
Job
Sal
Comm
HireDate
Mgr
DeptNo
Number(4)
Varchar2(10)
Varchar2(9)
Number(7,2)
Number(7,2)
Date
Number(4)
Number(2)
Create relational tables
To create a relation in SQL the following ‘Create Table’ command is required:
create table R (A1 D1, A2 D2, … An Dn,
integrity constraint1, integrity constarint2)
Where: R = Relation (table) name
A = Attribute name
D = Attribute domain
create table Dept (deptno number(2), name varchar2(14), location varchar2(13),
constraint DeptPK primary key (deptno));
create table Emp (empno number(4), name varchar2(10), job varchar2(9),
sal number(7,2), comm number(7,2), hiredate date, mgr number(4),
deptno number(2),
constraint EmpPK primary key (empno),
constraint EmpFK1 foreign key (mgr) references Emp,
constraint EmpFK2 foreign key (deptno) references Dept);
Populate relational tables
To create a tuple in SQL the following ‘Insert’ command is required:
insert into R (attribute1, attribute2, … attributen )
values (value1, value2, … valuen)
insert into Dept (deptno, name, location )
values (10, ‘Accounting’, ‘New York’)
insert into Dept (deptno, name, location )
values (30, ‘Sales’, ‘Chicago)
The insert order matters in
terms of referential integrity
constraints!
insert into Emp (empno, name, job, sal, comm, hiredate, mgr, deptno )
values (7839, ‘King’, ‘President’, 5000, NULL, ‘17-Nov-81’, NULL, 10)
insert into Emp (empno, name, job, sal, comm, hiredate, mgr, deptno )
values (7698, ‘Blake’, ‘Manager’, 1600, NULL, ’01-May-81’, 7839, 30)
Query relational tables
To query a relation in SQL the following ‘Select’ command is required:
SELECT [ALL | DISTINCT] attribute1, attribute2, … attributen
FROM relation1, relation2, … relationn
[WHERE condition-expression]
[GROUP BY attribute1, attribute2, … attributen ]
[HAVING condition-expression]
[ORDER BY attribute1, attribute2, … attributen ]
Simple Example: list all Employees and the departments they work in
select empno, name, deptno
from Emp;
7839
7698
King
Blake
10
30
Query relational tables
Simple Example: list all Employees that work in department 30
select empno, name
from Emp
where deptno = 30;
7698
Blake
Simple Example: list all Employees that work in either department 10 or 30
select empno, name
from Emp
where deptno = 10 or deptno = 30;
7839
7698
King
Blake
Query relational tables - Join
Example: list Employee and Department names of all employees that work
in either department 10 or 30
select emp.name, dept.name
from Emp, Dept
where (emp.deptno = 10
or emp.deptno = 30);
King
King
Blake
Blake
Accounting
Sales
Accounting
Sales
select emp.name, dept.name
from Emp, Dept
where (emp.deptno = dept.deptno)
and (emp.deptno = 10 or emp.deptno = 30)
King
Blake
Accounting
Sales
Query relational tables – Order by
select emp.name, dept.name
from Emp, Dept
where (emp.deptno = dept.deptno)
and (emp.deptno = 10 or emp.deptno = 30)
order by emp.name asc;
select emp.name, dept.name
from Emp, Dept
where (emp.deptno = dept.deptno)
and (emp.deptno = 10 or emp.deptno = 30)
order by dept.name desc;
select name
from Dept
order by name;
Accounting
Sales
Blake
King
Sales
Accounting
Blake
King
Sales
Accounting
Remember in relations
neither tuples nor attributes
have any intrinsic order!
Example relations
Emp
Dept
EMPNO
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
DEPTNO
10
20
30
40
NAME
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
JOB
CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK
NAME
ACCOUNTING
RESEARCH
SALES
OPERATIONS
MGR
7902
7698
7698
7839
7698
7839
7839
7566
7698
7788
7698
7566
7782
HIREDATE
17-DEC-80
20-FEB-81
22-FEB-81
02-APR-81
28-SEP-81
01-MAY-81
09-JUN-81
19-APR-87
17-NOV-81
08-SEP-81
23-MAY-87
03-DEC-81
03-DEC-81
23-JAN-82
LOCATION
NEW YORK
DALLAS
CHICAGO
BOSTON
SAL
800
1600
1250
2975
1250
2850
2450
3000
5000
1500
1100
950
3000
1300
COMM DEPTNO
20
300
30
500
30
20
1400 30
30
10
20
10
0
30
20
30
20
10
Query relational tables - Outer Join
Example: list all departments and the names of staff that work in them.
select dept.name, emp.name
from Emp, Dept
where (emp.deptno (+) = dept.deptno)
order by dept.name, emp.name;
Accounting
Accounting
Accounting
Operations
Research
Research
Research
Research
Research
Sales
Sales
Sales
Sales
Sales
Sales
Clark
King
Miller
Adams
Ford
Jones
Scott
Smith
Allen
Blake
James
Martin
Turner
Ward
Query relational tables – Group by
Example: List employee’s departments giving a count of employees in each.
select deptno
from Emp
order by deptno;
10
10
10
20
20
20
20
20
30
30
30
30
30
30
select deptno
from Emp
group by deptno;
10
20
30
Aggregate Function!
select deptno, count(*)
from Emp
group by deptno;
10
20
30
3
5
6
Select queries can contain functions and
calculations as well as attribute names in
the select condition!
Query relational tables – Group by
select deptno, sum(sal), sum(sal)/count(*)
from Emp
group by deptno;
10
20
30
8750 2916.6667
10875 2175
9400 1566.6667
As we are dealing with groups of tuples, rather than individual tuples, there
are only certain types of data that can be selected: 1) attributes named in the
group by clause; 2) group aggregate functions; 3) expressions involving
combinations of 1) and 2).
Example: List employee’s departments giving a count of employees in each,
provided that there are over four employees in the department.
select deptno, count(*)
from Emp
group by deptno
having count(*) > 4;
20
30
5
6
The having clause is used in Group
Selections in the same way that the
where clause is used in standard
tuple selections.
Query relational tables – Group by
Combined Example: List, in reverse order of average department salary, the
total salary and average salary for each department that has employees, and
where the average salary of each department is over £2000
select deptno, sum(sal), sum(sal)/count(*)
from Emp
group by deptno
having sum(sal)/count(*) > 2000
order by sum(sal)/count(*) desc;
10 8750 2916.6667
20 10875 2175
The result of an SQL Select is, as shown, a new (unnamed) relation. The
attributes of this new relation are named on the basis of the select statement.
DEPTNO
10
20
SUM(SAL)
8750
10875
SUM(SAL)/COUNT(*)
2916.6667
2175
ALIAS:- select deptno, sum(sal) “Total Salary”, sum(sal)/count(*) “Avg Salary”
DEPTNO
10
20
Total Salary
8750
10875
Avg Salary
2916.6667
2175
Query relational tables – Inner Selects
As the result of an SQL Select on a relation(s) is itself a relation, it follows
that, as with Relational Algebra, the result of one select can be used as the
input into another SQL Select!
However, rather than create, and name, a separate relation to contain the
output of the first select and then use this relation in the second select, it is
possible to ‘pipe’ the output of the first select directly into the second select!
Example: List employee’s who earn more than the average company salary.
2
select name, sal
from Emp
where sal > (select AVG(sal) from Emp)
order by name;
1 2073.2143, is substituted
for this select statement
NAME SAL
BLAKE 2850
CLARK 2450
FORD
3000
JONES 2975
KING
5000
SCOTT 3000
Query relational tables – Inner Selects
Remember: Inner Selects (also called sub-selects or sub-queries) are fullbodied SQL Select statements: Therefore, they can, when required to do so,
return more than a single value (one tuple, one attribute) relation.
Example: List employees who earn the maximum salary in each of the
departments.
select name, deptno, sal
from Emp
where (deptno, sal) in (select deptno, max(sal)
from emp
group by deptno)
order by deptno;
This relation is substituted for
the inner select statement
DEPT
10
20
30
NAME DEPT SAL
KING
10
5000
SCOTT 20
3000
FORD
20
3000
BLAKE 30
2850
MAX(SAL)
5000
3000
2850
Query relational tables – Inner Selects
Note: It is sometimes necessary to make reference to an attribute value from
the outer select, within the Where Clause of the inner select. This can be
achieved by using a Relation Alias!
Example: List employee’s who earn more than the average salary in the
department they work in.
select name, deptno, sal
Relation Alias
from Emp E1
where sal > (select AVG(sal) from Emp
where deptno = E1.deptno
group by deptno)
order by deptno;
NAME DEPT SAL
KING
10
5000
JONES 20
2975
SCOTT 20
3000
FORD
20
3000
ALLEN 30
1600
BLAKE 30
2850
The deptno attribute value of the current tuple will be substituted here! If
there are 14 employee tuples there will be 14 separate substitutions (even
though there are only three departments)!
SQL set operations – Union
Example: list all staff that work in either of two departments (each dept. has
a separate database), showing their staff number, and date of birth.
DepB
DepA
staffno
SL10
SA51
DS40
dob
14-02-64
21-11-82
01-01-40
select staffno, dob
from DepA
UNION
select staffno, dob
from DepB;

staffno dob
CC15 11-03-66
SA51 21-11-82
staffno
SL10
SA51
DS40
CC15
dob
14-02-64
21-11-82
01-01-40
11-03-66
SQL set operations – Intersection
Example: list all staff that work in both departments (each dept. has a
separate database), showing their staff number, and date of birth.
DepB
DepA
staffno
SL10
SA51
DS40
dob
14-02-64
21-11-82
01-01-40
select staffno, dob
from DepA
INTERSECT
select staffno, dob
from DepB;

staffno dob
CC15 11-03-66
SA51 21-11-82
staffno dob
SA51 21-11-82
SQL set operations – difference
Example: list all staff that only work in department A (each dept. has a
separate database), showing their staff number, and date of birth.
DepB
DepA
staffno
SL10
SA51
DS40
dob
14-02-64
21-11-82
01-01-40
select staffno, dob
from DepA
MINUS
select staffno, dob
from DepB;
staffno dob
CC15 11-03-66
SA51 21-11-82
staffno dob
SL10 14-02-64
DS40 01-01-40
SQL - Group Insert statement
To create a set of tuples in SQL the following ‘Insert’ command can be used:
insert into R (attribute1, attribute2, … attributen )
select (attribute1, attribute2, … attributen)
from relation1, relation2, … relationn
[where condition-expression]
[group by attribute1, attribute2, … attributen ]
[having condition-expression]
[order by attribute1, attribute2, … attributen ]
Example: copy details of all employees that work in department 10 from
the Emp relation into the DepA relation.
insert into DepA (staffno, name, job, hiredate)
select empno, name, job, hiredate
from Emp
where deptno = 10;
corresponding
attributes have to
be of the same type
Each tuple to be inserted has to be unique!
SQL - Delete statement
To delete a set of tuples in SQL the following ‘Delete’ command is used:
delete from R
[where condition-expression]
Example: remove details of all employees that work in department 10
from the Emp relation.
Delete from Emp
where deptno = 10;
If the where clause is omitted then all
tuples in the relation will be removed!
SQL - Update statement
To alter a set of tuples in SQL the following ‘Update’ command is used:
update R
set attribute1 = datavalue1,
attribute2 = datavalue2, ...
attributen = datavaluen
[where condition-expression]
Example: increase the salary of all employees that work in department
10 by 10%.
update Emp
set sal = sal *1.1
where deptno = 10;
If the where clause is omitted then all
tuples in the relation will be altered!