Integrity Constraints

1. Integrity Constraints

2. Integrity Constraints Integrity constraints ensure that changes (update deletion, insertion) made to the database by authorized users do not result in a loss of data consistency. Thus, integrity constraints guard against accidental damage to the database. Database integrity refers to the validity and consistency of stored data. Integrity is usually expressed in terms of constraints, which are consistency rules that the database is not permitted to violate. Constraints may apply to each attribute or they may apply to relationships between tables. For examples: an integrity constraint could state that A student’s grade point average cannot be less than 0.00 or grater than 4.00. A brood group must be ‘A’ or ‘B’ or ‘AB’ or ‘O’ only (can not any other values else). A branch name contained in the table account and loan, representing the branch that the customer opens an account or a loan, must correspond to an existing branch name in the table branch.

3. Integrity Constraints Integrity Constraints can be categorized into 4 types : Domain Constraints Referential Integrity Triggers Functional Dependency

4. Domain Constraints Since every attribute has an associated domain, there are constraints (called domain constraints) in the form of restrictions on the set of values allowed for the attributes of relations. (A requirement that the values of an attribute must come from a specific domain). Domain Constraints are the most elementary form of integrity constraint. They are tested easily by the system whenever a new data item is entered into the database.

6. Referential Integrity Referential Integrityis a constraint to enforce relationship between primary key of one relation and foreign key of the other. Referential Integrity is used to ensure that each value of a foreign key attribute refers to an entity that appears in the foreign table. As with other constraints, any attempt to modify the database contents that would cause a foreign key constraint violation must be disallowed. Relational database systems provide enforcement of referential integrity constraints. The constraint is specified in the database schema, and the database system enforces it. Referential Integrity: Basic Concept

7. Referential Integrity: Basic Concept Consider a pair of relations r and s and the natural join between r and s. There may be a tuple tr in r that does not join with any tuple in s. That is, there is no ts in s such that tr[R  S] = ts[R  S]. Such tuples are called dangling tuples. Depending on the entity set or relationship set being modeled, dangling tuples may or may not be acceptable. Suppose there is a tuple t1 in the account relation with t1[branch_name] = “Ngamvongvan”, but there is no tuple in the branch relation for the “Ngamvongvan” . This situation would be undesirable. We expect the branch relation to list all bank branches. Therefore, tuple t1 would refer to an account at a branch that does not exist. Clearly, we would like to have an integrity constraints that prohibits the occurrence of dangling tuples in database.

8. Referential Integrity constraint can be written as (r2) k(r1) foreign keyofr2must be asubsetofPrimary keyofr1 Note that, for a referential integrity constraint to make sense, either  must be equal to K, or and K must be compatible sets of attributes. The important of this rule is when referencing from one relation to the other, DBMS must ensure that the instances in both relations must exist. (ความสำคัญของกฎข้อนี้คือ เมื่อมีการอ้างอิงจากรีเลชั่นหนึ่งไปยังอีกรีเลชั่นหนึ่งแล้ว ระบบการจัดการฐานข้อมูลต้องรับประกันว่าข้อมูลในรีเลชั่นทั้ง 2 จะต้องมีตัวตนเสมอ)

11. Trigger A trigger is a statement that the system executes automatically as a side effect of a modification to the database. Trigger is rule managed by a DBMS. Because a trigger involves an event, a condition, and a sequence of actions, it also is known as an event-condition-action rule. Writing the action part or trigger body is similar to writing a procedure or a function except that a trigger has no parameters. Triggers are executed by the rule system of the DBMS not by explicit calls as for procedures and functions. Trigger is a type of stored procedure which would be alarmed (fired) every time instance of database is modified (inserted, updated, or deleted). Trigger is used to prevent a modification of unreasonable values to a database. SQL-based database systems use triggers widely. Unfortunately, each DBMS implemented its own syntax for triggers, leading to incompatibilities.

12. Motivation and Classification of Triggers Triggers are widely implemented in DBMSs because they have a variety of uses in business applications. The typical uses of triggers :- • Complex integrity constraints: Integrity constraints that cannot be declaratively specified by check constraints in CREATE TABLE statements. • Transition constraints: Integrity constraints that compare the values before and after an update occurs. • Update propagation: Update derived columns in related tables. • Exception reporting: Create a record of unusual conditions as an alternative to rejecting a transaction.

13. Triggers, which are part of SQL:1999, classifies triggers by granularity, timing, and applicable event. For granularity, a trigger can involve each row affected by an SQL statement or an entire SQL statement. Row triggers are more common than statement triggers. For timing, a trigger can fire before or after an event. Typically, triggers for constraint checking fire before an event, while triggers updating related tables and performing other actions fire after an event. For applicable event, a trigger can apply to INSERT, UPDATE, and DELETE statements.

14. A trigger can be created to maintain database consistency in term of Data Integrity , Referential Integrity and Encapsulate Business Rules. To design a trigger mechanism, we must meet 2 requirements: 1. Specify when a trigger is to be executed. This is broken up into an event that causes the trigger to be checked and a condition that must be satisfied for trigger execution to proceed. 2. Specify the actions to be taken when the trigger executes. Once we enter a trigger into the database, the database system takes on the responsibility of executing it whenever the specified event occurs and the corresponding condition is satisfied.

