Transcript Integrity Constraints

Integrity Constraints
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.
Integrity Constraints
Integrity Constraints can be categorized into 4 types :
Domain Constraints
Referential Integrity
Triggers
Functional Dependency
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.
Referential Integrity
Referential Integrity is 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
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.
Referential Integrity constraint can be written as
 (r2)

k (r1)
foreign key of r2 must be a subset of Primary key of r1
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 จะต้องมีตวั ตนเสมอ)
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 eventcondition-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.
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.
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.
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.
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.
BookID
BookName
avail
B01
Finance
Y
B02
Accounting
Y
B03
Database
Y
B04
Data Communication
Y
B05
Automata
Y
B06
Stock and Bond
Y
B07
Image Processing
Y
B08
Embedded System
Y
B09
Architecture
Y
B10
Data Structure
y
CREATE TRIGGER borrow_insert
on borrow
for insert
as
update book
set avail = 'N'
where BookID in
(Select BookID from inserted);
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))
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.
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
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.
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.
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
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'
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)
Types of Trigger
FOR Trigger or AFTER Trigger is a trigger which fires after transac-SQL
instruction already modified (insert, update, or delete) database instance. The
execution steps of after trigger :
1.
2.
3.
4.
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.
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.
create trigger 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'
CASE STUDY