Transcript PPTX

Database Systems I
SQL Constraints and Triggers
Ensuring business rules are met.
INTEGRITY CONSTRAINTS

An integrity constraint (IC) describes conditions
that every legal instance of a relation must
satisfy.



Inserts/deletes/updates that violate IC’s are
disallowed.
Can be used to ensure application semantics (e.g., sid
is a key), or prevent inconsistencies (e.g., sname has to
be a string, age must be < 200).
Types of IC’s:
domain constraints and NOT NULL constraints,
 primary key constraints and foreign key constraints,
 general constraints.

2
NOT-NULL CONSTRAINTS

The IC NOT NULL disallows NULL values for a
specified attribute.
CREATE TABLE Students
(sid CHAR(20) PRIMARY KEY,
name CHAR(20) NOT NULL,
login CHAR(10) NOT NULL,
age INTEGER,
gpa REAL);
Primary key attributes are implicitly NOT NULL.
3
GENERAL CONSTRAINTS

Attribute-based CHECK
defined in the declaration of an attribute,
 activated on insertion to the corresponding table or
update of attribute.


Tuple-based CHECK
defined in the declaration of a table,
 activated on insertion to the corresponding table or
update of tuple.


Assertion
defined independently from any table,
 activated on any modification of any table mentioned
in the assertion.

4
ATTRIBUTE-BASED CHECK
Attribute-based CHECK constraint is part of an
attribute definition.
 Is checked whenever a tuple gets a new value for
that attribute (INSERT or UPDATE)
 Violating modifications are rejected
 CHECK constraint can contain an SQL query
referencing other attributes (of the same or other
tables), if their relations are mentioned in the
FROM clause
 CHECK constraint is not activated if other
attributes mentioned get new values

5
ATTRIBUTE-BASED CHECK

Ex: not null constraint

Ex: sex char(1) CHECK (sex IN (‘F’, ‘M’))

domain constraint

Ex: Create domain gender-domain CHAR (1)
CHECK (VALUE IN (‘F’, ‘M’))

define sex in schema definition to be of type genderdomain
6
ATTRIBUTE-BASED CHECK

Attribute-based CHECK constraints are most
often used to restrict allowable attribute values.
CREATE TABLE Sailors
( sid INTEGER PRIMARY KEY,
sname CHAR(10),
rating INTEGER
CHECK ( rating >= 1
AND rating <= 10),
age REAL);
7
TUPLE-BASED CHECK
Tuple-based CHECK constraints can be used to
constrain multiple attribute values within a table.
 Condition can be anything that can appear in a
WHERE clause.
 Same activation and enforcement rules as for
attribute-based CHECK.

CREATE TABLE Sailors
( sid INTEGER PRIMARY KEY,
sname CHAR(10),
previousRating INTEGER,
currentRating INTEGER,
age REAL,
CHECK (currentRating >= previousRating)
);
8
TUPLE-BASED CHECK

