Database Systems I SQL Constraints and Triggers Ensuring business rules are met.

1. 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. Not-Null Constraints • The ICNOT NULLdisallows 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 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

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

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. Tuple-based CHECK • Tuple Based CHECK contraint: CREATE TABLE Emp ( name CHAR(30) UNIQUE gender CHAR(1) CHECK (gender in (‘F’, ‘M’) age int dnoint CHECK (age < 100 AND age > 20) CHECK (dno IN (SELECT dno FROM dept)) ) these are checked on insertion to relation or tuple update

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 other tables. Interlake boats cannot be reserved

11. Tuple-based CHECK CREATE TABLE dept ( mgrname CHAR(30) dnoint 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. 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. 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.

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

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

17. Different Constraint Types Type Where Declared When activated Guaranteed to hold? Attribute with attribute on insertion not if contains CHECK or update subquery Tuple relation schema insertion or not if contains CHECK update to subquery relation Assertion database schema on change to Yes any relation mentioned

18. Altering Constraints ALTER TABLE Product DROP CONSTRAINTpositivePrice ALTER TABLE Product ADD CONSTRAINT positivePrice CHECK (price >= 0) ALTER DOMAIN ssnADD CONSTRAINT no-leading-1s CHECK (value >= 200000000) DROP ASSERTION assert1.

19. Triggers

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. 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. 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. Triggers CREATE TRIGGERyoungSailorUpdate AFTER INSERT ON SAILORS /* Event */ REFERENCING NEW TABLENewSailors 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. Example: Row Level Trigger CREATE TRIGGERNoLowerPrices 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. Triggers CREATE TRIGGERnotTooManyReservations AFTER INSERT ON Reserves /* Event */ REFERENCING NEW ROWNewReservation 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. 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.

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.

28. Database Systems I SQL Stored Procedures

29. Example Procedure

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. Stored Procedures: Examples CREATE PROCEDURE ShowNumReservationsSELECT S.sid, S.sname, COUNT(*)FROM Sailors S, Reserves RWHERE S.sid = R.sidGROUP 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 + increaseWHERE sid = sailor_sid

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

34. Database Systems I SQL Transactions Ensuring database consistency one query at a time.

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

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

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

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 of its SQL statements are executed. T: A=A+100, B=B-100 time

40. 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 transactionis 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 System’s view T: R(A), W(A), R(B), W(B) time

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

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

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 of the DBS can be maximized. • To specify the next transaction as read-only: SET TRANSACTION READ ONLY;

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. 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. 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. 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. Isolation Level Write locks Readlocks View locks Read Uncommitted No No No Read Committed Yes No No Repeatable Reads Yes Yes No Serializable Yes Yes Yes Isolation Levels • The semantics of the four different isolation levels is defined as follows:

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 of transactions.