Integrity Constraints

1
Integrity Constraints

• B.Ramamurthy

2
Introduction

• Integrity constraints make sure that changes to
  the database do not result in data inconsistency.
• Key declarations (candidate, primary, foreign)
  are a basic form of integrity constraint.
• A Integrity constraint can be any arbitrary
  predicate pertaining to the database.

3
Domain Constraint

• Domain constraints allows us to test the values
  inserted into the database and to test the
  queries to make sure comparisons made are
  appropriate.
• Examples
• create domain wage numeric (5,2) constraint
  wage-value-test check (value gt 4.0)

4
Referential Integrity

• Let r1(R1) and r2(R2) be relations with primary
  key K1 and K2
• A subset a of attributes R2 is a foreign key in
  relation r1, if for every tuple t2 in r2, there
  must be a tuple t1 in r1 such that t1K1 t2a
• Example
• r1 Branch-scheme (branch_name,
• r2 Account-schema (account, branch_name

Foreign key
5
Foreign Key

• ?? (r2) ? ?K1 (r1)
• Formally expressing the integrity constraint.
• For referential constraint to make sense ? must
  be equal to K1.
• Intuitively, consider a query insert account xyz
  with abc as branch . What if abc does not
  exist?

6
Referential Integrity in SQL

• When creating table we specify primary ,
  candidate and foreign keys. These are specifying
  referential integrity.
• Example Fig6.2
• Create table depositor
• (cname char(20) not null,
• acctNo char(10) not null,
• primary key (cname, acctNo),
• foreign key (cname) references customer,
• foreign key (acctNo) references account)

7
Cascading delete and update

• Create table depositor
• (cname char(20) not null,
• acctNo char(10) not null,
• primary key (cname, acctNo),
• foreign key (cname) references customer
• on delete cascade
• on update cascade,
• foreign key (acctNo) references account)

8
Assertions

• An assertion is a predicate expressing a
  condition that the database should always
  satisfy.
• It is more global in scope than domain and
  referential integrity constraints.
• Format
• Create assertion ltassertion_namegt check predicate

9
Assertion example

• Create assertion sum_constraint check
• (not exists (select from branch
• where (select sum(amount) from loan where
  loan.bname branch.bname)
• gt (select sum(amount) from account where
  loan.bname branch.bname)))

10
Assertion Semantics

• When an assertion is created system tests it for
  validity.
• If the assertion is valid any future
  modifications to the database will be allowed
  only if it does not violate the assertion.
• The example in last slide makes sure total loan
  amount is NOT gt total balance in a branch
  involved in loans.

11
Trigger

• A trigger is a statement that is executed
  automatically by the system as a side effect of
  modification to the database.
• To design a trigger
• Specify conditions under which the trigger is to
  be executed.
• Specify actions to be taken when trigger is
  executed.
• Triggers are performance deflators.

12
Trigger example

• define trigger overdraft on update of account T
• (if new T.balance lt 0
• then (insert into loan values
• (T.bname , T.acctNo, - new T.balance)
• insert into borrower
• (select cname,acctNo
• from depositor
• where T.acctNo depositor.acctNo)
• update account S
• set S.balance 0 where S.acctNo T.acctNo))

13
Functional Dependencies

• Functional dependency is a an extension of the
  notion of key.
• Functional dependencies are constrains on a set
  of legal relations.
• Functional dependency for ? ? R, ? ? R,
• ? ?
• Holds on R if for all pairs of tuples t1 and t2
  if t1? t2? then t1? t2?

14
Example

• A B C D
• a1 b1 c1 d1
• a1 b2 c1 d2
• a2 b2 c2 d2
• a2 b3 c2 d3
• a3 b3 c2 d4
• A ? C but not C ? A
• AB?D

15
Functional dependency in Bank Example

• Branch-schema
• Bname?branch-city
• Bname ? assets
• Customer-schema
• Cname? customer-city
• Cname?customer-street
• Loan-Schema
• loanNo ?amount
• loanNo ?bname
• Account-schema
• accntNo?bname
• acctNo ?balance
• Others none

16
Closure of a set of Functional Dependencies

• Given a set of functional dependencies determine
  all the set of all functional dependencies that
  are implied.
• Consider R (A, B, C, G, H, I) and a set of
  functional dependencies F
• A ? B
• A ? C
• CG ? H
• CG? I
• B ? H
• Determine the closure F denoted by F.