Tuple Based CHECK contraint:
CREATE TABLE Emp (
name CHAR(30) UNIQUE
gender CHAR(1) CHECK (gender in (‘F’, ‘M’)
age int
dno int
CHECK (age < 100 AND age > 20)
CHECK (dno IN (SELECT dno FROM dept))
)
these are checked on insertion to relation or tuple update
9
TUPLE-BASED CHECK

CHECK constraint that refers to other table:
CREATE TABLE Reserves
( sname CHAR(10),
Interlake boats
bid INTEGER,
cannot be reserved
day DATE,
PRIMARY KEY (bid,day),
CHECK (‘Interlake’ <>
( SELECT B.bname
FROM Boats B
WHERE B.bid=bid)));

But: these constraints are invisible to other
tables, i.e. are not checked upon modification of
other tables.
10
TUPLE-BASED CHECK
CREATE TABLE dept (
mgrname CHAR(30)
dno int
dname CHAR(20)
check (mgrname NOT IN (SELECT name
FROM emp
WHERE emp.sal < 50000))
)


If someone made a manager whose salary is less than 50K
that insertion/update to dept table will be rejected.
However, if manager’s salary reduced to less than 50K, the
corresponding update to emp table will NOT be rejected.
11
ASSERTIONS

Number of boats plus number of sailors is < 100.
CREATE TABLE Sailors
( sid INTEGER,
sname CHAR(10),
rating INTEGER,
age REAL,
PRIMARY KEY (sid),
CHECK
( (SELECT COUNT (S.sid) FROM Sailors S)
+ (SELECT COUNT (B.bid) FROM Boats B) < 100 )
);



Tuple-based CHECK awkward and wrong!
If Sailors is empty, the number of Boats tuples can be
anything!
ASSERTION is the right solution; not associated with either
table.
12
ASSERTIONS
Assertions are constraints over a table as a whole
or multiple tables.
 General form:


CREATE ASSERTION <name> CHECK <cond>
An assertion must always be true at transaction
boundaries. Any modification that causes it to
become false is rejected.
 Similar to tables, assertions can be dropped by a
DROP command.

13
ASSERTIONS
Condition can be anything allowed in a WHERE
clause.
 Constraint is tested whenever any (!) of the
referenced tables is modified.
 Different from CHECK constraints, ICs expressed
as assertion are always enforced (unless they are
deferred until the end of the transaction).
 CHECK constraints are more efficient to
implement than ASSERTIONs.

14
ASSERTIONS



Number of boats plus number of sailors is < 100.
CREATE ASSERTION smallClub
CHECK
( (SELECT COUNT (S.sid) FROM Sailors S)
+ (SELECT COUNT (B.bid) FROM Boats B) < 100 );
All relations are checked to comply with above.
Number of reservations per sailor is < 10.
CREATE ASSERTION notTooManyReservations
CHECK ( 10 > ALL
(SELECT COUNT (*)
FROM Reserves
GROUP BY sid)
);
15
EXAMPLE ASSERTION

CREATE ASSERTION RichMGR CHECK
(NOT EXISTS
(SELECT *
FROM dept, emp
WHERE emp.name = dept.mgrname AND
emp.salary < 50000))
This assertion correctly guarantees that each manager makes
more than 50000.
If someone made a manager whose salary is less than 50K
that insertion/update to dept table will be rejected.
Furthermore, if manager’s salary reduced to less than 50K,
the corresponding update to emp table will be rejected.
16
DIFFERENT CONSTRAINT TYPES
Type
Where Declared
When activated Guaranteed
to hold?
Attribute
CHECK
with attribute
on insertion
or update
not if contains
subquery
Tuple
CHECK
relation schema
insertion or
update to
relation
not if contains
subquery
Assertion
database schema
on change to Yes
any relation
mentioned
17
ALTERING CONSTRAINTS
ALTER TABLE Product DROP CONSTRAINT
positivePrice
ALTER TABLE Product ADD CONSTRAINT
positivePrice CHECK (price >= 0)
ALTER DOMAIN ssn ADD CONSTRAINT no-leading-1s
CHECK (value >= 200000000)
DROP ASSERTION assert1.
18
TRIGGERS
TRIGGERS


Trigger: procedure that starts automatically if
specified changes occur to the DB.
Three parts of a trigger:




Event (activates the trigger)
insert, delete or update of the database
Condition (tests whether the trigger should run)
a Boolean statement or a query (nonempty answer set
= true, empty answer set = false)
Action (what happens if the trigger runs)
wide variety of options
The action can refer to both the old and new state of the
database.
20
TRIGGERS




Synchronization of the Trigger with the activating
statement (DB modification)
 Before
 After
 Instead of
 Deferred (at end of transaction).
Update events may specify a particular column or set of
columns.
A condition is specified with a WHEN clause.
Number of Activations of the Trigger
 Once per modified tuple
(FOR EACH ROW)
 Once per activating statement
(default).
21
TRIGGERS

Options for the REFERENCING clause:
NEW TABLE: the set (!) of tuples newly inserted
(INSERT).
 OLD TABLE: the set (!) of deleted or old versions of
tuples (DELETE / UPDATE).
 OLD ROW: the old version of the tuple (FOR EACH
ROW UPDATE).
 NEW ROW: the new version of the tuple (FOR EACH
ROW UPDATE).


The action of a trigger can consist of multiple SQL
statements, surrounded by BEGIN . . . END.
22
TRIGGERS
CREATE TRIGGER youngSailorUpdate
AFTER INSERT ON SAILORS
/* Event */
REFERENCING NEW TABLE NewSailors
FOR EACH STATEMENT
INSERT
/* Action */
INTO YoungSailors(sid, name, age, rating)
SELECT sid, name, age, rating
FROM NewSailors N
WHERE N.age <= 18;


This trigger inserts young sailors into a separate table.
It has no (i.e., an empty, always true) condition.
23
EXAMPLE: ROW LEVEL TRIGGER
CREATE TRIGGER
NoLowerPrices
AFTER UPDATE OF price ON Product
REFERENCING
OLD AS OldTuple
NEW AS NewTuple
WHEN (OldTuple.price > NewTuple.price)
UPDATE Product
SET price = OldTuple.price
WHERE name = NewTuple.name
FOR EACH ROW
24
TRIGGERS
CREATE TRIGGER notTooManyReservations
AFTER INSERT ON Reserves
/* Event */
REFERENCING NEW ROW NewReservation
FOR EACH ROW
WHEN (10 <= (SELECT COUNT(*) FROM Reserves
WHERE sid =NewReservation.sid))
/* Condition */
DELETE FROM Reserves R
WHERE R.sid= NewReservation.sid
/* Action */
AND day=
(SELECT MIN(day) FROM Reserves R2 WHERE
R2.sid=R.sid);


This trigger makes sure that a sailor has less than 10
reservations, deleting the oldest reservation of a given
sailor, if neccesary.
It has a non- empty condition (WHEN).
25
TRIGGERS VS. GENERAL CONSTRAINTS

Triggers can be harder to understand.
Several triggers can be activated by one SQL
statement (arbitrary order!).
 A trigger may activate other triggers (chain
activation).


Triggers are procedural.
Assertions react on any database modification, trigger
only only specified event.
 Trigger execution cannot be optimized by DBMS.


Triggers have more applications than constraints.
monitor integrity constraints,
 construct a log,
 gather database statistics, etc.

26
SUMMARY
SQL allows specification of rich integrity
constraints (ICs): attribute-based, tuple-based
CHECK and assertions (table-independent).
 CHECK constraints are activated only by
modifications of the table they are based on,
ASSERTIONs are activated by any modification
that can possibly violate them.
 Choice of the most appropriate method for a
particular IC is up to the DBA.
 Triggers respond to changes in the database. Can
also be used to represent ICs.

27
Database Systems I
SQL Stored Procedures
EXAMPLE PROCEDURE
29
STORED PROCEDURES

What is a stored procedure:
Program executed through a single SQL statement
 Executed in the process space of the server


Advantages:
Can encapsulate application logic while staying “close”
to the data
 Reuse of application logic by different users
 Avoid tuple-at-a-time return of records through
cursors

30
STORED PROCEDURES: EXAMPLES
CREATE PROCEDURE ShowNumReservations
SELECT S.sid, S.sname, COUNT(*)
FROM Sailors S, Reserves R
WHERE S.sid = R.sid
GROUP BY S.sid, S.sname
Stored procedures can have parameters:
 Three different modes: IN, OUT, INOUT
CREATE PROCEDURE IncreaseRating(
IN sailor_sid INTEGER, IN increase INTEGER)
UPDATE Sailors
SET rating = rating + increase
WHERE sid = sailor_sid
31
STORED PROCEDURES
Stored procedure do not have to be written in SQL:
CREATE PROCEDURE TopSailors(
IN num INTEGER)
LANGUAGE JAVA
EXTERNAL NAME “file:///c:/storedProcs/rank.jar”
32
CALLING STORED PROCEDURES
EXEC SQL BEGIN DECLARE SECTION
Int sid;
Int rating;
EXEC SQL END DECLARE SECTION
// now increase the rating of this sailor
EXEC CALL IncreaseRating(:sid,:rating);
33
Database Systems I
SQL Transactions
Ensuring database consistency one query at a time.
TRANSACTIONS




So far, we have implicitly assumed that there is
only one DB user who executes one SQL
statement at a time.
In reality, a DBS may have many concurrent
users.
Each user may issue a sequence of SQL
statements that form a logical unit (transaction).
The DBS is in charge of ordering the SQL
statements from different users in a way
(serializable) that produces the same results as if
the statements would have been executed in a
single user scenario.
35
SERIALIZABILITY


T1:
T2:
Consider two users who simultaneously want to
book a seat on a certain flight: they first find an
empty seat and then book it (set it occupied).
In an unconstrained system, their operations
might be executed in the following order:
find empty
seat 22A,
book seat 22A
find empty
seat 22A,
book seat 22A
time

In the end, who gets seat 22A?
36
SERIALIZABILITY


To avoid such a problem, we need to consider the
sequence of statements of a user transaction as a
unit.
Statements from two different user transactions
must be ordered in a way that is equivalent to
both transactions being performed serially in
either order



transaction 1 before transaction 2
transaction 2 before transaction 1
In our example, either user 1 or user 2 would get
seat 22A. The other user would see 22A as
occupied and would have to find another seat.
37
ATOMICITY



So far, we have also assumed that all SQL
statements are executed correctly.
In reality, various types of system errors can
occur during the execution of a user transaction.
At the time of a system crash, transactions can
be incomplete: some, but not all of their SQL
statements have been executed.
38
ATOMICITY

Consider a bank transaction T:
T:
A=A+100, B=B-100
time



T is transferring $100 from B’s account to A’s
account.
What if the system crashes right after the first
statement of T has been executed, i.e. the second
statement is not executed?
The DBS has to ensure that every transaction is
treated as an atomic unit, i.e. either all or none
of its SQL statements are executed.
39
TRANSACTIONS


A user’s program may carry out many operations
on the data retrieved from the database, but the
DBMS is only concerned about what data is
read/written from/to the database.
A transaction is the DBMS’s abstract view of a
user program: a sequence of DB reads (R) and
writes (W).
T:
A=A+100, B=B-100
User’s view
T:
R(A), W(A), R(B), W(B)
System’s view
time
40
SERIALIZABILITY


Serial schedule: Schedule that does not
interleave the SQL statements of different
transactions.
Equivalent schedules: For any database state,
the effect of executing the first schedule is
identical to the effect of executing the second
schedule:
The resulting DB instance / state.
 The result of read operations, i.e. what the user sees.


Serializable schedule: A schedule that is
equivalent to some serial schedule.
41
SERIALIZABILITY


Serializability is normally ensured by locking the
DB objects read or written.
Before reading or writing a DB object (table or
tuple), the transaction needs to obtain the
appropriate lock:





Shared locks for reading,
Exclusive locks for writing,
View locks to prevent new tuples from being
returned by a repeated reading.
Locks are normally released only at the end of a
transaction.
If a transaction cannot get the lock it has
requested, it needs to wait until it is realeased
by the other transaction currently holding it.
42
TRANSACTIONS IN SQL

By default, each SQL statement (any query or
modification of the database or its schema) is
treated as a separate transaction.


Transaction includes the effects of triggers.
Transactions can also be defined explicitly.
START TRANSACTION;
<sequence of SQL statements>
COMMIT;
or ROLLBACK;

COMMIT makes all modifications of the
transaction permanent, ROLLBACK undoes all
DB modifications made.
43
READ-ONLY TRANSACTIONS




A transaction that reads only (and does not write
the DB) is easy to serialize with other read-only
transactions.
Only shared locks need to be set.
This means that read-only transactions do not
need to wait for each other, and the throughput
of the DBS can be maximized.
To specify the next transaction as read-only:
SET TRANSACTION READ ONLY;
44
DIRTY READS


T1:
T2:


Dirty data is data that has been modified by a
transaction that has not yet committed.
If that transaction is rolled back after another
transaction has read its dirty data, a nonserializable schedule results.
R(A), W(A),
R(B), W(B), Rollback
R(A), W(A), Commit
Consider T1 who wants to book two seats and T2
who wants to book one seat.
T2 does not get his favorite seat or maybe not
even any seat on his favorite flight, although he
could have if he had waited for the end of T1.
45
ISOLATION LEVELS




Control the degree of locking which occurs when
selecting data
SQL allows you to specify four different isolation
levels for a transaction.
SET TRANSACTION ISOLATION LEVEL . . . ;
The isolation level of a transaction defines what
data that transaction may see
Note that other, concurrent transactions may be
executed at different isolation levels
46
ISOLATION LEVELS

SERIALIZABLE

This isolation level specifies that all transactions occur in a
completely isolated fashion



i.e., as if all transactions in the system had executed serially, one after
the other
The DBMS may execute two or more transactions at the same
time only if the illusion of serial execution can be maintained
REPEATABLE READ
All data records read by a SELECT statement cannot be
changed
 if the SELECT statement contains any ranged WHERE
clauses, phantom reads can occur

Phantom Read: 3rd query returns
identical results as 1st query.
47
ISOLATION LEVELS

READ COMMITTED
Data records retrieved by a query are not prevented
from modification by some other transactions
 Non-repeatable reads may occur



READ UNCOMMITTED

dirty reads are allowed


data retrieved in a SELECT statement may be modified by
some other transaction when it commits
One transaction may see uncommitted changes made by
some other transaction
Default isolation level

varies quite widely
48
ISOLATION LEVELS

The semantics of the four different isolation
levels is defined as follows:
Isolation Level
Write
locks
Read Uncommitted No
Read
locks
No
View
locks
No
Read Committed
Yes
No
No
Repeatable Reads
Yes
Yes
No
Serializable
Yes
Yes
Yes
49
TRANSACTIONS

Requirements for transactions:
Atomicity: “all or nothing”,
 Consistency: transforms consistent DB state into
another consistent DB state,
 Independence: from all other transactions
(serializability),
 Durability: survives any system crashes.


These requirements are called ACID properties
of transactions.
50
SUMMARY




A transaction consists of a sequence of read /
write operations.
The DBMS guarantees the atomicity,
consistency, independence and durability of
transactions.
Serializability guarantees independence of
transactions.
Lower isolation levels can be specified. They may
be acceptable and more efficient in certain
scenarios.
51