Chapter 6: Integrity Constraints. Domain Constraints Referential Integrity Assertions Triggers Functional Dependencies. Domain Constraints.
Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.
create domain hourly-wage numeric(5,2)constraintvalue-test check(value > = 4.00)
(r2) K (r1)
t2  K (r1)
= t1[K} (r2)
If this set is not empty, either the delete command is rejected as an error, or the tuples that reference t1 must themselves be deleted (cascading deletions are possible).
= t1[K} (r2)
using the old value of t1 (the value before the update is applied). If this set is not empty, the update may be rejected as an error, or the update may be cascaded to the tuples in the set, or the tuples in the set may be deleted.
create table customer(customer-namechar(20) not null,customer-streetchar(30),customer-citychar(30),primary key (customer-name))
create table branch(branch-namechar(15) not null,branch-citychar(30),assetsinteger,primary key(branch-name))
create table account(branch-namechar(15),account-numberchar(10) not null,balanceinteger,primary key (account-number), foreign key (branch-name) references branch)
create table depositor(customer-namechar(20) not null,account-numberchar(10) not null,primary key (customer-name, account-number),foreign key (account-number) references account,foreign key (customer-name) references customer)
create table account
. . .foreign key(branch-name) references branchon delete cascadeon update cascade.. . . )
create assertion <assertion-name> check <predicate>
create assertion sum-constraint check(not exists (select * from branchwhere (select sum(amount) from loanwhere loan branch-name = branch branch-name) >=(select sum(amount) from accountwhere loan branch-name = branch branch-name)))
create assertion balance-constraint check(not exists (select * from loanwhere not exists ( select *from borrower, depositor, accountwhere loan loan-number = borrower loan-numberand borrower customer-name = depositor customer-nameand depositor account-number = account.account-numberand account balance >= 1000)))
define trigger overdrafton update of account T(if new T.balance < 0then (insert into loan values(T.branch-name, T.account-number, –new T.balance)insert into borrower(select customer-name, account-numberfrom depositorwhere T.account-number - depositor account-number)update account.Sset S.balance = 0where S.account-number = T.Account-number))
The keyword new used before T.balance indicates that the value of T.balance after the update should be used; if it is omitted, the value before the update is used.
holds on R if and only if for any legal relations r(R), whenever any two tuples t1and t2 of r agree on the attributes , they also agree on the attributes . That is,
t1 = t2  t1 = t2 
Loan-info-schema = (branch-name, loan-number,customer-name, amount).
We expect this set of functional dependencies to hold:
loan-number amountloan-number branch-name
but would not expect the following to hold:
These rules are sound and complete.
The above rules can be inferred from Armstrong’s axioms.
is in F+ +
result := ;while (changes to result) dofor each in F dobeginif result then result := result ;end
1.result = AG
2.result = ABCG(A C and A AGB)
3.result = ABCGH(CG H and CG AGBC)
4.result = ABCGHI(CG I and CG AGBCH)
2.does A+ R?
3.does G+ R?
A BB C