Integrity Constraints B Ramamurthy 1312022 B Ramamurthy 1

Integrity Constraints B. Ramamurthy 1/31/2022 B. Ramamurthy 1

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. 1/31/2022 B. Ramamurthy 2

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 >= 4. 0) 1/31/2022 B. Ramamurthy 3

Referential Integrity Let r 1(R 1) and r 2(R 2) be relations with primary key K 1 and K 2 A subset “a” of attributes R 2 is a foreign key in relation r 1, if for every tuple t 2 in r 2, there must be a tuple t 1 in r 1 such that t 1[K 1] = t 2[a] Example: Foreign r 1 Branch-scheme (branch_name, … key r 2 Account-schema (account#, branch_name 1/31/2022 B. Ramamurthy 4

Foreign Key (r 2) K 1 (r 1) Formally expressing the integrity constraint. For referential constraint to make sense must be equal to K 1. Intuitively, consider a query “insert account xyz with abc as branch”. What if “abc” does not exist? 1/31/2022 B. Ramamurthy 5

Referential Integrity in SQL When creating table we specify primary , candidate and foreign keys. These are specifying referential integrity. Example Fig 6. 2 Create table depositor (cname char(20) not null, acct. No char(10) not null, primary key (cname, acct. No), foreign key (cname) references customer, foreign key (acct. No) references account) 1/31/2022 B. Ramamurthy 6

Cascading delete and update Create table depositor (cname char(20) not null, acct. No char(10) not null, primary key (cname, acct. No), foreign key (cname) references customer on delete cascade on update cascade, foreign key (acct. No) references account) 1/31/2022 B. Ramamurthy 7

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 <assertion_name> check predicate 1/31/2022 B. Ramamurthy 8

Assertion : example Create assertion sum_constraint check (not exists (select * from branch where (select sum(amount) from loan where loan. bname = branch. bname) >= (select sum(amount) from account where loan. bname =branch. bname))) 1/31/2022 B. Ramamurthy 9

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 > total balance in a branch involved in loans. 1/31/2022 B. Ramamurthy 10

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: n n Specify conditions under which the trigger is to be executed. Specify actions to be taken when trigger is executed. Triggers are performance deflators. 1/31/2022 B. Ramamurthy 11

Trigger example define trigger overdraft on update of account T (if new T. balance < 0 then (insert into loan values (T. bname , T. acct. No, - new T. balance) insert into borrower (select cname, acct. No from depositor where T. acct. No = depositor. acct. No) update account S set S. balance = 0 where S. acct. No = T. acct. No)) 1/31/2022 B. Ramamurthy 12

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 t 1 and t 2 if t 1[ ] = t 2[ ] then t 1[ ] = t 2[ ] 1/31/2022 B. Ramamurthy 13

Example A a 1 a 2 a 3 B b 1 b 2 b 3 C c 1 c 2 c 2 D d 1 d 2 d 3 d 4 A C but not C A AB D 1/31/2022 B. Ramamurthy 14

Functional dependency in Bank Example Branch-schema Bname branch-city Bname assets Customer-schema Cname customer-city Cname customer-street Loan-Schema loan. No amount loan. No bname Account-schema accnt. No bname acct. No balance Others : none 1/31/2022 B. Ramamurthy 15

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. 1/31/2022 B. Ramamurthy 16