Database Systems I SQL Constraints and Triggers Ensuring business - - PDF document

Database Systems I SQL Constraints and Triggers

Ensuring business rules are met.

2

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.

3 3 3

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.

4

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.

5

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 modificationsare 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

6 6 6

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 gender-

domain

7

ATTRIBUTE-BASED CHECK

 Attribute-based CHECK constraints are most

• ften 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);

8

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) );

9 9 9

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

10 10 10

TUPLE-BASED CHECK

 CHECK constraint that refers to other table: CREATE TABLE Reserves ( sname CHAR(10), bid INTEGER, 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

• ther tables.

Interlake boats cannot be reserved

11 11 11

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.

12 12 12

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.

13 13 13

ASSERTIONS

 Assertions are constraints over a table as a whole

• r 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.

14 14 14

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.

15 15 15

ASSERTIONS

 Number of boats plus number of sailorsis < 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 reservationsper sailor is < 10.

CREATE ASSERTION notTooManyReservations CHECK ( 10 > ALL (SELECT COUNT (*) FROM Reserves GROUP BY sid) );

16 16 16

EXAMPLE ASSERTION

 CREATE ASSERTION RichMGR CHECK

(NOT EXISTS (SELECT * FROM dept, emp WHERE emp.name = dept.mgrname AND

emp.salary < 50000))

This assertion correctlyguarantees 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.

17 17 17

Type Where Declared When activated Guaranteed to hold? Attribute with attribute

• n insertion

not if contains CHECK

• r update

subquery Tuple relation schema insertion or not if contains CHECK update to subquery relation Assertion database schema

• n change to

Yes any relation mentioned

DIFFERENT CONSTRAINT TYPES

18 18 18

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.

TRIGGERS

20 20 20

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.

21 21 21

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).

22 22 22

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.

23 23 23

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. 24 24 24

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

25 25 25

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). 26 26 26

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

• nly only specified event.

 Trigger execution cannot be optimized by DBMS.  Triggers have more applicationsthan constraints.  monitor integrity constraints,  construct a log,  gather database statistics, etc.

27 27 27

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.

Database Systems I SQL Stored Procedures

29 29 29

EXAMPLE PROCEDURE

30 30 30

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

31 31 31

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

32 32 32

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”

33 33 33

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);

Database Systems I SQL Transactions

Ensuring databaseconsistency one query at a time.

35 35 35

TRANSACTIONS



So far, we have implicitly assumed that there is

• nly 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 orderingthe 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.

36 36 36

SERIALIZABILITY



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:



In the end, who gets seat 22A?

T1: find empty book seat 22A seat 22A, T2: find empty book seat 22A seat 22A, time

37 37 37

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

• ccupied and would have to find another seat.

38 38 38

ATOMICITY



So far, we have also assumed that all SQL statements are executed correctly.



In reality, various types of system errors can

• ccur 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.

39 39 39

ATOMICITY



Consider a bank transaction T:



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

• f its SQL statements are executed.

T: A=A+100, B=B-100

time

40 40 40

TRANSACTIONS



A user’s program may carry out many operations

• n 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 T: R(A), W(A), R(B), W(B) User’s view System’s view

time

41 41 41

SERIALIZABILITY



Serial schedule: Schedule that does not interleavethe 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.

42 42 42

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.

43 43 43

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;

• r ROLLBACK;



COMMIT makes all modifications of the transaction permanent, ROLLBACK undoes all DB modifications made.

44 44 44

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

• f the DBS can be maximized.



To specify the next transaction as read-only:

SET TRANSACTION READ ONLY;

45 45 45

DIRTY READS



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 non- serializable schedule results.



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. T1: R(A), W(A), R(B), W(B), Rollback T2: R(A), W(A), Commit

46 46 46

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

47 47 47

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.

48 48 48

ISOLATION LEVELS

 READ COMMITTED  Data records retrieved by a query are not prevented

from modification by some other transactions

 Non-repeatable reads may occur

 data retrieved in a SELECT statement may be modified by

some other transaction when it commits  READ UNCOMMITTED  dirty reads are allowed

 One transaction may see uncommitted changes made by

some other transaction  Default isolation level  varies quite widely

49 49 49

ISOLATION LEVELS



The semantics of the four different isolation levels is defined as follows: Yes Yes Yes Serializable No Yes Yes RepeatableReads No No Yes Read Committed No No No Read Uncommitted View locks Read locks Write locks Isolation Level

50 50 50

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

• f transactions.

51 51 51

SUMMARY



A transaction consists of a sequence of read / write operations.



The DBMS guarantees the atomicity, consistency, independence and durability of transactions.



Serializability guaranteesindependence of transactions.



Lower isolation levels can be specified. They may be acceptable and more efficient in certain scenarios.