Integrity Constraints - PowerPoint PPT Presentation

1 / 16
About This Presentation
Title:

Integrity Constraints

Description:

Integrity Constraints B.Ramamurthy – PowerPoint PPT presentation

Number of Views:99
Avg rating:3.0/5.0
Slides: 17
Provided by: cseBuffal7
Learn more at: https://cse.buffalo.edu
Category:

less

Transcript and Presenter's Notes

Title: 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.
Write a Comment
User Comments (0)
About PowerShow.com