15. Understanding of transition table. Inserted and deleted are transition table. While a trigger is being executed, special and temporary tables, Inserted and Deleted, are automatically created in main memory. These 2 tables are existed within an execution time of a trigger. When a trigger complete its execution, the tables are destroyed. When first being created, schema (name, number and data type of attribute) of a transition table is similar to a table in which a trigger reside. An event involved with database modification causes an insertion of a record into this special table as following: INSERT : When a new record is inserted into a table in which a trigger reside, DBMS will insert this record into INSERTED table simultaneously. DELETE: When an existing record is deleted from a table in which a trigger reside, DBMS will insert this record into DELETED table simultaneously. UPDATE: When an update occurs in a table in which a trigger reside, DBMS will perform 2 following steps: storing the before-updated record into DELETED table and storing modified record into INSERTED table. These transition tables used to store data items due to insertion, deletion or update enable trigger to detect consistency as required.

16. CREATE TRIGGER borrow_delete on borrow for delete as update book set avail = 'Y' where bookID in (select bookID from deleted); Create Trigger Book_Update On book For update As If update (BookID) Begin declare @newID char(5) select @newID = i.bookID from inserted as i Update borrow Set BookID = @newID where bookID in (Select BookID from deleted)) CREATE TRIGGER borrow_insert on borrow for insert as update book set avail = 'N' where BookID in (Select BookID from inserted);

17. Example of Need for Triggers • Triggers are useful mechanisms for alerting humans or for starting certain tasks automatically when certain conditions are met. As an illustration, suppose that, instead of allowing negative account balances, the bank deals with overdrafts by setting the account balance to zero, and creating a loan in the amount of the overdraft. The bank gives this loan a loan number identical to the account number of the overdrawn account. For this example, the condition for executing the trigger is an update to the account relation that results in a negative balance value. Suppose that Jones’ withdrawal of some money from an account made the account balance negative. Let t denote the account tuple with a negative balance value. The actions to be taken are: • Insert a new tuple s in the loan relation with • s [loan_number] = t [account_number] • s [branch_name] = t [branch_name] • s [amount] = t [balance] • (Note that, since t [balance] is negative, we negate t [balance] to get the loan amount – a positive number. • Insert a new tuple u in the borrower relation with • u [customer_name] = “Jones” • u [loan_number] = t [account_number] • Set t [balance] to 0.

19. Example of a trigger that automate an application according to general business logic. //create trigger overdraft_trigger after update of balance on account Create trigger overdraft_trigger after update on account Referencing new row as nrow For each row When nrow.balance < 0 Begin atomic insert into borrower (select customer_name, account_number from depositor where nrow.account_number = depositor.account_number) insert into loan values (nrow.account_number, nrow.branch_name, -nrow.balance) update account set balance = 0 where account.account_number = nrow.account_number end

20. This trigger definition specifies that the trigger is initiated after any update of the relation account is executed. An SQL update statement could update multiple tuples of the relation, and the for each row clause in the trigger code would then explicitly iterate over each updated row. The referencing new row as clause creates a variable nrow (called a transition table variable), which stores the value of an updated row after the update. The when statement specifies a condition, namely nrow.balance < 0. The system executes the rest of the trigger body only for tuples that sattisfy the condition. The begin atomic… end clause serves to collect multiple SQL statements into a single compound statement. The two insert statements with the begin..end structure carry out the specific tasks of creating new tuples in the borrower and loan relations to represent the new loan. The update statement serves to set the account balance back to 0 from its earlier negative value.

21. As another example of the use of triggers, suppose a warehouse wishes to maintain a minimum inventory of each item; when the inventory level of an item falls below the minimum level, an order should be placed automatically. This is how the business rule can implemented by triggers: On an update of the inventory level of an item, the trigger should compare the level with the minimum inventory level for the item, and if the level is at or below the minimum, a new order is added to an orders relation.

22. CREATE TRIGGER tr_InsUpdtmpSales On tmp_sales for insert, update as declare @quantity int select @quantity = i.qty from inserted as i if @quantity < 20 begin print 'No record to insert or update' print 'Because quantity must more than 20' rollback transaction end

23. CREATE TRIGGER tr_InsUpdtmpSales2 On tmp_sales for update as if update(qty) declare @quantity int select @quantity = i.qty from inserted as i if @quantity < 20 begin print 'No record to insert or update' print 'Because quantity must more than 20' ROLLBACK TRANSACTION end else print 'Triggers not done because qty column is not updated'

24. Performing of delete cascading by Trigger We can utilize a trigger to perform delete cascading. That is a deletion of a tuple in a major table makes the tuple of another table whose foreign key is a subset of the primary of that major table to be deleted accordingly. CREATE TRIGGER trbookdel on book for delete as delete borrow from deleted as d where borrow.bookID = d.bookID Create TRIGGER tr_DelBorrowDelete On borrow for delete as declare @rowcount int raiserror('%d rows are going to be deleted from table borrow',0,1,@rowcount)

25. Types of Trigger FOR Trigger or AFTER Triggeris a trigger which fires after transac-SQL instruction already modified (insert, update, or delete) database instance. The execution steps of after trigger : • Transac-SQL (insert, update or delete) performs on a specific relation. • DBMS checks the relation’s constraints specified in the trigger. • DBMS creates INSERTED or DELETED as required. • Start the execution of FOR or AFTER Trigger. Example: Create trigger overdraft-trigger after update of balance on account relation: The update statement will adjust an account balance from negative value to 0.

26. BEFORE Triggers : Such triggers can serve as extra constraints that can prevent invalid updates. For instance, if we wish not to permit overdrafts, we can create a before trigger that rolls back the transaction if the new balance is negative. As another example, suppose the value in a phone number field of an inserted tuple is blank, which indicates absence of a phone number. We can define a trigger that replaces the value by the 02-942-8555. The set statement can be used to carry out such modifications. createtrigger setnew_trigger before update on customer referencing new row as nrow for each row when nrow.phone_number = ' ' set nrow.phone_number = '02-942-8555'

27. CASE STUDY